Executing SQL Server Stored Procedures using ODBC.NET

[Stuart Ferguson]

I am currently writing code to execute SQL Server 2000 Stored procedured
using the ODBC.NET SQL Server Driver.

The particular stored procedure I am using has multiple paramaters which
I have created in my code (OdbcParamater objects), these are then being
added to the Command objects paramaters, however when it comes to
running the SP the paramaters are not present and the following
exception is thrown

06/01/2005|22:49:23.156|SQLSRV32.DLL|ERROR [42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]Procedure 'ts3_LoadFDDTxn' expects parameter
'@RecordType', which was not supplied.

The trace from SQL Profiler shows the SP being called with paramaters
yet the Command object does have the paramaters

Has anyone else had this or a similair problem ?

Any help is most appreciated

Stuart Ferguson

 

[Dan Bass]

Stuart

1. Why not move from ODBC to the SqlClient?

2. The parameter @RecordType doesn't appear to be supplied... Could you post
some code where the command is created/used, and also the start of the SQL
Stored Procedure where the parameters are defined?

Thanks.

Daniel.

 

[Stuart Ferguson]

Stored Procedure
CREATE PROCEDURE ts3_LoadFDDTxn @RecordType int AS ....

Building Params Array

// Populate Param Arrays
Params[0] = new OdbcParameter( "@RecordType" ,
OdbcType.Int);//OdbcType.Char );
Params[0].Value = 0;

Adding Params to Command object
for (int i=0 ; i <= Param.GetUpperBound(0); i++)
{
myCommand.Parameters.Add(Param);
}

Executing Command
myCommand =
CreateCommand("ts3_LoadFDDTxn",Param,CommandType.StoredProcedure);
myCommand.ExecuteNonQuery();

Many Thanks In Advance

Stuart

 

[Dan Bass]

For your code, try this:

// presuming myOdbConnection has been created and opened

string storedProcName = "ts3_LoadFDDTxn";
string parameterName = "@RecordType";
int parameterValue = 0;

OdbcCommand myCommand = new OdbcCommand();
myCommand.Connection = myOdbcConnection;
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "{call " + storedProcName + " (?) }";
myCommand.Parameters.Add ( parameterName , parameterValue );
myCommand.ExecuteNonQuery();

// close the connection down here

the main difference (besides creating variables for parameters etc) is that
I create a generic CommandText string for the stored procedure. This should
work with SQL Server, Oracle and other ODBC compliant databases. I believe
the failure is due to your ommission of the (?) for the parameter. Again,
I'll stress if you're sure you're using SQL Server, it would be better to
use SqlClient and not Odbc.

Let me know how you get on.

Daniel.

 

[Stuart Ferguson]

Hi Daniel,

Yep that does fix the problem, found it on another website.

Thanks for your help

Stuart