INSERT into Access DB not inserting

[tyleri]

I have a form that I am trying to get to insert into an Access Database with ASP. Below I am inserting the code parts of my ASP pages. The form code is here:

demo.asp
-------------------------------------------------------
<%
Dim path
Dim conn
Dim query
Dim formtype
Dim id
Dim first
Dim last
Dim email
Dim address
Dim city
Dim state
Dim zip
Dim phone

path = Server.MapPath(&quot;/database/demo.mdb&quot
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & path & &quot;;&quot;

id = Request(&quot;id&quot
if (id = &quot;&quot then
id = 0
end if

Set query = conn.Execute(&quot;SELECT * FROM demo_table WHERE id = &quot; & id)

if query.EOF then
formtype = &quot;add&quot;
first = &quot;&quot;
last = &quot;&quot;
email = &quot;&quot;
address = &quot;&quot;
city = &quot;&quot;
state = &quot;&quot;
zip = &quot;&quot;
phone = &quot;&quot;

else
formtype = &quot;update&quot;
first = query.Fields(&quot;first&quot.value
last = query.Fields(&quot;last&quot.value
email = query.Fields(&quot;email&quot.value
address = query.Fields(&quot;address&quot.value
city = query.Fields(&quot;city&quot.value
state = query.Fields(&quot;state&quot.value
zip = query.Fields(&quot;zip&quot.value
phone = query.Fields(&quot;phone&quot.value
end if
%>

<!--------FORM-------------->
<form method=&quot;post&quot; action=&quot;submit_success.asp&quot;>
<input name=&quot;formtype&quot; type=&quot;hidden&quot; value=&quot;<%=add%>&quot;>

<table width=500 border=0 align=center>
<tr>
<td>

<table align=&quot;left&quot; cellspacing=&quot;3&quot; cellpadding=&quot;0&quot;>
<tr>
<td align=&quot;left&quot; width=75><font face=arial size=2>First Name</font></td>
<td align=&quot;left&quot;>
<input type=&quot;text&quot; name=&quot;first&quot; maxlength=&quot;100&quot; size=&quot;20&quot; value=&quot;<%=first%>&quot;>
</td>
</tr>
<tr>
<td align=&quot;left&quot; width=75><font face=arial size=2>Last Name</font></td>
<td align=&quot;left&quot;>
<input type=&quot;text&quot; name=&quot;last&quot; maxlength=&quot;100&quot; size=&quot;20&quot; value=&quot;<%=last%>&quot;>
</td>
</tr>
<tr>
<td align=&quot;left&quot; width=75><font face=arial size=2>Address</font></td>
<td align=&quot;left&quot;>
<input type=&quot;text&quot; name=&quot;address&quot; maxlength=&quot;100&quot; size=&quot;20&quot; value=&quot;<%=address%>&quot;>
</td>
</tr>
<tr>
<td align=&quot;left&quot; width=75><font face=arial size=2>City</font></td>
<td align=&quot;left&quot;>
<input type=&quot;text&quot; name=&quot;city&quot; maxlength=&quot;100&quot; size=&quot;20&quot; value=&quot;<%=city%>&quot;>
</td>
</tr>
<tr>
<td align=&quot;left&quot; width=75><font face=arial size=2>State</font></td>
<td align=&quot;left&quot;>
<input type=&quot;text&quot; name=&quot;state&quot; maxlength=&quot;100&quot; size=&quot;2&quot; value=&quot;<%=state%>&quot;>
</td>
</tr>
<tr>
<td align=&quot;left&quot; width=75><font face=arial size=2>Zip</font></td>
<td align=&quot;left&quot;>
<input type=&quot;text&quot; name=&quot;zip&quot; maxlength=&quot;100&quot; size=&quot;10&quot; value=&quot;<%=zip%>&quot;>
</td>
</tr>
<tr>
<td align=&quot;left&quot; width=75><font face=arial size=2>e-Mail</font></td>
<td align=&quot;left&quot;>
<input type=&quot;text&quot; name=&quot;email&quot; maxlength=&quot;100&quot; size=&quot;20&quot; value=&quot;<%=email%>&quot;>
</td>
</tr>
<tr>
<td align=&quot;left&quot; width=75><font face=arial size=2>Phone</font></td>
<td align=&quot;left&quot;>
<input type=&quot;text&quot; name=&quot;phone&quot; maxlength=&quot;100&quot; size=&quot;20&quot; value=&quot;<%=phone%>&quot;>  <input type = &quot;submit&quot; value = &quot;Submit&quot;>
</td>
</tr>
</table>
</td></tr></table>

</form>
<!------END-FORM----------------->

-------------------------------------------------------

This form then sends the information to this ASP file called &quot;Submit_Success.asp.&quot; THis inserts with a SIMPLE INSERT STATEMENT (this is where the problem has arisen) into the access database. The problem seems to be lying in the Insert statement line below.
-------------------------------------------------------


<%

Dim path
Dim conn

path = Server.MapPath(&quot;/database/demo.mdb&quot
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & path & &quot;;&quot;

Dim formtype
Dim id

Dim thefirst
Dim thelast
Dim thestate
Dim thecity
Dim theaddress
Dim thephone
Dim thezip
Dim theemail

formtype = Request.Form(&quot;formtype&quot


thefirst = Replace(Request.Form(&quot;first&quot, &quot;'&quot;, &quot;'&quot
thelast = Replace(Request.Form(&quot;last&quot, &quot;'&quot;, &quot;'&quot
thestate = Replace(Request.Form(&quot;state&quot, &quot;'&quot;, &quot;'&quot
thecity = Replace(Request.Form(&quot;city&quot, &quot;'&quot;, &quot;'&quot
theaddress = Replace(Request.Form(&quot;address&quot, &quot;'&quot;, &quot;'&quot
thephone = Replace(Request.Form(&quot;phone&quot, &quot;'&quot;, &quot;'&quot
thezip = Replace(Request.Form(&quot;zip&quot, &quot;'&quot;, &quot;'&quot
theemail = Replace(Request.Form(&quot;email&quot, &quot;'&quot;, &quot;'&quot

Dim query

Set query = conn.Execute(&quot;INSERT INTO demo_table (first, last, address, city, state, zip, email, phone) VALUES ('&quot; & thefirst & &quot;', '&quot; & thelast & &quot;', '&quot; & theaddress & &quot;', '&quot; & thecity & &quot;', '&quot; & thestate & &quot;', '&quot; & thezip & &quot;', '&quot; & theemail & &quot;', '&quot; & thephone & &quot;')&quot

conn.Close
Set conn = Nothing

Response.Redirect &quot;/demo/demo_view.asp&quot;
%>

----------------------------------------------------------

Here is a link to my access database on a testing server...

http://www.omnisoul.com/database/demo.mdb

 

[tyleri]

*** the issue is with my INSERT Statement, something isn't right with it.

 

[Genimuse]

1. When you post code here, please put [ code ] and [ /code ] tags around it (without the spaces) -- it makes it much easier to read.

2. This line doesn't do anything:

thefirst = Replace(Request.Form("first"), "'", "'")

You want to replace your single quotes with two single quotes in a row, like this:

thefirst = Replace(Request.Form("first"), "'", "''")

3. What problem are you having, exactly. Do you receive an error message? Try taking the Response.Redirect out if you can't tell. Remove any &quot;On Error Resume Next&quot; at the top if you have one so the error will appear.

 

[tyleri]

sorry about the [ code ] thing.

Well - all my variables work - I just can't get it to dump any of them into the database. The error message I get is &quot;page cannot be displayed&quot;... the 404 error, but it's showing the submit_success.asp filename in the browser window...

I tried using the Insert statement in SQL Viewer in Access and that worked too. So I dont know what the issue is.

 

[tyleri]

when i comment out the INSERT Statement, It goes to the redirect page...

 

[Genimuse]

404 error? Hmm. Are you absolutely positively definitely without question certain that the page you're submitting to is defnitely called &quot;Submit_Success.asp&quot; and that your path is completely correct?

 

[basil3legs]

The 404 Error is probably due to your server not being very helpful and returning the real error. Some servers are set up like this to avoid returning useful information to would be hackers (e.g. the path to the database). The page name must be right if you are getting redirected when you leave out the insert.

Looking at the code, it appears that the database is in a directory off where the ASP pages are and is hence part of the website (not very secure!), bearing this in mind you might want to check the access rights to it as it may be read only.

Also, you might want to check what the Insert line is actually saying by putting it in a string first and then sending it to the screen (although that bit looks OK, assuming all the fields are text as they appear to be).

Also, try putting a set INSERT command in to test it, e.g.

("INSERT INTO demo_table (first, last, address, city, state, zip, email, phone) VALUES ('myfirst', 'mylast', 'myaddress', 'mycity', 'mystate', 'myzip', 'myemail', 'myphone')")

and check to see if this works.

 

[Tarwn]

If the above doesn't work but you can copy and paste your SQL into the database and it does work (and you have checked permissions) the problem is likely reserved words. I generally never name an access field without putting an underscore in it somewhere because I have never found a complete list of al lthe reserved words it uses. When it sees a reserved word in an SQL statement it attempts to process that word as a function/reserved statement/whatever rathethan simply look at the table you have specifie and see if it is a fieldname. The easiest way to get around this si to surround all of your fieldnames with square brackets to force the database to recognize them as field names., ie:

("INSERT INTO demo_table ([first], [last], [address], [city], [state], [zip], [email], [phone]) VALUES ('myfirst', 'mylast', 'myaddress', 'mycity', 'mystate', 'myzip', 'myemail', 'myphone')")

-Tarwn


[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]

http://www.tiernok.com/

The never-completed website

 

[tyleri]

hey thanks for all your help - we figured out that First and Last are reserved words in ACCESS - and that was causing the problme