Sql Statement for searches

Hello, I have the following SQL Statement, I will have about 8 different textboxes, for different search criteria, firstname, lastlame, amountpaid, etc...  I will just use 2 for the example though.  Right now, if I put in the first and lastname, it finds them.  If I only put one or the other in, it throws an error, saying that one of them was null.  I would like the user to be able to search by any of the criteria, making none of them required.

DsSearch1.Clear()
            Cmd.CommandText = "SELECT ReceiptNo, PolicyNo, pmt1, pmt2, pmt3, type1, type2, type3, total, rcvdby, firstname, lastname, typeaccount, company, checkmaker, checkno, Datercvd, notebox FROM Receiptinfo WHERE firstname='" & txtfirstname.Text & "' Or lastname='" & txtlastname.Text & "';"
            Cmd.CommandType = CommandType.Text
            Da = New OleDb.OleDbDataAdapter(Cmd)
            DGResults.DataSource = DsSearch1.Receiptinfo
            Da.Fill(DsSearch1.Receiptinfo)
Sean RhudyPresidentAsked:
Who is Participating?
 
Kinger247Connect With a Mentor Commented:
Sorry bit quick on the enter key ...

something like this ..

Dim strSQL as string = "SELECT ReceiptNo, PolicyNo, pmt1, pmt2, pmt3, type1, type2, type3, total, rcvdby, firstname, lastname, typeaccount, company, checkmaker, checkno, Datercvd, notebox FROM Receiptinfo "

Dim strWHERE as string = ""

If txtfirstname.Text <> "" Then
  if strWHERE <> "" then strWHERE &= " OR "
  strWHERE &= " firstname='" & txtfirstname.Text & "'"
End if

If txtlastname.Text <> "" Then
  if strWHERE <> "" then strWHERE &= " OR "
  strWHERE &= " lastname='" & txtlastname.Text & "'"
End if

If strWHERE <> "" Then
  strSQL = " WHERE " & strWHERE
End If
0
 
Kinger247Commented:
Why dont you create the SQL WHERE clause as

Dim strWhere as string

If txtfirstname.Text <> "" Then
0
 
gangwischCommented:
           Cmd.CommandText = "SELECT ReceiptNo, PolicyNo, pmt1, pmt2, pmt3, type1, type2, type3, total, rcvdby, firstname, lastname, typeaccount, company, checkmaker, checkno, Datercvd, notebox FROM Receiptinfo WHERE"
dim inserted as boolean =false
if firstname.textlength>0 then
Cmd.CommandText &= firstname='" & txtfirstname.Text & "'"
inserted=true
end if

if lastname.textlength>0 then
Cmd.CommandText &= iif(inserted," OR ","") & lastname='" & txtfirstname.Text & "'"
inserted=true
end if
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Sean RhudyPresidentAuthor Commented:
What does the last statement do?
0
 
Kinger247Commented:
sorry, should be :

 strSQL &= " WHERE " & strWHERE
0
 
Sean RhudyPresidentAuthor Commented:
What does that if statement do though?
0
 
YZlatCommented:
the code below checks if there is any condition in the WHERE clause and if there is, it adds the WHERE clause to the sql statement:

If strWHERE <> "" Then
  strSQL = " WHERE " & strWHERE
End If
0
 
YZlatCommented:
for example if your where clause is "lastname='Smith',

and your sql statement is "SELECT * FROM TAble1"

then  that last statement will convert your sql statement to

"SELECT * FROM Table1 WHERE lastname='Smith'
0
 
Kinger247Commented:
It makes sure that only the desired columns are searched on and not all by default.
So as YZlat states you create the main SELECT SQL statement first then add on the customised WHERE clause after to create the full SQL query.
0
 
Sean RhudyPresidentAuthor Commented:
ok, thank you, I dont like to just copy and paste code, I like to understand it first, makes it easier later....
0
 
Sean RhudyPresidentAuthor Commented:
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim Da As OleDb.OleDbDataAdapter
        Try
            Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/backup/receiptprogram/receiptmanagement/receipt.mdb"
            Con.Open()
            Cmd.Connection = Con
        Catch ex As Exception
        End Try

        Try

            DsSearch1.Clear()
            Cmd.CommandText = "SELECT ReceiptNo, PolicyNo, pmt1, pmt2, pmt3, type1, type2, type3, total, rcvdby, firstname, lastname, typeaccount, company, checkmaker, checkno, Datercvd, notebox FROM Receiptinfo "
            Cmd.CommandType = CommandType.Text
            Da = New OleDb.OleDbDataAdapter(Cmd)
            DGResults.DataSource = DsSearch1.Receiptinfo
            Da.Fill(DsSearch1.Receiptinfo)

            Dim strSQL As String = "SELECT ReceiptNo, PolicyNo, pmt1, pmt2, pmt3, type1, type2, type3, total, rcvdby, firstname, lastname, typeaccount, company, checkmaker, checkno, Datercvd, notebox FROM Receiptinfo "

            Dim strWHERE As String = ""

            If txtfirstname.Text <> "" Then
                If strWHERE <> "" Then strWHERE &= " OR "
                strWHERE &= " firstname='" & txtfirstname.Text & "'"
            End If

            If txtlastname.Text <> "" Then
                If strWHERE <> "" Then strWHERE &= " OR "
                strWHERE &= " lastname='" & txtlastname.Text & "'"
            End If

            If strWHERE <> "" Then
                strSQL &= " WHERE " & strWHERE
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Con.Close()
            Cmd.Connection = Nothing
        End Try
    End Sub

It is bringing back a null value for the total column, when I know there is a value in there
0
 
Sean RhudyPresidentAuthor Commented:
Ok, I fixed that problem, but no matter what I search for, everything row in the db is found.
0
 
Sean RhudyPresidentAuthor Commented:
*correction, Every row in the database is found, and displayed.
0
 
Sean RhudyPresidentAuthor Commented:
Ok its working, I changed the commandtext to "cmd.commandtext = strSQL and I put all the dataset code after the strSQL and where statements.  Thanks!!
0
 
Kinger247Commented:
no problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.