473,394 Members | 1,277 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

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

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
Nov 20 '05 #1
2 2917
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" <ja******@med.miami.edu> wrote in message
news:27*************************@posting.google.co m...
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

Nov 20 '05 #2
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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Nuno | last post by:
Is there any SQL Error? Or I have to use Select case in VB code to control SQL instead. Thank you for any ans. Nuno
5
by: George Copeland | last post by:
This is a request for help fixing a SQL Server 2000/ADO problem on Windows XP. I would appreciate any useful assistance. PROBLEM: SQL Server access on my machine fails as follows: 1. All of...
5
by: Rachel Weeden | last post by:
I'm working on an ASP Web application, and am having syntax issues in a WHERE statement I'm trying to write that uses the CInt Function on a field. Basically, I want to select records using...
4
by: James | last post by:
Please help me with this sql: SELECT z005aNORTHEAST_3days_Formula.Region, z005aNORTHEAST_3days_Formula.DiffofClaimsAssignment FROM (SELECT .Region, (.)-( .) AS DiffofClaimsAssignment FROM...
9
by: Mike Bridge | last post by:
I am using MS Access 2003, and I typed in a query in SQL view which was working fine. Now when I open it, it gives me the error "Join expression not supported". Apparently, Access "fixed" it for...
5
by: Kivak Wolf | last post by:
Hey everyone, I have a textbox in my web page that is going to be used to enter an E-mail into (just plain text, no HTML). Now, this will interact with a SQL database where the contents of the...
5
by: Glen Buell | last post by:
Hi all, I have a major problem with my ASP.NET website and it's SQL Server 2005 Express database, and I'm wondering if anyone could help me out with it. This site is on a webhost...
3
Minion
by: Minion | last post by:
I hate to even post this message as I probably know the answer before I begin. Still there are several problems facing me with this particular challenge. First though I'll goto into the overview...
22
by: MLH | last post by:
100 Dim db As Database, rst As Recordset 120 Set db = CurrentDb 140 PString = "SELECT qryBatchList.ReadyFor906, qryBatchList.BatchID FROM qryBatchList WHERE...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.