Issue w/ Returning Parameter 1

[Compkitty]

I am creating a stored procedure that returns the numbers of records found based on the number of parameters the user inputs into the form.
there are 9 boxes they can fill in(at least 1, up to 9)
I got the stored procedure working, but now I am trying to add the parameters into the Ok Button. When I run it though I'm not getting any return number... Can anyone help?? THANKS

Private Sub BtnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnOk.Click
        Dim cliId, AcctNum, Last, First As String
        cliId = Me.txtCliId.Text
        AcctNum = Me.txtAcctNum.Text
        Last = Me.txtCLName.Text
        First = Me.TxtCFName.Text

        Da.SelectCommand.Parameters("@CliId").Value = cliId
        Da.SelectCommand.Parameters("@AcctNum").Value = DBNull.Value
        Da.SelectCommand.Parameters("@CLName").Value = DBNull.Value
        Da.SelectCommand.Parameters("@CFName").Value = DBNull.Value
        Da.SelectCommand.Parameters("@Custodian").Value = DBNull.Value
        Da.SelectCommand.Parameters("@MMGr").Value = DBNull.Value
        Da.SelectCommand.Parameters("@Advisor").Value = DBNull.Value
        Da.SelectCommand.Parameters("@PA").Value = DBNull.Value
        Da.SelectCommand.Parameters("@Status").Value = DBNull.Value
        Da.SelectCommand.Parameters("@Rc").Value = DBNull.Value

        SqlConn.Open()
        Da.SelectCommand.ExecuteNonQuery()
        SqlConn.Close()

        Dim Rc As Integer
        Da.SelectCommand.Parameters("@Rc").Value = Rc

        If Rc < 1 Then
            Response.Redirect("NoClientPage.aspx")
        ElseIf Rc = 1 Then
            Response.Redirect("Broadcast.aspx")
        Else
            Response.Redirect("AcctsMany.aspx")
        End If
    End Sub

Any help would be appreciated...

 

[huel]

Hi,

You need to specify "@Rc" as a return value, if I'm reading your code correctly.

Da.SelectCommand.Parameters("@Rc").Direction = ParameterDirection.ReturnValue
Da.SelectCommand.Parameters("@Rc").Value = DBNull.Value

This will tell the data adapter that your stored procedure is returning a value, and that the returned value will be stored in the parameter "@Rc".

Jay

 

[Compkitty]

Actually I have Rc set as an Output.. Should I change it to a return value??

 

[RiverGuy]

According to your code, you are declaring all parameters, executing the command, then setting the Rc parameter again to an integer which is initialized at zero.

You need to say Rc = TheParameter, instad of TheParameter = Rc.

 

[Compkitty]

THAT WORKS...THANKS...

 

[Compkitty]

Okay one more question..I think

When I'm doing a search w/ a Stored procedure.. say I have a textbox and I input "Alliance" and it should return 3 records, but the records don't have just Alliance in them.. how does this work?
I have my stored procedure like

WHERE Field = COALESCE(@Parameter, Field)
how do I get it to return any records w/ any of the inputs in it???

THANKS

 

[chiph]

Compkitty -
This is a separate question from your original one. Can you post it in another thread? Otherwise people reading the forum won't know that a topic named Issue w/ Returning Parameter has anything to do with outer joins.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first