passing parms to sql stored procedure

[Phil Hellmuth]

I hope this is the correct forum for this issue. I'm trying to call a
SQL stored procedure using parameters, but am running into problems.
Here's pertinent SP code:

CREATE PROCEDURE dbo.sp_TestSP
( @Field1 nvarchar(11)
, @Field2 nvarchar(30)
, @Field3 nvarchar(30)
)
AS
declare @Field4 int
, @Field5 int

etc...

Here's the code that calls the sp:

cn = New System.Data.Odbc.OdbcConnection(connectionString)
Try
cn.Open()
cmdSP = New System.Data.Odbc.OdbcCommand("sp_TestSP", cn)
cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11).Value =
txtField1.Text
cmdSP.Parameters.Add("@Field2", Odbc.OdbcType.NVarChar, 30).Value =
txtField2.Text
cmdSP.Parameters.Add("@Field3", Odbc.OdbcType.NVarChar,
30).Value = txtField3e.Text
rc = cmdSP.ExecuteNonQuery()

etc....

When executing the SP, I get the following error message:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
'sp_TestSP' expects parameter '@Field1', which was not supplied.

Did I miss a step? I've tried different methods for creating parms, but
nothing seems to work. I'm banging my head against the wall on this.

Thanks in advance for your help.

[rowe_newsgroups]

cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11).Value =

txtField1.Text

Change that to the below (or use .addwithvalue instead of .add to do it
in one step)

cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11)
cmdSP.Parameters("@Field1").Value = txtField1.Text

Thanks,

Seth Rowe
Phil Hellmuth wrote:

I hope this is the correct forum for this issue. I'm trying to call a
SQL stored procedure using parameters, but am running into problems.
Here's pertinent SP code:

CREATE PROCEDURE dbo.sp_TestSP
( @Field1 nvarchar(11)
, @Field2 nvarchar(30)
, @Field3 nvarchar(30)
)
AS
declare @Field4 int
, @Field5 int

etc...

Here's the code that calls the sp:

cn = New System.Data.Odbc.OdbcConnection(connectionString)
Try
cn.Open()
cmdSP = New System.Data.Odbc.OdbcCommand("sp_TestSP", cn)
cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11).Value =
txtField1.Text
cmdSP.Parameters.Add("@Field2", Odbc.OdbcType.NVarChar, 30).Value =
txtField2.Text
cmdSP.Parameters.Add("@Field3", Odbc.OdbcType.NVarChar,
30).Value = txtField3e.Text
rc = cmdSP.ExecuteNonQuery()

etc....

When executing the SP, I get the following error message:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
'sp_TestSP' expects parameter '@Field1', which was not supplied.

Did I miss a step? I've tried different methods for creating parms, but
nothing seems to work. I'm banging my head against the wall on this.

Thanks in advance for your help.

[rowe_newsgroups]

Oh, and after I read through your code againg I realized I told you
wrong. You need to set the command type to StoredProcedure, instead of
the default of Text

Thanks,

Seth Rowe

rowe_newsgroups wrote:

cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11).Value =
txtField1.Text

Change that to the below (or use .addwithvalue instead of .add to do it
in one step)

cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11)
cmdSP.Parameters("@Field1").Value = txtField1.Text

Thanks,

Seth Rowe
Phil Hellmuth wrote:

I hope this is the correct forum for this issue. I'm trying to call a
SQL stored procedure using parameters, but am running into problems.
Here's pertinent SP code:

CREATE PROCEDURE dbo.sp_TestSP
( @Field1 nvarchar(11)
, @Field2 nvarchar(30)
, @Field3 nvarchar(30)
)
AS
declare @Field4 int
, @Field5 int

etc...

Here's the code that calls the sp:

cn = New System.Data.Odbc.OdbcConnection(connectionString)
Try
cn.Open()
cmdSP = New System.Data.Odbc.OdbcCommand("sp_TestSP", cn)
cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11).Value =
txtField1.Text
cmdSP.Parameters.Add("@Field2", Odbc.OdbcType.NVarChar, 30).Value =
txtField2.Text
cmdSP.Parameters.Add("@Field3", Odbc.OdbcType.NVarChar,
30).Value = txtField3e.Text
rc = cmdSP.ExecuteNonQuery()

etc....

When executing the SP, I get the following error message:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
'sp_TestSP' expects parameter '@Field1', which was not supplied.

Did I miss a step? I've tried different methods for creating parms, but
nothing seems to work. I'm banging my head against the wall on this.

Thanks in advance for your help.

[Phil Hellmuth]

I tried your suggestions, but get the same error. Any other thoughts?

rowe_newsgroups wrote:

Oh, and after I read through your code againg I realized I told you
wrong. You need to set the command type to StoredProcedure, instead of
the default of Text

Thanks,

Seth Rowe

rowe_newsgroups wrote:

>> cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11).Value =
txtField1.Text

Change that to the below (or use .addwithvalue instead of .add to do it
in one step)

cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11)
cmdSP.Parameters("@Field1").Value = txtField1.Text

Thanks,

Seth Rowe
Phil Hellmuth wrote:

>>I hope this is the correct forum for this issue. I'm trying to call a
SQL stored procedure using parameters, but am running into problems.
Here's pertinent SP code:

CREATE PROCEDURE dbo.sp_TestSP
( @Field1 nvarchar(11)
, @Field2 nvarchar(30)
, @Field3 nvarchar(30)
)
AS
declare @Field4 int
, @Field5 int

etc...

Here's the code that calls the sp:

cn = New System.Data.Odbc.OdbcConnection(connectionString)
Try
cn.Open()
cmdSP = New System.Data.Odbc.OdbcCommand("sp_TestSP", cn)
cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11).Value =
txtField1.Text
cmdSP.Parameters.Add("@Field2", Odbc.OdbcType.NVarChar, 30).Value =
txtField2.Text
cmdSP.Parameters.Add("@Field3", Odbc.OdbcType.NVarChar,
30).Value = txtField3e.Text
rc = cmdSP.ExecuteNonQuery()

etc....

When executing the SP, I get the following error message:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
'sp_TestSP' expects parameter '@Field1', which was not supplied.

Did I miss a step? I've tried different methods for creating parms, but
nothing seems to work. I'm banging my head against the wall on this.

Thanks in advance for your help.

[Izzy]

If your using SQL Server, then you should use SQL objects. Try this:

Dim cmdSQL As New Data.SqlClient.SqlCommand
Dim conSQL As New Data.SqlClient.SqlConnection(conString)
Dim RowsAffected As Integer

conSQL.Open()

With cmdSQL
.CommandText = "StoredProcedureName"
.CommandType = CommandType.StoredProcedure
.Connection = conSQL
.Parameters.Add("@Parm1", SqlDbType.VarChar, 10).Value =
"foo"
RowsAffected = .ExecuteNonQuery()
End With

You can still use odbc objects in this format as well.


Phil Hellmuth wrote:

I tried your suggestions, but get the same error. Any other thoughts?

rowe_newsgroups wrote:

Oh, and after I read through your code againg I realized I told you
wrong. You need to set the command type to StoredProcedure, instead of
the default of Text

Thanks,

Seth Rowe

rowe_newsgroups wrote:

> cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11).Value =
txtField1.Text
Change that to the below (or use .addwithvalue instead of .add to do it
in one step)

cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11)
cmdSP.Parameters("@Field1").Value = txtField1.Text

Thanks,

Seth Rowe
Phil Hellmuth wrote:
I hope this is the correct forum for this issue. I'm trying to call a
SQL stored procedure using parameters, but am running into problems.
Here's pertinent SP code:

CREATE PROCEDURE dbo.sp_TestSP
( @Field1 nvarchar(11)
, @Field2 nvarchar(30)
, @Field3 nvarchar(30)
)
AS
declare @Field4 int
, @Field5 int

etc...

Here's the code that calls the sp:

cn = New System.Data.Odbc.OdbcConnection(connectionString)
Try
cn.Open()
cmdSP = New System.Data.Odbc.OdbcCommand("sp_TestSP", cn)
cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11).Value =
txtField1.Text
cmdSP.Parameters.Add("@Field2", Odbc.OdbcType.NVarChar, 30).Value =
txtField2.Text
cmdSP.Parameters.Add("@Field3", Odbc.OdbcType.NVarChar,
30).Value = txtField3e.Text
rc = cmdSP.ExecuteNonQuery()

etc....

When executing the SP, I get the following error message:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
'sp_TestSP' expects parameter '@Field1', which was not supplied.

Did I miss a step? I've tried different methods for creating parms, but
nothing seems to work. I'm banging my head against the wall on this.

Thanks in advance for your help.

[Cor Ligthert [MVP]]

Phil,

There is almost no difference by using text SQL transactioncode or a stored
procedure.

The only thing is that you have to tell the SQLCommand that you are using a
storing procedure in the commandtype while the default is text.

http://msdn2.microsoft.com/en-us/lib...mmandtype.aspx

I hope this helps,

Cor
"Phil Hellmuth" <bi*****@pacbell.netschreef in bericht
news:%5*****************@newssvr21.news.prodigy.co m...

>I hope this is the correct forum for this issue. I'm trying to call a SQL
stored procedure using parameters, but am running into problems. Here's
pertinent SP code:

CREATE PROCEDURE dbo.sp_TestSP
( @Field1 nvarchar(11)
, @Field2 nvarchar(30)
, @Field3 nvarchar(30)
)
AS
declare @Field4 int
, @Field5 int

etc...

Here's the code that calls the sp:

cn = New System.Data.Odbc.OdbcConnection(connectionString)
Try
cn.Open()
cmdSP = New System.Data.Odbc.OdbcCommand("sp_TestSP", cn)
cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11).Value =
txtField1.Text
cmdSP.Parameters.Add("@Field2", Odbc.OdbcType.NVarChar, 30).Value =
txtField2.Text
cmdSP.Parameters.Add("@Field3", Odbc.OdbcType.NVarChar,
30).Value = txtField3e.Text
rc = cmdSP.ExecuteNonQuery()

etc....

When executing the SP, I get the following error message:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
'sp_TestSP' expects parameter '@Field1', which was not supplied.

Did I miss a step? I've tried different methods for creating parms, but
nothing seems to work. I'm banging my head against the wall on this.

Thanks in advance for your help.