Gossamer Forum
(Advanced Search)
for
Home : General : Databases and SQL :

Need help on update

Aug 5, 2003, 7:47 PM
gnehs
Novice (26 posts)
gnehs's avatar
Aug 5, 2003, 7:47 PM
Post #1 of 7
Views: 8157
Shortcut
Quote Reply
Need help on update
I keep getting a error i still cannot update. I dont know whats wrong??

UPDATE Login SET Staffid ='Staff C', Staffpass ='password', Department ='IT', Designation ='Lecturer', Room ='L544', Telephone ='62258930', Handphone ='98863246', Email ='staffc@nyp.com', WHERE Staffid = 'Staff C' Syntax error in UPDATE statement.No update permissions!





<html>
<body background="image/chatback.jpg" text="white">
<h2>Update Record</h2>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("Train.mdb"))

if Request.form("room")="" then
set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM Login WHERE Staffid='" & session("userid2") & "'",conn
%>
<form method="post" action="editpro.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 Login SET "
sql=sql & "Staffid ='" & Request.Form("Staffid") & "', "
sql=sql & "Staffpass ='" & Request.Form("Staffpass") & "', "
sql=sql & "Department ='" & Request.Form("Department") & "', "
sql=sql & "Designation ='" & Request.Form("Designation") & "', "
sql=sql & "Room ='" & Request.Form("Room") & "', "
sql=sql & "Telephone ='" & Request.Form("Telephone") & "', "
sql=sql & "Handphone ='" & Request.Form("Handphone") & "', "
sql=sql & "Email ='" & Request.Form("Email") & "', "

sql=sql & " WHERE Staffid = '" & session("userid2") & "'"


on error resume next
conn.Execute sql
if err<>0 then
response.write(sql &" "& err.description & "No update permissions!")
else
response.write("<font color='white'><br>Record Updated</font>")
end if
end if
conn.close
%>
</body>
</html>
Aug 5, 2003, 7:51 PM
Stealth
Veteran (17240 posts)
Stealth's avatar
Aug 5, 2003, 7:51 PM
Post #2 of 7
Views: 7953
Shortcut
Quote Reply
Re: [gnehs] Need help on update In reply to
Since I assume that StaffID is an INT column, you need to remove the single quotes around the parameter.

Example:

You have:

sql=sql & "Staffid ='" & Request.Form("Staffid") & "', "

Needs to be:

sql=sql & "Staffid =" & Request.Form("Staffid") &", "
========================================
Buh Bye!

Cheers,
Me
Aug 5, 2003, 7:55 PM
gnehs
Novice (26 posts)
gnehs's avatar
Aug 5, 2003, 7:55 PM
Post #3 of 7
Views: 7979
Shortcut
Quote Reply
Re: [Stealth] Need help on update In reply to
Hey thanks for ur help but i still get this error



UPDATE Login SET Staffid =Staff C, Staffpass ='password', Department ='IT', Designation ='Lecturer', Room ='L544', Telephone ='62258930', Handphone ='98863246', Email ='staffc@nyp.com', WHERE Staffid = 'Staff C' Syntax error (missing operator) in query expression 'Staff C'.No update permissions!
Aug 5, 2003, 7:57 PM
Stealth
Veteran (17240 posts)
Stealth's avatar
Aug 5, 2003, 7:57 PM
Post #4 of 7
Views: 7960
Shortcut
Quote Reply
Re: [gnehs] Need help on update In reply to
I gave you one example, you should've taken a little more time to use the logic I outlined throughout your query. You still have a set of single quotes for the StaffID INT field....

Staffid = 'Staff C'

single quotes need to be removed.
========================================
Buh Bye!

Cheers,
Me
Aug 5, 2003, 8:06 PM
gnehs
Novice (26 posts)
gnehs's avatar
Aug 5, 2003, 8:06 PM
Post #5 of 7
Views: 7953
Shortcut
Quote Reply
Re: [Stealth] Need help on update In reply to
okay

I change this

sql=sql & " WHERE Staffid = '" & session("userid2") & "',"

to

sql=sql & " WHERE Staffid = " & session("userid2") &""

still got this error

UPDATE Login SET Staffid =Staff C, Staffpass ='password', Department ='IT', Designation ='Lecturer', Room ='L544', Telephone ='62258930', Handphone ='98863246', Email ='staffc@nyp.com', WHERE Staffid = Staff C Syntax error (missing operator) in query expression 'Staff C'.No update permissions!
Aug 6, 2003, 8:33 AM
Paul
Veteran (19537 posts)
Aug 6, 2003, 8:33 AM
Post #6 of 7
Views: 7957
Shortcut
Quote Reply
Re: [gnehs] Need help on update In reply to
What does the final query look like after it is built?
Aug 6, 2003, 9:11 AM
gnehs
Novice (26 posts)
gnehs's avatar
Aug 6, 2003, 9:11 AM
Post #7 of 7
Views: 7838
Shortcut
Quote Reply
Re: [Paul] Need help on update In reply to
well Thanks paul and stealth.......i manage to solve it by a chance. I also dont know how i do it