Record Count in VB

[IlyaRabyy]

Colleagues,
Here's the code that runs successfully:

Dim loSelectSQLCommand As SqlCommand = New SqlCommand()

With loSelectSQLCommand
   .CommandTimeout = 60
   .Connection = New SqlConnection(gcDBConnectStr)
   .CommandType = CommandType.Text
   .CommandText = "SELECT Job_Number, Customer, P_office, Latest_Status, Web_FileName, Web_Share, Web_Server " & _
                     "FROM Rpt2Web " & _
                     "WHERE (Customer = '" & lcCustomer & "') AND (Job_Number = '" & lcJobNo & "') AND " & _
                           "(Latest_Status = 'Waiting for Transfer')"
   .Connection.Open()
End With

Dim loDataRdr As SqlDataReader

' "Engage" SqlDataReader object
Try
   loDataRdr = loSelectSQLCommand.ExecuteReader()
Catch loErr As Exception
   gcLogStr = gcLogStr & Now.ToString("yyyy-MM-dd HH:mm:ss") & ": ERROR OCCURED!" & vbCrLf & Space(21) & loErr.Message & _
              vbCrLf & Space(21) & "Occured in " & vbCrLf & Space(21) & loErr.Source & vbCrLf & Space(21) & loErr.StackTrace & _
              vbCrLf & Space(21) & "Program quits." & vbCrLf
   File.WriteAllText(gcLogFile, gcLogStr) ' There's not much text in the Process Log string yet, write it all anyway

   lcErrMsg = "Error occurred when testing SQL Server Connection." & vbCrLf & "See details in the " & gcLogFile & vbCrLf & _
              "Program quits."
   MsgBox(lcErrMsg, MsgBoxStyle.Critical, gcProgName)
   lnRet = -1
End Try

If lnRet <> 0 Then
   Environment.Exit(lnRet)
End If

' Check the result of reader's "activity"
If Not loDataRdr.HasRows() Then 'Resulted in a zero-recs cursor? Report and quit
   loSelectSQLCommand.Connection.Close()
   loDataRdr.Close()
   lcLogStr = Now.ToString("yyyy-MM-dd HH:mm:ss:") & " No records found with files ready for transfer." & vbCrLf & _
              Space(21) & "Program exits." & vbCrLf
   gcLogStr = gcLogStr & lcLogStr
   File.WriteAllText(gcLogFile, gcLogStr) ' There's not much text in the Process Log string yet, write it all anyway

   lcMsgStr = "No records found with files ready for transfer." & vbCrLf & "Program exits."
   MsgBox(lcMsgStr, MsgBoxStyle.Information, gcProgName & ": Nothing to do!")
Else
' ???
End If 'Not loDataRdr.HasRows() Then 'Resulted in a zero-recs cursor? Report and quit

And the Question is ...... How do I know the number of records SQL Data Reader has returned?

(On the footnote: How I miss VFP when dealing with databases in VB/C# .NET, let alone Sequel Server!!! The VFP has function RECCOUNT(cAliasName), which'd tell you the number of recs U got... )

Regards,

Ilya

 

[strongm]

You are using the wrong object if you want to know the number of records returned. The design of the SqlDataReader means it cannot provide that info. There are a couple of ways around it - loop through the object, counting row by row. or run two queries, the first being a COUNT() query.

But if you really need record counts, you should instead look at a DataSet and, more specifically, DataTable

 

[IlyaRabyy]

Hmmm... AFAIK, SqlDataReader.Read() reads only forward, and only one record.
Being used to VFP (which is row-based, unlike set-based SQL Server), I wonder if DataSet or DataTable classes have something similar to
1. SKIP [n] command - moves record pointer in a cursor n records down (or up, if n is negative)
2. GO TOP command - moves record pointer to the top record in a cursor
3. GO BOTTOM command - moves record pointer to the bottom record in a cursor

And I know that there's no equivalent to

4. GO n - moves record pointer to the record #n

coz (again) SQL Server is not row- but set-based.

I looked up description of both, DataSet and DataTable classes and found none of the above... :-(

Regards,

Ilya

 

[strongm]

A DataTable has a Rows property that you might want to look at

>set-based SQL Server

It is ADO.NET that is 'set-based', not SQL Server. You'd be wise to do some investigation into ADO.NET and how it differs in design from the earlier ADO