What is wrong with this UPDATE code

minhas4all · 09-14-2005, 06:34 AM

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

neocool00 · 09-14-2005, 01:52 PM

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 (http://www.codingforums.com/showthread.php?t=9843), this will prevent errors if someone uses single quotes in your input box and also will prevent sql injection attacks.

minhas4all · 09-14-2005, 07:07 PM

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 (http://www.codingforums.com/showthread.php?t=9843), 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

minhas4all · 09-15-2005, 04:50 PM

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

neocool00 · 09-15-2005, 05:39 PM

@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.

minhas4all · 09-15-2005, 06:22 PM

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 ???????

neocool00 · 09-16-2005, 02:22 PM

But you are talking about two different tables. Your select statement is pointing to users whereas your update statement is pointing to login.

minhas4all · 09-16-2005, 02:42 PM

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