Re: Calling a parameterized stored proc with adtaper or command obj

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Chandra Sekhar (Chandra.Shekhar_at_am.joneslanglasalle.com)
Date: 08/07/04 

Date: Sat, 7 Aug 2004 09:50:03 -0500

If the Session("ContactID") is nothing then the parameter would be passed in
as Default and must be handled in the stored proc - if not, you will get the
parameter not found error.

You may try this:

 If Not Page.IsPostBack Then
            vendorID = Session("VendorID") ' As is this

            Dim Connection As New
SqlClient.SqlConnection(System.Configuration.ConfigurationSettings.AppSettin
gs("PurchaseDB"))
            MyComm = New SqlClient.SqlCommand
            MyComm.Connection = Connection

            MyComm.Parameters.Add("@Contact_ID", Session("ContactID"))
' You may even try hardcoded values to verify if the stored proc works:
' MyComm.Parameters.Add("@Contact_ID", 45)

            MyComm.CommandType = CommandType.StoredProcedure
            MyComm.CommandText = "spGetContact"

            myAdapter = New SqlClient.SqlDataAdapter(MyComm.CommandText,
Connection)
            dsOne = New System.Data.DataSet

            Connection.Open()
            myAdapter.Fill(dsOne) ' It crahses here every time.
            Connection.Close()

Sekhar.

"Eric Land" <Eric Land@discussions.microsoft.com> wrote in message
news:32CF0FEC-66F6-40CC-88B8-EBF6FA9139F3@microsoft.com...
> Help! I'm trying to call a parameterized stored proc in ASP.NET in VB. I
am
> creating a command object and creating a parametr list, and assigning a
value
> from a session variable (this is working) so that I can sen in the value
as a
> parameter. When I try to use the adapter's fill command to fill the data
set,
> it get a error tellig me that I am not giving the stored procedure the
> parameter that it expects, even though I am! I've looked through some of
my
> old code that works, and it still doesn't seem to make sense.
>
> Here is what I have:
>
> If Not Page.IsPostBack Then
> contactID = Session("ContactID") 'This is a valid value!
> vendorID = Session("VendorID") ' As is this
>
> Dim Connection As New
>
SqlClient.SqlConnection(System.Configuration.ConfigurationSettings.AppSettin
gs("PurchaseDB"))
> MyComm = New SqlClient.SqlCommand
> MyComm.Connection = Connection
>
> MyComm.Parameters.Add(New
SqlClient.SqlParameter("@Contact_ID",
> SqlDbType.Int, 4)).Direction = ParameterDirection.Input
> MyComm.Parameters("@Contact_ID").Value = contactID
>
> MyComm.CommandType = CommandType.StoredProcedure
> MyComm.CommandText = "spGetContact"
>
> myAdapter = New SqlClient.SqlDataAdapter(MyComm.CommandText,
> Connection)
> dsOne = New System.Data.DataSet
>
> Connection.Open()
> myAdapter.Fill(dsOne) ' It crahses here every time.
> Connection.Close()
>
> Am I missing something? Does this not work with an adapter?
>
> I've also tried just using the MyComm.ExecuteNonQuery(), but then I don't
> know how to get at the record set that the stroed proc returns. I have
about
> 12 different item that get returned and I thought that a data set would be
> best here, but It doesn't think it's getting the parameter values for some
> reason.

Quantcast