0

SQL Procedure

CREATE PROCEDURE xxx.test
    @p1 int 
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @p1
END

C# source

using(OdbcConnection Connection=new OdbcConnection("Driver={SQL Native client};SERVER=###;DATABASE=###;Trusted_Connection=yes;"))
{
    Connection.Open();
    using(OdbcTransaction Trans=Connection.BeginTransaction())
    {
        using(OdbcCommand Command=Connection.CreateCommand())
        { 
            try
            {
                Command.CommandType = System.Data.CommandType.StoredProcedure;
                Command.CommandText = "xxx.test";
                Command.Transaction = Trans;
                OdbcParameter param=new OdbcParameter("@p1",OdbcType.Int);
                param.Value = 1;
                Command.Parameters.Add(param);
                Command.ExecuteNonQuery();
            }
            catch (OdbcException OdbcException)   
            {
                Console.Write(OdbcException.Message);
            }
        }
        Trans.Rollback();
    }
    Connection.Close();
}

I always get exception saying ERROR [42000] [Microsoft][SQL Native Client][SQL Server]Procedure or function 'test' expects parameter '@p1', which was not supplied."

However when i debug, put breakpoint on line ExecuteNonQuery and click through Command.Parameters until I see items I can see that this parameter is definitely there and its value is set, type is correct - see http://s29.postimg.org/5cqvo7iqv/Visicom_Windows.png (i don't have enough of reputation yet)

what am i doing wrong?

Michal Seliga
  • 3
  • 2
  • 1
    Is there a reason for you to use `OdbcConnection` which is using `SQL Native Client` instead of `SqlConnection`? – Andrey Korneyev Sep 19 '14 at 10:44
  • possible duplicate of [Execute Parameterized SQL StoredProcedure via ODBC](http://stackoverflow.com/questions/3583074/execute-parameterized-sql-storedprocedure-via-odbc) – Andrey Korneyev Sep 19 '14 at 10:51
  • no, it is not duplicate. mentioned "Execute Parameterized SQL StoredProcedure via ODBC" was solved by using AddWithValue - which didn't worked for me (parameter was inserted to parameter collection with wrong type and exception remained the same) I changed temporarily from Odbc* to Sql* and indeed everything works well now. But SqlClient namespace is for SQL server only and Odbc should be working with every database system which has ODBC driver, so I would prefer to have it universal – Michal Seliga Sep 19 '14 at 17:12
  • As per first answer on question by link I've posted - the command syntax for odbcCommand not simply "stored_procedure_name", but "call ....". Have you tried it? – Andrey Korneyev Sep 19 '14 at 18:15
  • I tried but it is not general solution (nive workaround anyway). SQL Server won't accept 'call xx(?)' and requires 'exec xx ?' - but this will not work for example in Sybase SQL Anywhere. Yes, I could have to make different case for each SQL dialect when needed, but still, it is not what MSDN writes about OdbcCommand,CommandText (SQL statement or stored procedure to execute) – Michal Seliga Sep 22 '14 at 08:00

0 Answers0