SQL Stored Procedures With Parameters

[VBrian]

I am currently building an n-tier application in VB.Net. I am using SQL Server 2000 as the database. I have created a simple stored procedure which returns a result set of two linked tables when given a single parameter value of "@SalesOrderID". This procedure works fine when calling it within SQL Server. I am using the results to fill a dataset object. When I call this same procedure from within VB.Net I get no returned results. I have experimented with editing the stored procedure to return all values with no filtering and then calling it from within VB and I get a complete dataset containing all inner joined values but as soon as I try to add the parameter value I get nothing. I have tried several different approaches which do not result in any errors but still return no values. Below is the code I am currently trying. (Error handling removed) Any help would be greatly appreciated.

Public Function GetSalesInfo() As DataSet
Dim data As New SalesOrderInfoData()'(Builds Table in Business Tier)

Dim da As New SqlDataAdapter()

da.TableMappings.Add("Table", "hpo_getsalesorderinfo")

With da

.SelectCommand = New SqlCommand("hpo_getsalesorderinfo @SalesOrderID = & '" (Parameter Value) & "'", New SqlConnection(HPOpsConfig.ConnectionString))

.Fill(data)

data.AcceptChanges()Return data

End With

End Function

I've also tried the following with the same results.

Public Function GetSalesInfo() As DataSet
Dim data As New SalesOrderInfoData()'(Builds table in business tier)

Dim da As New SqlDataAdapter()

da.TableMappings.Add("Table", "hpo_getsalesorderinfo")

With da

.SelectCommand = New SqlCommand("hpo_getsalesorderinfo", New SqlConnection(HPOpsConfig.ConnectionString))

.SelectCommand.CommandType = CommandType.StoredProcedure

.SelectCommand.Parameters.Add(New SqlParameter(SALESORDERID_PARM, SqlDbType.VarChar, 10))

.SelectCommand.Parameters(SALESORDERID_PARM).Value = (Parameter Value)

.Fill(data)

data.AcceptChanges()

Return data

End With

End Function

I have tested the value being passed to the stored procedure within the debug "Command Window" and in both cases I get the following results when placing a stop on ".fill(data)

?da.selectcommand.commandtext
"hpo_getsalesorderinfo @SalesOrderID = '041223-007'"

(041223-007 is the correct parameter value for this scenario and a corresponding value does exist in the database)

Thanks for your help,

VBrian

 

[VBrian]

Stop the press! I've made a giant foopa! As I went back to the problem (right after initiating this thread, of course), I discovered that the error was in the database, not the code. I am using a temporary database while writing the application and discovered that there were records which existed in one of the tables which had no corresponding record in the linked table. It just so happens that I was selecting those records with no match. When I went back to make another attempt I inadvertently selected a valid record and everything worked great! The working code was that used in the FIRST example of my original post (Just in case anyone else runs onto this type of problem). Sorry to bother.

(Note: the record used in the original post (041223-007) only existed in one of the two linked tables. This was the cause of the error.)