[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 690
  • Last Modified:

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
0
ayha1999
Asked:
ayha1999
1 Solution
 
raterusCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now