Do Until, For Each, EOF - SQL in ASP.NET (VB)

I want to query an SQL table for all instances of a particular field. (i.e. SELECT COUNT(*) FROM myTable where ID = '187')

Then I want to cycle through each record and do something based on the data. (For Each, EOF/BOF, etc.)

What is the best way to go about doing this in ASP.NET(VB)?
Mike MillerSoftware EngineerAsked:
Who is Participating?
 
AanvikConnect With a Mentor Commented:

    Dim strSQL As String = "SELECT * FROM myTable where ID = '187'"
    Dim Connection As New OleDbConnection("PROVIDER=....")
    Dim DA As New OleDbDataAdapter(strSQL, Connection)
    Dim DS As New DataSet
 
    DA.Fill(DS, "Codes")
 
    Dim dt As New DataTable = DS.Tables(0)
    Dim dr As DataRow
    For Each dr In dt.Rows()
       'Do whatever you want to do with each record.
    Next

Open in new window

0
 
Mike MillerSoftware EngineerAuthor Commented:
Ok that looks good. So how would i reference what is in the columns?

Like

Label1.text = ??????? for whatever is in the 3rd column
0
 
urir10Commented:
Label1.text = dt(0)   for the first column
or Label1.text = dt("name of column")
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Mike MillerSoftware EngineerAuthor Commented:
I'm getting "End of statement expected" on:
Dim dt As New DataTable = DS.Tables(0)

and

Name dt is not declared on:
 For Each dr In dt.Rows()
        Dim strSQLlsd As String = "SELECT * FROM tblMultiTours where ID like '" & Label1.Text & "'"
        Dim Connection As New Data.OleDb.OleDbConnection("Data Source=ussrm-netsql\netdata;" & _
      "Initial Catalog=Employee;" & _
      "Integrated Security=SSPI")
        Dim DA As New Data.OleDb.OleDbDataAdapter(strSQLlsd, Connection)
        Dim DS As New Data.DataSet
        DA.Fill(DS, "Codes")
        Dim dt As New DataTable = DS.Tables(0)
        Dim dr As Data.DataRow
        For Each dr In dt.Rows()
            
        Next

Open in new window

0
 
AanvikCommented:
Change DA.Fill(DS, "Codes") to  DA.Fill(DS)
0
 
urir10Commented:
Sorry i meant dr not dt
0
 
Mike MillerSoftware EngineerAuthor Commented:
DA.Fill(DS)

gives me the same 2 errors
0
 
urir10Commented:
Do you have the right Namespaces included?

Imports System.Data
Imports System.Data.OleDb
0
 
Mike MillerSoftware EngineerAuthor Commented:
Yes
0
 
urir10Connect With a Mentor Commented:
try this instead:
            Dim dt As New DataTable
            dt = DS.Tables(0)
0
 
Mike MillerSoftware EngineerAuthor Commented:
Ok yeah that worked. I had it as data.datatable without the namespace and tried that before. Once i added the namespace and just did datatable it worked, thanks guys
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.