Calling a parameterized stored proc with adtaper or command obj

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.AppSettings("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.
 
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.
 
Back
Top