Procedure xxx expects parameter '@ID' which was not supplied

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

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

using (OdbcCommand cm = new OdbcCommand())
{
cm.CommandText = "sp_StoreData";
cm.CommandType = CommandType.StoredProcedure;
cm.Connection = Connection;

cm.Parameters.Add(new OdbcParameter("@ID", ID));
cm.Parameters.Add(new OdbcParameter("@postcode", postcode));
cm.Parameters.Add(new OdbcParameter("@Address", Address));
cm.Parameters.Add(new OdbcParameter("@hseNum", hseNum));
cm.Parameters.Add(new OdbcParameter("@State", State));

cm.ExecuteNonQuery();
return true;
}

I have this code, and it gives an error which I have no idea how to solve.
Please help.

Thanks in advance

regards,
Andrew
 
Andrew,

First, why use Odbc when connecting to SQL Server? You should be using
the System.Data.SqlClient namespace.

That being said, run an instance of SQL Server profiler and see what
comes in when calling this code. You should see the call and the error in
the profiler and it should help you track down your code.

It looks like you are creating the parameter correctly, but there isn't
enough supporting information to tell.
 
coz I'm testing on SQL Server 7.

any help ?

Thanks in advance

regards,
Andrew


Nicholas Paldino said:
Andrew,

First, why use Odbc when connecting to SQL Server? You should be using
the System.Data.SqlClient namespace.

That being said, run an instance of SQL Server profiler and see what
comes in when calling this code. You should see the call and the error in
the profiler and it should help you track down your code.

It looks like you are creating the parameter correctly, but there isn't
enough supporting information to tell.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Andrew said:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
'sp_StoreData' expects parameter '@ID', which was not supplied.

using (OdbcCommand cm = new OdbcCommand())
{
cm.CommandText = "sp_StoreData";
cm.CommandType = CommandType.StoredProcedure;
cm.Connection = Connection;

cm.Parameters.Add(new OdbcParameter("@ID", ID));
cm.Parameters.Add(new OdbcParameter("@postcode", postcode));
cm.Parameters.Add(new OdbcParameter("@Address", Address));
cm.Parameters.Add(new OdbcParameter("@hseNum", hseNum));
cm.Parameters.Add(new OdbcParameter("@State", State));

cm.ExecuteNonQuery();
return true;
}

I have this code, and it gives an error which I have no idea how to solve.
Please help.

Thanks in advance

regards,
Andrew
Click to expand...
Click to expand...
 
coz I'm testing on SQL Server 7.
Click to expand...

What does that have to do with anything?
any help ?
Click to expand...

He did help. He told you to run SQL Profiler and see exactly what the SQL
statement is that is being passed to the server. It'll look like "EXEC
sp_StoreData <stuff>". The <stuff> is the important part. Copy and paste the
whole command in your next reply, and include the source of the stored
procedure as well.

If you don't know how to use SQL Profiler, feel free to ask, although
ultimately you might be better served in
microsoft.public.dotnet.framework.adonet.
 
coz I'm testing on SQL Server 7.
Click to expand...

Should work. According to Microsoft Courseware (2389), the OleDb client
is needed for Sql Server 6.5, but version 7 should be O.K. with the
SqlClient.
 
This is the sp:

CREATE PROCEDURE sp_StoreData
@ID int,
@postcode int,
@Address varchar(250),
@hseNum varchar(250),
@State varchar(250)
AS

BEGIN

INSERT INTO tblStoreData
(ID, Name, Value)
VALUES
(@ID, 'postcode', @postcode)

INSERT INTO tblStoreData
(ID, Name, Value)
VALUES
(@ID, 'Address', @Address)

INSERT INTO tblStoreData
(ID, Name, Value)
VALUES
(@ID, 'hseNum', @hseNum)

INSERT INTO tblStoreData
(ID, Name, Value)
VALUES
(@ID, 'State', @State)

END

if @@ERROR <> 0
begin
rollback tran
raiserror ('Unable to insert tblStoreData', 11, 1)
return
end
GO

--
Thanks in advance

regards,
Andrew
 
Andrew,

What is the trace when you run the profiler and execute your code
against the server?
 
This is the sp:

CREATE PROCEDURE sp_StoreData
@ID int,
@postcode int,
@Address varchar(250),
@hseNum varchar(250),
@State varchar(250)
AS

BEGIN

INSERT INTO tblStoreData
(ID, Name, Value)
VALUES
(@ID, 'postcode', @postcode)

INSERT INTO tblStoreData
(ID, Name, Value)
VALUES
(@ID, 'Address', @Address)

INSERT INTO tblStoreData
(ID, Name, Value)
VALUES
(@ID, 'hseNum', @hseNum)

INSERT INTO tblStoreData
(ID, Name, Value)
VALUES
(@ID, 'State', @State)

END

if @@ERROR <> 0
begin
rollback tran
raiserror ('Unable to insert tblStoreData', 11, 1)
return
end
GO

--
Thanks in advance

regards,
Andrew
Click to expand...

Note: If I am not mistaken, the @@error gets reset on every command.
This means that your test only really tests the *last* insert (of
'state').

Hans Kesting
 
I changed the code to:

using (OdbcCommand cm = new OdbcCommand("{call sp_StoreData
(?,?,?,?,?,?,?,?,?,?)}", Connection))


It works. Thanks.

regards,
Andrew
 
I changed the code to:

using (OdbcCommand cm = new OdbcCommand("{call sp_StoreData
(?,?,?,?,?,?,?,?,?,?)}", Connection))


It works. Thanks.
Click to expand...

Wow. Coming from the ADO/RDO/DAO world, that was actually the first thing
that popped into my mind (using the ODBC syntax), but I didn't know if it
still applied to ADO.NET. Now I do.
 
Back
Top