SqlCommand C# Issue

Question

The below piece of code that throws the following exception..

Error Message:

Object reference not set to an instance of an object. Stack Trace:
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

I am lost as to the reason why..

sqlcon = new SqlConnection(strSqlconnection);

SqlCommand sqlcomSMCheckin = new SqlCommand("prc_CheckIn", sqlcon);

sqlcomSMCheckin.CommandType = CommandType.StoredProcedure;

sqlcomSMCheckin.Parameters.Add("@Description", SqlDbType.VarChar).Value = "My App";

sqlcomSMCheckin.CommandTimeout = this.iCommandTimeOut;

if (sqlcon.State == ConnectionState.Closed)
{
   sqlcon.Open();
}

if (sqlcomSMCheckin != null)
{
    sqlcomSMCheckin.ExecuteNonQuery(); // error here
    sqlcomSMCheckin.Dispose();
}

Answer 1

Make sure the sproc "prc_CheckIn" exist in the SQL Server you are connecting to. Maybe it's misspelled and or not accessible to the user in the connection.

Answer 2

Not 100% sure what's going on here - but can you try this snippet of code?

using(sqlcon = new SqlConnection(strSqlconnection))
{
   using(SqlCommand sqlcomSMCheckin = new SqlCommand("dbo.prc_CheckIn", sqlcon))
   {
       sqlcomSMCheckin.CommandType = CommandType.StoredProcedure;

       sqlcomSMCheckin.Parameters.Add("@Description", SqlDbType.VarChar, 50)
            .Value = "My App";

       sqlcomSMCheckin.CommandTimeout = this.iCommandTimeOut;

       sqlcon.Open();
       sqlcomSMCheckin.ExecuteNonQuery();
       sqlcon.Close();
   }
}

I replace "prc_CheckIn" with "dbo.prc_CheckIn", I specify a max length on the VARCHAR parameter (adjust as needed), wrapped everything in using {} blocks - that's about it.

Do you still get the same error??

Marc

Answer 3

You should always close your sqlconnection immediately after executing the sqlcommand

If this is not part of a function, then reinstating it with new is not necessary - just opening it will be sufficient.

Answer 4

HI

The code seems OK (I recommend that you use the using clause as demonstrated to you in a previous answrer). I wonder if the problem is notfrom within the stored procedure. Try to debug it, or at least add some log recording atthe begining and end of your stored procedure to make sure that it exits OK every time.

Answer 5

Have you tried disabling connection pooling and/or multiple active result sets? I forget the connection string option for that but if you invoke the connection string builder it should be on the advanced property grid. Or on www.connectionstrings.com.

Also, is this a "user instance" database like when you add a .mdf file to a Visual Studio project using SQL Express?

Answer 6

Is sqlCon a class level variable, do you have issues with the same method being called multiple times? Is there anything static in the class? Are you using multiple threads?