p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: RE: searching for multiple items in one datab- ase


Message #1 by "PUFFER, TIM" <TIM.PUFFER@T...> on Mon, 3 Dec 2001 10:43:07 -0600
the problem is not with the like statement, it is trying to get the search

to look through all of the fields.  I get it to work this way.



Case Else

	TheMessage = "Search Results"

	sqltext = "SELECT * FROM newbookings"

	sqltext = sqltext & " WHERE " & varin

	sqltext = sqltext & " LIKE  '%" & varfind1 & "%'"

	sqltext = sqltext & " AND  '%" & varfind2 & "%'"

	sqltext = sqltext & " AND  '%" & varfind3 & "%'"

	sqltext = sqltext & " AND  '%" & varfind4 & "%'"

	sqltext = sqltext & " AND  '%" & varfind5 & "%'"

	sqltext = sqltext & "ORDER by " & varsort 



but if I add this if gives me an expected end of statement.



Case Else

	TheMessage = "Search Results"

	sqltext = "SELECT * FROM newbookings"

	sqltext = sqltext & " WHERE " & varin, varin2<<<<<<<< second

variable

	sqltext = sqltext & " LIKE  '%" & varfind1 & "%'"

	sqltext = sqltext & " AND  '%" & varfind2 & "%'"

	sqltext = sqltext & " AND  '%" & varfind3 & "%'"

	sqltext = sqltext & " AND  '%" & varfind4 & "%'"

	sqltext = sqltext & " AND  '%" & varfind5 & "%'"

'	sqltext = sqltext & "ORDER by " & varsort 

End Select



I really don't want to tell it which fields to look in at all if that is

possible?



Thanks again for the help.





-----Original Message-----

From: Kim Iwan Hansen [mailto:kimiwan@k...]

Sent: Friday, November 30, 2001 4:27 PM

To: ASP Databases

Subject: [asp_databases] RE: searching for multiple items in one datab

ase





i guess you'll need to cast/convert each field that's not a string type to a

string type and then do the search on those fields..  i think that'll be the

easiest approach.



the LIKE function only works on string type fields.



-Kim





-----Original Message-----

From: PUFFER, TIM [mailto:TIM.PUFFER@T...]

Sent: 30. november 2001 23:08

To: ASP Databases

Subject: [asp_databases] RE: searching for multiple items in one datab

ase





I would like to look in all of the fields for an instance of the search

items.



-----Original Message-----

From: Kim Iwan Hansen [mailto:kimiwan@k...]

Sent: Friday, November 30, 2001 3:50 PM

To: ASP Databases

Subject: [asp_databases] RE: searching for multiple items in one

database





you need to put which field should match each of the LIKEs..



SELECT * FROM newbookings WHERE field1 LIKE '%40%' AND field 2 LIKE '%2001%

etc.



-Kim











-----Original Message-----

From: tim.puffer@t... [mailto:tim.puffer@t...]

Sent: 30. november 2001 21:32

To: ASP Databases

Subject: [asp_databases] searching for multiple items in one database





I am trying to look for several items in one database.  I think i am on

the write track ??? could someone take a look at my code and let me know

if i am close?



this is the response.write that it generates.



SELECT * FROM newbookings WHERE LIKE '%40%' AND '%2001%' AND '%bic%'ORDER

by



Here is my code.



<%

	dim varIn, varFind1, varfind2,varfind3, varfind4, varfind5,

cnbook, connstr, theMessage, sqltext, rsbook, varsort, total

	varsort= Request.Form("sort")

	varIn = Request.form("in")

	varFind1 = Request.form("find1")

	varFind2 = Request.form("find2")

	varFind3 = Request.form("find3")

	varFind4 = Request.form("find4")

	varFind5 = Request.form("find5")

	response.write varFind1

	response.write varFind2

	response.write varFind3

	response.write varFind4

	response.write varFind5

'	response.write varIn

'	reponse.write varsort

'	reponse.write total



set cnbook = server.createobject ("adodb.connection")

connstr = "driver={microsoft access driver (*.mdb)};" & _

			"dbq=" & server.MapPath("bookings 2000.mdb")

cnbook.open connstr, "", ""



select case UCase (Request.QueryString("view"))

	Case "ALL"

		TheMessage = "All Bookings"

		sqltext = "SELECT * FROM newbookings"

	Case Else

		TheMessage = "Search Results"

		sqltext = "SELECT * FROM newbookings"

		sqltext = sqltext & " WHERE "

		sqltext = sqltext & " LIKE  '%" & varfind3 & "%'"

		sqltext = sqltext & " AND  '%" & varfind4 & "%'"

		sqltext = sqltext & " AND  '%" & varfind5 & "%'"

		sqltext = sqltext & "ORDER by " & varsort

End Select

Response.Write sqltext

set rsbook = cnbook.Execute(sqltext)<<< this is line 39>>>>



thanks in advance.






$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid

=rn_ebooks









tim.puffer@t...


$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid

=rn_ebooks








$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid

=rn_ebooks





 



tim.puffer@t...


$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid

=rn_ebooks

Message #2 by David Cameron <dcameron@i...> on Tue, 4 Dec 2001 09:40:07 +1100
This message is in MIME format. Since your mail reader does not understand

this format, some or all of this message may not be legible.



------_=_NextPart_001_01C17C4B.759E0EEE

Content-Type: text/plain;

	charset="iso-8859-1"



That will give you a parse error. You are doing something similar to:



Mystring = "test " & var1, var2



valid code:



Mystring = "test " & var1 & ", " & var2



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: PUFFER, TIM [mailto:TIM.PUFFER@T...]

Sent: Tuesday, 4 December 2001 2:43 AM

To: ASP Databases

Subject: [asp_databases] RE: searching for multiple items in one datab

ase





the problem is not with the like statement, it is trying to get the search

to look through all of the fields.  I get it to work this way.



Case Else

	TheMessage = "Search Results"

	sqltext = "SELECT * FROM newbookings"

	sqltext = sqltext & " WHERE " & varin

	sqltext = sqltext & " LIKE  '%" & varfind1 & "%'"

	sqltext = sqltext & " AND  '%" & varfind2 & "%'"

	sqltext = sqltext & " AND  '%" & varfind3 & "%'"

	sqltext = sqltext & " AND  '%" & varfind4 & "%'"

	sqltext = sqltext & " AND  '%" & varfind5 & "%'"

	sqltext = sqltext & "ORDER by " & varsort 



but if I add this if gives me an expected end of statement.



Case Else

	TheMessage = "Search Results"

	sqltext = "SELECT * FROM newbookings"

	sqltext = sqltext & " WHERE " & varin, varin2<<<<<<<< second

variable

	sqltext = sqltext & " LIKE  '%" & varfind1 & "%'"

	sqltext = sqltext & " AND  '%" & varfind2 & "%'"

	sqltext = sqltext & " AND  '%" & varfind3 & "%'"

	sqltext = sqltext & " AND  '%" & varfind4 & "%'"

	sqltext = sqltext & " AND  '%" & varfind5 & "%'"

'	sqltext = sqltext & "ORDER by " & varsort 

End Select



I really don't want to tell it which fields to look in at all if that is

possible?



Thanks again for the help.





-----Original Message-----

From: Kim Iwan Hansen [mailto:kimiwan@k...]

Sent: Friday, November 30, 2001 4:27 PM

To: ASP Databases

Subject: [asp_databases] RE: searching for multiple items in one datab

ase





i guess you'll need to cast/convert each field that's not a string type to a

string type and then do the search on those fields..  i think that'll be the

easiest approach.



the LIKE function only works on string type fields.



-Kim





-----Original Message-----

From: PUFFER, TIM [mailto:TIM.PUFFER@T...]

Sent: 30. november 2001 23:08

To: ASP Databases

Subject: [asp_databases] RE: searching for multiple items in one datab

ase





I would like to look in all of the fields for an instance of the search

items.



-----Original Message-----

From: Kim Iwan Hansen [mailto:kimiwan@k...]

Sent: Friday, November 30, 2001 3:50 PM

To: ASP Databases

Subject: [asp_databases] RE: searching for multiple items in one

database





you need to put which field should match each of the LIKEs..



SELECT * FROM newbookings WHERE field1 LIKE '%40%' AND field 2 LIKE '%2001%

etc.



-Kim











-----Original Message-----

From: tim.puffer@t... [mailto:tim.puffer@t...]

Sent: 30. november 2001 21:32

To: ASP Databases

Subject: [asp_databases] searching for multiple items in one database





I am trying to look for several items in one database.  I think i am on

the write track ??? could someone take a look at my code and let me know

if i am close?



this is the response.write that it generates.



SELECT * FROM newbookings WHERE LIKE '%40%' AND '%2001%' AND '%bic%'ORDER

by



Here is my code.



<%

	dim varIn, varFind1, varfind2,varfind3, varfind4, varfind5,

cnbook, connstr, theMessage, sqltext, rsbook, varsort, total

	varsort= Request.Form("sort")

	varIn = Request.form("in")

	varFind1 = Request.form("find1")

	varFind2 = Request.form("find2")

	varFind3 = Request.form("find3")

	varFind4 = Request.form("find4")

	varFind5 = Request.form("find5")

	response.write varFind1

	response.write varFind2

	response.write varFind3

	response.write varFind4

	response.write varFind5

'	response.write varIn

'	reponse.write varsort

'	reponse.write total



set cnbook = server.createobject ("adodb.connection")

connstr = "driver={microsoft access driver (*.mdb)};" & _

			"dbq=" & server.MapPath("bookings 2000.mdb")

cnbook.open connstr, "", ""



select case UCase (Request.QueryString("view"))

	Case "ALL"

		TheMessage = "All Bookings"

		sqltext = "SELECT * FROM newbookings"

	Case Else

		TheMessage = "Search Results"

		sqltext = "SELECT * FROM newbookings"

		sqltext = sqltext & " WHERE "

		sqltext = sqltext & " LIKE  '%" & varfind3 & "%'"

		sqltext = sqltext & " AND  '%" & varfind4 & "%'"

		sqltext = sqltext & " AND  '%" & varfind5 & "%'"

		sqltext = sqltext & "ORDER by " & varsort

End Select

Response.Write sqltext

set rsbook = cnbook.Execute(sqltext)<<< this is line 39>>>>



thanks in advance.






$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid

=rn_ebooks









tim.puffer@t...


$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid

=rn_ebooks








$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid

=rn_ebooks





 



tim.puffer@t...


$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid

=rn_ebooks



 




$subst('Email.Unsub')





  Return to Index