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

[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

 

[Nicholas Paldino [.NET/C# MVP]]

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.

 

[Andrew]

coz I'm testing on SQL Server 7.

any 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.

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

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

 

[Jeff Johnson]

coz I'm testing on SQL Server 7.

What does that have to do with anything?

any help ?

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.

 

[Alberto Poblacion]

coz I'm testing on SQL Server 7.

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.

 

[Andrew]

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

 

[Nicholas Paldino [.NET/C# MVP]]

Andrew,

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

 

[Hans Kesting]

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

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

 

[Andrew]

I changed the code to:

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


It works. Thanks.

regards,
Andrew

 

[Jeff Johnson]

I changed the code to:

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


It works. Thanks.

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.