We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Sql Statement for searches

Sean Rhudy
Sean Rhudy asked
on
Medium Priority
237 Views
Last Modified: 2010-04-23
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)
Comment
Watch Question

Why dont you create the SQL WHERE clause as

Dim strWhere as string

If txtfirstname.Text <> "" Then
           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
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Sean RhudyPresident

Author

Commented:
What does the last statement do?
sorry, should be :

 strSQL &= " WHERE " & strWHERE
Sean RhudyPresident

Author

Commented:
What does that if statement do though?
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT

Commented:
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'
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.
Sean RhudyPresident

Author

Commented:
ok, thank you, I dont like to just copy and paste code, I like to understand it first, makes it easier later....
Sean RhudyPresident

Author

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
Sean RhudyPresident

Author

Commented:
Ok, I fixed that problem, but no matter what I search for, everything row in the db is found.
Sean RhudyPresident

Author

Commented:
*correction, Every row in the database is found, and displayed.
Sean RhudyPresident

Author

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!!
no problem.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.