ASP .NET C# SQL return DBNULL on ExecuteScalar

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.

---

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.

---

try writing your if condition like this

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

---

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

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