Forum Moderators: open
Does anyone have any idea what I am doing wrong?
Below is my .asp code.
<%
set conn=Server.CreatePbject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "data/login2.mdb"
sql="UPDATE member SET "
sql=sql & "userid='" & Request.Form("userid") & "',"
sql=sql & "password='" & Request.Form("password") & "',"
on error resume next
conn.Execute sql
if err<>0 then
response.write("No update permissions!")
else
response.write("Record " & cid & " was updated!")
end if
end if
conn.close
%>
Any help would be greatly appreciated
Then you'll be able to debug your code easier.
Microsoft VBScript compilation error '800a03ea'
This is now the error I am receiving
Syntax error
/demo_add.asp, line 12
connection.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0"\;Data Source=" &_
Here is my Code:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<%@ language="VBScript" %>
<html>
<head>
<title>first asp</title>
</head>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
connection.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0"\;Data Source=" &_
server.MapPath("data/login2.mdb")
connection.Open
sql="UPDATE member SET "
sql=sql & "userid='" & Request.Form("userid") & "',"
sql=sql & "password='" & Request.Form("password") & "'"
response.write(sql)
response.end
on error resume next
conn.Execute sql
if err<>0 then
response.write("No update permissions!")
else
response.write("Record " & cid & " was updated!")
end if
conn.close
%>
</body>
</html>
set conn=Server.CreateObject("ADODB.Connection")
connection.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0"\;Data Source=" &_
server.MapPath("data/login2.mdb")
connection.Open
You created an object called conn and then set a property and attempted to fire a method for an object that you did not create. That's going to generate an error.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<%@ language="VBScript" %>
<html>
<head>
<title>first asp</title>
</head>
<body>
<%
Set connection = Server.CreateObject("ADODB.Connection")
connection.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
server.MapPath("data/login2.mdb")
connection.Open
sql="UPDATE member SET"
sql=sql & "userid='" & Request.Form("userid") & "',"
sql=sql & "password='" & Request.Form("password") & "'"
on error resume next
conn.Execute sql
if err<>0 then
response.write("No update permissions!")
else
response.write("Record " & cid & " was updated!")
end if
conn.close
%>
</body>
</html>
if you response.write(sql), you'll see that your statement actually looks like:
UPDATE member SETuserid='YourUserIDValue',password='YourPasswordValue'
So, do this instead:
sql="UPDATE member SET "
sql=sql & "userid='" & Request.Form("userid") & "', "
sql=sql & "password='" & Request.Form("password") & "'"
Note the added spaces at the end of the first 2 lines.
Added: instead of response.write("No update permissions"), do:
Response.Write(Err.Description)
This will show you the exact problem you're having while debugging.
Syntax error in UPDATE statement.
Here is my code:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<%@ language="VBScript" %>
<html>
<head>
<title>first asp</title>
</head>
<body>
<%
Set connection = Server.CreateObject("ADODB.Connection")
connection.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
server.MapPath("data/login2.mdb")
connection.Open
sql="UPDATE member SET "
sql=sql & "username='" & Request.Form("username") & "',"
sql=sql & "password='" & Request.Form("password") & "'"
on error resume next
connection.Execute sql
if err<>0 then
Response.Write(Err.Description)
else
response.write("Record was updated!")
end if
connection.close
%>
</body>
</html>
Another thing I was wondering, should I be doing an update or an insert into command, or does it not make a difference?
Again, thanks for your help
An INSERT sql statement inserts one or more new rows into the database.
To check your sql, response.write it out as mentioned above, then create a new query in your Access database and paste the sql into it and test.
You're using UPDATE sql syntax in your INSERT statement. Change it to:
INSERT INTO member (username, password)
VALUES ('lisa', 'momma')
BTW you should also do a check or two on your passed Request.Form values. At the moment if someone puts a ' in the username or password field it will screw up your sql. You can protect against this by replacing a single apostrophe with two apostrophes:
strUsername = Request.Form("username")
strUsername = Replace(strUsername,"'","''")
etc
You may also want to check that the username and password are not blank, and/or are a minimum number of characters.
HTH
<%
Set connection = Server.CreateObject("ADODB.Connection")
connection.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
server.MapPath("data\login2.mdb")
connection.Open
sql="INSERT INTO member (username, password)"
sql=sql & "VALUES"
sql=sql & "('" & Request.Form("username") & "',"
sql=sql & "'" & Request.Form("password") & "')"
response.write(sql)
on error resume next
connection.Execute sql
if err<>0 then
Response.Write(Err.Description)
else
response.write("Record was updated!")
end if
connection.close
%>
</body>
</html>
This is my error:
INSERT INTO member (username, password)VALUES('try','again')Syntax error in INSERT INTO statement.
I just dont understand what is happening. I see from my sql that my form is passing the data I inputed. My syntax is telling it to insert into member table in the username and password columns. I am so frustrated. I think the hardest part is I do not understand the "syntax" of the code. ie-sql=sql, "'", etc
I have a feeling we are close to getting this figured out. I hope so!
If that's what the problem is, you should be all set after making that change.
Let us know?
If you can't rename a field, it's possible to use reserved words by putting them in square brackets. eg change your sql to:
sql="INSERT INTO member (username, [password])"
...
