Here's the situation, I'm trying to generate a SQL select statement with 

muliple AND clauses based on form fields.  My problem is getting rid of the 

AND clause when it's not needed.



Here's what I have so far:



DIM series,title,pp,org_name,region,pd_no

     pd_no  = Request.QueryString("pd_no")

     series = Request.QueryString("series")

     title = Request.QueryString("title")

     pp = Request.QueryString("pp")

     org_name = Request.QueryString("org_name")

     region = Request.QueryString("region")



sqlTEXT = "select * from tbl_pd where "

If pd_no <> "" then sqlTEXT = sqlTEXT & "(pd_no = ' "& pd_no &" ') and " 

end if

If series <> "" then sqlTEXT = sqlTEXT & "(series = ' "& series &" ') and " 

end if

If title <> "" then sqlTEXT = sqlTEXT & "(title = ' " & title & " ') and " 

end if

If pp <> "" then sqlTEXT = sqlTEXT & "(pp = ' " & pp & " ') and " end if

If org_name <> "" then sqlTEXT = sqlTEXT & "(org_name = ' " & org_name & " 

') and " end if

If region <> "" then sqlTEXT = sqlTEXT & "(region =  " & region &" )" end 

if

			

Response.write(sqlTEXT)

		

Set dataRec = dataConn.execute(sqlTEXT)



Thanks



Scott Stewart

do something like



SQLHEAD="select * from tableX where "

SQLBODY=""



if length(pd_no)>0

	SQLBODY=SQLBODY&" and (pd_no='"&pd_no"')"

end if

if second<>""

	SQLBODY=SQLBODY&" and (second='"&second"')"

end if



...



then at the end just trim off the first four characters - it is quicker 

to do it this way rather than check to see if it is the first addition 

to SQLBODY each time.



SQLBODY=Mid(SQLBODY,0,4)



> -----Original Message-----

> Sent: 24 August 2000 21:25

> To: asp_databases@p...; scott.a.stewart@c...

> Subject: FW: [asp_databases] creating SQL statement (with AND clauses)

> based on form fields

> 

> 

> Here's the situation, I'm trying to generate a SQL select 

> statement with 

> muliple AND clauses based on form fields.  My problem is 

> getting rid of the 

> AND clause when it's not needed.

> 

> Here's what I have so far:

> 

> DIM series,title,pp,org_name,region,pd_no

>      pd_no  = Request.QueryString("pd_no")

>      series = Request.QueryString("series")

>      title = Request.QueryString("title")

>      pp = Request.QueryString("pp")

>      org_name = Request.QueryString("org_name")

>      region = Request.QueryString("region")

> 

> sqlTEXT = "select * from tbl_pd where "

> If pd_no <> "" then sqlTEXT = sqlTEXT & "(pd_no = ' "& pd_no 

> &" ') and " 

> end if

> If series <> "" then sqlTEXT = sqlTEXT & "(series = ' "& 

> series &" ') and " 

> end if

> If title <> "" then sqlTEXT = sqlTEXT & "(title = ' " & title 

> & " ') and " 

> end if

> If pp <> "" then sqlTEXT = sqlTEXT & "(pp = ' " & pp & " ') 

> and " end if

> If org_name <> "" then sqlTEXT = sqlTEXT & "(org_name = ' " & 

> org_name & " 

> ') and " end if

> If region <> "" then sqlTEXT = sqlTEXT & "(region =  " & 

> region &" )" end 

> if

> 			

> Response.write(sqlTEXT)

> 		

> Set dataRec = dataConn.execute(sqlTEXT)

> 

> Thanks

> 

> Scott Stewart

>