0

I've got a stored procedure that runs just fine if I execute it on my server and if I build an execute statement with my parameters (e.g. string sql = "Exec Get_Data '" + St + " ' ...". However, as soon as I try:

string strQS = "Exec Get_Data @param1,@param2,@param3,@param4..."

using (SqlConnection conSQL = new SqlConnection(connectionString))
{
    using (SqlCommand cmdSQL = new SqlCommand(strQS, conSQL))
    {
        conSQL.Open();

        cmdSQL.Parameters.AddWithValue("@param1", SqlDbType.VarChar).Value = St;
        cmdSQL.Parameters.AddWithValue("@param2", SqlDbType.VarChar).Value = Loc;
        ...

I get the following error:

Column name or number of supplied values does not match table definition.

Obviously if I can run it before I use a parametrized query I don't have anything wrong with my column names, but something with how my values are being treated. All my variables are of type string and the SQL Server is expecting all parameters to by of type varchar...Any ideas?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chris
  • 265
  • 2
  • 16
  • 1
    Show the entire code. Are you setting the command type to stored procedure? Do you have a typo in the other parameter names? All we can do is guess with the limited amount you've shown us. Please provide a [mcve]. – itsme86 Feb 15 '18 at 16:13

1 Answers1

1

You need to change your command type to StoredProcedure.

And then drop the EXEC and all the parameters from your string.

cmdSQL.CommandType = CommandType.StoredProcedure;
string strQS = "Get_Data"
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • No, he doesn't. You _can_ use CommandType.StoredProcedure this way, in which case you need no `Exec` keyword and no parameter placeholders in the command string: just the procedure name. This might even fix his error message, by side-stepping the mismatch between the placeholders in the SQL string and the parameter objects added to the SqlCommand. But you don't have to. You can also leave the default CommandType. It's just that then you also need the `Exec` keyword and the parameter placeholders as part of the sql string. – Joel Coehoorn Feb 15 '18 at 16:16
  • 1
    @JoelCoehoorn true. But then it is like using dynamic sql to execute a stored proc when it is just adding an unnecessary layer of confusion. – Sean Lange Feb 15 '18 at 16:45