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

Recommended Answers

All 6 Replies

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

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

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

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

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.

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?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.