Getting errors with SqlParameter and ExecuteScalar

Question

public TransImport()
{
    ConnString = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
    conn_new = new SqlConnection(ConnString);
    command_serial_new = conn_new.CreateCommand();
    command_serial_new.CommandText = "SELECT 1 FROM YSL00 WHERE SERLNMBR = @slnr";
    var p = new SqlParameter("@slnr", SqlDbType.NVarChar, 50);
    command_serial_new.Parameters.Add(p);
    //Here you will start reading flat file to get serialnumber. 
    //Here I have shown a simple call using one value 12345 but it will be 1000's of
    //lines from flatfile.

    if (CheckSerialNumber('12345'))
        DisplayMessage("Good serialnumber"); //this function is not copied here.
}

private Boolean CheckSerialNumber(string SerialNumber)
{
    command_serial_new.Parameters["@slnr"].Value = SerialNumber;
    try
    {
        var itExists = (Int32)command_serial_new.ExecuteScalar() > 0;
        if (itExists)
        {
            return true;
        }
    }
    catch (Exception ex)
    {
        LogException(ex, "Error in CheckSerialNumber =>"+ command_serial_new.CommandText.ToString());
    }
    return false;
}

I get error in the above catch. It mentions

object reference not set to an instance of object

It is failing with the line that has ExecuteScalar.

I guess I am doing something wrong here, but unable to figure out so far.

Update 1: I have modified this question. Basically I created another question with an issue I am facing.. Also I have marked it as answered.

Here is the NEW question I have posted just now.

Getting timeout errors with SqlTransaction on same table

Have you tried debugging, and check the value of command_serial_new.ExecuteScalar()? You're both casting and comparing the value in one line, which complicates the troubleshooting. — Dave Swersky, Oct 09 '13 at 19:19

score 2 · Answer 1 · answered Oct 09 '13 at 19:18

2

This happens if the ExecuteScalar() returns null. (eg, because no rows matched)

Since int is a value type, it cannot be null; therefore, you get an error.

Instead, you can cast it to int?, which is nullable.

answered Oct 09 '13 at 19:18

SLaks

868,454
176
1,908
1,964

score 0 · Answer 2 · answered Oct 09 '13 at 19:20

0

try:

int i;
object o = command_serial_new.ExecuteScalar();
if(o != null && Convert.ToInt32(o.ToString()) > 0)
{

   //....
}

to verify that your query returned something

answered Oct 09 '13 at 19:20

Jonesopolis

25,034
12
68
112

score 0 · Accepted Answer · answered Oct 09 '13 at 19:26

0

@SLaks answer is correct.

Here is one more way to avoid the error. No need of null checking etc, Convert.ToInt32 takes care of everything.

var itExists = Convert.ToInt32(command_serial_new.ExecuteScalar()) > 0;

answered Oct 09 '13 at 19:26

Sriram Sakthivel

72,067
7
111
189

I'll suggest you to create a new question – Sriram Sakthivel Oct 09 '13 at 19:53
I will create new question. – Anirudh Oct 09 '13 at 19:56

Getting errors with SqlParameter and ExecuteScalar

3 Answers3