INSERT into ACCESS DB will not WORK??

[hamning]

OK. Now I'm stumped.

No matter how I set it up, it fails.

Here is what I've got:
<%
' ================================
' = CONSTANTS
' ================================
'Const adOpenForwardOnly = 0
'Const adOpenDynamic = 2
'Const adLockReadOnly = 1
'Const adLockPessimistic = 2
Const adOpenKeyset = 1
Const adLockOptimistic = 3

' =================================
' = FORM DATA RETRIEVAL
' =================================
set fmLocation = Request.Form(&quot;fmLocation&quot
set fmState = Request.Form(&quot;fmState&quot

' =================================
' = DATABASE CONNECTION
' =================================
connString = &quot;DBQ=&quot; & Server.MapPath(&quot;PTJobs.mdb&quot & &quot;;Driver={Microsoft Access Driver (*.mdb)}&quot;
set conn = server.createobject(&quot;ADODB.Connection&quot
conn.open connString

' =================================
' = SQL QUERIES
' =================================
locSQL = &quot;INSERT INTO PTJobs ('location','state') values ('Brian','IL')&quot;
set rs = server.createobject(&quot;ADODB.Recordset&quot
' Also tried rs = conn.execute(sql)

' =================================
' = INITIAL RECORDSET CONFIGURATIONS
' =================================
rs.Open locSQL, conn, 3, 3
'Also tried &quot;PTJobs(also locSQL)&quot;,&quot;&quot;, adOpenKeyset, adLockOptimistic

' ================================
' = ACTION WITH EXTRACTED DATA
' ================================
Response.Write locSQL & &quot;<p>&quot;


' ===============================
' = DATABASE CLEANUP
' ===============================
rs.close
set rs = nothing
conn.close
set conn = nothing
%>

Always returns: HTTP 500 - Internal server error

Any suggestions are greatly appreciated..

Bri

 

[codestorm]

Hrm .. would have expected a different error.

Try changing

locSQL = &quot;INSERT INTO PTJobs ('location','state') values ('Brian','IL')&quot;
set rs = server.createobject(&quot;ADODB.Recordset&quot
' Also tried rs = conn.execute(sql)

to

locSQL = &quot;INSERT INTO PTJobs (location, state) values ('Brian','IL')&quot;
conn.Execute locSQL

and removing these lines

rs.Open locSQL, conn, 3, 3
rs.close
set rs = nothing

The problem is that you are trying to run an ACTION statement and get a result into a recordset - however ACTION statements don't produce any output (hence no need for recordset). The Connection object is what connects to
the database, a recordset object is not also needed if all you want to do is insert/update/delete stuff in the database.

Hope that helps.


<insert witticism here>
codestorm

 

[hamning]

Thanks. That makes total sense. I have altered the information to the following, but it fails the minute I allow it to read the &quot;conn.Execute locSQL&quot; line..

Any ideas??

Here's what I've got now:


<%
' =================================
' = DATABASE CONNECTION
' =================================
connString = &quot;DBQ=&quot; & Server.MapPath(&quot;PTJobs.mdb&quot & &quot;;Driver={Microsoft Access Driver (*.mdb)}&quot;
set conn = server.createobject(&quot;ADODB.Connection&quot
conn.open connString

' =================================
' = SQL QUERIES
' =================================
locSQL = &quot;INSERT INTO PTJobs (location, state) values ('Brian','IL')&quot;
conn.Execute locSQL


Response.Write &quot;Test&quot;

' ===============================
' = DATABASE CLEANUP
' ===============================
conn.close
set conn = nothing
%>

Thanks (As Always!!)

Bri

 

[codestorm]

What's the error? <insert witticism here>
codestorm

 

[hamning]

It always returns: HTTP 500 - Internal server error

I'm checking with my host provider to see if anything is messed up with my folder securities at this time..

Thanks,
Bri

 

[link9]

Many times, that can mean that you have forgotten to put:

<%@language=vbscript%>

as the very first line on the page.

Other than that, I have had to completely rebuild pages because of that obtuse error... never could run down what it meant.

hope that fixes it.
Paul Prewett

 

[vasah20]

Check the spacing in your connection string, I can't tell from the way you cut and paste it, but it might be off.

ODBC is VERY picky about how you pass in the Driver... if the spacing isn't right, it won't let you pass.

Since it seems that you are using ASP 3.0 (which has an automatic error trapping method), you won't be able to see the specific ASP error being given, you just get the generic 500 internal error. I'll put up a post next week on how to better trap the 500 internal error.

Anyhow -
my ultimate suggestion would be to use OLEDB when connecting to your DB (see

http://www.learnasp.com/learn/dbopen.asp)

it offers more stability and better access than ODBC.

see if that helps any.
leo