Select statement error 3

[dwg23]

Hello,
This is probably pretty basic but I can’t seem to figure it out. I am writing a select statement.
If I write it like this:
<%dim operator
operator = request.querystring("op")
if request.form("SUBMIT") = "Search" then
SQL="Select * From tbl_customer_support"
set DB = server.createobject("ADODB.Connection")
DB.Open constring
set RS = server.createobject("ADODB.Recordset")
RS.Open Sql,DB,adOpenForwardOnly%>

It works just fine.

I am using Microsoft SBS 2003 with SQL Server 2003

Please help!






If I add this:

<%
dim operator
operator = request.querystring("op")
if request.form("SUBMIT") = "Search" then
SQL="Select * From tbl_customer_support where
op="&operator
set DB = server.createobject("ADODB.Connection")
DB.Open constring
set RS = server.createobject("ADODB.Recordset")
RS.Open Sql,DB,adOpenForwardOnly%>

Then I get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.
/web_staff/cslsearch.asp, line 38

I have tried every variation that I can think of and still get the error.

Please help!

 

[DotNetGnat]

that means your variable 'operator' is empty...make sure that you are passing the query string parameter correctly...

-DNG

 

[dwg23]

The variable is populated and when I say
SQL="Select * From tbl_customer_support where op="dkr
I get the same error
Thanks
Dave

 

[jasonlhoward]

Try...

SQL=" Select * From tbl_customer_support "
SQL = SQL & " where op= '" & operator & "'"

if operator is a string. If it's numerical then your code would work if it contained valid data.
Always, always validate data before using it.



-Jason

 

[dwg23]

Jason,
The information is a string and your idea worked great!
Can you give me an idea why it would not work before?
I am still kind of confused as to why the way I was doing it used to work and now it doesn't. Could it be because we switched to SBS 2003?
Thanks
Dave

 

[SQLBill]

Do you mean SQL Server 2005? As far as I know there isn't a SQL Server 2003.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLSister]

More than likely the example you pulled the code from was sending numeric data and thus did not need the quotes. Strings have always needed the single quotes. Incidentally never use select * unless you fully intend to use every single bit of data in the table. For performance reasons you should never ask for a field that you are not using.

Questions about posting. See faq183-874

 

[SQLBill]

SQL Server requires single quotes around a string.

SELECT * FROM mytable
WHERE string1 = 'string'

But Integers don't need single quotes.

SELECT * FROM mytable
WHERE mynumber = 12

So, your variable needed to be put into single quotes since it was a string.

-SQLBill

Posting advice: FAQ481-4875

 

[dwg23]

Thanks everyone for your help and information. I am now rockin and rolling on my page!
And yes SSQLBill it is a 2005 server.

Thanks again
Dave