problem executing sproc from ASP(ADO).NET

ADO >> problem executing sproc from ASP(ADO).NET can anyone tell me why the following will not work or how to fix it. This
works fine for executing sprocs with no parms but fails (always returns -1)
when executing with parms.

from webform:
Private Sub Find_Button_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Find_Button.Click
Dim resultDS As DataSet
Dim retVal As Integer
Dim parmArray(2) As SqlClient.SqlParameter

parmArray(0) = New SqlClient.SqlParameter("@SEARCHTYPE",
SqlDbType.VarChar)
parmArray(0).Value = SearchType.SelectedValue
parmArray(1) = New SqlClient.SqlParameter("@SEARCHTERM",
SqlDbType.VarChar)
parmArray(1).Value = SearchTerm.Text
parmArray(2) = New SqlClient.SqlParameter("@INC_CLOSED",
SqlDbType.VarChar)
parmArray(2).Value = UCase(IncClosed.Checked.ToString)

Call ExecSP("PROC_MASTER_SEARCH", retVal, parmArray, resultDS)

DataGrid_Results.DataSource = resultDS
DataGrid_Results.DataBind()
End Sub

from db access module (adapted from Using Visual Basic.Net by Siler and
Spotts)

Private Sub PopulateCommandParms(ByVal parmArray() As SqlParameter, ByRef
cmd As SqlCommand)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'adds each parameter to the command object

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim i As Integer
For i = LBound(parmArray) To UBound(parmArray)
cmd.Parameters.Add(parmArray(i))
Next
End Sub

Private Sub PopulateOutputParms(ByVal cmd As SqlCommand, ByRef
parmArray() As SqlParameter)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'copies output parameter values back into the array
'assumes parameter array is already populated

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim i As Integer
For i = LBound(parmArray) To UBound(parmArray)
If parmArray(i).Direction = ParameterDirection.Output Then
parmArray(i).Value =
cmd.Parameters(parmArray(i).ParameterName).Value
End If
Next
End Sub

Public Sub ExecSP(ByVal procName As String, ByRef retVal As Integer, _
Optional ByRef parmArray() As SqlParameter = Nothing, _
Optional ByRef resultDS As DataSet = Nothing)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'executes stored procedures returning output parameters or filling a
dataset

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim dbConn As SqlConnection
Dim dbCmd As SqlCommand
Dim dbAdp As SqlDataAdapter

'open the connection
dbConn = New SqlConnection
dbConn.ConnectionString = GetConnectionString()
dbConn.Open()

'create the command object
dbCmd = New SqlCommand
dbCmd.CommandText = procName
dbCmd.CommandType = CommandType.StoredProcedure
dbCmd.Connection = dbConn

'add parameters to the connection object if they exist
If Not (parmArray Is Nothing) Then
Call PopulateCommandParms(parmArray, dbCmd)
End If

'execute the command
If resultDS Is Nothing Then
retVal = dbCmd.ExecuteNonQuery
Else
resultDS = New DataSet
dbAdp = New SqlDataAdapter
dbAdp.SelectCommand = dbCmd
retVal = dbAdp.Fill(resultDS)
End If

'close the connection
dbConn.Close()

'populate output parameters
If Not (parmArray Is Nothing) Then
Call PopulateOutputParms(dbCmd, parmArray)
End If
End Sub

DotNet245

ADO >> problem executing sproc from ASP(ADO).NET Mike,

Have you ever *tried* it as just a normal commandtext SQL string
That makes testing much easier

I hope this helps,

Cor

ADO >> problem executing sproc from ASP(ADO).NET No. Haven't tried that but I did just figure out the problem. I created
my DataSet but failed to instantiate it. Thanks for the suggestion.

Mike

"Cor Ligthert" <EMail@HideDomain.com> wrote in message
news:EMail@HideDomain.com...
> Mike,
>
> Have you ever *tried* it as just a normal commandtext SQL string
> That makes testing much easier
>
> I hope this helps,
>
> Cor
>