CODEWITHSUNDEEP
c#asp.netsql-server
user626873
user626873

Reputation: 177

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.

Upvotes: 1

Views: 6236

Answers (3)

jfollas
jfollas

Reputation: 1235

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.

Upvotes: 8

serhat_pehlivanoglu
serhat_pehlivanoglu

Reputation: 982

try writing your if condition like this

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

Upvotes: 1

VinPepe
VinPepe

Reputation: 561

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

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

Upvotes: 0

Related Questions