Where clause not working

Hi

I'm using asp and an Access database to create a combo box sourced from an Access Table to update another Access Table. The drop down data is to be controlled by a value on the page and the initial part of the code is as follows:

<%
Dim objConnection
Dim objRecordset

Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "DSN=TilesW"

Set objRecordset = Server.CreateObject("ADODB.Recordset")

Dim strSQL
strSQL = "SELECT RangeRef, ID, Available FROM OnSite "& _
"WHERE RangeRef = 'form.RangeID.value'; "

objRecordset.Open strSQL, objConnection
%>

The where statement seems to be the problem because if I replace it with a real RangeID value as below everything works fine.

"WHERE RangeRef = 2; "

I'm not sure if it is to do with quotes, single quotes, spaces, etc, etc, but I've tried numerous variations ang get all messages including "mismatch in criteria expression" for the version above.

I'm quite new to this and would appreciate some help please.

Thanks in advance
David

Make the above change in red if this a number field. If it is a text field use: = '" & form.RangeID.value & "'"

Hi

It is a number field. I get the following message using the number option:
Syntax error (missing operator) in query expression 'RangeRef = & form.RangeID.value'.

And the following using the text option:
Expected end of statement
/TileLibrary/pages/html/updates.asp, line 312, column 47
"WHERE RangeRef = '" & form.RangeID.value & "'"; "
----------------------------------------------^

Thanks

Hi,

if its a number field, use this query, should be able to just copy and paste it




hope this helps

Thanks, but error message:

Expected end of statement
/TileLibrary/pages/html/updates.asp, line 345, column 40
"WHERE RangeRef = " & form.RangeID.value; "

Try

Thanks. Tried that, message below:
Expected end of statement
/TileLibrary/pages/html/updates.asp, line 345, column 40
"WHERE RangeRef = " & form.RangeID.value; "

Hi,


hope this helps

I did leave the ; " at the end, but get the same message:
Expected end of statement
/TileLibrary/pages/html/updates.asp, line 345, column 40
"WHERE RangeRef = " & form.RangeID.value; "

ok

do a response.write strSQL, and response.end
after this line and post the result here

Put code like this:
strSQL = "SELECT RangeRef, ID, Available FROM OnSite "& _
"WHERE RangeRef = " & form.RangeID.value; "
response.write strSQL
response.end

The page doesn't display so there is no result, just the error message as before;
Expected end of statement
/TileLibrary/pages/html/updates.asp, line 345, column 40
"WHERE RangeRef = " & form.RangeID.value; "

Am I misunderstanding you.

did you copy and paste this code here?

the ;" must NOT be there, that is what is causing the error.

hope this helps

OK. Code looks like this:
strSQL = "SELECT RangeRef, ID, Available FROM OnSite "& _
"WHERE RangeRef = " & form.RangeID.value
response.write strSQL
response.end

Message:
Object required: ''
/TileLibrary/pages/html/updates.asp, line 344

Thanks