Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Sep 2005
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What is wrong with this UPDATE code

    hello to all
    i just want to update table data using form and then on submitting it goes to update page for changing and again on submitting it should update that field but it didn't
    and giving the error message
    Incorrect syntax near the keyword 'WHERE'
    here is my form code
    Code:
    <html>
    <body>
    <%
    Set Conn = Server.CreateObject("ADODB.Connection")
    conn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = medicalrecords; User Id = sa; Password="
    set rs=Server.CreateObject("ADODB.Recordset")
    rs.open "SELECT * FROM users1",conn
    %>
    <h2>List Update</h2>
    <table border="1" width="39%">
    <tr> <%
    for each x in rs.Fields
      response.write("<th>" & ucase(x.name) & "</th>")
    next %>
      </tr>
      <% do until rs.EOF %>
      <tr>
        <form method="post" action="update.asp">
          <%
    for each x in rs.Fields
      if lcase(x.name)="uname" then%>
          <td>
            <input type="submit" name="uname" value="<%=x.value%>"  >
          </td>
    	  <% else %>
          <td><%Response.Write(x.value)%></td>
          <%end if
    	next %>
        </form>
        <%rs.MoveNext%>
      </tr> <%
    loop %>
    </table>
    </body>
    </html>
    and when i clink any of name in name field it goes to update page showing the all details now i have to make any changes and submit and it shows the error
    here is my other code
    Code:
    <html>
    <body><h2>Update Record</h2>
    <%
    Dim cname
    Set Conn = Server.CreateObject("ADODB.Connection")
    conn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = medicalrecords; User Id = sa; Password="
    cname=Request.Form("uname")
    if Request.form("pword")="" then
      set rs=Server.CreateObject("ADODB.Recordset")
      rs.open "SELECT * FROM users1 WHERE uname='" & cname & "'",conn
      %>
      <form method="post" action="update.asp">
      <table>
      <%for each x in rs.Fields%>
      <tr><td><%=x.name%></td>
      <td><%
      if lcase(x.name)="pword" then %>
      <td><input  type="password" name="<%=x.name%>" value="<%=x.value%>"></td>
      <% else %>
      <td><input name="<%=x.name%>" value="<%=x.value%>"></td>
      <%end if %>
      <%next%>
      </tr>
      </table>
      <br /><br />
      <input type="submit" value="Update record">
      </form>
      <%
    else
      sql="UPDATE users1 SET "
      sql=sql & "uname='" & Request.Form("uname") & "',"
      sql=sql & "pword='" & Request.Form("pword") & "',"
      sql=sql & "phint='" & Request.Form("phint") & "',"
      sql=sql & " WHERE uname='" & cname & "'"
      on error resume next
      conn.Execute sql
      if err<>0 then
    	response.write err.description
    	  else 
        response.write("Record " & cname & " was updated!")
      end if 
    end if
    conn.close
    %>
    </body>
    </html>
    so please you all tell me that where is problem in my code
    i m not sure that i explain my problem well or not
    thanks in advance

  • #2
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    On this line: sql=sql & "phint='" & Request.Form("phint") & "',"
    Remove the trailing ,. One other thing I would like to point out, if you change the uname in the form, it's going to mess your data up.

    For example, let's say on the first page, you have the following list of unames:
    Todd
    Mary
    Cody
    Jane

    And I click on "Todd" because I want to change his name to something else. So on the update page I now see this.

    Todd - editable field "uname"
    - editable field "pword" (it's blank b/c it's the first time)
    - editable field "phint" (same as pword).

    Now, what happens when I change Todd's name to Barry? Your update statement is going to look like this:

    sql="UPDATE users1 SET "
    sql=sql & "uname='Barry',"
    sql=sql & "pword='MyPassword',"
    sql=sql & "phint='What is my password?',"
    sql=sql & " WHERE uname='Barry'"

    This is not going to change Todd's name to Barry b/c you have no way of identifying Todd's record in the database. This is where an identity column comes in handy. I would suggest adding another field to your database, set it as the primary key and make it an identity (autonumber in Access). Then you can use it to identity the record you want to update and don't have to worry about someone chaning your primary key value and not know which record to update. Also, read the sticky thread on single quotes (Single Quotes give me a syntax error!, this will prevent errors if someone uses single quotes in your input box and also will prevent sql injection attacks.

  • #3
    New Coder
    Join Date
    Sep 2005
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by neocool00
    On this line: sql=sql & "phint='" & Request.Form("phint") & "',"
    Remove the trailing ,. One other thing I would like to point out, if you change the uname in the form, it's going to mess your data up.

    For example, let's say on the first page, you have the following list of unames:
    Todd
    Mary
    Cody
    Jane

    And I click on "Todd" because I want to change his name to something else. So on the update page I now see this.

    Todd - editable field "uname"
    - editable field "pword" (it's blank b/c it's the first time)
    - editable field "phint" (same as pword).

    Now, what happens when I change Todd's name to Barry? Your update statement is going to look like this:

    sql="UPDATE users1 SET "
    sql=sql & "uname='Barry',"
    sql=sql & "pword='MyPassword',"
    sql=sql & "phint='What is my password?',"
    sql=sql & " WHERE uname='Barry'"

    This is not going to change Todd's name to Barry b/c you have no way of identifying Todd's record in the database. This is where an identity column comes in handy. I would suggest adding another field to your database, set it as the primary key and make it an identity (autonumber in Access). Then you can use it to identity the record you want to update and don't have to worry about someone chaning your primary key value and not know which record to update. Also, read the sticky thread on single quotes (Single Quotes give me a syntax error!, this will prevent errors if someone uses single quotes in your input box and also will prevent sql injection attacks.
    thanks for your detailed reply
    one thing that i already have primary key that is uname but as you said the problem with uname record now let me try with your proposal
    thanks once again

  • #4
    New Coder
    Join Date
    Sep 2005
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hello neocool00
    as per you i make a new table with auto number field and also put "," but still it has problems showing
    Invalid column name 'fullname'.
    while fullname column exists
    here is my code

    Code:
    <html>
    <body><h2>Update Record</h2>
    <%
    Dim uid
    Set Conn = Server.CreateObject("ADODB.Connection")
    conn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = medicalrecords; User Id = sa; Password=mohammed"
    uid=Request.Form("userid")
    if Request.form("username")="" then
      set rs=Server.CreateObject("ADODB.Recordset")
      rs.open "SELECT * FROM users WHERE userid='" & uid & "'",conn
      %>
      <form method="post" action="update.asp">
      <table>
      <%for each x in rs.Fields%>
      <tr>
       <td>
      <%=x.name%></td>
      <td><input name="<%=x.name%>" value="<%=x.value%>"></td>
      <%next%>
      </tr>
      </table>
      <br /><br />
      <input type="submit" value="Update record">
      </form>
      <%
    else
      sql="UPDATE users SET "
      sql=sql & "username='" & Request.form("username") & "',"
      sql=sql & "password='" & Request.Form("password") & "',"
      sql=sql & "fullname='" & Request.Form("fullname") & "'"
      sql=sql & " WHERE userid='" & uid & "'"
      on error resume next
      conn.Execute sql
      if err<>0 then
        	response.write err.description
    	  else 
        response.write("Record " & cname & " was updated!")
      end if 
    end if
    conn.close
    %>
    </body>
    </html>
    so please help me
    thanks
    Last edited by minhas4all; 09-16-2005 at 03:27 PM.

  • #5
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    @minhas4all,
    If you are getting "Invalid column name 'fullname'" as an error message, it only has one meaning: That column does not exist in the table you are selecting/editing. Are you sure that "fullname" is a column in the "login" table? I don't think Access is case sensitive when it comes to column names, but spelling does count. Double and triple check and let us know.

  • #6
    New Coder
    Join Date
    Sep 2005
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hi neocool00
    thanks for your reply but as i wrote earlier
    that table field is exists
    so here is my whole table fields

    Code:
    SELECT [UserID], [UserName], [Password], [FullName] FROM [medicalrecords].[dbo].[users]
    this i select from query analyzer
    so you can see that fullname exists
    so ???????

  • #7
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    But you are talking about two different tables. Your select statement is pointing to users whereas your update statement is pointing to login.

  • #8
    New Coder
    Join Date
    Sep 2005
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by neocool00
    But you are talking about two different tables. Your select statement is pointing to users whereas your update statement is pointing to login.
    you are right
    i just before this scan my page and i found the fault so its
    now ok thanks for all your suggestions


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •