Re: Access and ASP adding a record trouble

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Bob Barrows (reb01501_at_NOyahoo.SPAMcom)
Date: 03/17/04 

Date: Wed, 17 Mar 2004 18:44:43 -0500

Katie wrote:
> Hi,
>
> Could anyone help with this please, I on the verge of ripping my hair
> out.
>
> I'm trying to just add a new record into a database, a task that I
> thought may be relatively easy, doh!

Please always start by telling us the type and version of database you are
using. It is almost always relevant.

<snip>

> Then the Test.asp:
> <%
> dim con, dbPath, rs, sqlstr
> dbPath = "E:/webspace/xxxxx.com/db/test.mdb"
> set con = Server.CreateObject("ADODB.Connection")
> con.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
>

Good - no ODBC connection.

> Set rs = Server.CreateObject("ADODB.Recordset")
>
> sqlstr = "SELECT results.test1, results.test2 FROM results;"

Stop right there. Recordsets should mainly be used to retrieve data to be
displayed. It is very rare that they be used to modify data. Instead, you
should use sql statements, preferably embedded in stored procedures (known
in Access as saved parameter queries). Here's your best course of action:

1. Open your database in Access.
2. Click into the Queries tab and double-click the "Create query in design
view" text.
3. Close the Choose Tables dialog without selecting a table
4. Use the View menu, toolbar button, or right-click menu to switch to SQL
View.
5. Copy and paste this sql:

Insert Into results (test1,test2)
Values ([pTest1],[pTest2])

6. Click the toolbar button with the exclamation point on it to run the
query. Notice that you are prompted for values for pTest1 and pTest2. In
vbscript, we will supply these parameter values programmattically.
7. Enter values and let it run. Without closing the query window, open the
table to verify that the query worked.
8. Click back into the query window, switch it back to SQL View and use
File|Save or the toolbar button to save the query. Call it something like
"qInsResults" (no quotes, of course)

Now that we have a query that works when run in Access, we can write the
code to run it from asp. In your vbscript code, substitute the following for
your recordset code:

con.qInsResults Request.form("gender"), Request.form("gender")

<snip of recordset code>
> Set con = Nothing
>
> Con.close
> %>
>
> If I remove all the gubbins from the test.asp, and leave just the
> opening and closing statements of the database, it doesnt crash.

What does "crash" mean? Server shuts down? Error message? We're not looking
over your shoulder. If you're not getting an informative error message, it
usually means that you have "Show friendly error messages" turned on in IE
advanced options. (http://www.aspfaq.com/show.asp?id=2109)

Usually, failure to update an Access database is due to permissions
problems. You must realize that asp is not running under your user account:
it is running under an account called IUSR_machinename, where "machinename"
is the name of the machine on which IIS is installed. This IUSR account
needs NTFS Change permissions for the folder containing your database file
(not just the database file - all users of a database must be able to
create, modify and delete the .ldb lock file that is use to keep track of
users in the database.
http://www.aspfaq.com/show.asp?id=2062 - updatable cursor
http://www.aspfaq.com/show.asp?id=2009 - 80004005 errors

If this is not your problem, then I've just wasted 5 min. of my time because
you did not fully describe your problem :-) Please let this be a lesson for
future posts.

Bob Barrows 

-- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Quantcast