CODEWITHSUNDEEP
sqlvb.net
Ayden
Ayden

Reputation: 45

SQL select statement with 2 conditions

I have a table that has columns CustomerCell and ReceiptType. I need to create a SELECT statement that displays every record that matches CustomerCell or ReceiptType.

I tried this code: 

If TextBox1.Text.Trim.Length <> 0 OrElse CheckBox4.Checked = True Then
    Dim Conn As New SqlConnection(constr)
    Dim ds As New DataTable
    Dim sqlstr As String = "Select [RcptNum], [RcptDate], [RcptCustName], [RcptCustCell], [RcptAmount], [RcptType], [RcptFld1], [RcptFld2], [RcptFld3], [RcptUser] From [tblReceipt] where (RcptCustCell = '" & TextBox1.Text & "') or ([RcptType] = 'Cash') "
    Dim da As New SqlDataAdapter(sqlstr, Conn)
    ds.Reset()
    da = New SqlDataAdapter(sqlstr, Conn)
    da.Fill(ds)
    dgv.DataSource = ds
    Call griddraw()
    Conn.Close()
End If

Where Textbox1 is for CustomerCell and CheckBox4 is for ReceiptType. When I enter customer cell and receipt type I should see 2 records however with the above code I can see only one record.

This is my form:

enter image description here

Upvotes: 1

Views: 280

Answers (2)

pg1988
pg1988

Reputation: 59

Dim Conn As New SqlConnection(constr)
Dim ds As New DataTable
Dim sqlstr As String = "Select [RcptNum], [RcptDate], [RcptCustName], [RcptCustCell], [RcptAmount], [RcptType], [RcptFld1], [RcptFld2], [RcptFld3], [RcptUser] From [tblReceipt]"

If TextBox1.Text.trim.length <> 0 then
  sqlstr += "where (RcptCustCell = '" & TextBox1.Text & "')"
endif
If chkPaymentCheck.checked then
  if sqlstr.contains("where") = false then
    sqlstr += "where RcptType = 'Check'"
  EndIf
  sqlstr += "or RcptType = 'Check'"
endif
Dim da As New SqlDataAdapter(sqlstr, Conn)
ds.Reset()
da = New SqlDataAdapter(sqlstr, Conn)
da.Fill(ds)
dgv.DataSource = ds
Call griddraw()
Conn.Close()

Try this and you can continue with the if statements to add more checks.

Upvotes: 1

Bugs
Bugs

Reputation: 4489

As stated, look into parameters to avoid SQL injection and it does clear up your query a little more. I've put this together which may help. Might need a few tweaks for your application:

If TextBox1.Text.Trim.Length <> 0 OrElse CheckBox4.Checked = True Then
   Dim dt As DataTable
   Dim sqlstr As String = "Select [RcptNum], [RcptDate], [RcptCustName], [RcptCustCell], [RcptAmount], [RcptType], [RcptFld1], [RcptFld2], [RcptFld3], [RcptUser] From [tblReceipt] where (RcptCustCell = @RcptCustCell) or ([RcptType] = 'Cash') "
   Using con As New SqlConnection(constr),
         com As New SqlCommand(sqlstr, con)

       com.Parameters.Add("@RcptCustCell", SqlDbType.VarChar).Value = TextBox1.Text

       con.Open()

       dt = New DataTable
       dt.Load(com.ExecuteReader)

       dgv.DataSource = dt
       Call griddraw()
  End Using
End If

Upvotes: 2

Related Questions