ASP .NET C# SQL return DBNULL on ExecuteScalar

Question

OK I have created a product table [ID, itemCode], a sales table and a view that returns SUM of items in stock. Thing is if the item has not been sold yet, there is no record of it in the view. I need to check if item is in stock in order to complete further sales etc.

What I have done is this:

    string selectSQL = "SELECT [total] FROM [stock] WHERE ([itemCode] = " + TextBoxCode.Text + ")";
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand com = new SqlCommand(selectSQL, con);
    try
    {
        con.Open();          
        object obj = com.ExecuteScalar();

        if (obj == null) //(also tried is DBNull)
        {
            lblStatus.Text = "Does not exist in stock";
        }
        else
        {
            sum = com.ExecuteScalar().ToString();
            lblStatus.Text = "Items in stock: " + sum;
        }
    }
    catch (Exception err)
    {
        lblStatus.Text = err.Message;
    }
    finally
    {
        con.Close();
    }

It works fine when the item actually exists in stock but if there is no record i get the error:

Conversion failed when converting the nvarchar value '1E001' to data type int.

'1E001' is the first itemCode in my stock view but it is irrelevant to the itemCode I am trying to insert.

The problem seems to be in the line:

object obj = com.ExecuteScalar();

I have also tried a

"SELECT COUNT(total) FROM [stock] WHERE ([itemCode] = " + TextBoxCode.Text + ")";

with the same results. I can't get it to work.

By the way, by using com.ExecuteScalar() twice, you also execute the query twice while obj should already contain the answer. — Hans Keﬆing
– Hans Keﬆing, Commented Mar 21, 2011 at 14:26

jfollas · Accepted Answer · 2011-03-21 14:27:22Z

8

If ItemCode is not a number, then you would need to include single-quotes before and after the code that inserts TextBoxCode.Text. Example:

string selectSQL = "SELECT [total] FROM [stock] WHERE ([itemCode] = '" + TextBoxCode.Text + "')";

WARNING: Using this code would leave you wide open for SQL Injection attacks!

A preferred method, using a parameter, would be:

string selectSQL = "SELECT [total] FROM [stock] WHERE ([itemCode] = @ItemCode)";
SqlCommand com = new SqlCommand(selectSQL, con);
com.Parameters.AddWithValue("@ItemCode", TextBoxCode.Text);

And for your question itself, the result of ExecuteScalar will be null (a .NET null condition) if there are no rows in the resultset. This is different than if the first field of the first row of the resultset is a database Null value (DBNull.Value).

To check for both, use:

if (obj == null || obj == DBNull.Value)

Note: You shouldn't need to ExecuteScalar the second time (in the event that it's not null), since you'll already have the results in the obj variable.

edited Mar 21, 2011 at 14:27

answered Mar 21, 2011 at 14:14

jfollas

1,2357 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user626873 Over a year ago

I actually have parameters but I didn't post the whole thing for brevity

user626873 Over a year ago

I totally missed the single quotes again. Thank you

serhat_pehlivanoglu · Accepted Answer · 2011-03-21 14:12:54Z

1

try writing your if condition like this

 if ((obj == null) || (obj == DBNull.Value))

answered Mar 21, 2011 at 14:12

serhat_pehlivanoglu

9828 silver badges14 bronze badges

Comments

VinPepe · Accepted Answer · 2011-03-21 14:12:32Z

0

It looks like you need single quotes around your text like so:

"SELECT COUNT(total) FROM [stock] WHERE ([itemCode] = '" + TextBoxCode.Text + "')";

answered Mar 21, 2011 at 14:12

VinPepe

5611 gold badge4 silver badges9 bronze badges

Collectives™ on Stack Overflow

ASP .NET C# SQL return DBNULL on ExecuteScalar

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related