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
– Dave Swersky, Commented Oct 9, 2013 at 19:19

SLaks · Accepted Answer · 2013-10-09 19:18:42Z

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 9, 2013 at 19:18

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

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

Comments

Sriram Sakthivel · Accepted Answer · 2013-10-09 19:26:04Z

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 9, 2013 at 19:26

Sriram Sakthivel

73.8k7 gold badges118 silver badges200 bronze badges

2 Comments

Sriram Sakthivel Over a year ago

I'll suggest you to create a new question

Anirudh Over a year ago

I will create new question.

Jonesopolis · Accepted Answer · 2013-10-09 19:20:48Z

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 9, 2013 at 19:20

Jonesopolis

25.4k12 gold badges72 silver badges115 bronze badges

Collectives™ on Stack Overflow

Getting errors with SqlParameter and ExecuteScalar

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related