Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: Handling Multiple Search Criteria

    Hello,

    I am working on SQL Server in VB 2008. I have a table 'Records' having 8 columns. I have a search page where I can choose 5 different parameters to search as 'Category' , 'Name' , 'Date' etc.

    I can successfully search with a single criteria selected either Category Name Or Date. But I want to create a single SQL command that can search my 'Records' table for either two or all the parameters depending on the selections made by the user.

    Thanks
    Last edited by najamfazal; 03-20-12 at 04:25.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:, assuming @columnX = NULL or has the search value.
    Code:
    SELECT ...
    FROM DaTable
    WHERE column1 = COALESCE(@column1, column1) AND
    	column2 = COALESCE(@column2, column2) AND
    	....
    	column8 = COALESCE(@column8, column8)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Mar 2012
    Posts
    3
    Sorry for the late reply,
    I tried it but it will only return records if i fill all parameters. Say, i enter "Normal" in category textbox and leave other textboxes blank, it will not return any results because of "AND". Any more ideas?

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I tried it but it will only return records if i fill all parameters.
    As I wrote in my post, you should initialise all those variables to NULL, if you don't give them a real value. And you must DECLARE them before you can use them.

    Tell me if this works:
    Code:
    DECLARE @column1 youdatatype
    DECLARE @column2 youdatatype
    DECLARE @column3 youdatatype
    DECLARE @column4 youdatatype
    DECLARE @column5 youdatatype
    DECLARE @column6 youdatatype
    
    SET @column1 = NULL
    SET @column2 = NULL
    SET @column3 = NULL
    SET @column4 = NULL
    SET @column5 = NULL
    SET @column6 = NULL
    
    SET @column1 = aRealisticValue
    SET @column2 = aRealisticValue
    SET @column3 = aRealisticValue
    SET @column4 = aRealisticValue
    SET @column5 = aRealisticValue
    SET @column6 = aRealisticValue
    
    --execute my first query
    ....
    First replace "youdatatype" by the appropriate data type for the different columns (INT, DATETIME, CHAR(10), ...).

    Then replace "aRealisticValue" by some realistic value for that columns (16, '2012-03-25', 'Normal', ...). Verify (1) that the query returns at least one record for the combination of the values you just entered.

    Run my query. It should give the record(s) you verified (1).
    Start replacing aRealisticValue by NULL, one by one, you should still have the records from (1) in the result set.

    Say, i enter "Normal" in category textbox and leave other textboxes blank, it will not return any results because of "AND". Any more ideas?
    replace
    Code:
    WHERE column1 = COALESCE(@column1, column1) AND
    by
    Code:
    WHERE COALESCE(column1, -1) = COALESCE(@column1, column1, -1) AND
    for all columns that are not mandatory. I assumed the data type of column1 is an INT. In case of an INT, you could use -1 as default value, for a string you could use '"&(!'. Use values that do not occur in that column in the database (and that are compatible with the data type of that column).
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Mar 2012
    Posts
    3
    For now, I am searching the database for two parameters
    1. Category
    2. Entity
    User can select either Category or Entity or both.

    I tried your solution, please tell me where i am going wrong?

    Code:
    'Capture Searching Parameters
            Dim category As String = ComboBox1.Text
            Dim entity As String = ComboBox2.Text
    
            'Clearing up the result area
            DataGridView1.Rows.Clear()
            DataGridView1.ColumnCount = 5
    
    
            Dim sqlConnection As New SqlConnection
            sqlConnection.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"
    
            sqlConnection.Open()
    
            Dim command As String = "SELECT * FROM Records WHERE COALESCE(ID,-1) = COALESCE(@id,ID,-1) AND " & _
                                    "COALESCE(Category,'&(!') = COALESCE(@cat,Category,'&(!') AND " & _
                                    "COALESCE(Entity,'&(!') = COALESCE(@en,Entity,'&(!') AND " & _
                                    "COALESCE(Amount,-1) = COALESCE(@amount,Amount,-1) AND " & _
                                    "COALESCE(Date,'1950-1-1') = COALESCE(@dt,Date,'1950-1-1')"
    
            Dim sqlCommand As New SqlCommand(command, sqlConnection)
    
            'Declaring Variables
            sqlCommand.Parameters.Add("@id", SqlDbType.Int, 5)
            sqlCommand.Parameters.Add("@cat", SqlDbType.NVarChar, 50)
            sqlCommand.Parameters.Add("@en", SqlDbType.NVarChar, 50)
            sqlCommand.Parameters.Add("@amount", SqlDbType.Int, 10)
            sqlCommand.Parameters.Add("@dt", SqlDbType.Date, 10)
    
            'Initialize variables
            sqlCommand.Parameters("@id").Value = 0
            sqlCommand.Parameters("@cat").Value = "NULL"
            sqlCommand.Parameters("@en").Value = "NULL"
            sqlCommand.Parameters("@amount").Value = 0
            sqlCommand.Parameters("@dt").Value = "1-1-1950"
    
            'Giving a realistic values
            sqlCommand.Parameters("@cat").Value = category
            sqlCommand.Parameters("@en").Value = entity
    
            Dim sqlReader As SqlDataReader = sqlCommand.ExecuteReader
            Dim row(5) As Object
    
            Try
                While sqlReader.Read
                    sqlReader.GetValues(row)
                    DataGridView1.Rows.Add(row)     'Populate result datagrid
                End While
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
    
            sqlConnection.Close()

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I don't know .Net, Can't really help you with that. Though I think at least part of the problem lies with :
    Code:
            'Initialize variables
            sqlCommand.Parameters("@id").Value = 0
            sqlCommand.Parameters("@cat").Value = "NULL"
            sqlCommand.Parameters("@en").Value = "NULL"
            sqlCommand.Parameters("@amount").Value = 0
            sqlCommand.Parameters("@dt").Value = "1-1-1950"
    I have told you to give all variables initially the value NULL. You didn't do that.

    Suppose someone tells you to write nothing on the freshly painted wall. Next you take a permanent marker and you write the word "nothing" in big black letters on the wall. Do you think that person will be pleased?

    You are doing exactly that with
    sqlCommand.Parameters("@en").Value = "NULL"

    It should be something like
    sqlCommand.Parameters("@en").Value = DbNull.Value

    Do the same with all the other parameters.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •