CODEWITHSUNDEEP
sqlsql-servervb.netstored-procedures
TheIronCheek
TheIronCheek

Reputation: 1149

VB.NET - Stored procedure runs but parameters aren't working

I have the following bit of code:

Dim SQLCon As New SqlConnection
Dim cmd As New SqlCommand
Dim ds As New DataSet

SQLCon.ConnectionString = ConfigurationSettings.AppSettings("myConnString")
SQLCon.Open()
cmd.CommandType = CommandType.StoredProcedure

'run the stored procedure based on the view selected
If rdolstView.Items(0).Selected Then
    cmd = New SqlCommand("spCondensedView", SQLCon)
ElseIf rdolstView.Items(1).Selected Then
    cmd = New SqlCommand("spExtendedView", SQLCon)
End If

'filter by what the user searched for
If ddlSearchBy.SelectedValue = "Member" Then
    cmd.Parameters.AddWithValue("@MbrNum", txtSearchFor.Text)
ElseIf ddlSearchBy.SelectedValue = "Assistant" Then
    cmd.Parameters.AddWithValue("@AssignedAsst", ddlUWAssistants.SelectedValue)
ElseIf ddlSearchBy.SelectedValue = "Rep" Then
    cmd.Parameters.AddWithValue("@Rep", txtSearchFor.Text)
ElseIf ddlSearchBy.SelectedValue = "Dept Assistant" Then
    cmd.Parameters.AddWithValue("@DeptAsst", txtSearchFor.Text)
ElseIf ddlSearchBy.SelectedValue = "Creator" Then
    cmd.Parameters.AddWithValue("@Creator", txtSearchFor.Text)
End If

Dim da As New SqlDataAdapter(cmd)
da.Fill(ds)
SQLCon.Close()

My problem is that the parameters don't seem to be working. Both stored procedures are supposed to take the optional parameter (either @MbrNum, @AssignedAsst, @Rep, @DeptAsst, or @Creator) and filter by it in its WHERE clause.

I've confirmed that this is working properly when I run the stored procedures manually in SQL Server Management Studio. I've also confirmed that the If/ElseIf statements are validating as true properly. So my code is definitely hitting the AddWithValue() statements when it's supposed to.

My returned result, however, is the full dataset without the filters applied, as if I ran the stored procedure with no parameters specified.

Any help would be awesome. Thanks!

Upvotes: 0

Views: 347

Answers (1)

Trevor
Trevor

Reputation: 8004

Try specifying the a Command.Type...Set this when you create the command object. 

 cmd.CommandType = CommandType.StoredProcedure

By default it's set as Text I believe... Also wrap your connection and command's in Using statements so they are properly handled when done...

Upvotes: 2

Related Questions