Here are two ways to read data from a database and iterate through the results. The first way will use a Reader object (from the SQLClient class which will read a SQL Server database) and loop through the results using the .Read command of the Reader. The second way will again use the SQLClient class, but this time we will load the data into a DataTable and then loop through each row of the table.

Using a DataReader

CODE

    Dim MyDataReader As System.Data.SQLClient.SqlDataReader
        Dim MyCommand As System.Data.SQLClient.SqlCommand
        Dim MyConnection As System.Data.SQLClient.SqlConnection

    MyConnection = New System.Data.SQLClient.SqlConnection("server=127.0.0.1; initial catalog=Northwind;uid=USERNAME;pwd=PASSWORD")
        MyConnection.Open()
        MyCommand = New System.Data.SQLClient.SqlCommand
        MyCommand.Connection = MyConnection
        MyCommand.CommandText = "SELECT * FROM products"
        MyDataReader = MyCommand.ExecuteReader()

    While MyDataReader.Read
        Response.Write("Name: " & MyDataReader.Item("ProductName") & "<br>")
        End While

    MyDataReader = Nothing
    MyCommand = Nothing
    MyConnection.Close()
    MyConnection = Nothing

Using a DataTable

CODE

    Dim MyConnection As System.Data.SQLClient.SqlConnection
    Dim MyDataAdapter As SQLClient.SQLDataAdapter
    Dim MyDataTable As New DataTable
    Dim MyDataRow As DataRow
    Dim strSQL As String = "SELECT FIELD1, FIELD2 FROM MYTABLE"

    MyConnection = New System.Data.SQLClient.SqlConnection("server=127.0.0.1; initial catalog=MyDatabase;uid=USERNAME;pwd=PASSWORD")
    MyConnection.Open()
    MyDataAdapter = New SQLClient.SQLDataAdapter(strSQL, MyConnection)
    MyDataAdapter.Fill(MyDataTable)

    ' Loop through DataTable
    For Each MyDataRow In MyDataTable.Rows
            Response.Write(MyDataRow.Item(0))
    Next

Back to Microsoft: ASP.NET FAQ Index
Back to Microsoft: ASP.NET Forum

My Archive

My FAQ Archive

Come Join Us!

Posting Guidelines

Jobs

Microsoft: ASP.NET FAQ

Database

CODE

CODE

My Archive

Join Tek-Tips® Today!

Join Tek-Tips^® Today!