passing parms to sql stored procedure

**rowe_newsgroups** · Sep 20 '06, 11:35 PM

Re: passing parms to sql stored procedure

cmdSP.Parameter s.Add("@Field1" , Odbc.OdbcType.N VarChar, 11).Value =

txtField1.Text

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

cmdSP.Parameter s.Add("@Field1" , Odbc.OdbcType.N VarChar, 11)
cmdSP.Parameter s("@Field1").Va lue = 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.Odb c.OdbcConnectio n(connectionStr ing)
Try
cn.Open()
cmdSP = New System.Data.Odb c.OdbcCommand(" sp_TestSP", cn)
cmdSP.Parameter s.Add("@Field1" , Odbc.OdbcType.N VarChar, 11).Value =
txtField1.Text
cmdSP.Parameter s.Add("@Field2" , Odbc.OdbcType.N VarChar, 30).Value =
txtField2.Text
cmdSP.Parameter s.Add("@Field3" , Odbc.OdbcType.N VarChar,
30).Value = txtField3e.Text
rc = cmdSP.ExecuteNo nQuery()
>
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** · Sep 20 '06, 11:45 PM

Re: passing parms to sql stored procedure

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.Parameter s.Add("@Field1" , Odbc.OdbcType.N VarChar, 11).Value =
txtField1.Text

>
Change that to the below (or use .addwithvalue instead of .add to do it
in one step)
>
cmdSP.Parameter s.Add("@Field1" , Odbc.OdbcType.N VarChar, 11)
cmdSP.Parameter s("@Field1").Va lue = 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.Odb c.OdbcConnectio n(connectionStr ing)
Try
cn.Open()
cmdSP = New System.Data.Odb c.OdbcCommand(" sp_TestSP", cn)
cmdSP.Parameter s.Add("@Field1" , Odbc.OdbcType.N VarChar, 11).Value =
txtField1.Text
cmdSP.Parameter s.Add("@Field2" , Odbc.OdbcType.N VarChar, 30).Value =
txtField2.Text
cmdSP.Parameter s.Add("@Field3" , Odbc.OdbcType.N VarChar,
30).Value = txtField3e.Text
rc = cmdSP.ExecuteNo nQuery()

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** · Sep 21 '06, 12:15 AM

Re: passing parms to sql stored procedure

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.Parameter s.Add("@Field1" , Odbc.OdbcType.N VarChar, 11).Value =
>>txtField1.Tex t

>Change that to the below (or use .addwithvalue instead of .add to do it
>in one step)
>>
>cmdSP.Paramete rs.Add("@Field1 ", Odbc.OdbcType.N VarChar, 11)
>cmdSP.Paramete rs("@Field1").V alue = 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.Odb c.OdbcConnectio n(connectionStr ing)
>> Try
>> cn.Open()
>> cmdSP = New System.Data.Odb c.OdbcCommand(" sp_TestSP", cn)
>> cmdSP.Parameter s.Add("@Field1" , Odbc.OdbcType.N VarChar, 11).Value =
>>txtField1.Tex t
>> cmdSP.Parameter s.Add("@Field2" , Odbc.OdbcType.N VarChar, 30).Value =
>>txtField2.Tex t
>> cmdSP.Parameter s.Add("@Field3" , Odbc.OdbcType.N VarChar,
>>30).Value = txtField3e.Text
>> rc = cmdSP.ExecuteNo nQuery()
>>>
>>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** · Sep 21 '06, 01:25 AM

Re: passing parms to sql stored procedure

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(c onString)
Dim RowsAffected As Integer

conSQL.Open()

With cmdSQL
.CommandText = "StoredProcedur eName"
.CommandType = CommandType.Sto redProcedure
.Connection = conSQL
.Parameters.Add ("@Parm1", SqlDbType.VarCh ar, 10).Value =
"foo"
RowsAffected = .ExecuteNonQuer y()
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.Parameter s.Add("@Field1" , Odbc.OdbcType.N VarChar, 11).Value =
>txtField1.Te xt
Change that to the below (or use .addwithvalue instead of .add to do it
in one step)
>
cmdSP.Parameter s.Add("@Field1" , Odbc.OdbcType.N VarChar, 11)
cmdSP.Parameter s("@Field1").Va lue = 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.Odb c.OdbcConnectio n(connectionStr ing)
> Try
> cn.Open()
> cmdSP = New System.Data.Odb c.OdbcCommand(" sp_TestSP", cn)
> cmdSP.Parameter s.Add("@Field1" , Odbc.OdbcType.N VarChar, 11).Value =
>txtField1.Te xt
> cmdSP.Parameter s.Add("@Field2" , Odbc.OdbcType.N VarChar, 30).Value =
>txtField2.Te xt
> cmdSP.Parameter s.Add("@Field3" , Odbc.OdbcType.N VarChar,
>30).Value = txtField3e.Text
> rc = cmdSP.ExecuteNo nQuery()
>>
>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]** · Sep 21 '06, 04:35 AM

Re: passing parms to sql stored procedure

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.

SqlCommand.CommandType Property (System.Data.SqlClient)

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtype.aspx

Gets or sets a value indicating how the CommandText property is to be interpreted.

I hope this helps,

Cor

"Phil Hellmuth" <billort@pacbel l.netschreef in bericht
news:%5kQg.2352 $TV3.1228@newss vr21.news.prodi gy.com...

>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.Odb c.OdbcConnectio n(connectionStr ing)
Try
cn.Open()
cmdSP = New System.Data.Odb c.OdbcCommand(" sp_TestSP", cn)
cmdSP.Parameter s.Add("@Field1" , Odbc.OdbcType.N VarChar, 11).Value =
txtField1.Text
cmdSP.Parameter s.Add("@Field2" , Odbc.OdbcType.N VarChar, 30).Value =
txtField2.Text
cmdSP.Parameter s.Add("@Field3" , Odbc.OdbcType.N VarChar,
30).Value = txtField3e.Text
rc = cmdSP.ExecuteNo nQuery()
>
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.

passing parms to sql stored procedure

passing parms to sql stored procedure

Comment

Comment

Comment

Comment

Comment