asp syntax error - URGENT, Please

[samosa]

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(&quot;topic&quot),&quot;'&quot;,&quot;''&quot
name = Replace(Server.HTMLEncode(Request.Form(&quot;name&quot),&quot;'&quot;,&quot;''&quot
email = Replace(Server.HTMLEncode(Request.Form(&quot;email&quot),&quot;'&quot;,&quot;''&quot
http = Replace(Server.HTMLEncode(Request.Form(&quot;http&quot),&quot;'&quot;, &quot;''&quot
body = Request.Form(&quot;body&quot
body = Replace(body , &quot;'&quot;, &quot;''&quot
body = Replace(body, vbcrlf, &quot;<br>&quot

if topic = &quot;&quot; OR name = &quot;&quot; OR body = &quot;&quot; then
Response.Redirect(&quot;post.asp?error=1&quot
end if
if instr(LCase(body), &quot;<script&quot <> 0 then
Response.Redirect(&quot;post.asp?error=2&quot
end if
if email <> &quot;&quot; then
if instr(email, &quot;@&quot = 0 OR instr(email, &quot;.&quot = 0 then
Response.Redirect(&quot;post.asp?error=3&quot
end if
end if
if http <> &quot;&quot; then
if instr(http, &quot;

http://&quot;)

= 0 OR instr(http, &quot;.&quot = 0 then
Response.Redirect(&quot;post.asp?error=4&quot
end if
end if

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

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

 

[JuanitaC]

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]

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(&quot;topic&quot),&quot;'&quot;,&quot;''&quot
name = Replace(Server.HTMLEncode(Request.Form(&quot;name&quot),&quot;'&quot;,&quot;''&quot
email = Replace(Server.HTMLEncode(Request.Form(&quot;email&quot),&quot;'&quot;,&quot;''&quot
http = Replace(Server.HTMLEncode(Request.Form(&quot;http&quot),&quot;'&quot;, &quot;''&quot
body = Request.Form(&quot;body&quot
body = Replace(body , &quot;'&quot;, &quot;''&quot
body = Replace(body, vbcrlf, &quot;<br>&quot

if topic = &quot;&quot; OR name = &quot;&quot; OR body = &quot;&quot; then
Response.Redirect(&quot;post.asp?error=1&quot
end if
if instr(LCase(body), &quot;<script&quot <> 0 then
Response.Redirect(&quot;post.asp?error=2&quot
end if
if email <> &quot;&quot; then
if instr(email, &quot;@&quot = 0 OR instr(email, &quot;.&quot = 0 then
Response.Redirect(&quot;post.asp?error=3&quot
end if
end if
if http <> &quot;&quot; then
if instr(http, &quot;

http://&quot;)

= 0 OR instr(http, &quot;.&quot = 0 then
Response.Redirect(&quot;post.asp?error=4&quot
end if
end if

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

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

 

[JuanitaC]

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]

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=&quot;text/css&quot;>
A:link, A:visited { text-decoration: none; color: 'silver' }
</style>
</head>
<body bgcolor=&quot;#006699&quot;>
<p align=center>
<font face=arial color=white size=4><b>Public Works Bullettin Board</b></font><br>
<%
dim currentpage
if request.querystring(&quot;currentpage&quot = &quot;&quot; OR request.querystring(&quot;currentpage&quot < 1 then
currentpage = 1
else
currentpage = request.querystring(&quot;currentpage&quot
end if
Session(&quot;currentpage&quot=currentpage
set conn = Server.CreateObject(&quot;ADODB.connection&quot

Conn.open &quot;DSN=BBoard&quot;

'sConnection = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
'&quot;Data Source=&quot; & Server.MapPath(&quot;\BBoard\db\betaboard.mdb&quot & &quot;;&quot; & _
'&quot;Persist Security Info=False&quot;
'conn.Open(sConnection)





%>
<div align=&quot;center&quot;>
<table border=&quot;0&quot; width=&quot;90%&quot;>
<tr>
<td width=&quot;69%&quot;>
<% Response.Write &quot;<font face=arial size=1 color=white>The time now is: </font><font face=arial size=1 color=silver>&quot;&now()&&quot;</font>&quot;%>
</td>
<td><font face=arial size=2><b><a href='search.asp'>Search</a></b></font></td>
<td width=&quot;25%&quot;>
<p align=&quot;right&quot;><font face=&quot;arial&quot; size=&quot;1&quot;><b>
<%Set rsot = Server.CreateObject(&quot;ADODB.Recordset&quot
rsot.Open &quot;SELECT * FROM board where threadP=0&quot;,conn, 3%>
<%response.write &quot;<font face='arial' size=2 color=white>Total Topics: </font><b><font face='arial' size=2 color=silver>&quot;& rsot.recordcount &&quot;</font></b>&quot;%>
<% rsot.Close %>
<b><font face=&quot;arial&quot; size=&quot;2&quot; color=white> - </font></b>
<%Set rsott = Server.CreateObject(&quot;ADODB.Recordset&quot
rsott.Open &quot;SELECT * FROM board&quot;,conn, 3%>
<%response.write &quot;<font face='arial' size=2 color=white>Total Posts: </font><b><font face='arial' size=2 color=silver>&quot;& rsott.recordcount &&quot;</font></b>&quot;%>
<% rsott.Close %>
</b></font></td>
</tr>
</table>
</div>
<div align=center>
<table border=1 width=90% cellpadding=0 cellspacing=0>
<tr>
<td width=40%><p align=left><font face=arial size=2 color=white><b>Thread</b></font></td>
<td width=17%><p align=center><font face=arial size=2 color=white><b>Thread Starter</b></font></td>
<td width=10%><p align=center><font face=arial size=2 color=white><b>Replies</b></font></td>
<td width=10%><p align=center><font face=arial size=2 color=white><b>Views</b></font></td>
<td width=23%><p align=left><font face=arial size=2 color=white><b>Last Post</b></font></td>
</tr>
</table>
</div>
<%
Set rsttrv = Server.CreateObject(&quot;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 &quot;SELECT * FROM board WHERE ThreadP=0 ORDER BY msgID DESC&quot;, conn, 3

if rsttrv.EOF then Response.Write &quot;<p align=center><font face=arial size=2 color=white><b>No Post Yet</b></font>&quot;
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%><font face=arial size=2><b><a href='view.asp?msgID=<%=rsttrv(&quot;msgID&quot%>'><%=rsttrv(&quot;topic&quot%></a></b></font>
<% msgID = rsttrv(&quot;msgid&quot
Set rsnay = Server.CreateObject(&quot;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 &quot;SELECT * FROM board WHERE ThreadID=&quot;&msgID&&quot; ORDER BY msgID&quot;, 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 &quot;<font face='arial' size='1' color=white>&quot;
Response.Write &quot;<b><< Page:</b> &quot;
Response.Write &quot;<select name='whatever' onChange='window.location=this.options[this.selectedIndex].value'>&quot;
Response.Write &quot;<option value='view.asp?msgID=&quot;&msgID&&quot;&seepage=1 SELECTED'> - 1 - </option>&quot;
for i = 1 to TotalPage
Response.Write &quot;<option value='view.asp?msgID=&quot;&msgID&&quot;&seepage=&quot;&i&&quot;'> - &quot;&i&&quot; - </option>&quot;
next
Response.Write &quot;</select>&quot;
Response.Write &quot; >>&quot;
end if
%>
</td>
<td width=17% align=center>
<% if instr(rsttrv(&quot;email&quot & &quot;&quot; , &quot;@&quot > 0 then %>
<font face=arial size=2><b><a href='mailto:<%=trim(rsttrv(&quot;email&quot)%>'><%=trim(rsttrv(&quot;name&quot)%></a></b></font>
<%else%>
<font face=arial size=2 color=white><b><%=rsttrv(&quot;name&quot%></b></font>
<% end if %>
</td>
<%
Set rsr = Server.CreateObject(&quot;ADODB.Recordset&quot
rsr.Open &quot;SELECT * FROM board WHERE ThreadID=&quot;&msgID&&quot;AND ThreadP=1&quot;,conn, 3
%>
<td width=10% align=center><font face=arial color=silver size=2><b><%=rsr.recordcount%></b></font></td>
<td width=10% align=center><font face=arial color=silver size=2><b><%=rsttrv(&quot;counts&quot%></b></font></td>
<%
if rsr.recordcount <> 0 then
Set rslp = Server.CreateObject(&quot;ADODB.Recordset&quot
rslp.Open &quot;SELECT * FROM board WHERE ThreadID=&quot;&msgID&&quot; ORDER BY msgID&quot;,conn, 3
rslp.MoveLast
%>
<td width=23%><font face=arial color=white size=1><b><%=rslp(&quot;tdate&quot%> by </b></font>
<% if instr(rslp(&quot;email&quot & &quot;&quot; , &quot;@&quot > 0 then %>
<font face=arial color=silver size=1><b><a href='mailto:<%=trim(rslp(&quot;email&quot)%>'><%=trim(rslp(&quot;name&quot)%></a></b></font>
<% else %>
<font face=arial color=white size=1><b><%=rslp(&quot;name&quot%></b></font>
<% end if %>
</td>
<% else %>
<td width=&quot;23%&quot;><font face=arial color=white size=1><b><%=rsttrv(&quot;tdate&quot%></b></font></td>
<% end if %>
</tr></table>
</div>
<%
count = count + 1
rsttrv.MoveNext
Loop
%>
<br>
<p align=center>
<font face=arial size=2><b><a href=&quot;post.asp&quot;>Post New Topic</a></b></font>
<br>
<br>
<%
Response.Write &quot;<div align=center>&quot;
Response.Write &quot;<table border=0 width=840>&quot;
Response.Write &quot;<tr>&quot;
Response.Write &quot;<td width=210><p align=left><font face=arial size=2><b>&quot;
if rsttrv.RecordCount > 0 then
if currentpage > 1 then
Response.Write &quot;<a href='default.asp?currentpage=1'>First</a>&quot;
end if
end if
Response.Write &quot;</b></font></td>&quot;
Response.Write &quot;<td width=210><p align=center><font face=arial size=2><b>&quot;

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

Response.Write &quot;</b></font></td>&quot;
Response.Write &quot;<td width=210><p align=center><font face=arial size=2><b>&quot;

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

Response.Write &quot;</b></font></td>&quot;
Response.Write &quot;<td width=210><p align=right><font face=arial size=2><b>&quot;

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

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

Response.Write &quot;<font face=arial size=1 color=white>&quot;
Response.Write &quot;<p align=center>&quot;
Response.Write(&quot;Page &quot; & currentpage & &quot; of &quot; & TotalPages & &quot;</p>&quot
Response.Write &quot;</font>&quot;
end if
%>
<%
conn.Close
Set conn = Nothing
%>
</body>
</html>

 

[JuanitaC]

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

 

[samosa]

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]

One other thing, any time I post a message, it says &quot;No post yet&quot; 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]

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 &quot;SELECT * FROM board WHERE ThreadP=0 ORDER BY msgID DESC&quot;, 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 &quot;SELECT * FROM board WHERE ThreadID=&quot;&msgID&&quot; ORDER BY msgID&quot;, conn, 3
Does your newly inserted record match the msgID that you are trying to bring back.