469,085 Members | 1,035 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,085 developers. It's quick & easy.

Executing SQL Server Stored Procedures using ODBC.NET

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 16 '05 #1
4 5622
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" <st**************@btinternet.com> wrote in message
news:uY**************@TK2MSFTNGP14.phx.gbl...
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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 16 '05 #2
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[i]);
}

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

Many Thanks In Advance

Stuart

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 16 '05 #3
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" <st**************@btinternet.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
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[i]);
}

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

Many Thanks In Advance

Stuart

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 16 '05 #4
Hi Daniel,

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

Thanks for your help

Stuart

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 16 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

26 posts views Thread by David W. Fenton | last post: by
11 posts views Thread by Bob | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.