Read values from DB into Var

[bloukewer]

Hi. Me again. I know I've been posting a lot, but I have been working hard!

Can anyone tell me how to read a single value from your database into a string variable?

Again, a simple example would suffice.

 

[mzim]

  dim cn as new SqlConnection("integrated security=true;initial catalog=northwind;data source=mzim")
 cn.open
  dim da as new SqlAdapter("select * from sampletbl",cn)
  dim dt as new datatable()
  dim dr as datarow
  da.fill(dt)
  dim s as string = dr(1)

im using MSsql 2000

hope this helps...

 

[levyuk]

This is how I do it using OleDb I.E. MS Access

Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=E:\University\2nd year - resit\Multimedia\assaingment2\northwind.mdb")
Dim strSQL As String
strSQL = "SELECT * FROM Employees Where Lastname LIKE'" & TextBox1.Text & "%'"
Dim cmd As OleDbCommand = New OleDbCommand(strSQL, cnn)
cnn.Open()
'build the dataReader
Dim reader As OleDbDataReader = cmd.ExecuteReader
'read the first row
If reader.Read() Then
'set the textBoxs with appropriate data
TextBox1.text = reader.GetString(1)
Me.TextBoxFirstName.Text = reader.GetString(2)
Me.TextBoxPosition.Text = reader.GetString(3)
TextBoxCurtsey.text = reader.GetString(4)
TextBoxBirth.text = reader.GetValue(5)
reader.Close()
cnn.Close()

 

[bloukewer]

Thanx!

You guyz maka me vewy happy!

Great. Thnx a million.

 

[mzim]

same as we that can help..


good luck!

 

[Paszt]

Both examples posted so far are perfectly valid to do what you want. However the most effecient method would be to use the ExecuteScalar of a Command object. This method only returns the first column of the first row in the resultset returned by the query and is therefore quicker because less data is transmitted.

Here's an example:

Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection( _
  "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; " & _
  "Data Source=E:\University\2nd year - resit\Multimedia\assaingment2\northwind.mdb")
Dim strSQL As String
strSQL = "SELECT Name FROM Employees Where Lastname LIKE'" & TextBox1.Text & "%'"
Dim cmd As OleDbCommand = New OleDbCommand(strSQL, cnn)
Dim strName As String
cnn.Open()
strName = cmd.ExecuteScalar
cnn.Close()

 

[JuggaloBrotha]

I think it'd be nice if you could assign database values to a variable like this:

Variablename = dataadapter.table.field

like

custname as string

custname=dacustomers.customerinfo.name.tostring

which would be the currently selected record... that'd be helly easier

 

[Paszt]

A dataAdapter "Represents a set of data commands and a database connection that are used to fill the System.Data.DataSet and update the data source". It doesn't actually contain the data.

If you use a dataAdapter (or any other method) to fill a dataSet, you can reference a particular field in a particular record, similar to the method proposed. But that way, you would have to transmit the entire dataSource (or at least a whole record).