CODEWITHSUNDEEP
c#sqlsqlcommand
Anirudh
Anirudh

Reputation: 591

Getting errors with SqlParameter and ExecuteScalar

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

Upvotes: 1

Views: 386

Answers (3)

Sriram Sakthivel
Sriram Sakthivel

Reputation: 73482

@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;

Upvotes: 0

Jonesopolis
Jonesopolis

Reputation: 25370

try:

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

   //....
}

to verify that your query returned something

Upvotes: 0

SLaks
SLaks

Reputation: 887479

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.

Upvotes: 2

Related Questions