Error?

[wweidner]

Does anyone know why this would be giving me a runtime error? This is the line that it is saying has the problem.

Set recordSet=cn.Execute(sqlStatement),1,2

If you need to know more of the code just let me know and i will gladly provide it.

[digitalv]

Need a lot more info ... what is the SQL statement, the full error message and text, etc.

[wweidner]

This is the statement

sqlStatement = "INSERT INTO users (user_name, password, first_name, last_name, address1, address2, city, state, zip, phone, email, corp_name, corp_address, corp_city, corp_state, corp_zip, corp_phone, corp_email, mailings) VALUES ('" &user_name& "', '" &password& "', '" &first_name& "', '" &last_name& "', '" &address1& "', '" &address2& "', '" &city& "', '" &state& "', '" &zip& "', '" &phone& "', '" &email& "', '" &corp_name& "', '" &corp_address& "', '" &corp_city& "', '" &corp_state& "', '" &corp_zip& "', '" &corp_phone& "', '" &corp_email& "', '" &mailings& "')"
Set recordSet=cn.Execute(sqlStatement),1,2
cn.Close
Set cn=Nothing

[wweidner]

The full error is

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/register_action.asp, line 30

Set recordSet=cn.Execute(sqlStatement),1,2

[digitalv]

Do any of those fields have an apostrophe or quotation marks in them? That could be closing the field before you really want it closed.

Also for an INSERT statement like that you don't need to do "set recordset", the ,1,2 stuff, or waste memory creating a string called "sqlStatement".

just do this alone on a line:

cn.execute(YourInsertStatement)

See if that works - if you still get an error the problem is with a misplaced apostrophe or quotation mark.

[wweidner]

I have no idea right now what is going on.. I looke at all of the code to the insert statement and to me it looked like everything was where it was suppose to be. I also double checked all the spellings (compared to datebase then the rest of the code). Now it is telling me that i have a synax error in the INSERT statement...don't figure. Here is what it is telling me.

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/register_action.asp, line 29

Line 29 is the whole statement. I am goign to keep looking. Just letting you know how that worked.

[digitalv]

Ok, try this ... go ahead and save your insert statement as a string, but before you do cn.execute(string) response.write the string then paste it here.

That way I can see exactly what it's trying to insert.

[wweidner]

'wweidner','imne','Wanda','Weidner','','','','PA','','','[e-mail]','Web Star Design','','','PA','','','','Yes'

I this what you are talking about?

[digitalv]

Is your last "Yes" a Yes/No field? It's been a while since I used access, but if I remember correctly I dont think you can actuallyput "yes" in as text doesn't it have to be a 1 or zero (without the apostrophes around it)?

That might be your syntax error... maybe someone can correct me if I'm wrong or look it up though.

[john_k]

Set recordSet=cn.Execute(sqlStatement),1,2

Your original error was occurring because your second and third parameters are outside of your closing ")". It should look like this:

Set recordSet=cn.Execute(sqlStatement,1,2)

As for the SQL you posted in msg#8, no, that isn't enough. You need to capture the entire SQL statement from "INSERT" through the closing ")" after the values. (what you did post looks okay)

[wweidner]

First thing i changed the code to this
Set recordSet=cn.Execute(sqlStatement,1,2)....like said and it tells me this
Microsoft JET Database Engine error '80040e14'

Syntax error in FROM clause.

/register_action.asp, line 30

Yes the "yes" is that way by radio buttons...and it is a yes and no feild in the database.

Is this what you need?
user_name, password, first_name, last_name, address1, address2, city, state, zip, phone, email, corp_name, corp_address, corp_city, corp_state, corp_zip, corp_phone, corp_email, mailings, 'wweidner', 'mine', 'Wanda', 'Weidner', '', '', '', 'PA', '', '', '[e-mail]', 'Web Star Design', '', '', 'PA', '', '', '', 'Yes'

[digitalv]

Just out of curiosity, are you deliberately leaving out information that you don't want us to see? Posting "pieces" of your code makes it nearly impossible to troubleshoot. It would help to see everything instead of cutting and pasting portions of it.

Your most recent post said there was an error with a FROM clause, yet you have never posted any code with a FROM clause in it - how are we supposed to help you?

And again, there is no point in doing the Set Record thing on an insert statement. No data is being RETURNED to you so all you're doing is setting a null value. When doing an insert or update just use cn.execute(sqlstatement) and that's all you need. If you're selecting one value use:

stringname = cn.execute(sqlstatement)(0)

Only if you are running a SELECT statement where you're returning more than one value will you need to use the set recordset = stuff

[wweidner]

No i am not...that is all the code ....This is why i am having problems figureing it out myself

If you want to see all of the code for the whole page go to .....www.tsi-certifications.com
Click on session info then schedule then register
That is the page that is not working right, the register_action.asp is the one that has all the code.

Here is all the code for the register_action.asp page. I have alot of it blocked off.

<%@ language="vbscript" %>
<%
if request.form("submit") = "" then
response.redirect "register.asp"
end if
%>
<!-- #include file="includes/connection.inc" -->
<%
first_name = request.form("first_name")
last_name = request.form("last_name")
user_name = left(first_name, 1) & last_name
user_name = lcase(user_name)
password = request.form("password")
address1 = request.form("address1")
address2 = request.form("address2")
city = request.form("city")
state = request.form("state")
zip = request.form("zip")
phone = request.form("phone")
email = request.form("email")
corp_name = request.form("corp_name")
corp_address = request.form("corp_address")
corp_city = request.form("corp_city")
corp_state = request.form("corp_state")
corp_zip = request.form("corp_zip")
corp_phone = request.form("corp_phone")
corp_email = request.form("corp_email")
mailings = request.form("mailings")
response.write "user_name, password, first_name, last_name, address1, address2, city, state, zip, phone, email, corp_name, corp_address, corp_city, corp_state, corp_zip, corp_phone, corp_email, mailings, '" &user_name& "', '" &password& "', '" &first_name& "', '" &last_name& "', '" &address1& "', '" &address2& "', '" &city& "', '" &state& "', '" &zip& "', '" &phone& "', '" &email& "', '" &corp_name& "', '" &corp_address& "', '" &corp_city& "', '" &corp_state& "', '" &corp_zip& "', '" &corp_phone& "', '" &corp_email& "', '" &mailings& "'"

'sqlStatement = "INSERT INTO users (user_name, password, first_name, last_name, address1, address2, city, state, zip, phone, email, corp_name, corp_address, corp_city, corp_state, corp_zip, corp_phone, corp_email, mailings) VALUES ('" &user_name& "', '" &password& "', '" &first_name& "', '" &last_name& "', '" &address1& "', '" &address2& "', '" &city& "', '" &state& "', '" &zip& "', '" &phone& "', '" &email& "', '" &corp_name& "', '" &corp_address& "', '" &corp_city& "', '" &corp_state& "', '" &corp_zip& "', '" &corp_phone& "', '" &corp_email& "', '" &mailings& "')"
'Set recordSet=cn.Execute(sqlStatement,1,2)
'cn.Close
'Set cn=Nothing

'set fso = Server.CreateObject("Scripting.FileSystemObject")
'set countFile = fso.OpenTextFile(Server.MapPath("count.txt"))
'if not countFile.AtEndOfStream then
'full = countFile.Readline
' session("full") = full
' visitor = full mod 12
' if visitor = 0 then
' session("discount") = "true"
' else
' session("discount") = "false"
' end if
'end if
'countFile.close

'set fso = Server.CreateObject("Scripting.FileSystemObject")
'set countFile = fso.CreateTextFile(Server.MapPath("count.txt"))
'countFile.WriteLine full+1
'countFile.Close

'connection.Close
'set connection=Nothing
'session("user_name") = user_name
'session("password") = password
'session("name") = first_name & " " & last_name

'message_body = "Thank you for registering with TSI Certifications. Your user name is " & 'user_name & " and your password is " & password & "."
'Set NewMail = Server.CreateObject("CDONTS.NewMail")
'NewMail.To = "[e-mail] (TSI Certification)"
'NewMail.From = request.form("email") & " (" & request.form("name") & ")"
'NewMail.Cc = request.form("email") & " (" & request.form("name") & ")"
'NewMail.Subject = "Mail from the TSI-Certifications user login details"
'NewMail.Body = Body
'NewMail.Send
'Set NewMail=Nothing

'response.redirect "session.asp"
%>

[wweidner]

I have also changed some many things to try to get this to work....I am sorry if i came across a bit upset...i just have been trying to get this to work for the last 3 day now. Again I am sorry

[john_k]

You need to remove the current Response.Write, then uncomment the "sqlStatement = ..." line of code so that the SQL is built, and then follow that with

Response.Write sqlStatement
Response.End

What digitalv is getting at is that we need to see the entire SQL statement that is being executed as the error lies somewhere in there.

[digitalv]

Replace this:

response.write "user_name, password, first_name, last_name, address1, address2, city, state, zip, phone, email, corp_name, corp_address, corp_city, corp_state, corp_zip, corp_phone, corp_email, mailings, '" &user_name& "', '" &password& "', '" &first_name& "', '" &last_name& "', '" &address1& "', '" &address2& "', '" &city& "', '" &state& "', '" &zip& "', '" &phone& "', '" &email& "', '" &corp_name& "', '" &corp_address& "', '" &corp_city& "', '" &corp_state& "', '" &corp_zip& "', '" &corp_phone& "', '" &corp_email& "', '" &mailings& "'"

'sqlStatement = "INSERT INTO users (user_name, password, first_name, last_name, address1, address2, city, state, zip, phone, email, corp_name, corp_address, corp_city, corp_state, corp_zip, corp_phone, corp_email, mailings) VALUES ('" &user_name& "', '" &password& "', '" &first_name& "', '" &last_name& "', '" &address1& "', '" &address2& "', '" &city& "', '" &state& "', '" &zip& "', '" &phone& "', '" &email& "', '" &corp_name& "', '" &corp_address& "', '" &corp_city& "', '" &corp_state& "', '" &corp_zip& "', '" &corp_phone& "', '" &corp_email& "', '" &mailings& "')"
'Set recordSet=cn.Execute(sqlStatement,1,2)

With this:

sqlStatement = "INSERT INTO users (user_name,password,first_name,last_name,address1,address2,city,state,zip,phone,email,corp_name,corp_address,corp_city,corp_state,corp_zip,corp_phone,corp_email,mailings) VALUES ('" & user_name & "','" & password & "','" & first_name & "','" & last_name & "','" & address1 & "','" & address2 & "','" & city & "','" & state & "','" & zip & "','" & phone & "','" & email & "','" & corp_name & "','" & corp_address & "','" & corp_city & "','" & corp_state & "','" & corp_zip & "','" & corp_phone & "','" & corp_email & "','" & mailings & "')"

response.write sqlStatement

cn.Execute(sqlStatement)

If this produces a syntax error it's because one of the fields is wrong. Start by taking away the fields from the back, one at a time, until you find the problem. Take out mailings first, then corp_email, etc. I suspect the problem is with mailings, but not sure - make sure you take it out of the column names AND the values.

[john_k]

I forgot to add: because from your last posts, something is missing. The error points to a problem in a FROM clause, but the SQL you are posting is an INSERT statement that doesn't have a FROM clause.

[wweidner]

Is this it?

INSERT INTO users (user_name, password, first_name, last_name, address1, address2, city, state, zip, phone, email, corp_name, corp_address, corp_city, corp_state, corp_zip, corp_phone, corp_email, mailings) VALUES ('wweidner', 'mine', 'Wanda', 'Weidner', '', '', '', 'PA', '', '', '[e-mail]', 'Web Star Design', '', '', 'PA', '', '', '', 'Yes')

[john_k]

Is this it?

Yes. Now, don't change any other code except to remark out the Response.Write and Response.End statement. Then post the error message that occurs. (you have posted three different ones so far)

Also, before replying, try to execute that statement directly in Access if you can. You may get a more meaningful error message.

And lastly, the syntax of whay you posted looks okay. So I would take a closer look at the 'yes' going into the yes/no field. If you write a 1 (for yes) or 0 (for no). Try putting in a litteral value of 1 (without the apostrophes) instead of the 'yes' to see if that makes the error go away.

[wweidner]

Well iam back to the same problem that we started at...here it is

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/register_action.asp, line 33

and line 33 is

cn.Execute(sqlStatement)

This is after i have been taking things out and rerunning it. I guess i am not going to get it to work. I don't know what else to try and or do. but i thank you for all the help

[wweidner]

Another update...I have taken everything but the name, address, well fudge here is the code

first_name = request.form("first_name")
last_name = request.form("last_name")

address1 = request.form("address1")
address2 = request.form("address2")
city = request.form("city")
state = request.form("state")
zip = request.form("zip")
phone = request.form("phone")

corp_name = request.form("corp_name")
corp_address = request.form("corp_address")
corp_city = request.form("corp_city")
corp_state = request.form("corp_state")
corp_zip = request.form("corp_zip")
corp_phone = request.form("corp_phone")

sqlStatement = "INSERT INTO users ( first_name, last_name, address1, address2, city, state, zip, phone, corp_name, corp_address, corp_city, corp_state, corp_zip, corp_phone, ) VALUES ('" &first_name& "', '" &last_name& "', '" &address1& "', '" &address2& "', '" &city& "', '" &state& "', '" &zip& "', '" &phone& "', '" &corp_name& "', '" &corp_address& "', '" &corp_city& "', '" &corp_state& "', '" &corp_zip& "', '" &corp_phone& "')"

cn.Execute(sqlStatement)---> line 32 (right now)
Is the problem

[macrost]

Try this:
Set recordSet = cn.Execute(sqlStatement)

[duckhunter]

Try trapping the error and writing it out. A more detail error description is available from your connection object. You must resume next so it gets to the trap then close and exit operations if it hits it.

On Error Resume Next

cn.Execute(sqlStatement)

If cn.Errors.Count > 0 Then
If cn.Errors.Item(0).Number <> 0 Then
Response.write "Error: " & cn.Errors.Item(0).Description
cn.close
Response.End
End If
End If

[john_k]

sqlStatement = "INSERT INTO users ( first_name, last_name, address1, address2, city, state, zip, phone, corp_name, corp_address, corp_city, corp_state, corp_zip, corp_phone, ) VALUES ('" &first_name& "', '" &last_name& "', '" &address1& "', '" &address2& "', '" &city& "', '" &state& "', '" &zip& "', '" &phone& "', '" &corp_name& "', '" &corp_address& "', '" &corp_city& "', '" &corp_state& "', '" &corp_zip& "', '" &corp_phone& "')"

You have a comma after the field name corp_phone, and that is a syntax error.

Again, try and execute these directly in Access, or loop through the connection errors as suggested by duckhunter - you will (usually) get a more meaningful error message.

[wweidner]

Ok ...i think i'm preety sure where the problem is , by taking out and putting back. everything was working until i put this code back in ( ones marked)

first_name = request.form("first_name")
last_name = request.form("last_name")
-->user_name = left(first_name, 1) & last_name
-->user_name = lcase(user_name)
password = request.form("password")
address1 = request.form("address1")
address2 = request.form("address2")
city = request.form("city")
state = request.form("state")
zip = request.form("zip")
phone = request.form("phone")
email = request.form("email")
corp_name = request.form("corp_name")
corp_address = request.form("corp_address")
corp_city = request.form("corp_city")
corp_state = request.form("corp_state")
corp_zip = request.form("corp_zip")
corp_phone = request.form("corp_phone")
corp_email = request.form("corp_email")

sqlStatement = "INSERT INTO users (-->user_name, password--<, first_name, last_name, address1, address2, city, state, zip, phone, email, corp_name, corp_address, corp_city, corp_state, corp_zip, corp_phone,corp_email) VALUES (-->'" &user_name& "', '" &password& "'--<,'" &first_name& "', '" &last_name& "', '" &address1& "', '" &address2& "', '" &city& "', '" &state& "', '" &zip& "','" &phone& "','" &email& "', '" &corp_name& "', '" &corp_address& "', '" &corp_city& "', '" &corp_state& "', '" &corp_zip& "', '" &corp_phone& "', '" &corp_email& "')"

I am getting a syntax error...
Error: Syntax error in INSERT INTO statement.
Any ideas why.

[digitalv]

-->user_name = left(first_name, 1) & last_name
-->user_name = lcase(user_name)

Just a heads up, you can simplify this into one line by doing:

user_name = lcase(Left(first_name,1) & last_name)

Also if the username is ALWAYS going to be first initial/last name then you can save some space in the database and skip that field entirely. If the first and last name are stored in the DB you can dynamically build the username from that when someone logs in:

Username = request.form("username")
SELECT whatever FROM Users WHERE Left(first_name,1) = '" & Left(Username,1) & "' AND Last_name = '" & Right(Username,len(Username) - 1)) & "'"

Still not sure about the error, you blocked out the password field - was the problem with the Username or with the password or both?

[wweidner]

it was with both ...the user name and passwword....when i put all of that back in ...that is when it died

[john_k]

Again, do the Response.Write and Response.End.

The error is occurring because of something in that statement. You aren't posting it with your questions. Each time you change something, if you have another question to post, you should post

- The error message (or the problem if there is no message)
- The vbscript from the line on which the error occurs at least (surrounding code is always nice)
- If the error was in executing the SQL, then run your code again with the Response.Write and Response.End BEFORE the .Execute statement and include that actual SQL in your post.

I think that last bit is being lost on you. We need to see the VbScript that creates the SQL >>AND<< we need to see the actual SQL.

Having said all of that, I would guess that one of those two fields is an empty string.

Also, are you unable to run the actual SQL in Access?

[wweidner]

ok...i did the response.write and i made sure that all the fields have something in them....here it is

INSERT INTO users (user_name, password, first_name, last_name, address1, address2, city, state, zip, phone, email, corp_name, corp_address, corp_city, corp_state, corp_zip, corp_phone, corp_email) VALUES ('fweidner', 'mine','Fred', 'Weidner', '3456 My Street', 'suite 345', 'mine', 'PA', '26475','610-675-3567','[e-mail]', 'Web Star Design', '3456 My Street', 'mine', 'PA', '26475', '610-675-3567', '[e-mail]')Error: Syntax error in INSERT INTO statement.

here is the all of the code ( --> and --< is what i have added back in and then got the syntax error)

first_name = request.form("first_name")
last_name = request.form("last_name")
-->user_name = lcase(Left(first_name,1) & last_name)
-->password = request.form("password")
address1 = request.form("address1")
address2 = request.form("address2")
city = request.form("city")
state = request.form("state")
zip = request.form("zip")
phone = request.form("phone")
email = request.form("email")
corp_name = request.form("corp_name")
corp_address = request.form("corp_address")
corp_city = request.form("corp_city")
corp_state = request.form("corp_state")
corp_zip = request.form("corp_zip")
corp_phone = request.form("corp_phone")
corp_email = request.form("corp_email")

sqlStatement = "INSERT INTO users (-->user_name, password--<, first_name, last_name, address1, address2, city, state, zip, phone, email, corp_name, corp_address, corp_city, corp_state, corp_zip, corp_phone,corp_email) VALUES (-->'" &user_name& "', '" &password& "'--<,'" &first_name& "', '" &last_name& "', '" &address1& "', '" &address2& "', '" &city& "', '" &state& "', '" &zip& "','" &phone& "','" &email& "', '" &corp_name& "', '" &corp_address& "', '" &corp_city& "', '" &corp_state& "', '" &corp_zip& "', '" &corp_phone& "', '" &corp_email& "')"

On Error Resume Next
response.write sqlStatement
cn.Execute(sqlStatement)

If cn.Errors.Count > 0 Then
If cn.Errors.Item(0).Number <> 0 Then
Response.write "Error: " & cn.Errors.Item(0).Description
cn.close
Response.End
End If
End If
cn.Close
Set cn=Nothing

[john_k]

Okay, IF that statement works without the user_name and password fields, THEN

- What are the datatypes and lengths for those fields?
- Do they allow duplicates? If not, are these values already in another row?