This creates a DataSet; you can also use a DataTable.
---------------------------------
Dim ds As DataSet
'open the connection
Using cnn As New SqlConnection(My.Settings.PTConnectionString)
cnn.Open()
'define the command
Dim cmd As New SqlCommand
cmd.Connection = cnn
cmd.CommandText = "SELECT * FROM Product"
'define the data adapter and fill the data table
Dim da As New SqlDataAdapter(cmd)
ds = New DataSet
da.Fill(ds, "Product")
End Using
For Each dr As DataRow In ds.Tables("Product").Rows
Dim ProductID As Integer = CType(dr.Item("ProductID"), Integer)
Dim ProductName As String = dr.Item("ProductName").ToString
Dim ProductNumber As String = dr.Item("ProductNumber").ToString
'Dim Price As Nullable(Of Decimal)
'If .Item("Price") IsNot DBNull.Value Then
' Price = CType(.Item("Price"), Decimal)
'End If
Dim Description As String = dr.Item("Description").ToString
'Dim ProductType As Integer = CType(.Item("ProductType"), Integer)
'Dim StockType As String = .Item("StockType").ToString
Console.WriteLine(String.Format("ProductID {0}, " & _
"ProductName {1}, {2}ProductNumber {3}, " & _
"Description {4}", ProductID, ProductName, _
ControlChars.CrLf, ProductNumber, Description))
Next
-------------------------------
You can also load a dataset with multiple tables.
To do this, your sql would have multiple sql statements
in yhour commandtext:
SELECT * FROM PRODUCT; SELECT * FROM CUSTOMERS
and then you name the tables like this:
ds.Tables(0).TableName = "Product"
ds.Tables(1).TableName = "Customers"
Robin S.
---------------------------------------
"SmartbizAustralia" <to*@smartbiz.com.auwrote in message
news:11********************@j44g2000cwa.googlegrou ps.com...
Hi,
This seems to be a neglected bit of info as everyone gets carried away
with data binding examples instead.
Can simply use the datareader as below:
Private Sub PopulateControls1()
Dim sSql As String
Dim cn As SqlConnection
cn = New SqlConnection(m_sConnection)
cn.Open()
sSql = "Select top 10 * from Person.Contact"
Dim sqlCmd As New SqlCommand(sSql, cn)
Dim r As SqlDataReader = sqlCmd.ExecuteReader()
'Get the first line
r.Read()
Me.Label1.Text = "First Name"
Me.TextBox1.Text = r.Item("FirstName")
'Now get the new line
r.Read()
Me.Label2.Text = "First Name"
Me.TextBox2.Text = r.Item("FirstName")
cn.Close()
End Sub
but would love to do the same with a dataset instead.
But how do you walk through each row and select particular columns in a
dataset?
