Wrong Count(*) on an objCommand.ExecuteScalar but it is correct on "SQL Entreprise Manager" or "SQL

[Julio Allegue]

I am getting the wrong Count(*) on vb.net using the ExecuteScalar . It
returns all the rows. It doesn't seem to look at the WHERE clause. At
the same time, I am getting the correct count on "SQL Enterprise
Manager" or "SQL Query Analyser" using the same SQL statement.

Thanks in advance.
Julio.

Here is the SQL statement plus the source to the GetNumOfRow sub.

------------------------
SQL statement
------------------------
sSQL = "SELECT COUNT(*) FROM Rail_Detail d INNER JOIN
Rail_TaxDeduction t " & _
"ON d.DeductionCode=t.Code " & _
"WHERE d.MFReportRunDate Is NULL"

------------------------
Sub call GetNumOfRows
------------------------
iCounter = GetNumOfRows("METROPASS", "Rail_Detail", sSQL)

-----------------------------------
Source code GetNumOfrows
-----------------------------------
Private Function GetNumOfRows(ByRef sDBName As String, ByVal sTBName
As String, Optional ByVal sCondition As String = "") As Integer
'**************************************************************************
'GetNumOfRows - Returns the number of rows
'**************************************************************************

Dim objConnection As SqlConnection
Dim objCommand As SqlCommand
Dim sConnectionString As String
Dim sSQL As String

sConnectionString = "Integrated Security=SSPI;" & _
"Data Source=" & sgSQLServerName & ";" &
_
"Initial Catalog=" & sDBName

sSQL = "SELECT COUNT(*) FROM " & sTBName & " " & sCondition

objConnection = New SqlConnection(sConnectionString)
Try
objConnection.Open()
objCommand = New SqlCommand(sSQL, objConnection)
GetNumOfRows = objCommand.ExecuteScalar
Catch e3 As Exception
Call Display_Error(e3.Message, "GetNumOfRows")
Finally
If (objConnection.State = ConnectionState.Open) Then
objConnection.Close()
End If
End Try
objCommand.Dispose()
objCommand = Nothing
objConnection.Dispose()
objConnection = Nothing
End Function

 

[William Ryan]

It looks like you are passing in your Top SQL Statement with the join
condition in it to the GetNumOfRows. I'm surprised it's working at all, but
if you don't pass in the optional Parameter, then I can see why the
restriction isn't happening.

Can you put a breakpoint right before this line : objCommand = New
SqlCommand(sSQL, objConnection)
and then ? the sSQL and verify what the command text is? I'm guessing that
the commandtext is the problem.

Another alternative would be to use Parameters like so:

"SELECT COUNT(*) FROM @TBName Where MFReportRunDate = @Condition"

Then use

objCommand.Parameters.Clear
objCommand.Parameters.Add("@TbName", sDBN) 'You can overload these to get
much more precision, but I don't knw your schema, so I'm using this method
for clarity
objCommand.Parameters.Add("@Condition", sCondition)

Or use a Stored proc, which all you'd have to do is change the command text,
make sure the params you add match in name and type, change the commandtype
to stored procedure, and just make sure that you grant the permissions..

This doesn't address your problem per se, but would sure make it easier to
maintain and use.

First thing though, verify the command text....just copy it from the
immediate window into Query Analyzer and verify the results.

We'll get through this though..always willing to help a fellow UM alum.

Good Luck,

Bill

 

[Julio Allegue]

William,

Thanks for your reply.
I verified the command text from the immediate window and pasted into
the Query Analyzer. I do get the correct number of rows here. But if I
continue stepping through the VB code, I get the wrong row count. Not
sure if SqlCommand does NOT like count(*) with INNER JOIN and the WHERE
clause (using the Imports System.Data.SqlClient) ????
So I wrote the following stored procedure and I am getting the correct
row count. Thanks again. "GO CANES"

CREATE PROCEDURE [dbo].[sp_UserDef_GetNumOfRows]
@DBNameIN VARCHAR(255) = null, -- Database Name
@TBNameIN VARCHAR(600) = null, -- Table Name
@WhereIN VARCHAR(600) = null, -- Where statement
AS

EXEC ('use ' + @DBNameIN + ' SELECT COUNT(*) FROM ' + @TBNameIN + ' ' +
@WhereIN)
GO