Solved

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

Posted on 2009-05-12
11
682 Views
Last Modified: 2013-11-26
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)?
0
Comment
Question by:mwmiller78
  • 5
  • 4
  • 2
11 Comments
 
LVL 5

Accepted Solution

by:
Aanvik earned 250 total points
Comment Utility

    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
 

Author Comment

by:mwmiller78
Comment Utility
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
 
LVL 7

Expert Comment

by:urir10
Comment Utility
Label1.text = dt(0)   for the first column
or Label1.text = dt("name of column")
0
 

Author Comment

by:mwmiller78
Comment Utility
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
 
LVL 5

Expert Comment

by:Aanvik
Comment Utility
Change DA.Fill(DS, "Codes") to  DA.Fill(DS)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 7

Expert Comment

by:urir10
Comment Utility
Sorry i meant dr not dt
0
 

Author Comment

by:mwmiller78
Comment Utility
DA.Fill(DS)

gives me the same 2 errors
0
 
LVL 7

Expert Comment

by:urir10
Comment Utility
Do you have the right Namespaces included?

Imports System.Data
Imports System.Data.OleDb
0
 

Author Comment

by:mwmiller78
Comment Utility
Yes
0
 
LVL 7

Assisted Solution

by:urir10
urir10 earned 250 total points
Comment Utility
try this instead:
            Dim dt As New DataTable
            dt = DS.Tables(0)
0
 

Author Comment

by:mwmiller78
Comment Utility
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now