locked
Passing parameter to stored procedure RRS feed

  • Question

  • I am passing parameters to a stored procedure that accepts three parameters: @PerControlBegin, @PerControlEnd, @GreatPlainsCompanyCode

    in my catch block, i get the error below

    ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'usp_GreatPlains_GarnishmentIntegrations_GetTransactionsByPerControl_Test' expects parameter '@PerControlBegin', which was not supplied."

    Can anyone help with this?

    Thanks

    ==================================================

    private void FillDataGrid(string sSelectStatement, string PerControlBegin, string PerControlEnd, string GreatPlainsCompanyCode)
    { OdbcDataAdapter daVendorInfo;
     DataSet         dsVendorInfo;

     ClearDataGrid();

    OdbcCommand cmdVendorInfo = new OdbcCommand(sSelectStatement, cnUltiPro);
    OdbcParameter param;

    cmdVendorInfo.CommandType = CommandType.StoredProcedure;
    param = cmdVendorInfo.Parameters.Add(new OdbcParameter("@PerControlBegin",OdbcType.Char,9));
    param.Value = PerControlBegin;

    param = cmdVendorInfo.Parameters.Add(new OdbcParameter("@PerControlEnd",OdbcType.Char,9));
    param.Value = PerControlEnd;

    param = cmdVendorInfo.Parameters.Add(new OdbcParameter("@GreatPlainsCompanyCode",OdbcType.VarChar,20));
       param.Value = GreatPlainsCompanyCode;
      
    daVendorInfo = new OdbcDataAdapter(cmdVendorInfo);
    dsVendorInfo = new DataSet();  

    try

     daVendorInfo.Fill(dsVendorInfo, "ResultSet");
        dgDataFromGP.DataSource = dsVendorInfo;
        dgDataFromGP.DataMember = "ResultSet";
     dgDataFromGP.DataBind(); 
    }

    catch(Exception ex)
    {
                    this.Label2.Text = ex.Message;
    }
    finally
    {
                   if(dsVendorInfo != null)
         dsVendorInfo.Dispose();
     if(daVendorInfo != null)
         daVendorInfo.Dispose();
    }
    }

    Wednesday, October 25, 2006 10:18 PM

Answers

  • If you use ODBC provider to call a stored procedure, you have to learn the ODBC call syntax. An example:

    {? = CALL MyStoredProc(?, ?, ?)}

    Thursday, October 26, 2006 2:34 AM

All replies

  • have you tried doing it this way, just to see what happens?

    cmdVendorInfo.CommandType = CommandType.StoredProcedure;


    OdbcParameter param1 = new OdbcParameter("@PerControlBegin",OdbcType.Char,9);
    param1.Value = PerControlBegin;

    cmdVendorInfo.Parameters.Add(param1);

     

    OdbcParameter param2 = new OdbcParameter("@PerControlEnd",OdbcType.Char,9);
    param2.Value = PerControlEnd;

    cmdVendorInfo.Parameters.Add(param2);

     

    OdbcParameter param3 = new OdbcParameter("@GreatPlainsCompanyCode",OdbcType.VarChar,20);
    param3.Value = GreatPlainsCompanyCode;
    cmdVendorInfo.Parameters.Add(param3);

    Wednesday, October 25, 2006 11:02 PM
  • My guess is that the value of PerControlBegin is null. Because you have not set IsNullable to true on the parameter it will disallow this. You should either set IsNullable to true if it is acceptable, or check the inputs to the method to make sure they are non-null.
    Wednesday, October 25, 2006 11:04 PM
  • If you use ODBC provider to call a stored procedure, you have to learn the ODBC call syntax. An example:

    {? = CALL MyStoredProc(?, ?, ?)}

    Thursday, October 26, 2006 2:34 AM
  •  

      You are right, ODBC syntax requires this format : {? = CALL MyStoredProc(?, ?, ?)}

    Thanks to everyone!

    Thursday, October 26, 2006 3:35 PM