ExecuteScalar

Hi,

I am trying to the execute the the following code.

        Dim strAs String = "select Count(*) from MyTable where SerialNo = @Sl"
        Total = Convert.ToInt32(cmd.ExecuteScalar)   <---- error line

If there is any record existing in the table for selected serialNo I can get the Total but if no record existing in the table I get the follwing error.

System.Data.OleDb.OleDbException: No value given for one or more required parameters. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteScalar() at WeaponShop.frmJobEntry.btnSubmit_Click(Object sender, EventArgs e) in ...line 201

How can I solve the problem? I want assign Total to zero if no record available.

Ayha1999
LVL 7
ayha1999Asked:
Who is Participating?
 
raterusConnect With a Mentor Commented:
This is the wrong way, but will work,

try
  Total = Convert.ToInt32(cmd.ExecuteScalar)
Catch e as System.Data.OleDb.OleDbException
  Total = 0
End Try

I'm tempted to think it has something to do with count(*) instead of count(columnname), but I don't know for sure.  what does the query return it you run it directly in the database?
0
 
b1xml2Commented:
"SELECT ISNULL(COUNT(*),0) FROM myTable WHERE SerialNo = @Sl"

The same applies to MAX and MIN where null is returned if there are no records in the table at all.
0
 
ayha1999Author Commented:
Hi,

when I tried "SELECT ISNULL(COUNT(*),0) FROM myTable WHERE SerialNo = @Sl"

the following error occurred.

System.Data.OleDb.OleDbException: Wrong number of arguments used with function in query expression 'IsNull(Count(*),0)'. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteScalar() at WeaponShop.frmJobEntry.btnSubmit_Click(Object sender, EventArgs e) in

ayha
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
b1xml2Commented:
Is this a MSSQL Server or an MS Access database
0
 
ayha1999Author Commented:
Access 2000.

ayya
0
 
Anthony PerkinsCommented:
b1xml2

SELECT COUNT(*) can never return Null.  It is either 0 or more.

ayha1999,

Have you tried aliasing the COUNT as in:

Dim strAs String = "select Count(*) As MyCount from MyTable where SerialNo = @Sl"

Also, it would help if you would post all the relevant code, not just a snippet.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.