Read values from DB into Var

bloukewer

Well-known member
Joined
Jul 2, 2004
Messages
88
Programming Experience
3-5
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

Well-known member
Joined
Jun 3, 2004
Messages
187
Location
Other side of the rock
Programming Experience
1-3
VB.NET:
  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

Well-known member
Joined
Jun 7, 2004
Messages
313
Location
Wales, UK
Programming Experience
3-5
This is how I do it using OleDb I.E. MS Access

VB.NET:
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

Well-known member
Joined
Jul 2, 2004
Messages
88
Programming Experience
3-5
Thanx!

You guyz maka me vewy happy!

Great. Thnx a million.
 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
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:

VB.NET:
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

VB.NET Forum Moderator
Staff member
Joined
Jun 3, 2004
Messages
4,524
Location
Lansing, MI; USA
Programming Experience
10+
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

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
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).
 
Top Bottom