• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9477
  • Last Modified:

VB.Net How to reference rs.EOF , Move First, Move Last, Move Next like in ACCESS

Converting a Access project over to VB.Net ..

This is their code in Access

       strPayoutACC = "SELECT statement"
        Set rsInvoice = db.OpenRecordset(strPayoutACC, dbOpenDynaset, dbSeeChanges)
        If rsInvoice.RecordCount > 0 Then
            Do Until rsInvoice.EOF
 -------------------------------------------------------------------------    
This is mine converted to Vb.Net

            strPayOutACC = "SELECT Statement"
            da4 = New SqlDataAdapter(strPayOutACC, con)
            da4.Fill(ds4, "PayOutAcc")
            lnCount = ds4.Tables(0).Rows.Count

If lnCount > 0 Then

How do you say do something until the end of the file in VB.Net??? Also how would move it to the first record in dataset..

Any help or insight would be much appreciated...
0
nomar2
Asked:
nomar2
1 Solution
 
AaronroachCommented:
for each row as datarow in ds4.tables.rows
'do something
Next
0
 
strickddCommented:
Try the following, it is the .Net way of doing things
Dim connection As SqlConnection = new SqlConnection(myConnectionString)
Dim command As SqlCommand = new SqlCommand("SELECT STATEMENT", connection)
Dim reader As SqlDataReader
 
connection.Open()
reader = command.ExecuteReader()
 
while(reader.Read)
{
}
 
connection.Close()

Open in new window

0
 
Craig WagnerSoftware ArchitectCommented:
An ADO.NET DataSet/DataTable is fundamentally different than an ADO RecordSet.

If you want to loop over the rows in the DataTable, use a For Each loop:

        For Each row As DataRow In ds4.Tables(0).Rows
           ' get values from the row using row(columnIndex) or row("columnName")
        Next

You don't need to 'move' to the first row. If you want to reference the first row, just use ds4.Tables(0).Rows(0).
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Bob HoffmanDeveloperCommented:
Use a DataReader nto an Adapter
Dim strFieldName as string = ""
 
Dim oCmd As New SqlCommand("SELECT Statement", con)
Dim oDr As SqlDataReader = oCmd.ExecuteReader
While oDr.Read()
  strFieldName = oDr("FieldName") 
End While
oDr.Close()

Open in new window

0
 
nomar2Author Commented:
The data reader seems to be what I was looking for...is there any bulit functionality to get the totals records as I refernced earlier when using the dataset
0
 
Bob HoffmanDeveloperCommented:
The dataReader doesn't have a count. If you need the count on first read you could add it as a sub-select in your SQL statement. If that's not feesible you may need to user your dataAdaptor and load the records into a DataSet or Table and get your count there.
0
 
nomar2Author Commented:
Within this code I have inner blocks of code based on the variable coming from the outer blocks of code...can I have multiple datareaders open at once...I have the following...

            Dim oCmd As New SqlCommand(strPayOutACC, con)
            Dim oDr As SqlDataReader = oCmd.ExecuteReader
            While oDr.Read()
                lnStatusID = oDr("status")

                If (lnStatusID = 3) Or (lnStatusID = 5) Then
                    lInvoice = oDr("invoicenumber")
                    'write the header
                    strPayOut = "SELECT statement"

                    Dim oCmd1 As New SqlCommand(strPayOut, con)
                    Dim oDr1 As SqlDataReader = oCmd1.ExecuteReader
                    While oDr1.Read()
                        a = a + 1
                    End While

.but I get ..

"There is already an open DataReader associated with this Command which must be closed first."

Can I have multiple datareaders open??
0
 
Bob HoffmanDeveloperCommented:
No you cant, You need a seperate connection object for each dataReader.
0
 
Bob HoffmanDeveloperCommented:
Might be cleaner to use a DataSet
Dim strFieldName as string
Dim oData As SqlDataAdapter
Dim oDs As DataSet
 
 
oData = New SqlDataAdapter("Select Statement", con)
oData.Fill(dsLineItems, "Results")
 
Dim row As DataRow
For Each row in oDs.Tables(0).Rows
  strFieldName =  row("FieldName")  
Next
 
oDs.Close

Open in new window

0
 
nomar2Author Commented:
I will look at this again... when I did what you said up above ... it would only spit out the last record when I used datasets...it ignores all the other rows..

I will fix up and post some code..maybe you can see something obivious

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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now