Search Form Skipping Rows when TextBox Value IS NULL 2

[net123]

I have a search form in which one can search per many fields (textboxes). I have noticed that if I include a field that has a bunch of NULLs in its values of the table, then the query fails, i.e. doesn't return those values.

The where clause of my SQL statement is as follows:

'"AND dbo.Invoice.ThisNum LIKE '%" & txtThisNum.Text & "&' " & _
...
[/blue]
If nothing is typed into the txtThisNumber textbox I would like it to return anyways, kinda like an ALL return.

It's like when no one types anything into the search criteria of the form, all rows of the database table should be returned. But this behavior is not present here b/c it only returns the rows that have dbo.Invoice.ThisNum NOT NULL, i.e. with a value in them.

I know I should use parameterized queries, but can someone help me with the above logic/syntax?

 

[Isadore]

net --

just a thought. you know that's not really a bad result. If "nothing" is typed into the textbox, then "nothing" is returned.

net, here is something vaguely similar in Access:

Like(IIf(Not IsNull([txtValue],[txtValue],"*&quot))

...in which case I first see if a null exists, and if it does, retun a wild card, in your case a %.

Anyway, point is, if you can use an IIf type approach. Just a thought.

 

[net123]

Thanks Isadore for responding!

You said something on your post which was not correct in my initial statement:
just a thought. you know that's not really a bad result. If "nothing" is typed into the textbox, then "nothing" is returned.[/red]

When nothing is entered into the textbox, it should act as an ALL and return everything, i.e. NULL and NOT NULL values. But in my case, the query (with many parameters) returns values where NOT NULL, i.e. witha value in them such as 123456.

This is the last bug I have in my code, and if I can only finish it I will be super happy!

I was thinking something in the lines of this (but of course this does not work):

"PONumber = " & _
"CASE " & _
"WHEN dbo.Invoice.ThisNumber IS NULL THEN 'None' " & _
"WHEN dbo.Invoice.ThisNumber IS NOT NULL THEN dbo.Invoice.ThisNumber " & _
"END ," & _
[/blue]

Thanks in advance for anyone's assistance.

 

[Isadore]

net --

Yes your statement:

"When nothing is entered into the textbox, it should act as an ALL and return everything, i.e. NULL and NOT NULL values."

..is correct, I was just having a little fun with it all - logically, if nothing is typed, nothing is returned, albeit, true, that's not the way it works. I hear ya.

You're on the right track net. It has to come down to "If not this, then use this" type of approach. Simple logic.

This is a great question; although I am not working on an SQL server yet (my compadres are sooooooooooo slow) I do have a copy of SQL Server 7.0 on my laptop along with Query analyzer - might try a few approaches to this.

Another idea net, although they don't like you to "re-post" the same question on other forums, I think it is appropriate when the inital thread (this one) makes that recommendation.

I would go over the SQL forum and present this question. There are some sharp shooters over there, and chances are they can probably give you a few lines of code to help.

good luck, and if anything pops up, we'll post back.

 

[net123]

Isadore:

Thanks for your insightful input. I thought about it more and came up with a super simple solution (don't know if it is the most efficient yet):

If txtThisNum.Text <> '' Then
mySQL = &quot;SELECT statement WHERE ... &quot; & _
&quot;AND dbo.Invoice.ThisNum LIKE '%&quot; & txtThisNum.Text & &quot;%' &quot; & _
Else
mySQL = &quot;SELECT statement WHERE other cluases without the ThisNum criteria &quot;
[/blue]

 

[Isadore]

gee -- thanks for the star net; but really, you're the one who solved this. Good solution. It's a great technique, as I have always, since the beginning, have loved the mult-text field search capabilities.

 

[Isadore]

I think others on this forum gonna think we're in on a conspiracy to swap stars!

Hey net, its a good techinque, and you deserve one for developing it. Good luck, we'll see ya around.