[RESOLVED] Help with SQL Search String
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:
Code:
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!
Re: Help with SQL Search String
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?
Re: Help with SQL Search String
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.
Re: Help with SQL Search String
so what is your question? is there better way to commit the search
or how to save time of building the SQL string ?
Re: Help with SQL Search String
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.
Code:
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
Re: Help with SQL Search String
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.
Re: Help with SQL Search String
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.
Re: Help with SQL Search String
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
Re: Help with SQL Search String
btw, if you know the ID of the record, you don't need all the rest of the data...
Re: Help with SQL Search String
Quote:
Originally Posted by
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
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.
Re: Help with SQL Search String
Why have you got so much repetition in the Where clause?
Quote:
Originally Posted by tuber
Code:
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:
Code:
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!)
Quote:
Originally Posted by nagasrikanth
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.
Quote:
Originally Posted by tuber
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.
Re: Help with SQL Search String
Quote:
Originally Posted by
motil
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.
Re: Help with SQL Search String
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.
Code:
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!
