Skip to content
Advertisement

How make code for multiple search at the time in vb.net, SQL Server

For the last three months, I worked on code creating a single searching code in vb.net but I need help to make multiple search at the time like for ex:- I search in database using vb.net is done but then I need search shart + red olor is also done, then I need search shart + red color + brand + style and many more.

My main problem is I search multiple any, I need shart + red color + brand + style formal so how can i done. suggest me … check img for better understanding

search multiple at time

My single search code:

 Private Sub loadsearch_product_article(ByVal records As String)
    Try
        conn.Open()
        TabControl1.SelectedTab = TabPage2
        Dim cmd As New SqlCommand("select * from purchase_item_table2 where artical_no like '%" & txtPR_artical_no.Text & "%'", conn)
        Dim sdr As SqlDataReader = cmd.ExecuteReader()
        DataGridView1.Rows.Clear()
        While sdr.Read()

           DataGridView1.Rows.Add(sdr(0), sdr(1), sdr(2), sdr(3), sdr(4), sdr(7), sdr(8), sdr(9), sdr(10), sdr(11), sdr(12), sdr(13), sdr(14), sdr(15), sdr(16), sdr(17), sdr(18), sdr(19), sdr(21), sdr(22), sdr(23), sdr(24), sdr(25), sdr(26), sdr(27), sdr(28), sdr(29), sdr(30), sdr(31))
        End While
        conn.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
        conn.Close()
    End Try

End Sub

Advertisement

Answer

Here is an example of a query that allows you to search using zero, one or more parameters:

Dim sql = "SELECT *
FROM MyTable
WHERE (@Column1 IS NULL OR Column1 = @Column1)
AND (@Column2 IS NULL OR Column2 = @Column2)
AND (@Column3 IS NULL OR Column3 = @Column3)"

Dim command As New SqlCommand(sql, connection)

With command.Parameters
    .Add("@Column1", SqlDbType.VarChar, 50).Value = If(TextBox1.TextLength = 0, CObj(DBNull.Value), TextBox1.Text)
    .Add("@Column2", SqlDbType.VarChar, 50).Value = If(TextBox2.TextLength = 0, CObj(DBNull.Value), TextBox2.Text)
    .Add("@Column3", SqlDbType.VarChar, 50).Value = If(TextBox3.TextLength = 0, CObj(DBNull.Value), TextBox3.Text)
End With

If a parameter is set to NULL then the @ColumnN IS NULL part is true and that set of criteria matches every record and is effectively ignored, otherwise the ColumnN = @ColumnN part will only match the appropriate records.

EDIT:

For the record, it has been pointed out when I’ve posted an example like this in the past that the database would use the same execution plan every time and thus may be somewhat inefficient for some queries. If that’s an issue for you, you should look at building the SQL dynamically on a case by case basis.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement