|
 |
access_asp thread: This error is wrecking my head
Message #1 by "sean dillon" <sdillon@b...> on Mon, 15 Apr 2002 11:14:25
|
|
I keep getting this error when i go to update a record:
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement.
/moddata.asp, line 81"
Line 81 is:
"Conn.Execute (SQLstmt)"
Ive been told it might be because i have used a reserved word in my update
statement but ive checked my code for reserved words and cant seem to find
any.
My update statement is as follows
IF Len(TRIM(Request.Form("flag"))) = 0 THEN
SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
Request.QueryString("Recid")
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Set rs = conn.Execute(SQLstmt)
IF NOT RS.EOF THEN
%>
Update<HR>
<FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
<B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
=rs("AssignedTo")%>"><BR>
<B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
VALUE="<%=rs("ManagerSignOff")%>"><BR>
<B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
VALUE="<%=rs("ReplytoCustomer")%>"><BR>
<B>FurtherActionRequired:</B> <INPUT TYPE="text"
NAME="FurtherActionRequired" VALUE="<%=rs("FurtherActionRequired")%>"><BR>
<B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
VALUE="<%=rs("ActionRequired")%>"><BR>
<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
<INPUT TYPE="submit" VALUE="Update">
</FORM>
<%
rs.MoveNext
rs.Close
END IF
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "UPDATE QuerySheet SET "
SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
("AssignedTo")) & "',"
SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
("ManagerSignOff")) & "',"
SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
("ReplytoCustomer")) & "',"
SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
("FurtherActionRequired")) & "',"
SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
("ActionRequired")) & "',"
SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
("Recordid"))
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Conn.Execute (SQLstmt)
Response.Write "Operation Complete<br><A
HREF=""moddata.asp"">Home</A>"
Can anyone please help as its wrecking my head to find the problem??
Message #2 by "Peter Rooney" <peter.rooney@p...> on Mon, 15 Apr 2002 11:44:12 +0100
|
|
Sean,
Just a stab in the dark here but in your If statement you have 'flag' being
called from a Request.Form and in your WHERE clause you have 'Recid' being
called from a Request.QueryString, if you are submitting this via a form
then your 'Recid' value should also be a Request.Form value
Peter
-----Original Message-----
From: sean dillon [mailto:sdillon@b...]
Sent: 15 April 2002 11:14
To: Access ASP
Subject: [access_asp] This error is wrecking my head
I keep getting this error when i go to update a record:
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement.
/moddata.asp, line 81"
Line 81 is:
"Conn.Execute (SQLstmt)"
Ive been told it might be because i have used a reserved word in my update
statement but ive checked my code for reserved words and cant seem to find
any.
My update statement is as follows
IF Len(TRIM(Request.Form("flag"))) = 0 THEN
SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
Request.QueryString("Recid")
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Set rs = conn.Execute(SQLstmt)
IF NOT RS.EOF THEN
%>
Update<HR>
<FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
<B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
=rs("AssignedTo")%>"><BR>
<B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
VALUE="<%=rs("ManagerSignOff")%>"><BR>
<B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
VALUE="<%=rs("ReplytoCustomer")%>"><BR>
<B>FurtherActionRequired:</B> <INPUT TYPE="text"
NAME="FurtherActionRequired" VALUE="<%=rs("FurtherActionRequired")%>"><BR>
<B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
VALUE="<%=rs("ActionRequired")%>"><BR>
<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
<INPUT TYPE="submit" VALUE="Update">
</FORM>
<%
rs.MoveNext
rs.Close
END IF
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "UPDATE QuerySheet SET "
SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
("AssignedTo")) & "',"
SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
("ManagerSignOff")) & "',"
SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
("ReplytoCustomer")) & "',"
SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
("FurtherActionRequired")) & "',"
SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
("ActionRequired")) & "',"
SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
("Recordid"))
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Conn.Execute (SQLstmt)
Response.Write "Operation Complete<br><A
HREF=""moddata.asp"">Home</A>"
Can anyone please help as its wrecking my head to find the problem??
--
Virus scanned by edNET.
Message #3 by "Sean Dillon" <sdillon@b...> on Mon, 15 Apr 2002 11:48:27 +0100
|
|
Hi Peter,
I tried that but that also gives me the same error. Have you any ideas on
what else might be wrong.
Sean
-----Original Message-----
From: Peter Rooney [mailto:peter.rooney@p...]
Sent: 15 April 2002 11:44
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Sean,
Just a stab in the dark here but in your If statement you have 'flag' being
called from a Request.Form and in your WHERE clause you have 'Recid' being
called from a Request.QueryString, if you are submitting this via a form
then your 'Recid' value should also be a Request.Form value
Peter
-----Original Message-----
From: sean dillon [mailto:sdillon@b...]
Sent: 15 April 2002 11:14
To: Access ASP
Subject: [access_asp] This error is wrecking my head
I keep getting this error when i go to update a record:
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement.
/moddata.asp, line 81"
Line 81 is:
"Conn.Execute (SQLstmt)"
Ive been told it might be because i have used a reserved word in my update
statement but ive checked my code for reserved words and cant seem to find
any.
My update statement is as follows
IF Len(TRIM(Request.Form("flag"))) = 0 THEN
SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
Request.QueryString("Recid")
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Set rs = conn.Execute(SQLstmt)
IF NOT RS.EOF THEN
%>
Update<HR>
<FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
<B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
=rs("AssignedTo")%>"><BR>
<B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
VALUE="<%=rs("ManagerSignOff")%>"><BR>
<B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
VALUE="<%=rs("ReplytoCustomer")%>"><BR>
<B>FurtherActionRequired:</B> <INPUT TYPE="text"
NAME="FurtherActionRequired" VALUE="<%=rs("FurtherActionRequired")%>"><BR>
<B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
VALUE="<%=rs("ActionRequired")%>"><BR>
<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
<INPUT TYPE="submit" VALUE="Update">
</FORM>
<%
rs.MoveNext
rs.Close
END IF
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "UPDATE QuerySheet SET "
SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
("AssignedTo")) & "',"
SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
("ManagerSignOff")) & "',"
SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
("ReplytoCustomer")) & "',"
SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
("FurtherActionRequired")) & "',"
SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
("ActionRequired")) & "',"
SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
("Recordid"))
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Conn.Execute (SQLstmt)
Response.Write "Operation Complete<br><A
HREF=""moddata.asp"">Home</A>"
Can anyone please help as its wrecking my head to find the problem??
--
Virus scanned by edNET.
Message #4 by "Sean Dillon" <sdillon@b...> on Mon, 15 Apr 2002 11:57:17 +0100
|
|
Sorry Peter,
Ignore that reply. I changed around the wrong line of code, its Monday im
still trying to recover after the w/end.
When i changed the proper line of code i got the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
"[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
in query expression 'SheetNo ='.
/moddata.asp, line 50 "
Line 50 is:
Set rs = conn.Execute(SQLstmt).
Sean
-----Original Message-----
From: Peter Rooney [mailto:peter.rooney@p...]
Sent: 15 April 2002 11:44
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Sean,
Just a stab in the dark here but in your If statement you have 'flag' being
called from a Request.Form and in your WHERE clause you have 'Recid' being
called from a Request.QueryString, if you are submitting this via a form
then your 'Recid' value should also be a Request.Form value
Peter
-----Original Message-----
From: sean dillon [mailto:sdillon@b...]
Sent: 15 April 2002 11:14
To: Access ASP
Subject: [access_asp] This error is wrecking my head
I keep getting this error when i go to update a record:
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement.
/moddata.asp, line 81"
Line 81 is:
"Conn.Execute (SQLstmt)"
Ive been told it might be because i have used a reserved word in my update
statement but ive checked my code for reserved words and cant seem to find
any.
My update statement is as follows
IF Len(TRIM(Request.Form("flag"))) = 0 THEN
SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
Request.QueryString("Recid")
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Set rs = conn.Execute(SQLstmt)
IF NOT RS.EOF THEN
%>
Update<HR>
<FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
<B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
=rs("AssignedTo")%>"><BR>
<B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
VALUE="<%=rs("ManagerSignOff")%>"><BR>
<B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
VALUE="<%=rs("ReplytoCustomer")%>"><BR>
<B>FurtherActionRequired:</B> <INPUT TYPE="text"
NAME="FurtherActionRequired" VALUE="<%=rs("FurtherActionRequired")%>"><BR>
<B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
VALUE="<%=rs("ActionRequired")%>"><BR>
<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
<INPUT TYPE="submit" VALUE="Update">
</FORM>
<%
rs.MoveNext
rs.Close
END IF
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "UPDATE QuerySheet SET "
SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
("AssignedTo")) & "',"
SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
("ManagerSignOff")) & "',"
SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
("ReplytoCustomer")) & "',"
SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
("FurtherActionRequired")) & "',"
SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
("ActionRequired")) & "',"
SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
("Recordid"))
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Conn.Execute (SQLstmt)
Response.Write "Operation Complete<br><A
HREF=""moddata.asp"">Home</A>"
Can anyone please help as its wrecking my head to find the problem??
--
Virus scanned by edNET.
Message #5 by "Peter Rooney" <peter.rooney@p...> on Mon, 15 Apr 2002 12:05:12 +0100
|
|
What is the result of <%=SQLstmt%>?
Make sure you comment out the Set rs = conn.Execute(SQLstmt)
'rs = conn.Execute(SQLstmt)
so it doesn't try to execute the sql before you can see the results, also I
assume you have set up your connection (conn)
-----Original Message-----
From: Sean Dillon [mailto:sdillon@b...]
Sent: 15 April 2002 11:48
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Hi Peter,
I tried that but that also gives me the same error. Have you any ideas on
what else might be wrong.
Sean
-----Original Message-----
From: Peter Rooney [mailto:peter.rooney@p...]
Sent: 15 April 2002 11:44
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Sean,
Just a stab in the dark here but in your If statement you have 'flag' being
called from a Request.Form and in your WHERE clause you have 'Recid' being
called from a Request.QueryString, if you are submitting this via a form
then your 'Recid' value should also be a Request.Form value
Peter
-----Original Message-----
From: sean dillon [mailto:sdillon@b...]
Sent: 15 April 2002 11:14
To: Access ASP
Subject: [access_asp] This error is wrecking my head
I keep getting this error when i go to update a record:
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement.
/moddata.asp, line 81"
Line 81 is:
"Conn.Execute (SQLstmt)"
Ive been told it might be because i have used a reserved word in my update
statement but ive checked my code for reserved words and cant seem to find
any.
My update statement is as follows
IF Len(TRIM(Request.Form("flag"))) = 0 THEN
SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
Request.QueryString("Recid")
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Set rs = conn.Execute(SQLstmt)
IF NOT RS.EOF THEN
%>
Update<HR>
<FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
<B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
=rs("AssignedTo")%>"><BR>
<B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
VALUE="<%=rs("ManagerSignOff")%>"><BR>
<B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
VALUE="<%=rs("ReplytoCustomer")%>"><BR>
<B>FurtherActionRequired:</B> <INPUT TYPE="text"
NAME="FurtherActionRequired" VALUE="<%=rs("FurtherActionRequired")%>"><BR>
<B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
VALUE="<%=rs("ActionRequired")%>"><BR>
<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
<INPUT TYPE="submit" VALUE="Update">
</FORM>
<%
rs.MoveNext
rs.Close
END IF
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "UPDATE QuerySheet SET "
SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
("AssignedTo")) & "',"
SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
("ManagerSignOff")) & "',"
SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
("ReplytoCustomer")) & "',"
SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
("FurtherActionRequired")) & "',"
SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
("ActionRequired")) & "',"
SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
("Recordid"))
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Conn.Execute (SQLstmt)
Response.Write "Operation Complete<br><A
HREF=""moddata.asp"">Home</A>"
Can anyone please help as its wrecking my head to find the problem??
--
Virus scanned by edNET.
--
Virus scanned by edNET.
Message #6 by "Sean Dillon" <sdillon@b...> on Mon, 15 Apr 2002 12:19:25 +0100
|
|
Peter,
Heres my full code it might be easier for you to understand what im trying
to do:
<html>
<BODY>
<B>Processor Comments</B>
<%
Actionvar=Request.QueryString("actionvar")
Set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)};"
DSNtemp=dsntemp & "DBQ=" & server.mappath("dbNetFusion1.mdb")
conn.Open DSNtemp
'If the QueryString has the Actionvar = add then generate
'the page for adding items
IF Actionvar="add" THEN
IF Len(TRIM(request.form("flag")))=0 THEN
%>
Add<HR>
<FORM METHOD="post" ACTION="moddata.asp?Actionvar=add">
AssignedTo:<INPUT TYPE="text" NAME="AssignedTo"><BR>
ManagerSignOff:<INPUT TYPE="text" NAME="ManagerSignOff"><BR>
ReplytoCustomer:<INPUT TYPE="text" NAME="ReplytoCustomer"><BR>
FurtherAction Required:<INPUT TYPE="text"
NAME="FurtherActionRequired"><BR>
ActionRequired:<INPUT TYPE="text" NAME="ActionRequired"><BR>
<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="submit" VALUE="ADD">
</FORM>
<%
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "INSERT INTO QuerySheet
(AssignedTo,ManagerSignOff,ReplytoCustomer,FurtherActionRequired,ActionRequi
red)"
SQLstmt =SQLstmt & "VALUES('"& request.form("AssignedTo") &"','"&
request.form("ManagerSignOff") &"','"& request.form("ReplytoCustomer")
&"','"& request.form("FurtherActionRequired") &"', '"&
request.form("ActionRequired") &"')"
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
conn.execute(SQLstmt)
Response.Write "Operation Complete<BR><A HREF=""moddata.asp"">Home</A>"
END IF
'If the QueryString has the Actionvar = update then generate
'the page for updating items
ELSEIF Actionvar="update" THEN
IF Len(TRIM(Request.Form("flag"))) = 0 THEN
SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
Request.QueryString("Recid")
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Set rs = conn.Execute(SQLstmt)
IF NOT RS.EOF THEN
%>
Update<HR>
<FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
<B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo"
VALUE="<%=rs("AssignedTo")%>"><BR>
<B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
VALUE="<%=rs("ManagerSignOff")%>"><BR>
<B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
VALUE="<%=rs("ReplytoCustomer")%>"><BR>
<B>FurtherActionRequired:</B> <INPUT TYPE="text"
NAME="FurtherActionRequired" VALUE="<%=rs("FurtherActionRequired")%>"><BR>
<B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
VALUE="<%=rs("ActionRequired")%>"><BR>
<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
<INPUT TYPE="submit" VALUE="Update">
</FORM>
<%
rs.MoveNext
rs.Close
END IF
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "UPDATE QuerySheet SET "
SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form("AssignedTo")) &
"',"
SQLstmt = SQLstmt & "ManagerSignOff='" &
TRIM(Request.Form("ManagerSignOff")) & "',"
SQLstmt = SQLstmt & "ReplytoCustomer='" &
TRIM(Request.Form("ReplytoCustomer")) & "',"
SQLstmt = SQLstmt & "FurtherActionRequired='" &
TRIM(Request.Form("FurtherActionRequired")) & "',"
SQLstmt = SQLstmt & "ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "',"
SQLstmt = SQLstmt & "WHERE SheetNo=" & TRIM(Request.Form("Recordid"))
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Conn.Execute (SQLstmt)
Response.Write "Operation Complete<br><A HREF=""moddata.asp"">Home</A>"
END IF
'If the QueryString has the Actionvar= delete the item
'and generate an "Operation Complete" page
ELSEIF Actionvar="delete" THEN
SQLstmt="DELETE * FROM QuerySheet WHERE SheetNo=" &
TRIM(Request.QueryString("Recid"))
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
conn.execute(SQLstmt)
Response.Write "Operation Complete<BR><A HREF=""moddata.asp"">Home</A>"
'If the QueryString Actionvar isn't set to anything, generate the list of
items
ELSE
SQLstmt = "SELECT * FROM QuerySheet"
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Set rs = conn.Execute(SQLstmt)
%>
<TABLE BORDER=0>
<TR>
<TD><CENTER><B>AssignedTo</B></CENTER></TD>
<TD> </TD>
<TD><CENTER><B>ManagerSignOff</B></CENTER></TD>
<TD> </TD>
<TD><CENTER><B>ReplytoCustomer</B></CENTER></TD>
<TD> </TD>
<TD><CENTER><B>FurtherActionRequired</B></CENTER></TD>
<TD> </TD>
<TD><CENTER><B>ActionRequired</B></CENTER></TD>
<TD COLSPAN=2><CENTER><B>Modify</B></CENTER></TD>
<%
x=0
DO WHILE NOT rs.EOF
x=x+1
RecSheetNo = rs("SheetNo")
AssignedTo = rs("AssignedTo")
ManagerSignOff =rs("ManagerSignOff")
ReplytoCustomer =rs("ReplytoCustomer")
FurtherActionRequired =rs("FurtherActionRequired")
ActionRequired =rs("ActionRequired")
%>
<TR>
<TD><%=x%>) <%=AssignedTo%></TD>
<TD> </TD>
<TD><%=ManagerSignOff%></TD>
<TD> </TD>
<TD><%=RelytoCustomer%></TD>
<TD> </TD>
<TD><%=FurtherActionRequired%></TD>
<TD> </TD>
<TD><CENTER><A
HREF="moddata.asp?Actionvar=delete&Recid=<%=RecSheetNo%>">Delete</A></CENTER
></TD>
<TD><A
HREF="moddata.asp?Actionvar=update&Recid=<%=RecSheetNo%>">Update</A></TD>
<%
rs.MoveNext
LOOP
RS.Close
Response.Write "</TABLE>"
Response.Write "<HR><A HREF='moddata.asp?actionvar=add'>Add a Record</A>"
END IF
conn.Close
Set conn = nothing
%>
</BODY>
</html>
Hope this helps,
Sean
-----Original Message-----
From: Peter Rooney [mailto:peter.rooney@p...]
Sent: 15 April 2002 11:44
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Sean,
Just a stab in the dark here but in your If statement you have 'flag' being
called from a Request.Form and in your WHERE clause you have 'Recid' being
called from a Request.QueryString, if you are submitting this via a form
then your 'Recid' value should also be a Request.Form value
Peter
-----Original Message-----
From: sean dillon [mailto:sdillon@b...]
Sent: 15 April 2002 11:14
To: Access ASP
Subject: [access_asp] This error is wrecking my head
I keep getting this error when i go to update a record:
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement.
/moddata.asp, line 81"
Line 81 is:
"Conn.Execute (SQLstmt)"
Ive been told it might be because i have used a reserved word in my update
statement but ive checked my code for reserved words and cant seem to find
any.
My update statement is as follows
IF Len(TRIM(Request.Form("flag"))) = 0 THEN
SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
Request.QueryString("Recid")
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Set rs = conn.Execute(SQLstmt)
IF NOT RS.EOF THEN
%>
Update<HR>
<FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
<B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
=rs("AssignedTo")%>"><BR>
<B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
VALUE="<%=rs("ManagerSignOff")%>"><BR>
<B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
VALUE="<%=rs("ReplytoCustomer")%>"><BR>
<B>FurtherActionRequired:</B> <INPUT TYPE="text"
NAME="FurtherActionRequired" VALUE="<%=rs("FurtherActionRequired")%>"><BR>
<B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
VALUE="<%=rs("ActionRequired")%>"><BR>
<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
<INPUT TYPE="submit" VALUE="Update">
</FORM>
<%
rs.MoveNext
rs.Close
END IF
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "UPDATE QuerySheet SET "
SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
("AssignedTo")) & "',"
SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
("ManagerSignOff")) & "',"
SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
("ReplytoCustomer")) & "',"
SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
("FurtherActionRequired")) & "',"
SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
("ActionRequired")) & "',"
SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
("Recordid"))
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Conn.Execute (SQLstmt)
Response.Write "Operation Complete<br><A
HREF=""moddata.asp"">Home</A>"
Can anyone please help as its wrecking my head to find the problem??
--
Virus scanned by edNET.
Message #7 by "Sean Dillon" <sdillon@b...> on Mon, 15 Apr 2002 12:54:45 +0100
|
|
This is a program which i found and i have just changed it to suit my
needs. Ive no idea whether it was originally working.
I presume <%=SQLstmt%> just executes each SQLstmt one by one.
My connection is workin perfect because im able to delete and records but i
only have trouble when it comes to updating.
-----Original Message-----
From: Peter Rooney [mailto:peter.rooney@p...]
Sent: 15 April 2002 12:05
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
What is the result of <%=SQLstmt%>?
Make sure you comment out the Set rs = conn.Execute(SQLstmt)
'rs = conn.Execute(SQLstmt)
so it doesn't try to execute the sql before you can see the results, also I
assume you have set up your connection (conn)
-----Original Message-----
From: Sean Dillon [mailto:sdillon@b...]
Sent: 15 April 2002 11:48
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Hi Peter,
I tried that but that also gives me the same error. Have you any ideas on
what else might be wrong.
Sean
-----Original Message-----
From: Peter Rooney [mailto:peter.rooney@p...]
Sent: 15 April 2002 11:44
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Sean,
Just a stab in the dark here but in your If statement you have 'flag' being
called from a Request.Form and in your WHERE clause you have 'Recid' being
called from a Request.QueryString, if you are submitting this via a form
then your 'Recid' value should also be a Request.Form value
Peter
-----Original Message-----
From: sean dillon [mailto:sdillon@b...]
Sent: 15 April 2002 11:14
To: Access ASP
Subject: [access_asp] This error is wrecking my head
I keep getting this error when i go to update a record:
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement.
/moddata.asp, line 81"
Line 81 is:
"Conn.Execute (SQLstmt)"
Ive been told it might be because i have used a reserved word in my update
statement but ive checked my code for reserved words and cant seem to find
any.
My update statement is as follows
IF Len(TRIM(Request.Form("flag"))) = 0 THEN
SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
Request.QueryString("Recid")
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Set rs = conn.Execute(SQLstmt)
IF NOT RS.EOF THEN
%>
Update<HR>
<FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
<B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
=rs("AssignedTo")%>"><BR>
<B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
VALUE="<%=rs("ManagerSignOff")%>"><BR>
<B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
VALUE="<%=rs("ReplytoCustomer")%>"><BR>
<B>FurtherActionRequired:</B> <INPUT TYPE="text"
NAME="FurtherActionRequired" VALUE="<%=rs("FurtherActionRequired")%>"><BR>
<B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
VALUE="<%=rs("ActionRequired")%>"><BR>
<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
<INPUT TYPE="submit" VALUE="Update">
</FORM>
<%
rs.MoveNext
rs.Close
END IF
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "UPDATE QuerySheet SET "
SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
("AssignedTo")) & "',"
SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
("ManagerSignOff")) & "',"
SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
("ReplytoCustomer")) & "',"
SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
("FurtherActionRequired")) & "',"
SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
("ActionRequired")) & "',"
SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
("Recordid"))
%>
<HR>
SQL statement: <%=SQLstmt%>
<HR>
<%
Conn.Execute (SQLstmt)
Response.Write "Operation Complete<br><A
HREF=""moddata.asp"">Home</A>"
Can anyone please help as its wrecking my head to find the problem??
--
Virus scanned by edNET.
--
Virus scanned by edNET.
Message #8 by "Paul Winter" <p.winter@s...> on Mon, 15 Apr 2002 16:14:57 +0100
|
|
Sean
The <%=SQLstmt%> will display your SQL statement on the screen. Then
you'll be able to past the SQL into a new access query using the design
wizard (sql). If this query works OK then you've eliminated the SQL as being
the problem and then you can look at other possibilities.
Do your text fields allow zero-length strings? If not you will need to add
a "zero to null" function for the situation where there is an empty text box
on the form.
HTH
Paul
----- Original Message -----
From: "Sean Dillon" <sdillon@b...>
To: "Access ASP" <access_asp@p...>
Sent: Monday, April 15, 2002 12:54 PM
Subject: [access_asp] RE: This error is wrecking my head
> This is a program which i found and i have just changed it to suit my
> needs. Ive no idea whether it was originally working.
> I presume <%=SQLstmt%> just executes each SQLstmt one by one.
> My connection is workin perfect because im able to delete and records but
i
> only have trouble when it comes to updating.
>
>
> -----Original Message-----
> From: Peter Rooney [mailto:peter.rooney@p...]
> Sent: 15 April 2002 12:05
> To: Access ASP
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> What is the result of <%=SQLstmt%>?
>
> Make sure you comment out the Set rs = conn.Execute(SQLstmt)
>
> 'rs = conn.Execute(SQLstmt)
>
> so it doesn't try to execute the sql before you can see the results, also
I
> assume you have set up your connection (conn)
>
>
>
>
>
>
> -----Original Message-----
> From: Sean Dillon [mailto:sdillon@b...]
> Sent: 15 April 2002 11:48
> To: Access ASP
> Subject: [access_asp] RE: This error is wrecking my head
>
>
>
> Hi Peter,
> I tried that but that also gives me the same error. Have you any ideas on
> what else might be wrong.
> Sean
>
> -----Original Message-----
> From: Peter Rooney [mailto:peter.rooney@p...]
> Sent: 15 April 2002 11:44
> To: Access ASP
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> Sean,
>
> Just a stab in the dark here but in your If statement you have 'flag'
being
> called from a Request.Form and in your WHERE clause you have 'Recid' being
> called from a Request.QueryString, if you are submitting this via a form
> then your 'Recid' value should also be a Request.Form value
>
> Peter
>
>
>
>
> -----Original Message-----
> From: sean dillon [mailto:sdillon@b...]
> Sent: 15 April 2002 11:14
> To: Access ASP
> Subject: [access_asp] This error is wrecking my head
>
>
>
> I keep getting this error when i go to update a record:
> "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
> statement.
> /moddata.asp, line 81"
>
> Line 81 is:
> "Conn.Execute (SQLstmt)"
> Ive been told it might be because i have used a reserved word in my update
> statement but ive checked my code for reserved words and cant seem to find
> any.
> My update statement is as follows
>
> IF Len(TRIM(Request.Form("flag"))) = 0 THEN
> SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
> Request.QueryString("Recid")
> %>
> <HR>
> SQL statement: <%=SQLstmt%>
> <HR>
> <%
> Set rs = conn.Execute(SQLstmt)
> IF NOT RS.EOF THEN
> %>
> Update<HR>
> <FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
> <B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
> =rs("AssignedTo")%>"><BR>
> <B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
> VALUE="<%=rs("ManagerSignOff")%>"><BR>
> <B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
> VALUE="<%=rs("ReplytoCustomer")%>"><BR>
> <B>FurtherActionRequired:</B> <INPUT TYPE="text"
> NAME="FurtherActionRequired" VALUE="<%=rs("FurtherActionRequired")%>"><BR>
> <B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
> VALUE="<%=rs("ActionRequired")%>"><BR>
> <INPUT TYPE="hidden" NAME="flag" VALUE="2">
> <INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
> <INPUT TYPE="submit" VALUE="Update">
> </FORM>
> <%
> rs.MoveNext
> rs.Close
> END IF
> ELSEIF Request.Form("flag")="2" THEN
> SQLstmt = "UPDATE QuerySheet SET "
> SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
> ("AssignedTo")) & "',"
> SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
> ("ManagerSignOff")) & "',"
> SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
> ("ReplytoCustomer")) & "',"
> SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
> ("FurtherActionRequired")) & "',"
> SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
> ("ActionRequired")) & "',"
> SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
> ("Recordid"))
> %>
> <HR>
> SQL statement: <%=SQLstmt%>
> <HR>
> <%
> Conn.Execute (SQLstmt)
> Response.Write "Operation Complete<br><A
> HREF=""moddata.asp"">Home</A>"
>
> Can anyone please help as its wrecking my head to find the problem??
> ---
Message #9 by "Sean Dillon" <sdillon@b...> on Mon, 15 Apr 2002 16:28:17 +0100
|
|
Paul,
It wont display the SQL statement i just keep getting an error saying:
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement.
/moddata.asp, line 81"
This happens when i press the update button.
-----Original Message-----
From: Paul Winter [mailto:p.winter@s...]
Sent: 15 April 2002 16:15
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Sean
The <%=SQLstmt%> will display your SQL statement on the screen. Then
you'll be able to past the SQL into a new access query using the design
wizard (sql). If this query works OK then you've eliminated the SQL as being
the problem and then you can look at other possibilities.
Do your text fields allow zero-length strings? If not you will need to add
a "zero to null" function for the situation where there is an empty text box
on the form.
HTH
Paul
----- Original Message -----
From: "Sean Dillon" <sdillon@b...>
To: "Access ASP" <access_asp@p...>
Sent: Monday, April 15, 2002 12:54 PM
Subject: [access_asp] RE: This error is wrecking my head
> This is a program which i found and i have just changed it to suit my
> needs. Ive no idea whether it was originally working.
> I presume <%=SQLstmt%> just executes each SQLstmt one by one.
> My connection is workin perfect because im able to delete and records but
i
> only have trouble when it comes to updating.
>
>
> -----Original Message-----
> From: Peter Rooney [mailto:peter.rooney@p...]
> Sent: 15 April 2002 12:05
> To: Access ASP
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> What is the result of <%=SQLstmt%>?
>
> Make sure you comment out the Set rs = conn.Execute(SQLstmt)
>
> 'rs = conn.Execute(SQLstmt)
>
> so it doesn't try to execute the sql before you can see the results, also
I
> assume you have set up your connection (conn)
>
>
>
>
>
>
> -----Original Message-----
> From: Sean Dillon [mailto:sdillon@b...]
> Sent: 15 April 2002 11:48
> To: Access ASP
> Subject: [access_asp] RE: This error is wrecking my head
>
>
>
> Hi Peter,
> I tried that but that also gives me the same error. Have you any ideas on
> what else might be wrong.
> Sean
>
> -----Original Message-----
> From: Peter Rooney [mailto:peter.rooney@p...]
> Sent: 15 April 2002 11:44
> To: Access ASP
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> Sean,
>
> Just a stab in the dark here but in your If statement you have 'flag'
being
> called from a Request.Form and in your WHERE clause you have 'Recid' being
> called from a Request.QueryString, if you are submitting this via a form
> then your 'Recid' value should also be a Request.Form value
>
> Peter
>
>
>
>
> -----Original Message-----
> From: sean dillon [mailto:sdillon@b...]
> Sent: 15 April 2002 11:14
> To: Access ASP
> Subject: [access_asp] This error is wrecking my head
>
>
>
> I keep getting this error when i go to update a record:
> "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
> statement.
> /moddata.asp, line 81"
>
> Line 81 is:
> "Conn.Execute (SQLstmt)"
> Ive been told it might be because i have used a reserved word in my update
> statement but ive checked my code for reserved words and cant seem to find
> any.
> My update statement is as follows
>
> IF Len(TRIM(Request.Form("flag"))) = 0 THEN
> SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
> Request.QueryString("Recid")
> %>
> <HR>
> SQL statement: <%=SQLstmt%>
> <HR>
> <%
> Set rs = conn.Execute(SQLstmt)
> IF NOT RS.EOF THEN
> %>
> Update<HR>
> <FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
> <B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
> =rs("AssignedTo")%>"><BR>
> <B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
> VALUE="<%=rs("ManagerSignOff")%>"><BR>
> <B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
> VALUE="<%=rs("ReplytoCustomer")%>"><BR>
> <B>FurtherActionRequired:</B> <INPUT TYPE="text"
> NAME="FurtherActionRequired" VALUE="<%=rs("FurtherActionRequired")%>"><BR>
> <B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
> VALUE="<%=rs("ActionRequired")%>"><BR>
> <INPUT TYPE="hidden" NAME="flag" VALUE="2">
> <INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
> <INPUT TYPE="submit" VALUE="Update">
> </FORM>
> <%
> rs.MoveNext
> rs.Close
> END IF
> ELSEIF Request.Form("flag")="2" THEN
> SQLstmt = "UPDATE QuerySheet SET "
> SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
> ("AssignedTo")) & "',"
> SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
> ("ManagerSignOff")) & "',"
> SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
> ("ReplytoCustomer")) & "',"
> SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
> ("FurtherActionRequired")) & "',"
> SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
> ("ActionRequired")) & "',"
> SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
> ("Recordid"))
> %>
> <HR>
> SQL statement: <%=SQLstmt%>
> <HR>
> <%
> Conn.Execute (SQLstmt)
> Response.Write "Operation Complete<br><A
> HREF=""moddata.asp"">Home</A>"
>
> Can anyone please help as its wrecking my head to find the problem??
> ---
Message #10 by "Sean Dillon" <sdillon@b...> on Mon, 15 Apr 2002 16:47:05 +0100
|
|
Paul,
This is exactly what is displayed on the screen when i press the update
button:
'SQL statement: UPDATE QuerySheet SET
AssignedTo='fsd',ManagerSignOff='dsfds',ReplytoCustomer='df',FurtherActionRe
quired='df',ActionRequired='dfs',WHERE SheetNo=189
----------------------------------------------------------------------------
----
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
/moddata.asp, line 81
Sean
-----Original Message-----
From: Paul Winter [mailto:p.winter@s...]
Sent: 15 April 2002 16:15
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Sean
The <%=SQLstmt%> will display your SQL statement on the screen. Then
you'll be able to past the SQL into a new access query using the design
wizard (sql). If this query works OK then you've eliminated the SQL as being
the problem and then you can look at other possibilities.
Do your text fields allow zero-length strings? If not you will need to add
a "zero to null" function for the situation where there is an empty text box
on the form.
HTH
Paul
----- Original Message -----
From: "Sean Dillon" <sdillon@b...>
To: "Access ASP" <access_asp@p...>
Sent: Monday, April 15, 2002 12:54 PM
Subject: [access_asp] RE: This error is wrecking my head
> This is a program which i found and i have just changed it to suit my
> needs. Ive no idea whether it was originally working.
> I presume <%=SQLstmt%> just executes each SQLstmt one by one.
> My connection is workin perfect because im able to delete and records but
i
> only have trouble when it comes to updating.
>
>
> -----Original Message-----
> From: Peter Rooney [mailto:peter.rooney@p...]
> Sent: 15 April 2002 12:05
> To: Access ASP
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> What is the result of <%=SQLstmt%>?
>
> Make sure you comment out the Set rs = conn.Execute(SQLstmt)
>
> 'rs = conn.Execute(SQLstmt)
>
> so it doesn't try to execute the sql before you can see the results, also
I
> assume you have set up your connection (conn)
>
>
>
>
>
>
> -----Original Message-----
> From: Sean Dillon [mailto:sdillon@b...]
> Sent: 15 April 2002 11:48
> To: Access ASP
> Subject: [access_asp] RE: This error is wrecking my head
>
>
>
> Hi Peter,
> I tried that but that also gives me the same error. Have you any ideas on
> what else might be wrong.
> Sean
>
> -----Original Message-----
> From: Peter Rooney [mailto:peter.rooney@p...]
> Sent: 15 April 2002 11:44
> To: Access ASP
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> Sean,
>
> Just a stab in the dark here but in your If statement you have 'flag'
being
> called from a Request.Form and in your WHERE clause you have 'Recid' being
> called from a Request.QueryString, if you are submitting this via a form
> then your 'Recid' value should also be a Request.Form value
>
> Peter
>
>
>
>
> -----Original Message-----
> From: sean dillon [mailto:sdillon@b...]
> Sent: 15 April 2002 11:14
> To: Access ASP
> Subject: [access_asp] This error is wrecking my head
>
>
>
> I keep getting this error when i go to update a record:
> "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
> statement.
> /moddata.asp, line 81"
>
> Line 81 is:
> "Conn.Execute (SQLstmt)"
> Ive been told it might be because i have used a reserved word in my update
> statement but ive checked my code for reserved words and cant seem to find
> any.
> My update statement is as follows
>
> IF Len(TRIM(Request.Form("flag"))) = 0 THEN
> SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
> Request.QueryString("Recid")
> %>
> <HR>
> SQL statement: <%=SQLstmt%>
> <HR>
> <%
> Set rs = conn.Execute(SQLstmt)
> IF NOT RS.EOF THEN
> %>
> Update<HR>
> <FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
> <B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
> =rs("AssignedTo")%>"><BR>
> <B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
> VALUE="<%=rs("ManagerSignOff")%>"><BR>
> <B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
> VALUE="<%=rs("ReplytoCustomer")%>"><BR>
> <B>FurtherActionRequired:</B> <INPUT TYPE="text"
> NAME="FurtherActionRequired" VALUE="<%=rs("FurtherActionRequired")%>"><BR>
> <B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
> VALUE="<%=rs("ActionRequired")%>"><BR>
> <INPUT TYPE="hidden" NAME="flag" VALUE="2">
> <INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
> <INPUT TYPE="submit" VALUE="Update">
> </FORM>
> <%
> rs.MoveNext
> rs.Close
> END IF
> ELSEIF Request.Form("flag")="2" THEN
> SQLstmt = "UPDATE QuerySheet SET "
> SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
> ("AssignedTo")) & "',"
> SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
> ("ManagerSignOff")) & "',"
> SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
> ("ReplytoCustomer")) & "',"
> SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
> ("FurtherActionRequired")) & "',"
> SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
> ("ActionRequired")) & "',"
> SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
> ("Recordid"))
> %>
> <HR>
> SQL statement: <%=SQLstmt%>
> <HR>
> <%
> Conn.Execute (SQLstmt)
> Response.Write "Operation Complete<br><A
> HREF=""moddata.asp"">Home</A>"
>
> Can anyone please help as its wrecking my head to find the problem??
> ---
Message #11 by "Paul Winter" <p.winter@s...> on Mon, 15 Apr 2002 16:59:13 +0100
|
|
Sean
I think you need to replace the , before the WHERE clause with a space.
i.e. you don't need the , in the ActionRequired line
e.g.
SQLstmt = SQLstmt & "ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "'"
rather than
SQLstmt = SQLstmt & "ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "',"
Possibly!
Paul
----- Original Message -----
From: "Sean Dillon" <sdillon@b...>
To: "Access ASP" <access_asp@p...>
Sent: Monday, April 15, 2002 4:47 PM
Subject: [access_asp] RE: This error is wrecking my head
> Paul,
> This is exactly what is displayed on the screen when i press the update
> button:
>
> 'SQL statement: UPDATE QuerySheet SET
>
AssignedTo='fsd',ManagerSignOff='dsfds',ReplytoCustomer='df',FurtherActionRe
> quired='df',ActionRequired='dfs',WHERE SheetNo=189
> --------------------------------------------------------------------------
--
> ----
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement.
>
> /moddata.asp, line 81
>
> Sean
>
> -----Original Message-----
> From: Paul Winter [mailto:p.winter@s...]
> Sent: 15 April 2002 16:15
> To: Access ASP
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> Sean
>
> The <%=SQLstmt%> will display your SQL statement on the screen. Then
> you'll be able to past the SQL into a new access query using the design
> wizard (sql). If this query works OK then you've eliminated the SQL as
being
> the problem and then you can look at other possibilities.
>
> Do your text fields allow zero-length strings? If not you will need to
add
> a "zero to null" function for the situation where there is an empty text
box
> on the form.
>
> HTH
>
> Paul
>
> ----- Original Message -----
> From: "Sean Dillon" <sdillon@b...>
> To: "Access ASP" <access_asp@p...>
> Sent: Monday, April 15, 2002 12:54 PM
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> > This is a program which i found and i have just changed it to suit my
> > needs. Ive no idea whether it was originally working.
> > I presume <%=SQLstmt%> just executes each SQLstmt one by one.
> > My connection is workin perfect because im able to delete and records
but
> i
> > only have trouble when it comes to updating.
> >
> >
> > -----Original Message-----
> > From: Peter Rooney [mailto:peter.rooney@p...]
> > Sent: 15 April 2002 12:05
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> > What is the result of <%=SQLstmt%>?
> >
> > Make sure you comment out the Set rs = conn.Execute(SQLstmt)
> >
> > 'rs = conn.Execute(SQLstmt)
> >
> > so it doesn't try to execute the sql before you can see the results,
also
> I
> > assume you have set up your connection (conn)
> >
> >
> >
> >
> >
> >
> > -----Original Message-----
> > From: Sean Dillon [mailto:sdillon@b...]
> > Sent: 15 April 2002 11:48
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> >
> > Hi Peter,
> > I tried that but that also gives me the same error. Have you any ideas
on
> > what else might be wrong.
> > Sean
> >
> > -----Original Message-----
> > From: Peter Rooney [mailto:peter.rooney@p...]
> > Sent: 15 April 2002 11:44
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> > Sean,
> >
> > Just a stab in the dark here but in your If statement you have 'flag'
> being
> > called from a Request.Form and in your WHERE clause you have 'Recid'
being
> > called from a Request.QueryString, if you are submitting this via a form
> > then your 'Recid' value should also be a Request.Form value
> >
> > Peter
> >
> >
> >
> >
> > -----Original Message-----
> > From: sean dillon [mailto:sdillon@b...]
> > Sent: 15 April 2002 11:14
> > To: Access ASP
> > Subject: [access_asp] This error is wrecking my head
> >
> >
> >
> > I keep getting this error when i go to update a record:
> > "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> > [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
> > statement.
> > /moddata.asp, line 81"
> >
> > Line 81 is:
> > "Conn.Execute (SQLstmt)"
> > Ive been told it might be because i have used a reserved word in my
update
> > statement but ive checked my code for reserved words and cant seem to
find
> > any.
> > My update statement is as follows
> >
> > IF Len(TRIM(Request.Form("flag"))) = 0 THEN
> > SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
> > Request.QueryString("Recid")
> > %>
> > <HR>
> > SQL statement: <%=SQLstmt%>
> > <HR>
> > <%
> > Set rs = conn.Execute(SQLstmt)
> > IF NOT RS.EOF THEN
> > %>
> > Update<HR>
> > <FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
> > <B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
> > =rs("AssignedTo")%>"><BR>
> > <B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
> > VALUE="<%=rs("ManagerSignOff")%>"><BR>
> > <B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
> > VALUE="<%=rs("ReplytoCustomer")%>"><BR>
> > <B>FurtherActionRequired:</B> <INPUT TYPE="text"
> > NAME="FurtherActionRequired"
VALUE="<%=rs("FurtherActionRequired")%>"><BR>
> > <B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
> > VALUE="<%=rs("ActionRequired")%>"><BR>
> > <INPUT TYPE="hidden" NAME="flag" VALUE="2">
> > <INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
> > <INPUT TYPE="submit" VALUE="Update">
> > </FORM>
> > <%
> > rs.MoveNext
> > rs.Close
> > END IF
> > ELSEIF Request.Form("flag")="2" THEN
> > SQLstmt = "UPDATE QuerySheet SET "
> > SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
> > ("AssignedTo")) & "',"
> > SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
> > ("ManagerSignOff")) & "',"
> > SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
> > ("ReplytoCustomer")) & "',"
> > SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
> > ("FurtherActionRequired")) & "',"
> > SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
> > ("ActionRequired")) & "',"
> > SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
> > ("Recordid"))
> > %>
> > <HR>
> > SQL statement: <%=SQLstmt%>
> > <HR>
> > <%
> > Conn.Execute (SQLstmt)
> > Response.Write "Operation Complete<br><A
> > HREF=""moddata.asp"">Home</A>"
> >
> > Can anyone please help as its wrecking my head to find the problem??
> > ---
>
>
>
>
>
>
Message #12 by "Sean Dillon" <sdillon@b...> on Mon, 15 Apr 2002 17:05:06 +0100
|
|
Paul
Ive just found out i was executing the SQLstatements twice put it seems like
everything is working correct now put my database wont update.
I get this:
SQL statement: UPDATE QuerySheet SET
AssignedTo='aaa',ManagerSignOff='aaa',ReplytoCustomer='aa',FurtherActionRequ
ired='aa',ActionRequired='aa',WHERE SheetNo=190
----------------------------------------------------------------------------
----
Operation Complete
which indicates every thing is working right but when i check my database it
hasn't updated.
Got ne ideas what could be wrong?
Sean
-----Original Message-----
From: Paul Winter [mailto:p.winter@s...]
Sent: 15 April 2002 16:59
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Sean
I think you need to replace the , before the WHERE clause with a space.
i.e. you don't need the , in the ActionRequired line
e.g.
SQLstmt = SQLstmt & "ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "'"
rather than
SQLstmt = SQLstmt & "ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "',"
Possibly!
Paul
----- Original Message -----
From: "Sean Dillon" <sdillon@b...>
To: "Access ASP" <access_asp@p...>
Sent: Monday, April 15, 2002 4:47 PM
Subject: [access_asp] RE: This error is wrecking my head
> Paul,
> This is exactly what is displayed on the screen when i press the update
> button:
>
> 'SQL statement: UPDATE QuerySheet SET
>
AssignedTo='fsd',ManagerSignOff='dsfds',ReplytoCustomer='df',FurtherActionRe
> quired='df',ActionRequired='dfs',WHERE SheetNo=189
> --------------------------------------------------------------------------
--
> ----
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement.
>
> /moddata.asp, line 81
>
> Sean
>
> -----Original Message-----
> From: Paul Winter [mailto:p.winter@s...]
> Sent: 15 April 2002 16:15
> To: Access ASP
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> Sean
>
> The <%=SQLstmt%> will display your SQL statement on the screen. Then
> you'll be able to past the SQL into a new access query using the design
> wizard (sql). If this query works OK then you've eliminated the SQL as
being
> the problem and then you can look at other possibilities.
>
> Do your text fields allow zero-length strings? If not you will need to
add
> a "zero to null" function for the situation where there is an empty text
box
> on the form.
>
> HTH
>
> Paul
>
> ----- Original Message -----
> From: "Sean Dillon" <sdillon@b...>
> To: "Access ASP" <access_asp@p...>
> Sent: Monday, April 15, 2002 12:54 PM
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> > This is a program which i found and i have just changed it to suit my
> > needs. Ive no idea whether it was originally working.
> > I presume <%=SQLstmt%> just executes each SQLstmt one by one.
> > My connection is workin perfect because im able to delete and records
but
> i
> > only have trouble when it comes to updating.
> >
> >
> > -----Original Message-----
> > From: Peter Rooney [mailto:peter.rooney@p...]
> > Sent: 15 April 2002 12:05
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> > What is the result of <%=SQLstmt%>?
> >
> > Make sure you comment out the Set rs = conn.Execute(SQLstmt)
> >
> > 'rs = conn.Execute(SQLstmt)
> >
> > so it doesn't try to execute the sql before you can see the results,
also
> I
> > assume you have set up your connection (conn)
> >
> >
> >
> >
> >
> >
> > -----Original Message-----
> > From: Sean Dillon [mailto:sdillon@b...]
> > Sent: 15 April 2002 11:48
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> >
> > Hi Peter,
> > I tried that but that also gives me the same error. Have you any ideas
on
> > what else might be wrong.
> > Sean
> >
> > -----Original Message-----
> > From: Peter Rooney [mailto:peter.rooney@p...]
> > Sent: 15 April 2002 11:44
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> > Sean,
> >
> > Just a stab in the dark here but in your If statement you have 'flag'
> being
> > called from a Request.Form and in your WHERE clause you have 'Recid'
being
> > called from a Request.QueryString, if you are submitting this via a form
> > then your 'Recid' value should also be a Request.Form value
> >
> > Peter
> >
> >
> >
> >
> > -----Original Message-----
> > From: sean dillon [mailto:sdillon@b...]
> > Sent: 15 April 2002 11:14
> > To: Access ASP
> > Subject: [access_asp] This error is wrecking my head
> >
> >
> >
> > I keep getting this error when i go to update a record:
> > "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> > [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
> > statement.
> > /moddata.asp, line 81"
> >
> > Line 81 is:
> > "Conn.Execute (SQLstmt)"
> > Ive been told it might be because i have used a reserved word in my
update
> > statement but ive checked my code for reserved words and cant seem to
find
> > any.
> > My update statement is as follows
> >
> > IF Len(TRIM(Request.Form("flag"))) = 0 THEN
> > SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
> > Request.QueryString("Recid")
> > %>
> > <HR>
> > SQL statement: <%=SQLstmt%>
> > <HR>
> > <%
> > Set rs = conn.Execute(SQLstmt)
> > IF NOT RS.EOF THEN
> > %>
> > Update<HR>
> > <FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
> > <B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
> > =rs("AssignedTo")%>"><BR>
> > <B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
> > VALUE="<%=rs("ManagerSignOff")%>"><BR>
> > <B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
> > VALUE="<%=rs("ReplytoCustomer")%>"><BR>
> > <B>FurtherActionRequired:</B> <INPUT TYPE="text"
> > NAME="FurtherActionRequired"
VALUE="<%=rs("FurtherActionRequired")%>"><BR>
> > <B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
> > VALUE="<%=rs("ActionRequired")%>"><BR>
> > <INPUT TYPE="hidden" NAME="flag" VALUE="2">
> > <INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
> > <INPUT TYPE="submit" VALUE="Update">
> > </FORM>
> > <%
> > rs.MoveNext
> > rs.Close
> > END IF
> > ELSEIF Request.Form("flag")="2" THEN
> > SQLstmt = "UPDATE QuerySheet SET "
> > SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
> > ("AssignedTo")) & "',"
> > SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
> > ("ManagerSignOff")) & "',"
> > SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
> > ("ReplytoCustomer")) & "',"
> > SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
> > ("FurtherActionRequired")) & "',"
> > SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
> > ("ActionRequired")) & "',"
> > SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
> > ("Recordid"))
> > %>
> > <HR>
> > SQL statement: <%=SQLstmt%>
> > <HR>
> > <%
> > Conn.Execute (SQLstmt)
> > Response.Write "Operation Complete<br><A
> > HREF=""moddata.asp"">Home</A>"
> >
> > Can anyone please help as its wrecking my head to find the problem??
> > ---
>
>
>
>
>
>
Message #13 by "Peter Rooney" <peter.rooney@p...> on Mon, 15 Apr 2002 17:23:08 +0100
|
|
Hi Sean,
Sorry for the delay in replying I have been out of the office and just got
back. Try removing the comma after 'aa'
ActionRequired='aa',WHERE SheetNo=190
To
ActionRequired='aa'WHERE SheetNo=190
Peter
-----Original Message-----
From: Sean Dillon [mailto:sdillon@b...]
Sent: 15 April 2002 17:05
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Paul
Ive just found out i was executing the SQLstatements twice put it seems like
everything is working correct now put my database wont update.
I get this:
SQL statement: UPDATE QuerySheet SET
AssignedTo='aaa',ManagerSignOff='aaa',ReplytoCustomer='aa',FurtherActionRequ
ired='aa',ActionRequired='aa',WHERE SheetNo=190
----------------------------------------------------------------------------
----
Operation Complete
which indicates every thing is working right but when i check my database it
hasn't updated.
Got ne ideas what could be wrong?
Sean
-----Original Message-----
From: Paul Winter [mailto:p.winter@s...]
Sent: 15 April 2002 16:59
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Sean
I think you need to replace the , before the WHERE clause with a space.
i.e. you don't need the , in the ActionRequired line
e.g.
SQLstmt = SQLstmt & "ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "'"
rather than
SQLstmt = SQLstmt & "ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "',"
Possibly!
Paul
----- Original Message -----
From: "Sean Dillon" <sdillon@b...>
To: "Access ASP" <access_asp@p...>
Sent: Monday, April 15, 2002 4:47 PM
Subject: [access_asp] RE: This error is wrecking my head
> Paul,
> This is exactly what is displayed on the screen when i press the update
> button:
>
> 'SQL statement: UPDATE QuerySheet SET
>
AssignedTo='fsd',ManagerSignOff='dsfds',ReplytoCustomer='df',FurtherActionRe
> quired='df',ActionRequired='dfs',WHERE SheetNo=189
> --------------------------------------------------------------------------
--
> ----
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement.
>
> /moddata.asp, line 81
>
> Sean
>
> -----Original Message-----
> From: Paul Winter [mailto:p.winter@s...]
> Sent: 15 April 2002 16:15
> To: Access ASP
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> Sean
>
> The <%=SQLstmt%> will display your SQL statement on the screen. Then
> you'll be able to past the SQL into a new access query using the design
> wizard (sql). If this query works OK then you've eliminated the SQL as
being
> the problem and then you can look at other possibilities.
>
> Do your text fields allow zero-length strings? If not you will need to
add
> a "zero to null" function for the situation where there is an empty text
box
> on the form.
>
> HTH
>
> Paul
>
> ----- Original Message -----
> From: "Sean Dillon" <sdillon@b...>
> To: "Access ASP" <access_asp@p...>
> Sent: Monday, April 15, 2002 12:54 PM
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> > This is a program which i found and i have just changed it to suit my
> > needs. Ive no idea whether it was originally working.
> > I presume <%=SQLstmt%> just executes each SQLstmt one by one.
> > My connection is workin perfect because im able to delete and records
but
> i
> > only have trouble when it comes to updating.
> >
> >
> > -----Original Message-----
> > From: Peter Rooney [mailto:peter.rooney@p...]
> > Sent: 15 April 2002 12:05
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> > What is the result of <%=SQLstmt%>?
> >
> > Make sure you comment out the Set rs = conn.Execute(SQLstmt)
> >
> > 'rs = conn.Execute(SQLstmt)
> >
> > so it doesn't try to execute the sql before you can see the results,
also
> I
> > assume you have set up your connection (conn)
> >
> >
> >
> >
> >
> >
> > -----Original Message-----
> > From: Sean Dillon [mailto:sdillon@b...]
> > Sent: 15 April 2002 11:48
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> >
> > Hi Peter,
> > I tried that but that also gives me the same error. Have you any ideas
on
> > what else might be wrong.
> > Sean
> >
> > -----Original Message-----
> > From: Peter Rooney [mailto:peter.rooney@p...]
> > Sent: 15 April 2002 11:44
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> > Sean,
> >
> > Just a stab in the dark here but in your If statement you have 'flag'
> being
> > called from a Request.Form and in your WHERE clause you have 'Recid'
being
> > called from a Request.QueryString, if you are submitting this via a form
> > then your 'Recid' value should also be a Request.Form value
> >
> > Peter
> >
> >
> >
> >
> > -----Original Message-----
> > From: sean dillon [mailto:sdillon@b...]
> > Sent: 15 April 2002 11:14
> > To: Access ASP
> > Subject: [access_asp] This error is wrecking my head
> >
> >
> >
> > I keep getting this error when i go to update a record:
> > "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> > [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
> > statement.
> > /moddata.asp, line 81"
> >
> > Line 81 is:
> > "Conn.Execute (SQLstmt)"
> > Ive been told it might be because i have used a reserved word in my
update
> > statement but ive checked my code for reserved words and cant seem to
find
> > any.
> > My update statement is as follows
> >
> > IF Len(TRIM(Request.Form("flag"))) = 0 THEN
> > SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
> > Request.QueryString("Recid")
> > %>
> > <HR>
> > SQL statement: <%=SQLstmt%>
> > <HR>
> > <%
> > Set rs = conn.Execute(SQLstmt)
> > IF NOT RS.EOF THEN
> > %>
> > Update<HR>
> > <FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
> > <B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
> > =rs("AssignedTo")%>"><BR>
> > <B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
> > VALUE="<%=rs("ManagerSignOff")%>"><BR>
> > <B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
> > VALUE="<%=rs("ReplytoCustomer")%>"><BR>
> > <B>FurtherActionRequired:</B> <INPUT TYPE="text"
> > NAME="FurtherActionRequired"
VALUE="<%=rs("FurtherActionRequired")%>"><BR>
> > <B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
> > VALUE="<%=rs("ActionRequired")%>"><BR>
> > <INPUT TYPE="hidden" NAME="flag" VALUE="2">
> > <INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
> > <INPUT TYPE="submit" VALUE="Update">
> > </FORM>
> > <%
> > rs.MoveNext
> > rs.Close
> > END IF
> > ELSEIF Request.Form("flag")="2" THEN
> > SQLstmt = "UPDATE QuerySheet SET "
> > SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
> > ("AssignedTo")) & "',"
> > SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
> > ("ManagerSignOff")) & "',"
> > SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
> > ("ReplytoCustomer")) & "',"
> > SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
> > ("FurtherActionRequired")) & "',"
> > SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
> > ("ActionRequired")) & "',"
> > SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
> > ("Recordid"))
> > %>
> > <HR>
> > SQL statement: <%=SQLstmt%>
> > <HR>
> > <%
> > Conn.Execute (SQLstmt)
> > Response.Write "Operation Complete<br><A
> > HREF=""moddata.asp"">Home</A>"
> >
> > Can anyone please help as its wrecking my head to find the problem??
> > ---
>
>
>
>
>
>
--
Virus scanned by edNET.
Message #14 by "Sean Dillon" <sdillon@b...> on Tue, 16 Apr 2002 09:18:19 +0100
|
|
Peter,
I tried removing the comma but it doesn't make any difference. It still
seems like everything is working right but it wont update the database
Sean
-----Original Message-----
From: Peter Rooney [mailto:peter.rooney@p...]
Sent: 15 April 2002 17:23
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Hi Sean,
Sorry for the delay in replying I have been out of the office and just got
back. Try removing the comma after 'aa'
ActionRequired='aa',WHERE SheetNo=190
To
ActionRequired='aa'WHERE SheetNo=190
Peter
-----Original Message-----
From: Sean Dillon [mailto:sdillon@b...]
Sent: 15 April 2002 17:05
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Paul
Ive just found out i was executing the SQLstatements twice put it seems like
everything is working correct now put my database wont update.
I get this:
SQL statement: UPDATE QuerySheet SET
AssignedTo='aaa',ManagerSignOff='aaa',ReplytoCustomer='aa',FurtherActionRequ
ired='aa',ActionRequired='aa',WHERE SheetNo=190
----------------------------------------------------------------------------
----
Operation Complete
which indicates every thing is working right but when i check my database it
hasn't updated.
Got ne ideas what could be wrong?
Sean
-----Original Message-----
From: Paul Winter [mailto:p.winter@s...]
Sent: 15 April 2002 16:59
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Sean
I think you need to replace the , before the WHERE clause with a space.
i.e. you don't need the , in the ActionRequired line
e.g.
SQLstmt = SQLstmt & "ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "'"
rather than
SQLstmt = SQLstmt & "ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "',"
Possibly!
Paul
----- Original Message -----
From: "Sean Dillon" <sdillon@b...>
To: "Access ASP" <access_asp@p...>
Sent: Monday, April 15, 2002 4:47 PM
Subject: [access_asp] RE: This error is wrecking my head
> Paul,
> This is exactly what is displayed on the screen when i press the update
> button:
>
> 'SQL statement: UPDATE QuerySheet SET
>
AssignedTo='fsd',ManagerSignOff='dsfds',ReplytoCustomer='df',FurtherActionRe
> quired='df',ActionRequired='dfs',WHERE SheetNo=189
> --------------------------------------------------------------------------
--
> ----
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement.
>
> /moddata.asp, line 81
>
> Sean
>
> -----Original Message-----
> From: Paul Winter [mailto:p.winter@s...]
> Sent: 15 April 2002 16:15
> To: Access ASP
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> Sean
>
> The <%=SQLstmt%> will display your SQL statement on the screen. Then
> you'll be able to past the SQL into a new access query using the design
> wizard (sql). If this query works OK then you've eliminated the SQL as
being
> the problem and then you can look at other possibilities.
>
> Do your text fields allow zero-length strings? If not you will need to
add
> a "zero to null" function for the situation where there is an empty text
box
> on the form.
>
> HTH
>
> Paul
>
> ----- Original Message -----
> From: "Sean Dillon" <sdillon@b...>
> To: "Access ASP" <access_asp@p...>
> Sent: Monday, April 15, 2002 12:54 PM
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> > This is a program which i found and i have just changed it to suit my
> > needs. Ive no idea whether it was originally working.
> > I presume <%=SQLstmt%> just executes each SQLstmt one by one.
> > My connection is workin perfect because im able to delete and records
but
> i
> > only have trouble when it comes to updating.
> >
> >
> > -----Original Message-----
> > From: Peter Rooney [mailto:peter.rooney@p...]
> > Sent: 15 April 2002 12:05
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> > What is the result of <%=SQLstmt%>?
> >
> > Make sure you comment out the Set rs = conn.Execute(SQLstmt)
> >
> > 'rs = conn.Execute(SQLstmt)
> >
> > so it doesn't try to execute the sql before you can see the results,
also
> I
> > assume you have set up your connection (conn)
> >
> >
> >
> >
> >
> >
> > -----Original Message-----
> > From: Sean Dillon [mailto:sdillon@b...]
> > Sent: 15 April 2002 11:48
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> >
> > Hi Peter,
> > I tried that but that also gives me the same error. Have you any ideas
on
> > what else might be wrong.
> > Sean
> >
> > -----Original Message-----
> > From: Peter Rooney [mailto:peter.rooney@p...]
> > Sent: 15 April 2002 11:44
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> > Sean,
> >
> > Just a stab in the dark here but in your If statement you have 'flag'
> being
> > called from a Request.Form and in your WHERE clause you have 'Recid'
being
> > called from a Request.QueryString, if you are submitting this via a form
> > then your 'Recid' value should also be a Request.Form value
> >
> > Peter
> >
> >
> >
> >
> > -----Original Message-----
> > From: sean dillon [mailto:sdillon@b...]
> > Sent: 15 April 2002 11:14
> > To: Access ASP
> > Subject: [access_asp] This error is wrecking my head
> >
> >
> >
> > I keep getting this error when i go to update a record:
> > "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> > [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
> > statement.
> > /moddata.asp, line 81"
> >
> > Line 81 is:
> > "Conn.Execute (SQLstmt)"
> > Ive been told it might be because i have used a reserved word in my
update
> > statement but ive checked my code for reserved words and cant seem to
find
> > any.
> > My update statement is as follows
> >
> > IF Len(TRIM(Request.Form("flag"))) = 0 THEN
> > SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
> > Request.QueryString("Recid")
> > %>
> > <HR>
> > SQL statement: <%=SQLstmt%>
> > <HR>
> > <%
> > Set rs = conn.Execute(SQLstmt)
> > IF NOT RS.EOF THEN
> > %>
> > Update<HR>
> > <FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
> > <B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
> > =rs("AssignedTo")%>"><BR>
> > <B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
> > VALUE="<%=rs("ManagerSignOff")%>"><BR>
> > <B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
> > VALUE="<%=rs("ReplytoCustomer")%>"><BR>
> > <B>FurtherActionRequired:</B> <INPUT TYPE="text"
> > NAME="FurtherActionRequired"
VALUE="<%=rs("FurtherActionRequired")%>"><BR>
> > <B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
> > VALUE="<%=rs("ActionRequired")%>"><BR>
> > <INPUT TYPE="hidden" NAME="flag" VALUE="2">
> > <INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
> > <INPUT TYPE="submit" VALUE="Update">
> > </FORM>
> > <%
> > rs.MoveNext
> > rs.Close
> > END IF
> > ELSEIF Request.Form("flag")="2" THEN
> > SQLstmt = "UPDATE QuerySheet SET "
> > SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
> > ("AssignedTo")) & "',"
> > SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
> > ("ManagerSignOff")) & "',"
> > SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
> > ("ReplytoCustomer")) & "',"
> > SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
> > ("FurtherActionRequired")) & "',"
> > SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
> > ("ActionRequired")) & "',"
> > SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
> > ("Recordid"))
> > %>
> > <HR>
> > SQL statement: <%=SQLstmt%>
> > <HR>
> > <%
> > Conn.Execute (SQLstmt)
> > Response.Write "Operation Complete<br><A
> > HREF=""moddata.asp"">Home</A>"
> >
> > Can anyone please help as its wrecking my head to find the problem??
> > ---
>
>
>
>
>
>
--
Virus scanned by edNET.
Message #15 by "Peter Rooney" <peter.rooney@p...> on Tue, 16 Apr 2002 09:52:29 +0100
|
|
Sean,
cut and paste this in and see if it makes a difference
SQLstmt = "UPDATE QuerySheet SET "
SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form("AssignedTo")) &
"',"
SQLstmt = SQLstmt & " ManagerSignOff='" &
TRIM(Request.Form("ManagerSignOff")) & "',"
SQLstmt = SQLstmt & " ReplytoCustomer='" &
TRIM(Request.Form("ReplytoCustomer")) & "',"
SQLstmt = SQLstmt & " FurtherActionRequired='" &
TRIM(Request.Form("FurtherActionRequired")) & "',"
SQLstmt = SQLstmt & " ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "'"
SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form("Recordid"))
Peter
-----Original Message-----
From: Sean Dillon [mailto:sdillon@b...]
Sent: 16 April 2002 09:18
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Peter,
I tried removing the comma but it doesn't make any difference. It still
seems like everything is working right but it wont update the database
Sean
-----Original Message-----
From: Peter Rooney [mailto:peter.rooney@p...]
Sent: 15 April 2002 17:23
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Hi Sean,
Sorry for the delay in replying I have been out of the office and just got
back. Try removing the comma after 'aa'
ActionRequired='aa',WHERE SheetNo=190
To
ActionRequired='aa'WHERE SheetNo=190
Peter
-----Original Message-----
From: Sean Dillon [mailto:sdillon@b...]
Sent: 15 April 2002 17:05
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Paul
Ive just found out i was executing the SQLstatements twice put it seems like
everything is working correct now put my database wont update.
I get this:
SQL statement: UPDATE QuerySheet SET
AssignedTo='aaa',ManagerSignOff='aaa',ReplytoCustomer='aa',FurtherActionRequ
ired='aa',ActionRequired='aa',WHERE SheetNo=190
----------------------------------------------------------------------------
----
Operation Complete
which indicates every thing is working right but when i check my database it
hasn't updated.
Got ne ideas what could be wrong?
Sean
-----Original Message-----
From: Paul Winter [mailto:p.winter@s...]
Sent: 15 April 2002 16:59
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Sean
I think you need to replace the , before the WHERE clause with a space.
i.e. you don't need the , in the ActionRequired line
e.g.
SQLstmt = SQLstmt & "ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "'"
rather than
SQLstmt = SQLstmt & "ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "',"
Possibly!
Paul
----- Original Message -----
From: "Sean Dillon" <sdillon@b...>
To: "Access ASP" <access_asp@p...>
Sent: Monday, April 15, 2002 4:47 PM
Subject: [access_asp] RE: This error is wrecking my head
> Paul,
> This is exactly what is displayed on the screen when i press the update
> button:
>
> 'SQL statement: UPDATE QuerySheet SET
>
AssignedTo='fsd',ManagerSignOff='dsfds',ReplytoCustomer='df',FurtherActionRe
> quired='df',ActionRequired='dfs',WHERE SheetNo=189
> --------------------------------------------------------------------------
--
> ----
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement.
>
> /moddata.asp, line 81
>
> Sean
>
> -----Original Message-----
> From: Paul Winter [mailto:p.winter@s...]
> Sent: 15 April 2002 16:15
> To: Access ASP
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> Sean
>
> The <%=SQLstmt%> will display your SQL statement on the screen. Then
> you'll be able to past the SQL into a new access query using the design
> wizard (sql). If this query works OK then you've eliminated the SQL as
being
> the problem and then you can look at other possibilities.
>
> Do your text fields allow zero-length strings? If not you will need to
add
> a "zero to null" function for the situation where there is an empty text
box
> on the form.
>
> HTH
>
> Paul
>
> ----- Original Message -----
> From: "Sean Dillon" <sdillon@b...>
> To: "Access ASP" <access_asp@p...>
> Sent: Monday, April 15, 2002 12:54 PM
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> > This is a program which i found and i have just changed it to suit my
> > needs. Ive no idea whether it was originally working.
> > I presume <%=SQLstmt%> just executes each SQLstmt one by one.
> > My connection is workin perfect because im able to delete and records
but
> i
> > only have trouble when it comes to updating.
> >
> >
> > -----Original Message-----
> > From: Peter Rooney [mailto:peter.rooney@p...]
> > Sent: 15 April 2002 12:05
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> > What is the result of <%=SQLstmt%>?
> >
> > Make sure you comment out the Set rs = conn.Execute(SQLstmt)
> >
> > 'rs = conn.Execute(SQLstmt)
> >
> > so it doesn't try to execute the sql before you can see the results,
also
> I
> > assume you have set up your connection (conn)
> >
> >
> >
> >
> >
> >
> > -----Original Message-----
> > From: Sean Dillon [mailto:sdillon@b...]
> > Sent: 15 April 2002 11:48
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> >
> > Hi Peter,
> > I tried that but that also gives me the same error. Have you any ideas
on
> > what else might be wrong.
> > Sean
> >
> > -----Original Message-----
> > From: Peter Rooney [mailto:peter.rooney@p...]
> > Sent: 15 April 2002 11:44
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> > Sean,
> >
> > Just a stab in the dark here but in your If statement you have 'flag'
> being
> > called from a Request.Form and in your WHERE clause you have 'Recid'
being
> > called from a Request.QueryString, if you are submitting this via a form
> > then your 'Recid' value should also be a Request.Form value
> >
> > Peter
> >
> >
> >
> >
> > -----Original Message-----
> > From: sean dillon [mailto:sdillon@b...]
> > Sent: 15 April 2002 11:14
> > To: Access ASP
> > Subject: [access_asp] This error is wrecking my head
> >
> >
> >
> > I keep getting this error when i go to update a record:
> > "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> > [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
> > statement.
> > /moddata.asp, line 81"
> >
> > Line 81 is:
> > "Conn.Execute (SQLstmt)"
> > Ive been told it might be because i have used a reserved word in my
update
> > statement but ive checked my code for reserved words and cant seem to
find
> > any.
> > My update statement is as follows
> >
> > IF Len(TRIM(Request.Form("flag"))) = 0 THEN
> > SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
> > Request.QueryString("Recid")
> > %>
> > <HR>
> > SQL statement: <%=SQLstmt%>
> > <HR>
> > <%
> > Set rs = conn.Execute(SQLstmt)
> > IF NOT RS.EOF THEN
> > %>
> > Update<HR>
> > <FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
> > <B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
> > =rs("AssignedTo")%>"><BR>
> > <B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
> > VALUE="<%=rs("ManagerSignOff")%>"><BR>
> > <B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
> > VALUE="<%=rs("ReplytoCustomer")%>"><BR>
> > <B>FurtherActionRequired:</B> <INPUT TYPE="text"
> > NAME="FurtherActionRequired"
VALUE="<%=rs("FurtherActionRequired")%>"><BR>
> > <B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
> > VALUE="<%=rs("ActionRequired")%>"><BR>
> > <INPUT TYPE="hidden" NAME="flag" VALUE="2">
> > <INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
> > <INPUT TYPE="submit" VALUE="Update">
> > </FORM>
> > <%
> > rs.MoveNext
> > rs.Close
> > END IF
> > ELSEIF Request.Form("flag")="2" THEN
> > SQLstmt = "UPDATE QuerySheet SET "
> > SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
> > ("AssignedTo")) & "',"
> > SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
> > ("ManagerSignOff")) & "',"
> > SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
> > ("ReplytoCustomer")) & "',"
> > SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
> > ("FurtherActionRequired")) & "',"
> > SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
> > ("ActionRequired")) & "',"
> > SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
> > ("Recordid"))
> > %>
> > <HR>
> > SQL statement: <%=SQLstmt%>
> > <HR>
> > <%
> > Conn.Execute (SQLstmt)
> > Response.Write "Operation Complete<br><A
> > HREF=""moddata.asp"">Home</A>"
> >
> > Can anyone please help as its wrecking my head to find the problem??
> > ---
>
>
>
>
>
>
--
Virus scanned by edNET.
--
Virus scanned by edNET.
Message #16 by "Sean Dillon" <sdillon@b...> on Tue, 16 Apr 2002 09:56:07 +0100
|
|
Peter,
Im a fool, just after realising i commented out my "conn.execute(SQLstmt)"
statement. Its working perfect now.
Thanx for all your help.
Really appreciated it.
Sean
-----Original Message-----
From: Peter Rooney [mailto:peter.rooney@p...]
Sent: 16 April 2002 09:52
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Sean,
cut and paste this in and see if it makes a difference
SQLstmt = "UPDATE QuerySheet SET "
SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form("AssignedTo")) &
"',"
SQLstmt = SQLstmt & " ManagerSignOff='" &
TRIM(Request.Form("ManagerSignOff")) & "',"
SQLstmt = SQLstmt & " ReplytoCustomer='" &
TRIM(Request.Form("ReplytoCustomer")) & "',"
SQLstmt = SQLstmt & " FurtherActionRequired='" &
TRIM(Request.Form("FurtherActionRequired")) & "',"
SQLstmt = SQLstmt & " ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "'"
SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form("Recordid"))
Peter
-----Original Message-----
From: Sean Dillon [mailto:sdillon@b...]
Sent: 16 April 2002 09:18
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Peter,
I tried removing the comma but it doesn't make any difference. It still
seems like everything is working right but it wont update the database
Sean
-----Original Message-----
From: Peter Rooney [mailto:peter.rooney@p...]
Sent: 15 April 2002 17:23
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Hi Sean,
Sorry for the delay in replying I have been out of the office and just got
back. Try removing the comma after 'aa'
ActionRequired='aa',WHERE SheetNo=190
To
ActionRequired='aa'WHERE SheetNo=190
Peter
-----Original Message-----
From: Sean Dillon [mailto:sdillon@b...]
Sent: 15 April 2002 17:05
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Paul
Ive just found out i was executing the SQLstatements twice put it seems like
everything is working correct now put my database wont update.
I get this:
SQL statement: UPDATE QuerySheet SET
AssignedTo='aaa',ManagerSignOff='aaa',ReplytoCustomer='aa',FurtherActionRequ
ired='aa',ActionRequired='aa',WHERE SheetNo=190
----------------------------------------------------------------------------
----
Operation Complete
which indicates every thing is working right but when i check my database it
hasn't updated.
Got ne ideas what could be wrong?
Sean
-----Original Message-----
From: Paul Winter [mailto:p.winter@s...]
Sent: 15 April 2002 16:59
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Sean
I think you need to replace the , before the WHERE clause with a space.
i.e. you don't need the , in the ActionRequired line
e.g.
SQLstmt = SQLstmt & "ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "'"
rather than
SQLstmt = SQLstmt & "ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "',"
Possibly!
Paul
----- Original Message -----
From: "Sean Dillon" <sdillon@b...>
To: "Access ASP" <access_asp@p...>
Sent: Monday, April 15, 2002 4:47 PM
Subject: [access_asp] RE: This error is wrecking my head
> Paul,
> This is exactly what is displayed on the screen when i press the update
> button:
>
> 'SQL statement: UPDATE QuerySheet SET
>
AssignedTo='fsd',ManagerSignOff='dsfds',ReplytoCustomer='df',FurtherActionRe
> quired='df',ActionRequired='dfs',WHERE SheetNo=189
> --------------------------------------------------------------------------
--
> ----
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement.
>
> /moddata.asp, line 81
>
> Sean
>
> -----Original Message-----
> From: Paul Winter [mailto:p.winter@s...]
> Sent: 15 April 2002 16:15
> To: Access ASP
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> Sean
>
> The <%=SQLstmt%> will display your SQL statement on the screen. Then
> you'll be able to past the SQL into a new access query using the design
> wizard (sql). If this query works OK then you've eliminated the SQL as
being
> the problem and then you can look at other possibilities.
>
> Do your text fields allow zero-length strings? If not you will need to
add
> a "zero to null" function for the situation where there is an empty text
box
> on the form.
>
> HTH
>
> Paul
>
> ----- Original Message -----
> From: "Sean Dillon" <sdillon@b...>
> To: "Access ASP" <access_asp@p...>
> Sent: Monday, April 15, 2002 12:54 PM
> Subject: [access_asp] RE: This error is wrecking my head
>
>
> > This is a program which i found and i have just changed it to suit my
> > needs. Ive no idea whether it was originally working.
> > I presume <%=SQLstmt%> just executes each SQLstmt one by one.
> > My connection is workin perfect because im able to delete and records
but
> i
> > only have trouble when it comes to updating.
> >
> >
> > -----Original Message-----
> > From: Peter Rooney [mailto:peter.rooney@p...]
> > Sent: 15 April 2002 12:05
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> > What is the result of <%=SQLstmt%>?
> >
> > Make sure you comment out the Set rs = conn.Execute(SQLstmt)
> >
> > 'rs = conn.Execute(SQLstmt)
> >
> > so it doesn't try to execute the sql before you can see the results,
also
> I
> > assume you have set up your connection (conn)
> >
> >
> >
> >
> >
> >
> > -----Original Message-----
> > From: Sean Dillon [mailto:sdillon@b...]
> > Sent: 15 April 2002 11:48
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> >
> > Hi Peter,
> > I tried that but that also gives me the same error. Have you any ideas
on
> > what else might be wrong.
> > Sean
> >
> > -----Original Message-----
> > From: Peter Rooney [mailto:peter.rooney@p...]
> > Sent: 15 April 2002 11:44
> > To: Access ASP
> > Subject: [access_asp] RE: This error is wrecking my head
> >
> >
> > Sean,
> >
> > Just a stab in the dark here but in your If statement you have 'flag'
> being
> > called from a Request.Form and in your WHERE clause you have 'Recid'
being
> > called from a Request.QueryString, if you are submitting this via a form
> > then your 'Recid' value should also be a Request.Form value
> >
> > Peter
> >
> >
> >
> >
> > -----Original Message-----
> > From: sean dillon [mailto:sdillon@b...]
> > Sent: 15 April 2002 11:14
> > To: Access ASP
> > Subject: [access_asp] This error is wrecking my head
> >
> >
> >
> > I keep getting this error when i go to update a record:
> > "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> > [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
> > statement.
> > /moddata.asp, line 81"
> >
> > Line 81 is:
> > "Conn.Execute (SQLstmt)"
> > Ive been told it might be because i have used a reserved word in my
update
> > statement but ive checked my code for reserved words and cant seem to
find
> > any.
> > My update statement is as follows
> >
> > IF Len(TRIM(Request.Form("flag"))) = 0 THEN
> > SQLstmt = "SELECT * FROM QuerySheet WHERE SheetNo ="&
> > Request.QueryString("Recid")
> > %>
> > <HR>
> > SQL statement: <%=SQLstmt%>
> > <HR>
> > <%
> > Set rs = conn.Execute(SQLstmt)
> > IF NOT RS.EOF THEN
> > %>
> > Update<HR>
> > <FORM METHOD="post" ACTION="moddata.asp?Actionvar=update">
> > <B>AssignedTo:</B> <INPUT TYPE="text" NAME="AssignedTo" VALUE="<%
> > =rs("AssignedTo")%>"><BR>
> > <B>ManagerSignOff:</B> <INPUT TYPE="text" NAME="ManagerSignOff"
> > VALUE="<%=rs("ManagerSignOff")%>"><BR>
> > <B>ReplytoCustomer:</B> <INPUT TYPE="text" NAME="ReplytoCustomer"
> > VALUE="<%=rs("ReplytoCustomer")%>"><BR>
> > <B>FurtherActionRequired:</B> <INPUT TYPE="text"
> > NAME="FurtherActionRequired"
VALUE="<%=rs("FurtherActionRequired")%>"><BR>
> > <B>ActionRequired:</B> <INPUT TYPE="text" NAME="ActionRequired"
> > VALUE="<%=rs("ActionRequired")%>"><BR>
> > <INPUT TYPE="hidden" NAME="flag" VALUE="2">
> > <INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("SheetNo")%>">
> > <INPUT TYPE="submit" VALUE="Update">
> > </FORM>
> > <%
> > rs.MoveNext
> > rs.Close
> > END IF
> > ELSEIF Request.Form("flag")="2" THEN
> > SQLstmt = "UPDATE QuerySheet SET "
> > SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form
> > ("AssignedTo")) & "',"
> > SQLstmt = SQLstmt & "ManagerSignOff='" & TRIM(Request.Form
> > ("ManagerSignOff")) & "',"
> > SQLstmt = SQLstmt & "ReplytoCustomer='" & TRIM(Request.Form
> > ("ReplytoCustomer")) & "',"
> > SQLstmt = SQLstmt & "FurtherActionRequired='" & TRIM(Request.Form
> > ("FurtherActionRequired")) & "',"
> > SQLstmt = SQLstmt & "ActionRequired='" & TRIM(Request.Form
> > ("ActionRequired")) & "',"
> > SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form
> > ("Recordid"))
> > %>
> > <HR>
> > SQL statement: <%=SQLstmt%>
> > <HR>
> > <%
> > Conn.Execute (SQLstmt)
> > Response.Write "Operation Complete<br><A
> > HREF=""moddata.asp"">Home</A>"
> >
> > Can anyone please help as its wrecking my head to find the problem??
> > ---
>
>
>
>
>
>
--
Virus scanned by edNET.
--
Virus scanned by edNET.
Message #17 by "Peter Rooney" <peter.rooney@p...> on Tue, 16 Apr 2002 10:06:18 +0100
|
|
Don't worry I have done the same many times, Glad you sorted it out
Peter
-----Original Message-----
From: Sean Dillon [mailto:sdillon@b...]
Sent: 16 April 2002 09:56
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Peter,
Im a fool, just after realising i commented out my "conn.execute(SQLstmt)"
statement. Its working perfect now.
Thanx for all your help.
Really appreciated it.
Sean
-----Original Message-----
From: Peter Rooney [mailto:peter.rooney@p...]
Sent: 16 April 2002 09:52
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Sean,
cut and paste this in and see if it makes a difference
SQLstmt = "UPDATE QuerySheet SET "
SQLstmt = SQLstmt & "AssignedTo='" & TRIM(Request.Form("AssignedTo")) &
"',"
SQLstmt = SQLstmt & " ManagerSignOff='" &
TRIM(Request.Form("ManagerSignOff")) & "',"
SQLstmt = SQLstmt & " ReplytoCustomer='" &
TRIM(Request.Form("ReplytoCustomer")) & "',"
SQLstmt = SQLstmt & " FurtherActionRequired='" &
TRIM(Request.Form("FurtherActionRequired")) & "',"
SQLstmt = SQLstmt & " ActionRequired='" &
TRIM(Request.Form("ActionRequired")) & "'"
SQLstmt = SQLstmt & " WHERE SheetNo=" & TRIM(Request.Form("Recordid"))
Peter
-----Original Message-----
From: Sean Dillon [mailto:sdillon@b...]
Sent: 16 April 2002 09:18
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Peter,
I tried removing the comma but it doesn't make any difference. It still
seems like everything is working right but it wont update the database
Sean
-----Original Message-----
From: Peter Rooney [mailto:peter.rooney@p...]
Sent: 15 April 2002 17:23
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Hi Sean,
Sorry for the delay in replying I have been out of the office and just got
back. Try removing the comma after 'aa'
ActionRequired='aa',WHERE SheetNo=190
To
ActionRequired='aa'WHERE SheetNo=190
Peter
-----Original Message-----
From: Sean Dillon [mailto:sdillon@b...]
Sent: 15 April 2002 17:05
To: Access ASP
Subject: [access_asp] RE: This error is wrecking my head
Paul
Ive just found out i was executing the SQLstatements twice put it seems like
everything is working correct now put my database wont update.
I get this:
SQL statement: UPDATE QuerySheet SET
AssignedTo='aaa',ManagerSignOff='aaa',Repl | |
View Cart
