handling form variables in a query

[PushCode]

I get a syntax error when the page initially loads. Search form and asp handler all on the same page (provDir.asp). My If Then statements are supposed to only use their contained code if the message respective form field isn't empty. The error is pointing to the "If" in the first If then statement. Any help would be great. Thanks!

Here's the form:

<form name="srchProv" method="post" action="provDir.asp">
  	<tr>
	  <td>Last Name:</td>
	  <td><input type="text" name="LastName" value="" /></td>
	</tr>
	<tr>
	  <td>City:</td>
	  <td><input type="text" name="city" value="" /></td>
	</tr>
	<tr>
	  <td>Specialty:</td>
	  <td><input type="text" name="specialty" value="" /></td>
	</tr>
	<tr>
	  <td colspan="2"><input type="submit" name="submitit" value="Search Providers" /></td>
	</tr>
  </form>

Here's the asp:

' FORM Variables
sLname = Request.QueryString("LastName")
sCity = Request.QueryString("city")
sSpecialty = Request.QueryString("specialty")

sSQLStatement = "SELECT LastName, FirstName, License, [practice name] AS Practice_Name, Address, City, State, Zip, Specialty, Phone, County FROM [Provider 2006] WHERE State IS NOT NULL"

If sLname <> "''" Then
		sSQLStatement = sSQLStatement & " AND LastName LIKE '%" & sLname & "%'" & _
	End If
	If sCity <> "''" Then
		sSQLStatement = sSQLStatement & " AND LastName LIKE '%" & sLname & "%'" & _
	End If
	If sSpecialty <> "''" Then
		sSQLStatement = sSQLStatement & " AND LastName LIKE '%" & sLname & "%'"
	End If

 

[Chopstik]

If the code is only supposed to run provided that the value is blank, then you need to check if it is blank (and not containing two single quotes). Try this:

If sLname <> "" Then
  sSQLStatement = sSQLStatement & " AND LastName LIKE '%" & sLname & "%'" & _
    End If

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[gmmastros]

Try removing the line continuation at the end.

sSQLStatement = sSQLStatement & " AND LastName LIKE '%" & sLname & "%'" [!][s]& _[/s][/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chopstik]

<looking sheepish> Yeah, I guess you could try the more obvious solution that George suggested, too... </still looking sheepish>



------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[PushCode]

Thanks. Did both of those suggestions and it's now operating without error. However, when I run a search it doesn't change the results. Meaning, no matter what I search for or which form field I search from, the query returns all records every time.

Any ideas?

Thanks!

 

[gmmastros]

    If [!]sLname[/!] <> "''" Then
        sSQLStatement = sSQLStatement & " AND [!]LastName[/!] LIKE '%" & [!]sLname[/!] & "%'"
    End If
    
    If [blue]sCity[/blue] <> "''" Then
        sSQLStatement = sSQLStatement & " AND [blue]LastName[/blue] LIKE '%" & [blue]sLname[/blue] & "%'"
    End If
    
    If [green]sSpecialty[/green] <> "''" Then
        sSQLStatement = sSQLStatement & " AND [green]LastName[/green] LIKE '%" & [green]sLname[/green] & "%'"
    End If

If I had to guess... I'd say you got a little too 'copy/paste' happy. You need to slow down and be a bit more careful.

Also... while you're at it... Why would those variables be equal to a pair of single-quotes?

Perhaps it should be...

    If sLname <> "" Then
        sSQLStatement = sSQLStatement & " AND LastName LIKE '%" & sLname & "%'"
    End If
    
    If sCity <> "" Then
        sSQLStatement = sSQLStatement & " AND City LIKE '%" & sCity & "%'"
    End If
    
    If sSpecialty <> "" Then
        sSQLStatement = sSQLStatement & " AND Specialty LIKE '%" & sSpecialty & "%'"
    End If

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PushCode]

But copy/paste is fun!!! Anyway, wow! Can't believe I did that. I had removed the single quotes based on Chopstik's suggestion.

It's still not refining the results at all. Here's my query now:

sSQLStatement = "SELECT LastName, FirstName, License, [practice name] AS Practice_Name, Address, City, State, Zip, Specialty, Phone, County FROM [Provider 2006] WHERE State IS NOT NULL"
	
	If sLname <> "" Then
		sSQLStatement = sSQLStatement & " AND LastName LIKE '%" & sLname & "%'"
	End If
	If sCity <> "" Then
		sSQLStatement = sSQLStatement & " AND City LIKE '%" & sCity & "%'"
	End If
	If sSpecialty <> "" Then
		sSQLStatement = sSQLStatement & " AND Specialty LIKE '%" & sSpecialty & "%'"
	End If

Any thoughts?

 

[gmmastros]

You have this...
[tt][blue]
<form name="srchProv" [!]method="post"[/!] action="provDir.asp">
[/blue][/tt]

And then later, this...

[tt][blue]
sLname = Request.[!]QueryString[/!]("LastName")
[/blue][/tt]

Form variable are always passed through the request object. When method="Post", your form variables are passed through Request.Form. When method="get", your form variables are passed through the url and end up in the Request.QueryString object.

So, you have a couple options.

1. You could change your method to.... method="get"
2. You could change how you retrieve your values to Request.Form("VariableName")


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PushCode]

Brilliant! Thanks all. It's working like a charm now.