Click here to Skip to main content
15,071,928 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a VB.NET code which originally had ADO connections with Recordsets to get the data from databases and do what it supposed to. But now I have replaced all the ADO connections with ADO.NET SqlDataReader() and no more Recordsets I have in the code. So previously the code had recordset.MoveNext() method to move to the next row in the records once it's done the work. But now since I do not have any recordsets and since I have SqlDataReader() to keep track of records how should I move to the next row or next record?



So the question again is, in the current version it does not loop to the next record automatically like it used to do with rs1.MoveNext(). What should I do here?

What I have tried:

VB
Example what I had before :

strSelectStatement = "SELECT * FROM " & Trim(strALL_LABOR_TABLE) & " WHERE posted = '' AND work_order IS NOT NULL ORDER BY SOURCE_ID"
FileIO.WriteLog("<--------------- Begining of posting records --------------->")

If rs1.State = 1 Then rs1.Close()
rs1.Open(strSelectStatement, cnADOEDGE_DSN, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)

strStatus = ""

Do Until rs1.EOF
'Do whatever the job it has to do

rs1.MoveNext()

Loop


Example what I have now :

strSelectStatement = "SELECT * FROM " & Trim(strALL_LABOR_TABLE) & " WHERE posted = '' AND work_order IS NOT NULL ORDER BY SOURCE_ID"
FileIO.WriteLog("<--------------- Begining of posting records --------------->")

Dim Sql3 = "SELECT * FROM " & Trim(strALL_LABOR_TABLE) & " WHERE posted = '' AND work_order IS NOT NULL ORDER BY SOURCE_ID"
Dim postCount3 As SqlDataReader
Dim newServer3 As String = ConfigurationManager.AppSettings("newServer")

Try
Using cn3 As New SqlConnection(newServer3),
cmd3 As New SqlCommand(Sql3, cn3)
cn3.Open()
postCount3 = cmd3.ExecuteReader()
If postCount3.HasRows Then
While postCount3.Read()

Try
'Do whatever the job it has to do
Catch ex As Exception
FileIO.WriteLog("Error : " & ex.Message)
End Try

End While
Else
FileIO.WriteLog("Error : There are no records to select from ALL_LABOR_DETAILS table")
End If

postCount3.Close()
cn3.Close()
End Using
Catch ex As Exception
FileIO.WriteLog(strOrderNumber & " There was an error getting the laborDetails FROM [MESDEV].[dbo].[ALL_LABOR_DETAILS] WHERE posted = '' AND work_order IS NOT NULL ORDER BY SOURCE_ID. Error Messege : " & ex.Message)
End Try
Posted
Updated 20hrs ago

1 solution

Edit, sorry, just remembered you are doing VB.Net.


You already have the code to go next. You have
While postCount3.Read()
which will read a record until it hits the end of your while and then it will call .Read() again and again until there are no more records.

End Edit

A lot has changed since then including the use of usings. So, your code should look something like this:

C#
try
{
  String sql = "StoredProcedureName";
  using (SqlConnection sqlCon = new SqlConnection(connectionString))
  {
    sqlCon.Open();

    using (SqlCommand cmd = new SqlCommand(sql, sqlCon))
    {
      cmd.CommandType = System.Data.CommandType.StoredProcedure;

      // add parameters if necessary

      using (SqlDataReader dr = cmd.ExecuteReader())
      {
        if (dr.HasRows)
        {
          while (dr.Read()){
          {
             someVar.field1 = dr["field1"].ToString();
             // ....

          }
        }   
       }
     }
   }      
}
catch (Exception ex)
{
  Log.LogError(ex);
//  do whatever
}
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900