|
 |
access_asp thread: Batch Update Help, Please
Message #1 by "matt faulk" <mfaulkner@b...> on Tue, 10 Dec 2002 14:18:25
|
|
Having some problems with the below batch update using an Access database.
I'm getting the error on the bottom of this post and can't seem to figure
out why. The SurveyID is an autonumber the other two fields are text. Any
help to get me over this last hump would be appreciated.
List Page:
<%response.buffer=true%>
<!-- METADATA TYPE="typelib" FILE="C:\Program Files\Common
Files\System\ado\msado15.dll" -->
<% Response.ExpiresAbsolute = Now() - 1 %>
<HTML>
<HEAD>
<SCRIPT LANGUAGE="JavaScript">
<!--
function UpdateReply()
{
document.forms[0].action = "TC2ReplyProcess.asp";
document.forms[0].submit();
}
// -->
</SCRIPT>
<BODY>
<%
Dim Conn, dsntemp
Set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("../fpdb/PMData.mdb")
conn.Open DSNtemp
%>
<FORM METHOD=POST ACTION="TC2ReplyProcess.asp">
<TABLE BORDER=1 CELLSPACING=1>
<%
Dim SqlStmt
SQLstmt = "SELECT * FROM bsasurvey order by SurveyID"
Set rs = conn.Execute(SQLstmt)
%>
<%
Dim iCount
iCount = 0
%>
<table width="420">
<TR>
<td width="4">
<%Do While Not rs.EOF%>
<td width="402">
<p align="left" align="left"><font color="#663300"><INPUT TYPE="text"
NAME="".Name"" VALUE="<%=rs("Name")%>"></font></p>
</td>
<td width="402">
<p align="left" align="left"><font color="#663300"><INPUT TYPE="text"
NAME="".BPReply"" VALUE="<%=rs("BPReply")%>"></font></p>
</td> </tr>
<td width="4"><font color="#663300"><INPUT TYPE="Hidden"
NAME="".SurveyID"" VALUE="<%= RS("SurveyID")%>"></font></td>
<%
iCount = iCount + 1
rs.MoveNext
lOOP
%>
<td width="402">
<P>
<INPUT TYPE=BUTTON VALUE="Update Replies" ONCLICK="UpdateReply();">
<INPUT TYPE=HIDDEN NAME=Count VALUE="<%=iCount - 1 %>"
</FORM>
</table>
</BODY>
</XMP>
Update Page:
<%@ Language=VBScript %>
<%
Response.Buffer = True
Dim iCount
iCount = Request("Count")
Response.write(icount)
%>
<%
Set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("../fpdb/PMData.mdb")
conn.Open DSNtemp
%>
<%
idarray=Split(Request("SurveyID"))
Namearray=Split(Request("Name"))
BPReplyArray=Split(Request("BPReply"))
for i=0 to UBound(idarray)
SQLStmt="UPDATE bsasurvey SET name = '"&Namearray(i)&"',BPReply
= '"&BPReplyarray(i)&"' WHERE SurveyID= "&idarray(i)
Response.Write(SQLStmt)
Set rs = conn.Execute(SQLstmt)
Next
%>
46UPDATE bsasurvey SET name = 'Terri',BPReply = 'tttttttttttttttttttt,'
WHERE SurveyID= 29,
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query
expression 'SurveyID= 29,'.
/BSA/TC2ReplyProcess.asp, line 32
Thanks, Mike
Message #2 by "matt faulk" <mfaulkner@b...> on Tue, 10 Dec 2002 14:19:50
|
|
Sorry for double post...
Having some problems with the below batch update using an Access database.
I'm getting the error on the bottom of this post and can't seem to figure
out why. The SurveyID is an autonumber the other two fields are text. Any
help to get me over this last hump would be appreciated.
List Page:
<%response.buffer=true%>
<!-- METADATA TYPE="typelib" FILE="C:\Program Files\Common
Files\System\ado\msado15.dll" -->
<% Response.ExpiresAbsolute = Now() - 1 %>
<HTML>
<HEAD>
<SCRIPT LANGUAGE="JavaScript">
<!--
function UpdateReply()
{
document.forms[0].action = "TC2ReplyProcess.asp";
document.forms[0].submit();
}
// -->
</SCRIPT>
<BODY>
<%
Dim Conn, dsntemp
Set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("../fpdb/PMData.mdb")
conn.Open DSNtemp
%>
<FORM METHOD=POST ACTION="TC2ReplyProcess.asp">
<TABLE BORDER=1 CELLSPACING=1>
<%
Dim SqlStmt
SQLstmt = "SELECT * FROM bsasurvey order by SurveyID"
Set rs = conn.Execute(SQLstmt)
%>
<%
Dim iCount
iCount = 0
%>
<table width="420">
<TR>
<td width="4">
<%Do While Not rs.EOF%>
<td width="402">
<p align="left" align="left"><font color="#663300"><INPUT TYPE="text"
NAME="".Name"" VALUE="<%=rs("Name")%>"></font></p>
</td>
<td width="402">
<p align="left" align="left"><font color="#663300"><INPUT TYPE="text"
NAME="".BPReply"" VALUE="<%=rs("BPReply")%>"></font></p>
</td> </tr>
<td width="4"><font color="#663300"><INPUT TYPE="Hidden"
NAME="".SurveyID"" VALUE="<%= RS("SurveyID")%>"></font></td>
<%
iCount = iCount + 1
rs.MoveNext
lOOP
%>
<td width="402">
<P>
<INPUT TYPE=BUTTON VALUE="Update Replies" ONCLICK="UpdateReply();">
<INPUT TYPE=HIDDEN NAME=Count VALUE="<%=iCount - 1 %>"
</FORM>
</table>
</BODY>
</XMP>
Update Page:
<%@ Language=VBScript %>
<%
Response.Buffer = True
Dim iCount
iCount = Request("Count")
Response.write(icount)
%>
<%
Set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("../fpdb/PMData.mdb")
conn.Open DSNtemp
%>
<%
idarray=Split(Request("SurveyID"))
Namearray=Split(Request("Name"))
BPReplyArray=Split(Request("BPReply"))
for i=0 to UBound(idarray)
SQLStmt="UPDATE bsasurvey SET name = '"&Namearray(i)&"',BPReply
= '"&BPReplyarray(i)&"' WHERE SurveyID= "&idarray(i)
Response.Write(SQLStmt)
Set rs = conn.Execute(SQLstmt)
Next
%>
46UPDATE bsasurvey SET name = 'Terri',BPReply = 'tttttttttttttttttttt,'
WHERE SurveyID= 29,
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query
expression 'SurveyID= 29,'.
/BSA/TC2ReplyProcess.asp, line 32
Thanks, Mike
Message #3 by "Ken Schaefer" <ken@a...> on Wed, 11 Dec 2002 12:13:20 +1100
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "matt faulk" <mfaulkner@b...>
Subject: [access_asp] Batch Update Help, Please
:
: 46UPDATE bsasurvey SET name = 'Terri',BPReply = 'tttttttttttttttttttt,'
: WHERE SurveyID= 29,
: Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
:
: [Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query
: expression 'SurveyID= 29,'.
:
: /BSA/TC2ReplyProcess.asp, line 32
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WHERE SurveyID = 29,
should be (with no trailing comma):
WHERE SurveyID = 29
Cheers
Ken
|
|
|
View Cart
