|
 |
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
|
 |
|
|
 |

August 13th, 2003, 12:50 PM
|
Registered User
|
|
Join Date: Aug 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Help! Cannot update records
Im new to asp so i found some code on the net and just changed it around a bit. I would like to be able to add,delete, and update records. So far only the add and delete work. Any assistance would be much appreciated.
Here comes the code:
<%
Set rs = conn.Execute(SQLstmt)
IF NOT RS.EOF THEN
%>
UPDATE
<FORM METHOD="post" ACTION="jobs.asp?Actionvar=UPDATE">
<B>Client</B> <INPUT TYPE="text" NAME="Client" VALUE="<%=rs("Client")%>"><BR>
<B>Dates</B> <INPUT TYPE="text" NAME="Dates" VALUE="<%=rs("Dates")%>"><BR>
<B>Phone Number</B> <INPUT TYPE="text" NAME="Employee" VALUE="<%=rs("Employee")%>"><BR>
<B>TimeStart</B> <INPUT TYPE="text" NAME="TimeStart" VALUE="<%=rs("TimeStart")%>"><BR>
<B>TimeFinished</B> <INPUT TYPE="text" NAME="TimeFinished" VALUE="<%=rs("TimeFinished")%>"><BR>
<B>Parts</B> <INPUT TYPE="text" NAME="Parts" VALUE="<%=rs("Parts")%>"><BR>
<B>Details</B> <INPUT TYPE="text" NAME="Details" VALUE="<%=rs("Details")%>"><BR>
<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("ID")%>">
<INPUT TYPE="submit" VALUE="UPDATE">
</FORM>
<%
rs.MoveNext
rs.Close
END IF
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "UPDATE jobs SET "
SQLstmt = (SQLstmt) & "client='" & TRIM(Request.Form("client")) & "'"
SQLstmt = (SQLstmt) & "Dates= '" & TRIM(Request.Form("Dates")) & "' "
SQLstmt = (SQLstmt) & "Employee='" & TRIM(Request.Form("Employee")) & "'"
SQLstmt = (SQLstmt) & "TimeStart= '" & TRIM(Request.Form("TimeStart")) & "' "
SQLstmt = (SQLstmt) & "TimeFinished= '" & TRIM(Request.Form("TimeFinished")) & "' "
SQLstmt = (SQLstmt) & "Parts='" & TRIM(Request.Form("Parts")) & "'"
SQLstmt = (SQLstmt) & "Details= '" & TRIM(Request.Form("Details")) & "'"
SQLstmt = (SQLstmt) & " WHERE ID=" & TRIM(Request.Form("Recordid"))
%>
SQL Partsment: <%=SQLstmt%>
<%
Conn.Execute(SQLstmt)
Response.Write "Operation Complete<br><A HREF=""jobs.asp"">Home</A>"
END IF
|

August 13th, 2003, 02:43 PM
|
Authorized User
|
|
Join Date: Aug 2003
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
what kind of error are you getting? can you please post the error message..
Chintu
|

August 13th, 2003, 03:12 PM
|
Registered User
|
|
Join Date: Aug 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
sorry, my bad. Here is the error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''City='Spart',State='Nj' ZipCode='ddd' WHERE ID=22'.
/test/clients.asp, line 87
|

August 13th, 2003, 10:05 PM
|
Authorized User
|
|
Join Date: Jun 2003
Location: Quezon City, MM, Philippines.
Posts: 97
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Hi,
Looking at error statement, it appears you only have to put a extra single-quote (') in the ID= part of your SQL statement. What you have is WHERE ID=22' so you need to add the ' to make it WHERE ID='22'. You might alsoi want to remove the two single-quotes ('') before City='Spart'.
Also, just noticed it but the SQL parameters in your error code (City='Spart',State='Nj' ZipCode='ddd') are not present in the ASP code you showed us.
Cheers!
Marlon Villarama
Support Team
Web Burner Hosting
marlon@webburner.com
www.webburner.com
Toll-Free: 877-535-2876
|

August 14th, 2003, 08:24 AM
|
Registered User
|
|
Join Date: Aug 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
thank you for your replies. As planeswalk pointed out, im missing some code so let me start over with this.
here is the code:
<FORM METHOD="post" ACTION="clients1.asp?Actionvar=update">
<B>Company</B> <INPUT TYPE="text" NAME="Company" VALUE="<%=rs("Company")%>"><BR>
<B>Contact</B> <INPUT TYPE="text" NAME="Contact" VALUE="<%=rs("Contact")%>"><BR>
<B>Phone Number</B> <INPUT TYPE="text" NAME="PhoneNumber" VALUE="<%=rs("PhoneNumber")%>"><BR>
<B>Street</B> <INPUT TYPE="text" NAME="Street" VALUE="<%=rs("Street")%>"><BR>
<B>City</B> <INPUT TYPE="text" NAME="City" VALUE="<%=rs("City")%>"><BR>
<B>State</B> <INPUT TYPE="text" NAME="State" VALUE="<%=rs("State")%>"><BR>
<B>ZipCode</B> <INPUT TYPE="text" NAME="ZipCode" VALUE="<%=rs("ZipCode")%>"><BR>
<INPUT TYPE="hidden" NAME="flag" VALUE="2">
<INPUT TYPE="hidden" NAME="Recordid" VALUE="<%=rs("ID")%>">
<INPUT TYPE="submit" VALUE="Update">
</FORM>
<%
rs.MoveNext
rs.Close
END IF
ELSEIF Request.Form("flag")="2" THEN
SQLstmt = "UPDATE customers SET "
SQLstmt = (SQLstmt) & "Company='" & TRIM(Request.Form("Company")) & "',"
SQLstmt = (SQLstmt) & "Contact='" & TRIM(Request.Form("Contact")) & "',"
SQLstmt = (SQLstmt) & "PhoneNumber='" & TRIM(Request.Form("PhoneNumber")) & "',"
SQLstmt = (SQLstmt) & "Street=" & TRIM(Request.Form("Steet")) & "'"
SQLstmt = (SQLstmt) & "City='" & TRIM(Request.Form("City")) & "',"
SQLstmt = (SQLstmt) & "State='" & TRIM(Request.Form("State")) & "' "
SQLstmt = (SQLstmt) & "ZipCode='" & TRIM(Request.Form("ZipCode")) & "' "
SQLstmt = (SQLstmt) & " WHERE ID='" & TRIM(Request.Form("Recordid"))
%>
SQL statement: <%=SQLstmt%>
<%
Conn.Execute(SQLstmt)
Response.Write "Operation Complete<br><A HREF=""clients1.asp"">Home</A>"
END IF
Here is the error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''City='Spart',State='Nj' ZipCode='07871' WHERE ID='22'.
/test/clients.asp, line 87
|

August 14th, 2003, 09:05 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
|
|
You need to separate all fields with a comma. The code you posted has no commas between Street and City, nor between State and ZipCode. Also you don't provide closing ' after RecordId (are you sure you need ' around this, isn't your id numeric?)
If you still get problems, show us the output from this line: SQL statement: <%=SQLstmt%>
hth
Phil
|

August 14th, 2003, 01:54 PM
|
Registered User
|
|
Join Date: Aug 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
thank you for the quick responses. It turned out to be a couple things wrong.
1. the sql statements needed to be seperated with a comma
2. spelling error (type streets was steet)
3. database was write protected
what can i say..im a n00b, but im learning.
Thanks again for all the help.
|
Thread Tools |
Search this Thread |
|
|
Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
 |