ASP variables in sql statement

#1 KamakaZ

Senior Member

Group: Members
Posts: 548
Joined: 26-August 08
Gender:Male
Location:Victoria

Posted 11 December 2009 - 08:49 AM

any idea's why this wouldn't work?

<html>
<body>
<%
' Declaring variables
Dim back_height, arms, data_source, con, sql_select

' A Function to check if some field entered by user is empty
Function ChkString(string)
	If string = "" Then string = " "
	ChkString = Replace(string, "'", "''")
End Function


' Receiving values from Form
arms= ChkString(Request.Form("arms"))
back= ChkString(Request.Form("back"))




	data_source = "DSN=GunnSrvModODBC"
	sql_select = "SELECT * FROM ZZ_Chair WHERE back ='" & back & "'" IF arms = 1 { & " AND na <> 0" } END IF 
	
	Response.Write sql_select

	' Creating Connection Object and opening the database
	Set con = Server.CreateObject("ADODB.Connection")
	con.Open data_source
	con.Execute sql_select
	
	' Done. Close the connection
	con.Close
	Set con = Nothing
	%>


	<br />
	<h4>Complete...</h4>
	<h5>Show Options:</h5>
	<form>
	<input type="button" onclick="window.location='index.asp'" value="Back"/>
	</form>
	<hr />

</body>
</html>

it's obviously a problem with the sql, it error's on line 22 always around the back = '"& back &"' ... section sometimes after the = sometimes after the second &.

~ Kam

If I am helping you and don't reply in 24 hours please send me a PM

There's no place like 127.0.0.1
There are 10 types of people in the world, those that can read binary, and those who can't.

#2 groovicus

Hail Groovicus!

Group: Moderator
Posts: 9,522
Joined: 05-June 04
Gender:Male
Location:Centerville, SD

Posted 11 December 2009 - 12:08 PM

You need to print out the content of the query string to make sure it is being built like you think it is. If it was a SQL syntax error, the error would be in the same place all the time. Since it isn't, make sure that there isn't some strange character in the strings being represented by your variable.

What happens to the query where arms = 1 and na = 0?

"Take the risk of thinking for yourself, much more happiness, truth, beauty, and wisdom will come to you that way" - Christopher Hitchens

#3 KamakaZ

Senior Member

Group: Members
Posts: 548
Joined: 26-August 08
Gender:Male
Location:Victoria

Posted 11 December 2009 - 12:25 PM

na is a field in the table, if it's avaliable with arm's it has a price, if not it's 0. double checked no weird characters are in the string... at the moment back should be = to M and arm's = to 0.

If I am helping you and don't reply in 24 hours please send me a PM

There's no place like 127.0.0.1
There are 10 types of people in the world, those that can read binary, and those who can't.

#4 groovicus

Hail Groovicus!

Group: Moderator
Posts: 9,522
Joined: 05-June 04
Gender:Male
Location:Centerville, SD

Posted 11 December 2009 - 12:34 PM

Could you print out the actual string so that I can see that? And the error message?

EDIT: Also, does the query work if arm = 0?

EDIT2: Also, try hardcoding a query and see if it works. And then finally, try the hardcoded query from the sql command line and see what happens.

"Take the risk of thinking for yourself, much more happiness, truth, beauty, and wisdom will come to you that way" - Christopher Hitchens