passing parms to sql stored procedure

  • Thread starter Thread starter Phil Hellmuth
  • Start date Start date
P

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.
 
cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11).Value =
txtField1.Text
Click to expand...

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 said:
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.
Click to expand...
 
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 said:
cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11).Value =
txtField1.Text
Click to expand...

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 said:
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.
Click to expand...
Click to expand...
 
I tried your suggestions, but get the same error. Any other thoughts?

rowe_newsgroups said:
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 said:
cmdSP.Parameters.Add("@Field1", Odbc.OdbcType.NVarChar, 11).Value =
txtField1.Text
Click to expand...
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
I tried your suggestions, but get the same error. Any other thoughts?

rowe_newsgroups said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top