Syntax Error in Insert Into Statement

[Carl]

I'm getting a syntax error in my insert into statement. I've retyped it over and over again. My current code.

sqlAddUser="INSERT INTO Users (username,password,email) VALUES ('"&username&"','"&password&"','"&email&"')"
conn.Execute(sqlAddUser)

I've also printed my SQL and everthing is fine. Username,password and email are all valid variables.

[whammy]

Can you post the SQL statement as it looks when you print it to the page?

[Mhtml]

There is nothing wrong with it, I just tested it by creating a connection and having the values like this.

<%
username = "Some"
password = "guy"
email = "someguy@guynet.net"
Set conn = Server.CreateObject("Adodb.connection")
Conn.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ="&server.MapPath("../db/odysseyUSERS.mdb")&";"
sqlAddUser="INSERT INTO Users (username,password,email) VALUES ('"&username&"','"&password&"','"&email&"')"
conn.Execute(sqlAddUser)
conn.close
Set conn = nothing%>


Could I see the rest of the code? Maybe the answer is in there! Also make sure that the variable are the same eg, username might be UserName .:)

[whammy]

Hmm... also try changing "password" to another variable.

I know on brinkster, that caused me a problem, although for some reason it doesn't seem to be a problem on my local machine.

It could be some setting in Access I'm not familiar with, but if I recall someone said password is a reserved word in access...(?!?).

Not to mention the code looks fine to me so it has to be something weird like that. :)

[Mhtml]

I have to agree, that code is almost identical to my user insert query with the exception of a few extra fields I have. I used it on my user database and it worked no problems so it must be the server.

I think Whammy is right about the word Password being a reserved word in access, I use PassW or Pword in my database related user scripts.

[Carl]

I've used password before on brinkster and in access. Why would it stop working now?

I'm still working on the code, but here it is so far.


<!-- #include file="dbcstring.inc" -->
<%
username=Request.Form("username")
password2=Request.Form("password")
email=Request.Form("email")

dim terror
terror=""

if username="" Then
terror="Please fill out the username field"
else if password="" Then
terror="Please fill out the password field"
else if email="" Then
terror="Please fill out the email feild"
else
sqlUser = "SELECT * FROM Users WHERE username='"&username&"'"
set recUser=conn.Execute(sqlUser)
if recUser.EOF Then
sqlAddUser="INSERT INTO Users (username,password,email) VALUES ('"&username&"','"&password2&"','"&email&"')"
conn.Execute(sqlAddUser)

sqlEmailUser="SELECT id,username FROM Users WHERE username='"&username&"'"
set recEmailUser=conn.Execute(sqlEmailUser)

Set oMail = Server.CreateObject("SMTPsvg.Mailer")
oMail.FromName = "CBoards Pro"
oMail.FromAddress= "carl@carlsoft.net"
oMail.RemoteHost = "mail.carlsoft.net"
oMail.AddRecipient "", email
oMail.Subject = "Validation Required"
oMail.BodyText = "Thank you for registering with CBoards Pro. To complete your registration, press the link below."&vbCrlf&vbCrlf&"<a href=""http://www.carlsoft.net/cboardspro/default.asp?page=register3&userid="&recEmailUser("id")&""">http://www.carlsoft.net/cboardspro/default.asp?page=register3&userid="&recEmailUser("id")&"</a>"&vbCrlF&vbCrlf&"Once again, thank you for registering with CBoards Pro"
If oMail.SendMail Then
Response.Write "Email sent successfully.<br>"
Else
Response.Write "Mail not sent, send failure. Error was " & oMail.Response & "<br>"
End If
oMail.ClearAllRecipients
oMail.ClearBodyText
Set oMail = Nothing
Response.Redirect="default.asp?page=register3"
else
terror="Sorry someone else has already chosen your username. Please try again"
end if
end if
end if
end if
conn.Close
%>
<form name="register" action="default.asp?page=doregister" method="post">
<table cellpadding="0" cellspacing="0" border="0" bgcolor="#000000" width="95%" align="center">
<tr>
<td>
<table width="100%" cellspacing="1" cellpadding="4">
<tr>
<td class="title" colspan="2"><B>Register - User Profile</B></td>
</tr>
<Tr>
<td colspan="2" class="cat"><b><%=terror%></b></td>
</Tr>
<Tr>
<td class="color1"><b>Username: </b></td>
<td class="color1"><input type="text" name="username"></td>
</Tr>
<tr>
<td class="color2"><b>Password: </b></td>
<td class="color2"><input type="password" name="password"></td>
</tr>
<tr>
<td class="color1"><b>Email: </b></td>
<td class="color1"><input type="text" name="email"></td>
</tr>
<tr>
<td class="cat" colspan="2"><input type="submit" value="Register"></td>
</tr>
</table>
</td>
</tr>
</table>
</form>


Also I have used password in other pieces in my code, so I don't think that is a problem

[Mhtml]

I can't see the problem anywhere, it must be your host.

PS: I'm creating a bulletin board similar to Cboards well at least I think it will be similar, I'm calling it QUICKboard it is a smaller version of my ASPbb bulletin board I'm also working on which will be better that vb3 in the way of features hopefully.

[Carl]

I'm using brinkster. X4 package too. This is version 3 of CBoards I'm making and I've built plenty of scripts on the server that do the same thing,

[whammy]

That "terror" variable scares me. ;)

[Carl]

All it does and print out an error message. Error is reserved, so I just slapped a t infront of it.

[Carl]

I emailed brinkster and they reset permissions for the directory. Stilll no go. Anyone know?

[whammy]

Since we have nothing further to work with (and you never did post your SQL statement printed to the page, as a starting point), how could we know anything?

:confused:

[Carl]

You wanted that? Why didn't you say so :)

INSERT INTO Users (username,password,email) VALUES ('test','test','carl@carlsoft.net')

[whammy]

Ok, that's fine except for the fact that you're still using "password", which like I said is a reserved word in Access, at least that's what I was told, and it caused problems with me on brinkster before - and when I changed the variable to "pass", it fixed the error - so whoever told me that must have been right! But I thought I already said that... perhaps try [password] in your insert into statement... ?

[Carl]

Oh man, you know what the problem was, the database had space after it. :rolleyes: And password is a reserved word.

[whammy]

The space shouldn't matter. ;)

[Carl]

Well it did oddly enough. :)

[whammy]

Oh, you mean somewhere where you called the database, there was a space? Or it should have been:

"INSERT INTO database() VALUES()"

?!?

I have never run into a problem with that (using SQL Server 200 at any rate)... but anyway, glad you got it working! :)

[Carl]

Yeah my field name was "password ".

[whammy]

That'll do it! ;)