Calling a parameterized stored proc with adtaper or command obj

[Eric Land]

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.Confi gurationSettings.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.

[Chandra Sekhar]

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.Confi gurationSettings.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 La**@discussions.microsoft.com> wrote in message
news:32**********************************@microsof t.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.Confi gurationSettings.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.