Parameter Problem with Stored Procedures -- dotnet datatools

RON
7/14/2006 12:23:01 PM

Using .NET 1.1; SQL Server 2000.

Here' s a simple SP:
CREATE PROCEDURE dbo.atest_INS
@col1 char(20),
@col2 int output
AS
INSERT INTO test(
col1)
VALUES ( @col1 )
Select @col2 = @@Identity

Here's the .NET code:
Dim objConnection As SqlClient.SqlConnection = New
SqlClient.SqlConnection("Data Source=XXX;Integrated Security=SSPI;Initial
Catalog=XXX")

Dim sqlcmd As New SqlClient.SqlCommand
With sqlcmd
.Connection = objConnection
.CommandType = CommandType.StoredProcedure
.CommandText = "atest_INS"
.Parameters.Add("col1", "BLAHLBAHBLAH")
.Parameters.Add("col2", 0)
.Connection.Open()
.ExecuteNonQuery()
end with

I get error 8145: co11 not a parameter for atest_INS.

I've tried similar statements with other SPs and get the same thing: the
first parameter is not a parameter.

I know it's something very simple, just can't figure out what.

THANKS!! FOR ANY HELP!

Brendan Green
7/17/2006 12:00:00 AM

You need to include the "@" when adding the parameters (see below).

Also, if you want to use @col2 as an output parameter, you'll need to set
its Direction.

Dim sqlcmd As New SqlClient.SqlCommand
With sqlcmd
.Connection = objConnection
.CommandType = CommandType.StoredProcedure
.CommandText = "atest_INS"
.Parameters.Add("@col1", "BLAHLBAHBLAH")
.Parameters.Add("@col2", 0)
.Connection.Open()
.ExecuteNonQuery()
end with

[quoted text, click to view]

dotnet datatools : Parameter Problem with Stored Procedures