[RESOLVED] Help with SQL Search String

[tuber]

In my vb.net program I have a search that allows you to search one table. This table includes 2 different sets of contact information.

On my search I have these text boxes labled for the search.

ID
BusinessName
Status
First Name
Last Name
Phone
Address
City
State
Zip
Fax
Email

However my table has changed, and now these input boxes need to search 2 sets of contact information.

First Name - FirstName1 And FirstName2
Last Name - LastName1 And LastName2
Phone - Phone1 And Phone2
Address - Address1 And Address2
etc....

There is only 1 of: ID, Status, BusinessName.

There has always been 2 different phone numbers and before I was doing the search like:

((ID = 1) AND (Phone1 = 555-555-5555)) OR ((ID = 1) AND (Phone2 = 555-555-5555))

I was building my string setting parts of the string to variables if the textboxlength was > 0. So it looks like this:

                If Trim(Me.txtPhoneSearch.TextLength) > 0 Then
                    PartF = " AND Phone1 LIKE '" & Me.txtPhoneSearch.Text & "%' "
                    PartG = " AND Phone2 LIKE '" & Me.txtPhoneSearch.Text & "%'"
                Else
                    PartF = String.Empty
                    PartG = String.Empty
                End If


WHERE  PartA & PartF & PartB & PartJ & _
                     PartL & PartC & PartD & PartE & PartH & PartI & PartM  &       PartK & " OR " & _
                         " PartA & PartB & PartJ & _
                           PartL & PartC & PartG & PartD & PartE & _
                           PartH & PartI & PartM & PartK

Now I'm not sure how to do this because it would equal a huge string if I did it this way. I'm not too worried on length of the search, timewise, right now because there are very few records in our database.

If someone could help me out, giving me advice on how to build this string, or what the best practice is that would AWESOME. I've never had to build a search this big and it's worrying me a bit.

Thanks in advance!

[motil]

Let me understand you want all the textboxs will be included in the search?
if so, why not to use FOR LOOP to loop all the textboxs and dynamically create the sql statement?

[tuber]

No, I know how to do that.

I need to know the best practice to actually do the search.

The SQL is what I'm not sure about.

[motil]

so what is your question? is there better way to commit the search
or how to save time of building the SQL string ?

[motil]

if its help here's a function to build the sql string with as much textbox you want as long as their Name is the same as their column name in the database.

 Private Function buildString() As String
        Dim addSql As String = String.Empty
        Dim counter As Integer = 0
        Dim cControl As Control
        For Each cControl In Me.Controls
            If (TypeOf cControl Is TextBox) Then
                If cControl.Text.Length > 0 Then
                    If counter > 0 Then
                        addSql += " AND " + _
                        cControl.Name.ToString() + " LIKE '" + cControl.Text + "%' "
                    Else
                        addSql += "  " + _
                                                cControl.Name.ToString() + " LIKE '" + cControl.Text + "%' "
                    End If
                    counter += 1

                End If



            End If
        Next cControl
        Return addSql

    End Function

[tuber]

I don't need to know the process for building the string But Thank you.

I need to know what string to build.

Question is.

How do I search a table for these things.

txtID.text = 102
txtPhoneNumber.text = 555-555-5555
txtFirstName.text = Adam
txtLastName.text = House

I need to search 2 columns for each of the textboxes except for the id.

In my table I have FirstName1 and FirstName2. PhoneNumber1 And PhoneNumber2. LastName1 and LastName2, but only 1 ID Field


So when someone enters

555-555-5555
Adam
House

I need it to search those 6 columns and return the appropriate results.

[motil]

And for you SQL, I don't it's a good idea to use so many LIKE and one sql statement especially when it's a large database, but if you must do it just make sure that these columns has good indexing.

[nagasrikanth]

tuber,

I think you are on the right way only.. If you want to search on 2 different fields, Your SQL Statement is perfectly right.
Coming to long SQL Statement concern, What is the problem even though it was too much long ?? Your requirement needs that..
Coming to speed also, I dont think, it will create a problem. In my case, I'm checking almost around 1 Lakh of records with around 12 to 15 criterias easily.

Developers, Correct me if i'm wrong..

Happy Coding,
Srikanth

[motil]

btw, if you know the ID of the record, you don't need all the rest of the data...

[tuber]

tuber,

I think you are on the right way only.. If you want to search on 2 different fields, Your SQL Statement is perfectly right.
Coming to long SQL Statement concern, What is the problem even though it was too much long ?? Your requirement needs that..
Coming to speed also, I dont think, it will create a problem. In my case, I'm checking almost around 1 Lakh of records with around 12 to 15 criterias easily.

Developers, Correct me if i'm wrong..

Happy Coding,
Srikanth

Yes, my only real concern is that this will be a very long query, with 9 search options that all need to be able to search 2 different columns.

Writing that many ORs is going to get really confusing too.

[si_the_geek]

Why have you got so much repetition in the Where clause?

WHERE  PartA & PartF & PartB & PartJ & PartL & PartC         & PartD & PartE & PartH & PartI & PartM & PartK & " OR " & _
     " PartA &         PartB & PartJ & PartL & PartC & PartG & PartD & PartE & PartH & PartI & PartM & PartK

It could be easily re-written without that, and still have the same effect, eg:

WHERE  PartA & PartB & PartJ & PartL & PartC & PartD & PartE & PartH & PartI & PartM & PartK & _
     "(" & PartF & " OR " & PartG & ")"

(will need minor work to integrate into your code, but should be enough for you to get the idea!)

Coming to long SQL Statement concern, What is the problem even though it was too much long ?? Your requirement needs that..

There is a limit to the allowed length of an SQL statement. It varies by database system, but is typically 8k characters.

In my table I have FirstName1 and FirstName2. PhoneNumber1 And PhoneNumber2. LastName1 and LastName2, but only 1 ID Field

That doesn't sound like a good idea to me, and I suspect the design would be better if you add an extra table for the duplicated fields.

That is particularly true if there are cases where you don't have exactly 2 sets of information (it would also allow you to have more sets if needed).

This would not only simplify queries, but would most likely make them run faster too.

[tuber]

btw, if you know the ID of the record, you don't need all the rest of the data...

This is true, and I will will probably put an entirely different string if it's included.

[tuber]

Hey thanks for all of your help guys! This was pretty simple, for some reason i wasn't comprehending it!

Here's an example of what I did. Hopefully when there gets a bunch of records in there it's not too slow, but it will definitely work for now.

 If Trim(Me.txtZipSearch.TextLength) > 0 Then
                    PartJ = " AND ((DecisionMakerZip LIKE '" & Trim(Me.txtZipSearch.Text) & "%') OR (BillingZip LIKE '" & Trim(Me.txtZipSearch.Text) & "%')) "
                Else : PartJ = String.Empty
                End If

  selectstr = "SELECT CompanyID, DecisionMakerPhone, BillingPhone, Status, CompanyName," & _
                                                  "DecisionMakerZip, DecisionMakerCity, DecisionMakerState, DecisionMakerFirstName, DecisionMakerLastName " & _
                                                  "FROM Company " & _
                                                  "WHERE (UpdateAction <> 'DELETE')" & PartA & PartF & PartB & PartJ & _
                                                        PartL & PartC & PartD & PartE & PartH & PartI & PartM & PartK

Much Appreciated!