INSERT INTO error, or is it? 1

madHatter1 · Aug 28, 2003

Hello

I am getting the following error message when I click on the 'Submit' button of a form:

city = request.form("city&quot

telephone = request.form("telephone&quot

message = request.form("message&quot

%>
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

/Valy.asp, line 275

This is where I have my INSERT INTO and wonder if this is where the error lies (it is the only INSERT INTO I have):

' this is where the information gets submitted to the db

DIM Conn,strConn,SQLstmt,RS
set Conn = server.createobject("adodb.connection&quot

strConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("/database/validation.mdb&quot

Conn.open strConn
SQLstmt = "INSERT INTO users (fullname,email,city,telephone,message)"

SQLstmt = SQLstmt & " VALUES ("

SQLstmt = SQLstmt & "'" & fullname & "',"
SQLstmt = SQLstmt & "'" & eMail & "',"
SQLstmt = SQLstmt & "'" & city & "',"
SQLstmt = SQLstmt & "'" & telephone & "',"
SQLstmt = SQLstmt & "'" & message & "',"
SQLstmt = SQLstmt & &quot

"

or, does the message at the beginning of the error message, that is, city = request.form("city&quot

telephone = request.form("telephone&quot

message = request.form("message&quot

%>
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

suggest the error lies elsewhere?

Many thanks for your views.

hatter

Veep · Aug 28, 2003

Debug your sql statement. See this thread thread333-641011 .

Also, I don't know that this is such a great idea without colons seperating the statements:
city = request.form("city&quot

telephone = request.form("telephone&quot

message = request.form("message&quot

%>

madHatter1 · Aug 28, 2003

Hello Veep

Thanks for your thread link.

I have inserted

response.write(SQLstmt)
response.end

and typed in a few things in the form (something in each field). Clicking on 'Submit', all the type remained in the field (except for the type in the messsage field - that disappeared).

This is what was generated:

city = request.form("city&quot

telephone = request.form("telephone&quot

message = request.form("message&quot

%> INSERT INTO users (fullname,email,city,telephone,message) VALUES ('Harry Evans','tttt@iaia.com','Acapulco','121212121212121','How are things out there!?',)

But I am not really any the wiser, except something might be wrong with message - but I'm not really sure.

hatter

dmhirsch · Aug 28, 2003

This looks funny...

Code:

city = request.form(&quot;city&quot;) telephone = request.form(&quot;telephone&quot;) message = request.form(&quot;message&quot;)%>

Are you missing the open script "<%" ? Also like Veep said, you need colons or line breaks between these commands. Post, if you will, that portion of the script...

madHatter1 · Aug 28, 2003

Thanks dmhirsch

This is the script which should insert the information from the fields into the database (where table has the name users and all the field names are the same as though below):

<%
' this is where the information finally gets submitted to the database

DIM Conn,strConn,SQLstmt,RS
set Conn = server.createobject("adodb.connection&quot

strConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("/database/validation.mdb&quot

Conn.open strConn

SQLstmt = "INSERT INTO users (fullname,email,city,telephone,message)"

SQLstmt = SQLstmt & " VALUES ("

SQLstmt = SQLstmt & "'" & fullname & "',"
SQLstmt = SQLstmt & "'" & eMail & "',"
SQLstmt = SQLstmt & "'" & city & "',"
SQLstmt = SQLstmt & "'" & telephone & "',"
SQLstmt = SQLstmt & "'" & message & "',"
SQLstmt = SQLstmt & &quot

"

response.write(SQLstmt)
response.end

Set RS = conn.execute(SQLstmt)
If err.number>0 then
response.write "VBScript Errors Occured:" & "<P>"
response.write "Error Number=" & err.number & "<P>"
response.write "Error Descr.=" & err.description & "<P>"
response.write "Help Context=" & err.helpcontext & "<P>"
response.write "Help Path=" & err.helppath & "<P>"
response.write "Native Error=" & err.nativeerror & "<P>"
response.write "Source=" & err.source & "<P>"
end if
IF conn.errors.count> 0 then
response.write "Database Errors Occured" & "<P>"
response.write SQLstmt & "<P>"
for counter= 0 to conn.errors.count
response.write "Error #" & conn.errors(counter).number & "<P>"
response.write "Error desc. -> " & conn.errors(counter).description & "<P>"
next
else
Response.Redirect("thankyouvisitor1.asp?updated=true&quot

end if
end if
end if
%>

Veep · Aug 28, 2003

The sql statement looks ok except you've got a comma trailing the last value 'How are things out there!?',)

You gotta clean up the rest of it though, get this stuff on seperate lines:
city = request.form("city&quot

telephone = request.form("telephone&quot

message = request.form("message&quot

and post a little more code so we can figure out why this: city = request.form("city&quot

telephone = request.form("telephone&quot

message = request.form("message&quot

%> is showing up in your insert statement.

madHatter1 · Aug 28, 2003

Hello to both of you again

OK, this is the bones of the code, minus some CDONTS and HTML stuff:

Incidentally, I am grateful to you both.

<%option explicit%>
<%DIM ErrorMsg, fullname,email,city,telephone,message%>
<%

const numFields = 5
dim errorArray()
redim preserve errorArray(numFields)

if request.form("isSubmitted&quot

= "yes" then
fullname = request.form("fullname&quot

city = request.form("city&quot

telephone = request.form("telephone&quot

email = request.form("email&quot

message = request.form("message&quot

ErrorMsg = ""
dim re
set re = New RegExp

'Full Name

re.Pattern = "^[^0-9\/><\.,\\!\^\$\*\+\?@#%&;:\[\]\{\}=""']+$"

re.Global = True
re.IgnoreCase = True
errorArray(0) = re.Test(fullname)
if errorArray(0) then
errorArray(0)=False
else
errorArray(0) = True
ErrorMsg = "Please type in your full name<br>"
end if

'Email

re.Pattern = "^\w+@\w+\.\w+"
errorArray(1) = re.Test(email)
if errorArray(1) then
errorArray(1) = False
else
errorArray(1) = True
ErrorMsg = ErrorMsg & "Please type in a valid email address<br>"
end if

'Telephone

re.Pattern = "^($?\+?[0-9]*$?)?[0-9_\- ]*$"
errorArray(3) = re.Test(telephone)
if errorArray(3) then
errorArray(3) = False
else
errorArray(3) = True
ErrorMsg = ErrorMsg & "Please type in your telephone number<br>"
end if

'Message

If Len(Trim(message)) > 9 Then
errorArray(4) = False
else
errorArray(4) = True
ErrorMsg = ErrorMsg & "Please type in your message<br/>"
end if
end if

%>

<%
if ErrorMsg <> "" then %>
<font etc><%= ErrorMsg %> </font>
<%end if%>
<form name="sample1" method="post">

'fullname

<% if errorArray(0) = True then %>
<font etc><% end if %>
<% if errorArray(0) = True then %>
</font>
<% end if %>

'email
<% if errorArray(1) = True then %>
<font etc>
<% end if %>
<% if errorArray(1) = True then %>
<% end if %>

'telephone
<% if errorArray(3) = True then %>
<font etc>
<% end if %>
<% if errorArray(3) = True then %>
<% end if %>

'message
<% if errorArray(4) = True then %>
<font etc>
<% end if %>
<% if errorArray(4) = True then %>
<% end if %>

<%if request.form("isSubmitted&quot

= "yes" then%>

<%
DIM all,i
all=False
For i=0 to 4
all=all OR CBOOL(errorArray(i))
Next
If Not all Then

fullname = request.form("fullname&quot

email = request.form("email&quot

%>
city = request.form("city&quot

telephone = request.form("telephone&quot

message = request.form("message&quot

%>

<%

CDONTS goes here

code in my post above (which begins: "' this is where the information finally gets submitted to the database&quot

then follows.

Is it possible the arrays are not working? I have trying to renumber them....without success.

Thanks

Hatter

Veep · Aug 28, 2003

OK. What's going on here?
fullname = request.form("fullname&quot

email = request.form("email&quot

[COLOR=ff0000]%>[/color]
city = request.form("city&quot

telephone = request.form("telephone&quot

message = request.form("message&quot

[COLOR=ff0000]%>[/color]

also just for yucks let's see what's actually getting posted to this form. Put this up at the top:

dim objItem
for each objItem in request.form()
response.write(objItem & " = " & request.form(objItem) & "<br>&quot

Next
response.end()

madHatter1 · Aug 28, 2003

With this, veep,

<%DIM ErrorMsg, fullname,email,city,telephone,message,objItem%>
<%

for each objItem in request.form()
response.write(objItem & " = " & request.form(objItem) & "<br>&quot

Next
response.end()

The page is completely blank. I've uploaded it and refreshed..........not a dicky bird!

hatter

madHatter1 · Aug 28, 2003

But I'll remove the two %> which you have highlighted - thanks

madHatter1 · Aug 28, 2003

No, when I remove the 2 %> (after email and message), I get an error message:

Microsoft VBScript compilation error '800a0400'

Expected statement

/Valy.asp, line 209

<%
^

Cheers

hatter

Veep · Aug 28, 2003

There's something that I'm not seeing because this should throw an error.
[COLOR=ff0000]<% 'start tag [/color]
DIM all,i
all=False
For i=0 to 4
all=all OR CBOOL(errorArray(i))
Next
If Not all Then

fullname = request.form("fullname&quot

email = request.form("email&quot

[COLOR=ff0000]%> End Tag[/color]
city = request.form("city&quot

telephone = request.form("telephone&quot

message = request.form("message&quot

[COLOR=ff0000]%> End Tag[/color]

[COLOR=ff0000]<% Start tag [/color]

As much as I hate to say it you're probably going to have to post the whole script.

madHatter1 · Aug 28, 2003

This is the whole script (less HTML and CDONTS) Veep....warts and all:

(again, I am very grateful)

<%option explicit%>

<%DIM ErrorMsg, fullname,email,city,telephone,message%>
<%

const numFields = 5
dim errorArray()
redim preserve errorArray(numFields)

if request.form("isSubmitted&quot

= "yes" then
fullname = request.form("fullname&quot

city = request.form("city&quot

telephone = request.form("telephone&quot

email = request.form("email&quot

message = request.form("message&quot

ErrorMsg = ""
dim re
set re = New RegExp

'Full Name

re.Pattern = "^[^0-9\/><\.,\\!\^\$\*\+\?@#%&;:\[\]\{\}=""']+$"
re.Global = True
re.IgnoreCase = True
errorArray(0) = re.Test(fullname)
if errorArray(0) then
errorArray(0)=False
else
errorArray(0) = True
ErrorMsg = "Please type in your
full name<br>"
end if

'Email
re.Pattern = "^\w+@\w+\.\w+"
errorArray(1) = re.Test(email)
if errorArray(1) then
errorArray(1) = False
else
errorArray(1) = True
ErrorMsg = ErrorMsg & "Please type in a valid email address<br>"
end if

'Telephone
'match phone numbers, allowing for an international dialing code at the start and hyphenation and spaces

re.Pattern = "^($?\+?[0-9]*$?)?[0-9_\- ]*$"
errorArray(3) = re.Test(telephone)
if errorArray(3) then
errorArray(3) = False
else
errorArray(3) = True
ErrorMsg = ErrorMsg & "Please type in your telephone number<br>"
end if

'Message
If Len(Trim(message)) > 9 Then
errorArray(4) = False
else
errorArray(4) = True
ErrorMsg = ErrorMsg & "Please type in your message<br/>"
end if

end if

%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>

</head>

<%
if ErrorMsg <> "" then %>
<%= ErrorMsg %> </font>
<%end if%>
<form name="sample1" method="post">

'fullname

<% if errorArray(0) = True then %>
<% end if %>
<% if errorArray(0) = True then %>
<% end if %></td>

'email

<% if errorArray(1) = True then %>
<% end if %>
<% if errorArray(1) = True then %>
<% end if %></td>

'telephone

<% if errorArray(3) = True then %>
<% end if %>
<% if errorArray(3) = True then %>
<% end if %>

'message
<% if errorArray(4) = True then %>
<% end if %>
<% if errorArray(4) = True then %>
<% end if %>

<input type="text" name="fullname" value="<%= fullname %>" class="txt">

<input type="text" name="city" value="<%= city %>" class="txt">

<input type="text" name="email" value="<%=email %>" class="txt">

<input type="text" name="telephone" value="<%= telephone %>" class="txt">

<textarea class="txt" name="message" value="<%=message%>" rows="5" cols="19"></textarea>

<p><input type="submit" name="B1" class="txt" value="Submit"
>

<input type="hidden" name="isSubmitted" value="yes">

<input type="reset" name="clear" class="txt" value="Reset">

</form>

<%if request.form("isSubmitted&quot

= "yes" then%>

<%
DIM all,i
all=False
For i=0 to 4
all=all OR CBOOL(errorArray(i))
Next
If Not all Then

fullname = request.form("fullname&quot

email = request.form("email&quot

%>
city = request.form("city&quot

telephone = request.form("telephone&quot

message = request.form("message&quot

%>

<%

<%

' this is where the information finally gets submitted to the database

DIM Conn,strConn,SQLstmt,RS
set Conn = server.createobject("adodb.connection&quot

strConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("/database/validation.mdb&quot

Conn.open strConn

SQLstmt = "INSERT INTO users (fullname,email,city,telephone,message)"

SQLstmt = SQLstmt & " VALUES ("

SQLstmt = SQLstmt & "'" & fullname & "',"
SQLstmt = SQLstmt & "'" & eMail & "',"
SQLstmt = SQLstmt & "'" & city & "',"
SQLstmt = SQLstmt & "'" & telephone & "',"
SQLstmt = SQLstmt & "'" & message & "',"
SQLstmt = SQLstmt & &quot

"

response.write(SQLstmt)
response.end

Set RS = conn.execute(SQLstmt)
If err.number>0 then
response.write "VBScript Errors Occured:" & "<P>"
response.write "Error Number=" & err.number & "<P>"
response.write "Error Descr.=" & err.description & "<P>"
response.write "Help Context=" & err.helpcontext & "<P>"
response.write "Help Path=" & err.helppath & "<P>"
response.write "Native Error=" & err.nativeerror & "<P>"
response.write "Source=" & err.source & "<P>"
end if
IF conn.errors.count> 0 then
response.write "Database Errors Occured" & "<P>"
response.write SQLstmt & "<P>"
for counter= 0 to conn.errors.count
response.write "Error #" & conn.errors(counter).number & "<P>"
response.write "Error desc. -> " & conn.errors(counter).description & "<P>"
next
else
Response.Redirect("thanks1.asp?updated=true&quot

end if
end if
end if
%>

Veep · Aug 28, 2003

fullname = request.form("fullname&quot

email = request.form("email&quot

[COLOR=ff0000]%> remove this one [/color]
city = request.form("city&quot

telephone = request.form("telephone&quot

message = request.form("message&quot

%>
**********************************
What's going on here? Is there a corresponding %> somewhere to close that first tag below? Is that where your CDONTS routine is? If not get rid of the red one.

message = request.form("message&quot

%>

[COLOR=ff0000]<%[/color]

<%

' this is where the information finally gets submitted to the database

SQLstmt = SQLstmt & "'" & fullname & "',"
SQLstmt = SQLstmt & "'" & eMail & "',"
SQLstmt = SQLstmt & "'" & city & "',"
SQLstmt = SQLstmt & "'" & telephone & "',"
SQLstmt = SQLstmt & "'" & message & "'[COLOR=ff0000],[/color]" [COLOR=ff0000] Get rid of this comma[/color]
SQLstmt = SQLstmt & &quot

"
*********************************************
Change this line:
strConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("/database/validation.mdb&quot

To this:
strConn="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("/database/validation.mdb&quot

& ";" _
"User Id=admin;" & _
"Password="

There's not a heck of a lot wrong here (that I can see) except some syntax stuff. BTW the reason that you didn't get anything by placing this:
for each objItem in request.form()
response.write(objItem & " = " & request.form(objItem) & "<br>&quot

Next
at the top of the form is because the action doesn't begin to happen until your variable isSubmitted becomes "Yes" so the snippet would need to go below that line.
Good luck. ;-)

madHatter1 · Aug 28, 2003

You've given me great help, Veep.
I have done this:

email = request.form("email&quot

%> remove this one

but kept the other <% because it's part of CDONTS, and I have removed the , before the ) right bracket.

Tomorrow I'll work on your:

for each objItem in request.form()
response.write(objItem & " = " & request.form(objItem) & "<br>&quot

Next

Many many thanks again for your time and your help.

hatter

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

INSERT INTO error, or is it? 1

madHatter1

Technical User

Veep

Programmer

madHatter1

Technical User

dmhirsch

Programmer

madHatter1

Technical User

Veep

Programmer

madHatter1

Technical User

Veep

Programmer

madHatter1

Technical User

madHatter1

Technical User

madHatter1

Technical User

Veep

Programmer

madHatter1

Technical User

Veep

Programmer

madHatter1

Technical User

Similar threads

Part and Inventory Search

Sponsor