sql statement with gaps using asp? 1

[techskool]

Im using a form to post info to an sql statement to return a set of records using asp

When some of the form objects are not filled out, say a textbox, that feeds into serachterm2 in the sql, if it is null what will happen?

ie. "SELECT * FROM table WHERE" &searchTerm1& "=" &searchTerm2&" ORDER BY" &searchTerm3& "

Cheers

Dave

 

[vbkris]

nothing much,
SearchTerm2 will be equal to ''

Known is handfull, Unknown is worldfull

 

[techskool]

I'll try and give you a bit more

Okay, so lets say the sql is

sql="SELECT * FROM stock WHERE Artist LIKE '%"&Artist&"%' AND Title LIKE '%"&Title&"%' AND Label LIKE '%"&Label&"%' AND Comment LIKE '%"&Comment&"%' AND YearMade LIKE '%"&YearMade&"%' AND DateLogged
LIKE '%"&DateLogged&"%' ORDER BY '%"&OrderBy&"%'"

The variables are read in form the querystring, posted from a form.

This will work fine if all the form objects are filled out, but if they are null i get no results.

What sort of code can i add to this to ensure that i only query againt the form objects that are filled out?????



Thanks in advance

Dave

 

[vbkris]

there are two ways of ensuring it:
client side scripting - javascript
server side scripting - ASP

i would prefer javascript

but if u prefer ASP then here is a simple snippet
if(Trim(Comment)="&quot then
'this means the comment field is empty. do something accordingly.
end if

Known is handfull, Unknown is worldfull

 

[techskool]

Isnt that just the same as doing

If Comment <> &quot;&quot; Then

do some code

.


I kinda want need see how this will be done,...what do you do to the sql statement if there isnt a value in one of the fields its testing against.

Wouldnt you need to remove that portion of the sql?????

Cheers

Dave

 

[Johno2090]

i personaly would just do something of the following


sql=&quot;SELECT * FROM stock WHERE&quot;

if artist <> &quot;&quot; then
sql = sql + &quot; Artist LIKE '%&quot;&Artist&&quot;%'&quot;
End if

obviously you would need some more thought behind it but it would leave out the segment of SQL that dosent have a value ill see if i can work something morew helpful out if i get the time

Johno2090

 

[techskool]

Thanks Johno2090

Thats perfect

Dave ;-)

 

[techskool]

Ive written something with that, but its very messy.

2 questions.

1.
How can i say in an sql statement select x from y where RECORDSET VALUE like NOT NULL

and

2.

Can i write this a bit neater

Artist = Request.QueryString(&quot;Artist&quot
Title = Request.QueryString(&quot;Title&quot
Label = Request.QueryString(&quot;Label&quot
YearMade = Request.QueryString(&quot;YearMade&quot
InStock = Request.QueryString(&quot;InStock&quot
Audio = Request.QueryString(&quot;Audio&quot
Picture = Request.QueryString(&quot;Picture&quot
CloseUp = Request.QueryString(&quot;CloseUp&quot
PriceLess = Request.QueryString(&quot;PriceLess&quot

sql=&quot;SELECT * FROM stock&quot;

If Artist <> &quot;&quot; Then
sql = sql + &quot; WHERE Artist LIKE '&quot;&Artist&&quot;'&quot;
End if

If Title <> &quot;&quot; Then
If Artist = &quot;&quot; Then
sql = sql + &quot; WHERE Title LIKE '&quot;&Title&&quot;'&quot;
Else
sql = sql + &quot; AND Title LIKE '&quot;&Title&&quot;'&quot;
End If
End If

If Label <> &quot;&quot; Then
If Artist = &quot;&quot; And Title = &quot;&quot; Then
sql = sql + &quot; WHERE Label LIKE '&quot;&Label&&quot;'&quot;
Else
sql = sql + &quot; AND Label LIKE '&quot;&Label&&quot;'&quot;
End If
End If

If YearMade <> &quot;&quot; Then
If Artist = &quot;&quot; And Title = &quot;&quot; And Label = &quot;&quot; Then
sql = sql + &quot; WHERE YearMade LIKE '&quot;&YearMade&&quot;'&quot;
Else
sql = sql + &quot; AND YearMade LIKE '&quot;&YearMade&&quot;'&quot;
End If
End If

If InStock <> &quot;&quot; Then
If Artist = &quot;&quot; And Title = &quot;&quot; And Label = &quot;&quot; And YearMade = &quot;&quot; Then
sql = sql + &quot; WHERE InStock LIKE '&quot;&InStock&&quot;'&quot;
Else
sql = sql + &quot; AND InStock LIKE '&quot;&InStock&&quot;'&quot;
End If
End If

If Audio <> &quot;&quot; Then
If Artist = &quot;&quot; And Title = &quot;&quot; And Label = &quot;&quot; And YearMade = &quot;&quot; And InStock = &quot;&quot; Then
sql = sql + &quot; WHERE Audio LIKE '&quot;&Audio&&quot;'&quot;
Else
sql = sql + &quot; AND Audio LIKE '&quot;&Audio&&quot;'&quot;
End If
End If

If Picture <> &quot;&quot; Then
If Artist = &quot;&quot; And Title = &quot;&quot; And Label = &quot;&quot; And YearMade = &quot;&quot; And InStock = &quot;&quot; And Audio = &quot;&quot; Then
sql = sql + &quot; WHERE Picture LIKE '&quot;&Picture&&quot;'&quot;
Else
sql = sql + &quot; AND Picture LIKE '&quot;&Picture&&quot;'&quot;
End If
End If

If CloseUp <> &quot;&quot; Then
If Artist = &quot;&quot; And Title = &quot;&quot; And Label = &quot;&quot; And YearMade = &quot;&quot; And InStock = &quot;&quot; And Audio = &quot;&quot; And Picture = &quot;&quot; Then
sql = sql + &quot; WHERE CloseUp LIKE '&quot;&CloseUp&&quot;'&quot;
Else
sql = sql + &quot; AND CloseUp LIKE '&quot;&CloseUp&&quot;'&quot;
End If
End If

If PriceLess <> &quot;&quot; Then
If Artist = &quot;&quot; And Title = &quot;&quot; And Label = &quot;&quot; And YearMade = &quot;&quot; And InStock = &quot;&quot; And Audio = &quot;&quot; And Picture = &quot;&quot; And CloseUp = &quot;&quot; Then
sql = sql + &quot; WHERE Price <= '&quot;&PriceLess&&quot;'&quot;
Else
sql = sql + &quot; AND Price <= '&quot;&PriceLess&&quot;'&quot;
End If
End If

sql = sql + &quot; ORDER BY Artist, Title, Label&quot;
End If



Cheers

Dave

 

[vbkris]

the other way is using javascript, using whcih u can force the user to enter all the details before submitting the form.

Known is handfull, Unknown is worldfull

 

[techskool]

Thanks vbkris, but i try to work on the assumption that the user either doesnt have javascript or doesnt have it enabled, so if they dont have it , i always have server side code to back it up.


Thanks for everyones help

This forum rules

Dave