p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Classic ASP Databases (http://p2p.wrox.com/forumdisplay.php?f=62)
-   -   Help! Cannot update records (http://p2p.wrox.com/showthread.php?t=2762)

mcalder August 13th, 2003 12:50 PM

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


chintu August 13th, 2003 02:43 PM

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

Chintu


mcalder August 13th, 2003 03:12 PM

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



planeswalk August 13th, 2003 10:05 PM

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

mcalder August 14th, 2003 08:24 AM

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



pgtips August 14th, 2003 09:05 AM

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

mcalder August 14th, 2003 01:54 PM

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.



All times are GMT -4. The time now is 10:23 PM.

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