CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
mac
mac

Reputation: 504

Windows Form SqlCommand C# Error

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);
  }

Upvotes: 1

Views: 160

Answers (1)

marc_s
marc_s

Reputation: 755073

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

Upvotes: 1

Related Questions