Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Confusing SQL Syntax Error 1

Status
Not open for further replies.
mattster

mattster

Programmer
Apr 26, 2007
15
US
I'm getting this error in my page:

===========================
Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[TCX][MyODBC]You have an error in your SQL syntax near '' at line 1

/forum/test5.asp, line 38
===========================

I really don't see any reason that there's something incorrect. But I'm also a bit new, so I may be wrong. Here's my file:

===========================
<%
'Get the Topic ID
TopicID = Request.QueryString(&quot;topic&quot;)
IF Request.QueryString(&quot;topic&quot;) = &quot;&quot; THEN
TopicID = &quot;1&quot;
ELSE IF Request.QueryString(&quot;topic&quot;) = &quot;0&quot; THEN
TopicID = &quot;1&quot;
END IF

'Database Connection/Read recordsets
strConnection = &quot;driver={MySQL};server=localhost;uid=mattster;pwd=chair;database=forum&quot;
SET Conn = Server.CreateObject(&quot;ADODB.Connection&quot;)
Conn.Open strConnection

strMessages = &quot;SELECT * FROM Messages WHERE ForumID = &quot; & TopicID & &quot; AND Original = 0 ORDER BY Date DESC&quot;
SET rsMessages = Conn.Execute(strMessages)

strSettings = &quot;SELECT * FROM Settings&quot;
SET rsSettings = Conn.Execute(strSettings)
%>

<HTML>
<HEAD>
<TITLE><%=rsSettings(&quot;SiteName&quot;)%></TITLE>
</HEAD>
<BODY bgcolor=&quot;#<%=rsSettings(&quot;BGColor&quot;)%>&quot; text=&quot;#<%=rsSettings(&quot;TextColor&quot;)%>&quot; link=&quot;#<%=rsSettings(&quot;LinkColor&quot;)%>&quot; vlink=&quot;#<%=rsSettings(&quot;VLinkColor&quot;)%>&quot; alink=&quot;#<%=rsSettings(&quot;ALinkColor&quot;)%>&quot;>

(Put code for an include file here soon)<br><br><br>

<%
DO UNTIL rsMessages.EOF
%>

<%=rsMessages(&quot;Subject&quot;)%> - <%=rsMessages(&quot;Author&quot;)%> (<%=rsMessages(&quot;Date&quot;)%>/<%=rsMessages(&quot;IP&quot;)%>)<br>

<%
strReplies = &quot;SELECT messageid, subject, author, date FROM Messages WHERE Original = &quot; & rsMessages(&quot;MessageID&quot;)
SET rsReplies = Conn.Execute(strReplies)
DO UNTIL rsReplies.EOF
%>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<%=rsMessages(&quot;Subject&quot;)%> - <%=rsMessages(&quot;Author&quot;)%> (<%=rsMessages(&quot;Date&quot;)%>/<%=rsMessages(&quot;IP&quot;)%>)<br>

<%
rsReplies.MoveNext
LOOP
%>

<%
rsMessages.MoveNext
LOOP
%>

<%
rsMessages.Close
rsSettings.Close
END IF
rsReplies.Close
%>

<br><br><br>(Put code for an include file here soon too)

</body>
</html>
===========================
 
Sort by date Sort by votes
Well, not positive, sine I mostly work with Access, (not enough money to learn with sql server), but I think sql server doesn't allow * for wild card. Try puting in specific fields into your code:

strMessages = &quot;SELECT Messages.field1, Messages.field2, Messages.firld3, Messages.field4 FROM Messages WHERE ForumID = &quot; & TopicID & &quot; AND Original = 0 ORDER BY Date DESC&quot;

Hope that work!
-Ovatvvon :cool:
 
Upvote 0 Downvote
The wild card for SQL Server is %

:)
penny.gif
penny.gif
 
Upvote 0 Downvote
SQL server does allow the model SELECT * FROM TABLE as it supports genuine SQL statements of which SELECT * is one. Paul is saying that the wild card in SQL is % meaning that if you are using the LIKE statement you would use a % as a wildcard operator, so the statement SELECT * FROM TABLE WHERE NAME LIKE 'N%' would return all the names beginning with N.

On to your problem...

The error is generated is saying your SQL syntax is wrong near &quot;. Im surprised you have a &quot; in your SQL statement as I have never needed to include one unless
it is part of a string I am using. Check and double check all the SQL strings you are generating by Response.Write-ing them to the screen instead of executing them. If you can see no error copy and paste them into a query analizer and run them against the same data source. A query analizer will frequently return more detailed errors.

G -GTM Solutions, Home of USITE-
-=
 
Upvote 0 Downvote
well i think the error is referring to my rsReplies thing. I don't see any &quot;s in there that I can see. So that's why it's so confusing to me
 
Upvote 0 Downvote
What line is #38??? And like Geee suggested, response.write the value of the SQL statement in question so you can see exactly what is being passed to the database. Post both here if the answer still is not apparent.

penny.gif
penny.gif
 
Upvote 0 Downvote
This is the area around line#38:
=======================
<%
strReplies = &quot;SELECT messageid, subject, author, date FROM Messages WHERE Original = &quot; & rsMessages(&quot;MessageID&quot;)
SET rsReplies = Conn.Execute(strReplies)
=======================

i'm not sure what you mean by Response.Write the values of the SQL statement?
 
Upvote 0 Downvote
Ok, by response.write, we mean to output the value of the variable to the screen. It's pretty much the most effective way of tracking down the error. Put this code right before your SET statement:

response.write(strReplies)

So that the little code block would look like this:

strReplies = &quot;SELECT messageid, subject, author, date FROM Messages WHERE Original = &quot; & rsMessages(&quot;MessageID&quot;)
response.write(strReplies)
SET rsReplies = Conn.Execute(strReplies)

Now, the contents of the variable, 'strReplies' will be output to the screen before your error gets thrown. You can then copy and paste that statement into the Access query builder and try to execute it. You're very likely to get a more intelligible error message from Access.

Let me know how it works out.
paul
penny.gif
penny.gif
 
Upvote 0 Downvote
If, in the code snippet below, MessageID is a string field, then your problem is the way you enter string values in your SELECT statement. Here is your statement:

strReplies = &quot;SELECT messageid, subject, author, date FROM Messages WHERE Original = &quot; & rsMessages(&quot;MessageID&quot;)

Here is what it should be:

strReplies = &quot;SELECT messageid, subject, author, date FROM Messages WHERE Original = '&quot; & rsMessages(&quot;MessageID&quot;) & &quot;'&quot;

Hope this helps.
 
Upvote 0 Downvote
Doesn't matter. Access can still anylize the sql statement. sql is sql. :cool:

-Ovatvvon
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
Problem: sql-Select with request.form
2
Replies
22
Views
319
leifoet
leifoet
Krus1972
  • Locked
  • Question
msxml3.dll error '80072f7d' - An error occurred in the secure Channel support
Replies
7
Views
348
Krus1972
Krus1972
leifoet
  • Locked
  • Question
Using date picker gives an error message, only at application start
Replies
1
Views
192
MarkSweetland
MarkSweetland
chikwendu
  • Locked
  • Question
OraOLEDB error '80040e4b'
Replies
1
Views
339
xwb
xwb
jd.ford1
  • Locked
  • Question
Need help with syntax
Replies
10
Views
171
jd.ford1
jd.ford1

Part and Inventory Search

Sponsor

Back
Top