Help witih Search Parameters

[westerncj]

Can someone help me with wiring up my search parameters. I have txtboxes on my webpage and a search button. So far I can only search for an ID. How can I expand this to include searching for AssignedTo, Location, Department, Model, InkType, Status, AquiredDate?

Here is my code. Can any one help? Do I just keep adding parameters? How does the DataKeyNames work? I want to able to search by all the fields in my where statement and not always have to put in an ID...

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        ' Define data objects
        Dim conn As SqlConnection
        Dim comm As SqlCommand
        Dim reader As SqlDataReader
        ' Initialize connection
        Dim connectionString As String = _
            ConfigurationManager.ConnectionStrings( _
            "PrinterTracking").ConnectionString
        conn = New SqlConnection(connectionString)
        ' Create command
      comm = New SqlCommand( _
          "SELECT ID, AssignedTo, Location, Department, Model, InkType, Status, AquiredDate " & _
          "FROM PrinterInfo WHERE ID LIKE @txtID", conn)
        ' Verify the ID is numeric
      Dim ID As Integer
        If (Not Integer.TryParse(txtID.Text, ID)) Then
            ' If the user didn't enter numeric ID...
         'userLabel.Text = "Please enter a numeric ID!"
        Else
            ' Add parameter
         comm.Parameters.Add("@txtID", System.Data.SqlDbType.Int)
         comm.Parameters("@txtID").Value = ID
            ' Enclose database code in Try-Catch-Finally
            Try
                ' Open the connection
                conn.Open()
                ' Execute the command
                reader = comm.ExecuteReader()
                ' Display the requested data
                If reader.HasRows() Then
                    ' Fill the grid with data
                    grid.DataSource = reader
               grid.DataKeyNames = New String() {"ID"}
                    grid.DataBind()
                    userLabel.Text = ""
                Else
                    userLabel.Text = _
                        "There is no user with this ID: " & ID
                End If
                ' Close the reader and the connection
                reader.Close()
            Catch
                ' Display error message
                userLabel.Text = "Error retrieving user data."
            Finally
                ' Close the connection
                conn.Close()
            End Try
        End If
    End Sub

[hericles]

Yep, just add more parameters, one for each column you want to include in the search.

[westerncj]

Can you show me an example? I'm not sure how the DataKeyNames work and what they are used for...

[hericles]

You don't need that to do the SQL query. For each other column you want to include in the query add another OR WHERE column = ?parameter

[westerncj]

Ok I added an AssignedTo Parameter but now I get an error

An SqlParameter with ParameterName '@txtAssignedTo' is not contained by this SqlParameterCollection.

 Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        ' Define data objects
        Dim conn As SqlConnection
        Dim comm As SqlCommand
        Dim reader As SqlDataReader
        ' Initialize connection
        Dim connectionString As String = _
            ConfigurationManager.ConnectionStrings( _
            "PrinterTracking").ConnectionString
        conn = New SqlConnection(connectionString)
        ' Create command
      comm = New SqlCommand( _
          "SELECT ID, AssignedTo, Location, Department, Model, InkType, Status, AquiredDate " & _
          "FROM PrinterInfo WHERE ID LIKE @txtID OR AssignedTo LIKE @txtAssignedTo", conn)
        ' Verify the ID is numeric
      Dim ID As Integer
      Dim AssignedTo As String
        If (Not Integer.TryParse(txtID.Text, ID)) Then
            ' If the user didn't enter numeric ID...
         'userLabel.Text = "Please enter a numeric ID!"
        Else
            ' Add parameter
         comm.Parameters.Add("@txtID", System.Data.SqlDbType.Int)
         comm.Parameters("@txtID").Value = ID
         comm.Parameters.Add("@AssignedTo", System.Data.SqlDbType.Char)
         comm.Parameters("@txtAssignedTo").Value = AssignedTo.ToString
            ' Enclose database code in Try-Catch-Finally
            Try
                ' Open the connection
                conn.Open()
                ' Execute the command
                reader = comm.ExecuteReader()
                ' Display the requested data
                If reader.HasRows() Then
                    ' Fill the grid with data
                    grid.DataSource = reader
               grid.DataKeyNames = New String() {"ID"}
                    grid.DataBind()
                    userLabel.Text = ""
                Else
                    userLabel.Text = _
                        "There is no user with this ID: " & ID
                End If
                ' Close the reader and the connection
                reader.Close()
            Catch
                ' Display error message
                userLabel.Text = "Error retrieving user data."
            Finally
                ' Close the connection
                conn.Close()
            End Try
        End If
    End Sub

[hericles]

You have a typo, thats all:

comm.Parameters.Add("@AssignedTo", System.Data.SqlDbType.Char)
comm.Parameters("@txtAssignedTo").Value = AssignedTo.ToString

The first line should be txtAssignedTo.

[westerncj]

Ok thanks. I fixed my typo but now I get this error: Parameterized Query '(@txtID int,@txtAssignedTo char(8000))SELECT ID, AssignedTo, Loc' expects parameter @txtAssignedTo, which was not supplied.

Source Error:

Line 75:                 conn.Open()
Line 76:                 ' Execute the command
Line 77:                 reader = comm.ExecuteReader()
Line 78:                 ' Display the requested data
Line 79:                 If reader.HasRows() Then

What am I doing right now?