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
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.