Windows Form SqlCommand C# Error

Question

I have a Windows Forms program I was writing at home (I mostly work with ASP.Net so it's been a while) and I'm having trouble executing stored procedure commands.

I create the SqlCommand object from the SqlConnection object and then create the SqlParameter from the SqlCommand. I specify the name, data type, and so on. However, whenever I call SqlCommand ExecuteReader() it's telling me it expects parameters that were not provided. I clearly added them and can see them populated when stepping through in Debug. Any ideas?

Stored procedure:

EXEC dbo.GetTransactions @StartDate = '2015-04-10 18:07:43',  
                         @EndDate = '2015-04-10 18:07:43'

Class DataAccess:

  public static DataTable Execute(SqlCommand iCommand) {
     DataTable objTable = new DataTable();
     try {
        iCommand.Connection.Open();
        SqlDataReader objReader = iCommand.ExecuteReader();
        objTable.Load(objReader);
        objReader.Close();
     }
     catch {
        throw;
     }
     finally {
        iCommand.Connection.Close();
     }
     return objTable;
  }

  public static SqlCommand CreateCommand(string iProcedureName) {
     try {
        SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString());
        SqlCommand objCommand = new SqlCommand(iProcedureName, objConnection);
        return objCommand;
     }
     catch {
        throw;
     }
  }

Class TransactionCollection:

  private static DataTable Load(DateTime iStartDate, DateTime iEndDate) {
     string strProcedureName = "GetTransactions";
     SqlCommand objCommand = DataAccess.CreateCommand(strProcedureName);
     SqlParameter param = objCommand.CreateParameter();
     param.ParameterName = "@StartDate";
     param.DbType = DbType.DateTime;
     param.Value = iStartDate;
     objCommand.Parameters.Add(param);

     param = objCommand.CreateParameter();
     param.ParameterName = "@EndDate";
     param.DbType = DbType.DateTime;
     param.Value = iEndDate;
     objCommand.Parameters.Add(param);

     return DataAccess.Execute(objCommand);
  }

Change second parameter creation, create a new parameter, instead of reassigning the existing one. e.g. SqlParameter param1 = objCommand.CreateParameter(); param.ParameterName = "@EndDate";.... — Michael
– Michael, Commented Apr 10, 2015 at 18:22
If you're just going to catch the exception and rethrow without logging, you might as well just use a using statement. Also, you can simplify a bit. SqlDataReader objReader = iCommand.ExecuteReader(); objTable.Load(objReader); can become objTable.Load(iCommand.ExecuteReader()); — mason
– mason, Commented Apr 10, 2015 at 18:29

marc_s · Accepted Answer · 2015-04-10 18:29:02Z

1

You need to tell your SqlCommand that it's executing a stored procedure! You need to set the CommandType of your SqlCommand - see here:

 public static SqlCommand CreateCommand(string iProcedureName) {
    try {
       SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString());
       SqlCommand objCommand = new SqlCommand(iProcedureName,  objConnection);
       // add this line here!!
       objCommand.CommandType = CommandType.StoredProcedure;

       return objCommand;
    }
    catch {
        throw;
    }
 }

answered Apr 10, 2015 at 18:29

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

1 Comment

mac Over a year ago

Thanks marc_s! I completely forgot about that step. Works fine now with that line added.

Collectives™ on Stack Overflow

Windows Form SqlCommand C# Error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related