ODBC Command Parameters Not Binding

Question

i know there are a lot of questions regarding this already, however, i don't see any that appears to solve my problem.

i have this code

using (var conn = new OdbcConnection(sConn))
{
    const string cmdIns = "spSound_Insert";
    using (var sqlCmdIns = new OdbcCommand(cmdIns, conn))
    {
        sqlCmdIns.CommandType = CommandType.StoredProcedure;
        sqlCmdIns.Parameters.Add("@uid", OdbcType.NVarChar, 40).Value = "123";
        sqlCmdIns.Parameters.Add("@data", OdbcType.VarBinary, -1).Value = new Byte[128];
        sqlCmdIns.Parameters.Add("@enabled", OdbcType.Bit).Value = true;
        sqlCmdIns.Parameters.Add("@note", OdbcType.NVarChar, 128).Value = "test note";

        conn.Open();
        sqlCmdIns.ExecuteNonQuery();
    }
}

and the error . . .

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function 'spSound_Insert' expects parameter '@uid', which was not supplied. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()

i've tried using AddWithValue and it gives me the same problem.

here is the SQL

use [SoundsDB];
go

create procedure [dbo].spSound_Insert
    @uid nvarchar(40),
    @data varbinary(max),
    @enabled bit = 1,
    @note nvarchar(128)
as
begin
    delete from [dbo].[Sound] where [uid] = @uid;

    insert [dbo].[Sound] ([uid], [data], [enabled], [note], [updated])
    values (@uid, @data, @enabled, @note, GETDATE());

end;

i'm changing the names a little, but when i ran the sp in sql console appears to work as expected.

Sounds like something in the stored procedure not the code shown. — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Feb 3, 2017 at 1:04
i changed the names a little... i also tested it in the sql console, it appeared to work as expected. — rbtLong
– rbtLong, Commented Feb 3, 2017 at 1:12

rbtLong · Accepted Answer · 2017-02-03 02:06:11Z

0

so i searched for parameterName and i found this article https://msdn.microsoft.com/en-us/library/system.data.odbc.odbcparameter.parametername(v=vs.110).aspx

they had a different way of calling a stored proc and it appears to work...

using (var conn = new OdbcConnection(sConn))
{
    const string cmdIns = "{ call spSound_Insert(?,?,?,?) }";
    using (var sqlCmdIns = new OdbcCommand(cmdIns, conn))
    {
        sqlCmdIns.CommandType = CommandType.StoredProcedure;
        sqlCmdIns.Parameters.Add("", OdbcType.NVarChar, 40).Value = "123";
        sqlCmdIns.Parameters.Add("", OdbcType.VarBinary, -1).Value = new Byte[128];
        sqlCmdIns.Parameters.Add("", OdbcType.Bit).Value = true;
        sqlCmdIns.Parameters.Add("", OdbcType.NVarChar, 128).Value = "test note";

        conn.Open();
    }
}

answered Feb 3, 2017 at 2:06

rbtLong

1,5823 gold badges14 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

ODBC Command Parameters Not Binding

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related