469,649 Members | 1,407 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,649 developers. It's quick & easy.

passing parms to sql stored procedure

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.
Sep 20 '06 #1
5 1850
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.
Sep 20 '06 #2
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.
Sep 20 '06 #3
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.
Sep 21 '06 #4
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.
Sep 21 '06 #5
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.

Sep 21 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by dataguy | last post: by
6 posts views Thread by Paul M | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.