Solved

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

Posted on 2009-07-13
10
7,460 Views
Last Modified: 2012-05-07
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
Comment
Question by:nomar2
10 Comments
 
LVL 5

Expert Comment

by:Aaronroach
ID: 24842752
for each row as datarow in ds4.tables.rows
'do something
Next
0
 
LVL 28

Expert Comment

by:strickdd
ID: 24842758
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
 
LVL 21

Expert Comment

by:Craig Wagner
ID: 24842771
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
 
LVL 8

Expert Comment

by:Bob Hoffman
ID: 24842791
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
 

Author Comment

by:nomar2
ID: 24843055
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 8

Expert Comment

by:Bob Hoffman
ID: 24843150
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
 

Author Comment

by:nomar2
ID: 24843377
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
 
LVL 8

Expert Comment

by:Bob Hoffman
ID: 24843434
No you cant, You need a seperate connection object for each dataReader.
0
 
LVL 8

Accepted Solution

by:
Bob Hoffman earned 161 total points
ID: 24843489
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
 

Author Comment

by:nomar2
ID: 24843762
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

932 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

10 Experts available now in Live!

Get 1:1 Help Now