Solved

Sql Statement for searches

Posted on 2006-11-02
15
195 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)
0
Comment
Question by:seanrhudy
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 10

Expert Comment

by:Kinger247
ID: 17857554
Why dont you create the SQL WHERE clause as

Dim strWhere as string

If txtfirstname.Text <> "" Then
0
 
LVL 10

Expert Comment

by:gangwisch
ID: 17857558
           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
 
LVL 10

Accepted Solution

by:
Kinger247 earned 250 total points
ID: 17857585
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:seanrhudy
ID: 17858200
What does the last statement do?
0
 
LVL 10

Expert Comment

by:Kinger247
ID: 17858248
sorry, should be :

 strSQL &= " WHERE " & strWHERE
0
 

Author Comment

by:seanrhudy
ID: 17858420
What does that if statement do though?
0
 
LVL 35

Expert Comment

by:YZlat
ID: 17858451
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
 
LVL 35

Expert Comment

by:YZlat
ID: 17858461
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
 
LVL 10

Expert Comment

by:Kinger247
ID: 17858511
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
 

Author Comment

by:seanrhudy
ID: 17858637
ok, thank you, I dont like to just copy and paste code, I like to understand it first, makes it easier later....
0
 

Author Comment

by:seanrhudy
ID: 17863370
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
 

Author Comment

by:seanrhudy
ID: 17863490
Ok, I fixed that problem, but no matter what I search for, everything row in the db is found.
0
 

Author Comment

by:seanrhudy
ID: 17863950
*correction, Every row in the database is found, and displayed.
0
 

Author Comment

by:seanrhudy
ID: 17865755
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
 
LVL 10

Expert Comment

by:Kinger247
ID: 17865771
no problem.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB.NET 2008 Winforms Signing 13 31
Iteration Help (Asp.net VB) 5 24
VB.NET String Settings and Temp Folder Question 3 53
Convert Ctime to date time in textfile? 7 30
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question