asp syntax error - URGENT, Please

samosa · Aug 30, 2002

I ran this code in access and it works.
We just recently moved to sql server, now I am getting two errors.
The first is:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AND'.
and the code is:
rsget.Open "SELECT * FROM board WHERE ThreadID= "&id&" AND ThreadP=0", conn

and the second is:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '8'.

and the code is not even inserting anything into the sql server db.
<%
response.buffer = true

topic = Replace(Server.HTMLEncode(Request.Form("topic&quot

),"'","''&quot

name = Replace(Server.HTMLEncode(Request.Form("name&quot

),"'","''&quot

email = Replace(Server.HTMLEncode(Request.Form("email&quot

),"'","''&quot

http = Replace(Server.HTMLEncode(Request.Form("http&quot

),"'", "''&quot

body = Request.Form("body&quot

body = Replace(body , "'", "''&quot

body = Replace(body, vbcrlf, " &quot

if topic = "" OR name = "" OR body = "" then
Response.Redirect("post.asp?error=1&quot

end if
if instr(LCase(body), "<script&quot

<> 0 then
Response.Redirect("post.asp?error=2&quot

end if
if email <> "" then
if instr(email, "@&quot

= 0 OR instr(email, ".&quot

= 0 then
Response.Redirect("post.asp?error=3&quot

end if
end if
if http <> "" then
if instr(http, "

http://")

= 0 OR instr(http, ".&quot

= 0 then
Response.Redirect("post.asp?error=4&quot

end if
end if

set conn = Server.CreateObject("ADODB.connection&quot

conn.open "DSN=BBoard"
sqlstring = "INSERT INTO board ( tdate, topic, name, email, http, body ) " &_
"values ( # " &now()& " #, ' " &topic& " ' , ' " &name& " ' , ' " &email& " ', ' " &http& " ' , ' " &body& " ')"
conn.Execute(sqlstring)
conn.Close
set conn = Nothing
Response.redirect "default.asp"
%>

JuanitaC · Aug 30, 2002

The easiest way to debug SQL statements is to write them to the screen before executing them. If you can't see the error, try copying the written sql statement from your browser to Query Analyzer and execute it there.

The first statement, I can't see anything obvious, unless your ThreadID is a char or varchar type instead of numeric. Print it to the screen and see if that helps.

The second statement, I do see a problem. Access uses # # around dates, but SQL Server uses ' ' (single quotes). Change that and see what happens.

samosa · Aug 30, 2002

Thanks for your response!
I have wrapped ''(single quotes) around the insert statement but still I cannot get it to work.
It inserts records into the sql server db but does not display the result to a bulletin board.
In my sql server db, I have msgId
as identity seed ( because it wa autonumber in access).
then I have threadId, threadp and counts.
Please help with this. Here is the entire code:
<%
response.buffer = true

topic = Replace(Server.HTMLEncode(Request.Form("topic&quot

),"'","''&quot

name = Replace(Server.HTMLEncode(Request.Form("name&quot

),"'","''&quot

email = Replace(Server.HTMLEncode(Request.Form("email&quot

),"'","''&quot

http = Replace(Server.HTMLEncode(Request.Form("http&quot

),"'", "''&quot

body = Request.Form("body&quot

body = Replace(body , "'", "''&quot

body = Replace(body, vbcrlf, " &quot

if topic = "" OR name = "" OR body = "" then
Response.Redirect("post.asp?error=1&quot

end if
if instr(LCase(body), "<script&quot

<> 0 then
Response.Redirect("post.asp?error=2&quot

end if
if email <> "" then
if instr(email, "@&quot

= 0 OR instr(email, ".&quot

= 0 then
Response.Redirect("post.asp?error=3&quot

end if
end if
if http <> "" then
if instr(http, "

http://")

= 0 OR instr(http, ".&quot

= 0 then
Response.Redirect("post.asp?error=4&quot

end if
end if

set conn = Server.CreateObject("ADODB.connection&quot

conn.open "DSN=BBoard"
sqlstring = "INSERT INTO board ( tdate, topic, name, email, http, body ) " &_
"values ( '" &now()& "', ' " &topic& " ' , ' " &name& " ' , ' " &email& " ', ' " &http& " ' , ' " &body& " ')"
conn.Execute(sqlstring)
conn.Close
set conn = Nothing
Response.redirect "default.asp"
%>

JuanitaC · Aug 30, 2002

Did you say that the record is being inserted in to the database? If so, this is not where your problem is.

Is there an error generated? If so, what is it?
Are you not getting back the results you expect? If so, are the results you expect in your database? If the records are there, post your select statement.

samosa · Aug 30, 2002

Thanks JuanitaC:
Here is the entire code with select statement:

<html>
<head>
<title> [ Public Works Bullettin Board ] - Active Server Page Bulletin Board </title>
<style type="text/css">
A:link, A:visited { text-decoration: none; color: 'silver' }
</style>
</head>
<body bgcolor="#006699">

Public Works Bullettin Board 
<%
dim currentpage
if request.querystring("currentpage&quot

= "" OR request.querystring("currentpage&quot

< 1 then
currentpage = 1
else
currentpage = request.querystring("currentpage&quot

end if
Session("currentpage&quot

=currentpage
set conn = Server.CreateObject("ADODB.connection&quot

Conn.open "DSN=BBoard"

'sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
'"Data Source=" & Server.MapPath("\BBoard\db\betaboard.mdb&quot

& ";" & _
'"Persist Security Info=False"
'conn.Open(sConnection)

%>
<div align="center">
<table border="0" width="90%">
<tr>
<td width="69%">
<% Response.Write "The time now is: "&now()&""%>
</td>
<td><a href='search.asp'>Search</a></td>
<td width="25%">

<%Set rsot = Server.CreateObject("ADODB.Recordset&quot

rsot.Open "SELECT * FROM board where threadP=0",conn, 3%>
<%response.write "Total Topics: "& rsot.recordcount &""%>
<% rsot.Close %>
 - 
<%Set rsott = Server.CreateObject("ADODB.Recordset&quot

rsott.Open "SELECT * FROM board",conn, 3%>
<%response.write "Total Posts: "& rsott.recordcount &""%>
<% rsott.Close %>
</td>
</tr>
</table>
</div>
<div align=center>
<table border=1 width=90% cellpadding=0 cellspacing=0>
<tr>
<td width=40%>Thread</td>
<td width=17%>Thread Starter</td>
<td width=10%>Replies</td>
<td width=10%>Views</td>
<td width=23%>Last Post</td>
</tr>
</table>
</div>
<%
Set rsttrv = Server.CreateObject("ADODB.Recordset&quot

rsttrv.CursorLocation = 3 'set cursorlocation to aduserclient
rsttrv.CacheSize = 10 'cache the number of record to display per page into cache
rsttrv.Open "SELECT * FROM board WHERE ThreadP=0 ORDER BY msgID DESC", conn, 3

if rsttrv.EOF then Response.Write "No Post Yet"
rsttrv.PageSize = 10 'Set that each page will list 10 records

Dim TotalPages, TotalRecords
TotalPages = rsttrv.PageCount 'Pagecount will count how many page will need if 10 record fill one page
TotalRecords = rsttrv.RecordCount

if rsttrv.RecordCount > 0 then
rsttrv.AbsolutePage = currentpage 'the currentpage value will be the rs.Absolute value
end if

Dim count 'Counting variable for our recordset
count = 0
Do While Not rsttrv.EOF AND count < rsttrv.PageSize
%>
<div align=center>
<table border=0 width=90% cellpadding=0 cellspacing=0>
<tr>
<td width=40%><a href='view.asp?msgID=<%=rsttrv("msgID&quot

%>'><%=rsttrv("topic&quot

%></a>
<% msgID = rsttrv("msgid&quot

Set rsnay = Server.CreateObject("ADODB.Recordset&quot

rsnay.CacheSize = 10 'cache the number of record to display per page into cache
rsnay.PageSize = 10 'Set that each page will list 10 records
rsnay.Open "SELECT * FROM board WHERE ThreadID="&msgID&" ORDER BY msgID", conn, 3
TotalPage = rsnay.PageCount 'Pagecount will count how many page will need if 10 record fill one page
if TotalPage > 1 then
Response.Write ""
Response.Write "<< Page: "
Response.Write "<select name='whatever' onChange='window.location=this.options[this.selectedIndex].value'>"
Response.Write "<option value='view.asp?msgID="&msgID&"&seepage=1 SELECTED'> - 1 - </option>"
for i = 1 to TotalPage
Response.Write "<option value='view.asp?msgID="&msgID&"&seepage="&i&"'> - "&i&" - </option>"
next
Response.Write "</select>"
Response.Write " >>"
end if
%>
</td>
<td width=17% align=center>
<% if instr(rsttrv("email&quot

& "" , "@&quot

> 0 then %>
<a href='mailto:<%=trim(rsttrv("email&quot

)%>'><%=trim(rsttrv("name&quot

)%></a>
<%else%>
<%=rsttrv("name&quot

%>
<% end if %>
</td>
<%
Set rsr = Server.CreateObject("ADODB.Recordset&quot

rsr.Open "SELECT * FROM board WHERE ThreadID="&msgID&"AND ThreadP=1",conn, 3
%>
<td width=10% align=center><%=rsr.recordcount%></td>
<td width=10% align=center><%=rsttrv("counts&quot

%></td>
<%
if rsr.recordcount <> 0 then
Set rslp = Server.CreateObject("ADODB.Recordset&quot

rslp.Open "SELECT * FROM board WHERE ThreadID="&msgID&" ORDER BY msgID",conn, 3
rslp.MoveLast
%>
<td width=23%><%=rslp("tdate&quot

%> by 
<% if instr(rslp("email&quot

& "" , "@&quot

> 0 then %>
<a href='mailto:<%=trim(rslp("email&quot

)%>'><%=trim(rslp("name&quot

)%></a>
<% else %>
<%=rslp("name&quot

%>
<% end if %>
</td>
<% else %>
<td width="23%"><%=rsttrv("tdate&quot

%></td>
<% end if %>
</tr></table>
</div>
<%
count = count + 1
rsttrv.MoveNext
Loop
%>
 

<a href="post.asp">Post New Topic</a>
 
 
<%
Response.Write "<div align=center>"
Response.Write "<table border=0 width=840>"
Response.Write "<tr>"
Response.Write "<td width=210>"
if rsttrv.RecordCount > 0 then
if currentpage > 1 then
Response.Write "<a href='default.asp?currentpage=1'>First</a>"
end if
end if
Response.Write "</td>"
Response.Write "<td width=210>"

if rsttrv.RecordCount > 0 then
if currentpage > 1 then
Response.Write "<a href='default.asp?currentpage=" & currentpage - 1 &"'><< Pervious</a>"
end if
end if

Response.Write "</td>"
Response.Write "<td width=210>"

if rsttrv.RecordCount > 0 then
if CInt(currentpage) <> CInt(TotalPages) then
Response.Write "<a href='default.asp?currentpage=" & currentpage + 1 &"'>Next >></a>"
end if
end if

Response.Write "</td>"
Response.Write "<td width=210>"

if rsttrv.RecordCount > 0 then
if CInt(currentpage) <> CInt(TotalPages) then
Response.Write "<a href='default.asp?currentpage=" & TotalPages &"'>Last</a>"
end if
end if

Response.Write "</td>"
Response.Write "</tr></table></div>"
%>
<%
if TotalPages > 1 then
Response.Write "<center>"
Response.Write ""
Response.Write "Page: "
Response.Write "<select name='whatever' onChange='window.location=this.options[this.selectedIndex].value'>"
Response.Write "<option value='default.asp?currentpage="&currentpage&" SELECTED'> - "&currentpage&" - </option>"
for i = 1 to TotalPages
if cStr(currentpage) <> cStr(i) then
Response.Write "<option value='default.asp?currentpage="&i&"'> - "&i&" - </option>"
end if
next
Response.Write "</select>"
Response.Write ""
Response.Write "</center>"

Response.Write ""
Response.Write ""
Response.Write("Page " & currentpage & " of " & TotalPages & "&quot

Response.Write ""
end if
%>
<%
conn.Close
Set conn = Nothing
%>
</body>
</html>

JuanitaC · Aug 30, 2002

Could you be more clear as to what your problem is, I am not sure what you want me to look for.

samosa · Aug 30, 2002

Ok, I click on the default.asp and it takes me to the bulletin board.
I click on post (post.asp) to post a thread to the bulletin board (just like I am doing now).
I enter all the values to the text boxes and click submit.
It stores the values in the sql server db but does not display the records to the bulletin board.
Per your last response, you indicated the problem might be with my select statement.
That is why I posted the code with the select statement to see if you might pick it up.
Thanks for all the help.
I showed this to my boss yesterday but today he discovered this bug and I am pulling my hair trying to figure out what the problem is with no sucess.

samosa · Aug 30, 2002

One other thing, any time I post a message, it says "No post yet" which is the generic information I put out until a thread is posted.
Even though it says no post yet, it still records the values into the database

JuanitaC · Aug 30, 2002

Well, you have several select statements in this page. First thing to do would be to figure out where you are losing the newly inserted records. Check your data to ensure that it matches...

For instance, in this select
rsttrv.Open "SELECT * FROM board WHERE ThreadP=0 ORDER BY msgID DESC", conn, 3

Does your newly inserted record have a proper value in the ThreadP field? This field was not in your Insert statment.

Or in this select
rsnay.Open "SELECT * FROM board WHERE ThreadID="&msgID&" ORDER BY msgID", conn, 3
Does your newly inserted record match the msgID that you are trying to bring back.

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!

asp syntax error - URGENT, Please

samosa

Technical User

JuanitaC

Programmer

samosa

Technical User

JuanitaC

Programmer

samosa

Technical User

JuanitaC

Programmer

samosa

Technical User

samosa

Technical User

JuanitaC

Programmer

Similar threads

Part and Inventory Search

Sponsor