Home All Groups Group Topic Archive Search About

passing parms to sql stored procedure

microsoft.public.dotnet.languages.vb
Author
20 Sep 2006 11:25 PM
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.

Author
20 Sep 2006 11:49 PM
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:
Show quoteHide quote
> 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.
Author
21 Sep 2006 12:00 AM
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:
Show quoteHide quote
> >         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.
Author
21 Sep 2006 12:28 AM
Phil Hellmuth
I tried your suggestions, but get the same error.  Any other thoughts?

rowe_newsgroups wrote:
Show quoteHide quote
> 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.
>
Author
21 Sep 2006 1:34 AM
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:
Show quoteHide quote
> 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.
> >
Author
21 Sep 2006 4:48 AM
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/library/system.data.sqlclient.sqlcommand.commandtype.aspx

I hope this helps,

Cor


Show quoteHide quote
"Phil Hellmuth" <bill***@pacbell.net> schreef in bericht
news:%5kQg.2352$TV3.1228@newssvr21.news.prodigy.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.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.



Post Other interesting topics
Slow startup of Deployed Application
How to reset Windows Application's default programmatically?
What is the Best Icon Maker?
Display Crystal Report
Problem Adding new record to a database
Data Access
Image list controls and image resources
sqldatareader padding varchar fields with extra white space
Tad bit of combo box newbie help, please!
Issues with scope on a text box after .net conversion from vb6