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

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

<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

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

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