syntax error

[shank]

I get this error and can't figure out the correct syntax.
Doesn't seem to make a difference changing the quotes around.
Thanks!
---------------------
Syntax error (missing operator) in query expression 'GenKTitles.Title= White
Christmas'.
---------------------
<%
Dim DataConn, SQL
SearchTerm = "White Christmas"
Set DataConn = Server.CreateObject("ADODB.Recordset")
DataConn.ActiveConnection = MM_GenKAccess_STRING
SQL = "SELECT GenKStock.*, GenKTitles.* "
SQL = SQL & "FROM GenKStock INNER JOIN GenKTitles ON GenKStock.OrderNo =
GenKTitles.ItemNumber "
SQL = SQL & "WHERE GenKTitles.Title= " + SearchTerm + ""
Set rsResults = DataConn.Open(SQL)
%>

 

[John]

SQL = SQL & " WHERE GenKTitles.Title= "" + SearchTerm + ""

 

[John]

or

SQL = SQL & " WHERE GenKTitles.Title= " & Chr(34) & Trim(SearchTerm) &
Chr(34)

Also check www.aspfaq.com to handle the problem with ' and " in string. In
my country name or often like D'haese whta cause a problem when you surround
the searchstring by "'" + searchstring + "'"

 

[Aaron [SQL Server MVP]]

SQL = SQL & "WHERE GenKTitles.Title= " + SearchTerm + ""

Try:

SQL = SQL & "WHERE GenKTitles.Title= '" & SearchTerm + "'"

 

[Aaron [SQL Server MVP]]

Sorry, that last + should be &

Shank, + is addition, while it doubles as string concatenation in VBScript,
you should try to always use & to avoid issues.

--
http://www.aspfaq.com/
(Reverse address to reply.)

 

[shank]

That worked! Thanks! But now I'm getting this error...
The query should return valid records. What object is missing?
------------------------
Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'open(...)'

------------------------
<%
Dim DataConn, SQL
SearchTerm = "White Christmas"
Set DataConn = Server.CreateObject("ADODB.Recordset")
DataConn.ActiveConnection = MM_GenKAccess_STRING
SQL = "SELECT GenKStock.*, GenKTitles.* "
SQL = SQL & "FROM GenKStock INNER JOIN GenKTitles ON GenKStock.OrderNo =
GenKTitles.ItemNumber "
SQL = SQL & "WHERE GenKTitles.Title= '" & SearchTerm + "'"
Set rsResults = DataConn.Open(SQL) <-- ERROR on this line
%>

 

[Bob Lehmann]

You created an instance of a Recordset not a Connection...
Set DataConn = Server.CreateObject("ADODB.Recordset")

Bob Lehmann

 

[Aaron [SQL Server MVP]]

Dim DataConn, SQL

SearchTerm = "White Christmas"
Set DataConn = Server.CreateObject("ADODB.Recordset")
DataConn.ActiveConnection = MM_GenKAccess_STRING
SQL = "SELECT GenKStock.*, GenKTitles.* "
SQL = SQL & "FROM GenKStock INNER JOIN GenKTitles ON GenKStock.OrderNo =
GenKTitles.ItemNumber "
SQL = SQL & "WHERE GenKTitles.Title= '" & SearchTerm + "'"
Set rsResults = DataConn.Open(SQL) <-- ERROR on this line

Where are you getting this syntax from? It's all wrong. Please tell us
where it came from so we can correct the source and ask them to stop
providing faulty code.

First off, you created an ADODB.Recordset as an object with conn in the
name. Surely that's a mistake. Second, a connection object returns a
recordset object to rsResults by calling connectionObject.EXECUTE(sql), not
connectionObject.OPEN(sql). How about this:

SearchTerm = "White Christmas"
set conn = CreateObject("ADODB.Connection")
conn.open MM_GenKAccess_STRING
sql = "SELECT ... "
set rsResults = conn.execute(sql)

And PLEASE, PLEASE, PLEASE STOP USING SELECT * !!!!!!!!!!!!!!!!! And it
wouldn't hurt to stop letting Macromedia products create big, unreadable and
error-prone variable names, either.