SQL data search?

[BarryMVS]

Hi all,

I'm trying to do a data serch on a database.

I have several different fields to which the search can be carried out from and I am offering these fields to the user on the search page.

My question is: Is it possible to get ASP to run a single RS lookup (rs = conn.Execute(sql)) using more than one SQL statement.

I can not find a way to do it and the data returned from the search entry page means a single sql statement doesn't work.

I don't want to have to resort to a single search box as this will take away the ease for the user selecting engineer's names orjob types which are presently done by drop down menus.

Any assistance would be most welcomed.

Thanks,

Barry

ICT Network Administrator
IT Services Manager

 

[spazman]

just create two record sets one called EngRS for the Engineer's names and JobRS the job.

 

[DreXor]

you can do a select where IN (select)

you can use a crosstab, or a join or severalother things to use two tables in one search .. or ou can just use 2 RS's and fire one after the other

or you can use GetRows, this makes a 2 dim array, do this for the 2 RS's then just manipulate the arrays into one.

i guess i would need a better description of how things are laid out, or what you're trying to do in more detail other than search a db and use 2 requests in one.

is this 2 dbs? 2 tables? 2 searches? are you trying to use drop downs in combo with a text box search?



DreX
aKa - Robert

 

[Barry1]

Don't mean to confuse you. I'm replying from Home!

I have several different search boxes to search from. The more data added, the more accurate the results should be.

Thus, if the user enters the engineers name, the date and the job type, only a small amout of hits should be returned, but if they only enter the date then a large amount will be returned.

All the data is contained in the same table and I don't want to get doubles in the results list, thus I only really want to run one recordset to show the results from.

Hope this helps,

Cheers,

Bary Smith
ASP, BKS, HTML, JS & CGI programmer.
BKS Graphic Design

 

[amorous]

Sure you can do this with single SQL...

Your SQL should look something like this...

SQL= "SELECT * FROM yourtable WHERE EngineerName='"&engname&"' AND JobDate='"&jobdate&"' AND JobType='"&jobtype&"' "

but here is the thing you have to do before doing this query to get small amount of hits if the user select more fields in the search criteria...

Dim engname,jobdate,jobtype

engname="%"
jobdate="%"
jobtype="%"

If LEN(TRIM(Request.Form("EngName"))) > 0 Then
engname = Request.Form("EngName")
End If
If LEN(TRIM(Request.Form("JobType"))) > 0 Then
jobtype = Request.Form("JobType")
End If
If LEN(TRIM(Request.Form("JobDate"))) > 0 Then
jobdate = Request.Form("JobDate")
End If


What we are doing in the above code is that when the user does not select or fill in a particular search field... we are assigning it to "%"...which when put in the query ignores this field and gets all the records...

Let me know if you need any more help...

-VJ

 

[vbkris]

it is better to build a query based on data entered.
e.g:
sql="select 8 from table where ..... "
if(EnginerName<>"") then
sql=sql&" and engineername='%"&EnginerName&"% '"
end if
if(JobName<>"") then
sql=sql&" and jobname='%"&JobName&"% '"
end if

so on and soforth...

Known is handfull, Unknown is worldfull

 

[BarryMVS]

VBKris,

Thanks for your suggestion, but it would leave stray 'and' floating about in the sql statement which can crash the script.

I am going to look at Amorous's suggestion and see how I do.

Thanks for your help.


Barry

ICT Network Administrator
IT Services Manager

 

[BarryMVS]

amorous,

I tried your code also, but even when I entered data in the search, it returned no hits, even though I know there are records under the criteria that I have entered.

My code is as follows:

serEngineer = "%"
		serCallNo = "%"
		serDate = "%"
		serCustomer = "%"
		serLocation = "%"
		serJobtype = "%"

		If LEN(TRIM(Request.Form("engineer"))) > 0 Then
  			serEngineer = request.Form("engineer")
		End If
		If LEN(TRIM(Request.Form("jobid"))) > 0 Then
  			serCallNo = request.Form("jobid")
		End If
		If LEN(TRIM(Request.Form("mdate"))) > 0 Then
  			serDate = request.Form("mdate")
		End If
		If LEN(TRIM(Request.Form("customer"))) > 0 Then
  			serCustomer = request.Form("customer")
		End If
		If LEN(TRIM(Request.Form("location"))) > 0 Then
  			serLocation = request.Form("location")
		End If
		If LEN(TRIM(Request.Form("jobtypeID"))) > 0 Then
  			serJobtype = request.form("jobtypeID")
		End If

		serSql = "SELECT * FROM AllocatedJobs WHERE EngineerIDs = '" & serEngineer & "' AND JobID = '" & serCallNo & "' AND Date = '" & serDate & "' AND customerID = '" & serCustomer & "' AND Location = '%" & serLocation & "%' AND jobtype = '" & serJobtype & "' ORDER BY Date;"
		Set serRs = conn.Execute(serSql)

Any help would be most welcomed.

Cheers,

Barry

ICT Network Administrator
IT Services Manager

 

[amorous]

1.
Date is a reserved Word....

2.
you may want to use LIKE key word for Location

3.

Change your Query to:

serSql = "SELECT * FROM AllocatedJobs WHERE EngineerIDs = '" & serEngineer & "' AND JobID = '" & serCallNo & "' AND [Date] = '" & serDate & "' AND customerID = '" & serCustomer & "' AND Location LIKE '%" & serLocation & "%' AND jobtype = '" & serJobtype & "' ORDER BY Date;"

4.

Also do a Response.Write serSql to see the actual variables being passed...


-VJ

 

[MakeItSo]

hi Barry,

Problem with "WHERE [field] LIKE '%': If the field is NULL, it won't be included in the results set.
VBKris's solution is far better there, except that =% won't work.
You avoid the "AND" problem by adding it to the end of the string instead of its beginning:

Dim sSQL
sSql="select * from table where "
if Request.form("Engineer")<>"" then
  sSql=sSql & "engineername='" & Request.form("Engineer") & "' AND "
end if
if Request.form("jobid")<>"" then
  sSql=sSql & "jobid='" & Request.form("jobid") & "' AND "
End if
...
If right(sSql,6)="where" then
  sSql=left(sSql, len(sSql) - 6)  'No criteria
Else
  sSql=left(sSql,len(sSql)-5) 'remove dangling AND
End if

Now you got an SQL string with all entered criteria...
;-)

Hope this helps,
Andy


[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
andreas.galambos@bowneglobal.de
HP:

http://andygalambos.de

 

[vbkris]

one more thing, how does an "and" get add in the end?

sql=sql&" and engineername='%"&EnginerName&"% '"

there is always something after the "and"

secondly i used % because it is like a wild card character....

Known is handfull, Unknown is worldfull

 

[BarryMVS]

Thanks for your feedback.

MakeItSo, can you explain what the second pat of your code does. I'm not sure how it removes the stray 'and'.

Thanks for your help.

Barry

ICT Network Administrator
IT Services Manager

 

[MakeItSo]

vbkris: I know why you used the %. But it won't work with the equal sign, only with LIKE. Additionally, you start off with "and..." as criteria, so the sql would be "...WHERE AND engineername" or whatever your first criteria will be. So you don't have a superfluous "AND" at the end, but at the beginning of your criteria. That is why I prefer adding the AND at the end of the criteria.

Barry The second part is an error catcher:
If no criteria are selected, your statement would be
"SELECT ... FROM... WHERE " without any criteria and would thus fail.
->In that case I delete the last 6 characters, namely "WHERE "

In the other case, you will get "SELECT ...FROM... WHERE...AND...AND ", i.e. one superfluous AND at the end.
->I delete the last 5 characters, namely " AND ".
;-)

Cheers,
MakeItSo

 

[vbkris]

in my original post i have written:
where.....

i thought there would be something after where and before my Ifs.

that can be easily solved:

sql="select * from table where EngineerName<>'' "
if(EnginerName<>"") then
 sql=sql&" and engineername='"&EnginerName&" '"
end if
if(JobName<>"") then
 sql=sql&" and jobname='"&JobName&" '"
end if

have a dummy check(e.g:EngineerName<>'')

Known is handfull, Unknown is worldfull

 

[BarryMVS]

Cracked it!

Thanks alot to all of you for your help.

The finished code is as follows:

serEngineer = request.Form("engineer")
serCallNo = request.Form("jobid")
serDate = request.Form("mdate")
serCustomer = request.Form("customer")
serLocation = request.Form("location")
serJobtype = request.form("jobtypeID")
serSql="SELECT * FROM AllocatedJobs WHERE"
if(serEngineer<>"") then
serSql=serSql&" EngineerIDs ='"&serEngineer&"' AND"
end if
if(serCallNo<>"") then
serSql=serSql&" JobID ='"&serCallNo&"' AND"
end if
if(serDate<>"") then
serSql=serSql&" [Date] ='"&serDate&"' AND"
end if
if(serCustomer<>"") then
serSql=serSql&" customerID ='"&serCustomer&"' AND"
end if
if(serLocation<>"") then
serSql=serSql&" Location LIKE'%"&serLocation&"%' AND"
end if
if(serJobtype<>"") then
serSql=serSql&" jobtype ='"&serJobtype&"' AND"
end if
If right(sSql,6)="where" then
serSql=left(serSql, len(serSql) - 5)  'No criteria
Else
serSql=left(serSql,len(serSql)-3) 'remove dangling AND
End if
serSql=serSql&" ORDER BY [Date];"

Thanks again for your help.


Barry

ICT Network Administrator
IT Services Manager