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?

In the DataTable that is inside the DataSet, the rows are just an array of
DataRow and all the columns are available in each 'DataRow'.

Something like this should put you on the right track:

  'Given as Dataset _ds having a single Datable

  Dim _dr As DataRow = _ds.Tables(0).Rows(0)
  Me.Label1.Text = "First Name"
  Me.TextBox1.Text = _dr("FirstName").ToString()

  _dr = _ds.Tables(0).Rows(1)
  Me.Label2.Text = "First Name"
  Me.TextBox2.Text = _dr("FirstName").ToString()

or, instead:

  Me.Label1.Text = "First Name"

  Me.TextBox1.Text = _ds.Tables(0).Rows(0)("FirstName").ToString()

  Me.Label2.Text = "First Name"

  Me.TextBox2.Text = _ds.Tables(0).Rows(1)("FirstName").ToString()


Show quoteHide quote

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.
---------------------------------------
Show quoteHide quote

How do you translate your WinForms app? (outsourcing localization)
How to recognize a Command Line parameter?
Attaching multiple files of any type to a mail message?
Need pre-built report administration application
Its Urgent: I have export database into excel file if office not installed then it gives me error
Adding Dropdownlist to GridView
Where is the VS mru list file stored?
how sql xml query to get empty element tag
Create a Deployment Package!
How to update .NET Development Environment to import a new namespa