Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 13th, 2003, 12:50 PM
Registered User
 
Join Date: Aug 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mcalder Send a message via Yahoo to mcalder
Default 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

Reply With Quote
  #2 (permalink)  
Old August 13th, 2003, 02:43 PM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

what kind of error are you getting? can you please post the error message..

Chintu

Reply With Quote
  #3 (permalink)  
Old August 13th, 2003, 03:12 PM
Registered User
 
Join Date: Aug 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mcalder Send a message via Yahoo to mcalder
Default

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


Reply With Quote
  #4 (permalink)  
Old 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
Send a message via MSN to planeswalk
Default

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
Reply With Quote
  #5 (permalink)  
Old August 14th, 2003, 08:24 AM
Registered User
 
Join Date: Aug 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mcalder Send a message via Yahoo to mcalder
Default

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


Reply With Quote
  #6 (permalink)  
Old 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
Default

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
Reply With Quote
  #7 (permalink)  
Old August 14th, 2003, 01:54 PM
Registered User
 
Join Date: Aug 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mcalder Send a message via Yahoo to mcalder
Default

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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
UpDate Records on a Form ManFriday Access 2 September 8th, 2007 04:21 AM
Update Records bspeck Dreamweaver (all versions) 5 October 5th, 2004 03:05 PM
UPDATE RECORDS!!! a_pathak BOOK: Beginning ASP 3.0 1 March 3rd, 2004 04:00 AM
update records error damnnono_86 Access 1 January 28th, 2004 03:22 PM
update records by the loop mateenmohd SQL Server 2000 2 July 16th, 2003 06:38 AM



All times are GMT -4. The time now is 02:36 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.