Re: submitted data not updated promptly with ms access

I'm not sure why your post came in double-spaced (did you do it
deliberately? I suspect not), but it makes it very difficult to read and
respond to. In the future, could you try to post plain text single-spaced?

And please, use your shift key to properly capitalize your text. I know I'm
sounding pedantic here, but if you want to receive help, it only makes sense
to make it easy for the people reading your messages, doesn't it? This isn't
a chat where it's vital to type your message as quickly as possible.

tony finishf wrote:
> i have included a form in my application. on
> submission, i store the form typed data in an MS
> ACCESS DATABASE. the number of inputs in the form is
> not constant
> and the user is expected to alter his
> choice. therefore, before writing to the database, i
> first delete the previous data linked to that user,
> then i write the new submitted data.
> this basic op. works fine. however, if i change the
> form content quickly, i see that the newly data is
> not included. generally, i have found 15-20 seconds
> enough time between 2 submissions to be both stored
> in the database.

To improve performance, Jet, which is the database engine used by Access,
uses a delayed-write caching system, which means that writes to the database
may not always happen immediately. This behavior can be disabled or altered,
but it is not recommended that this be done.

> i am using ISS on my local machine,
> so i have not deployed it on a common public server.
> the above inconvenient really annoys me, just
> imagine the inconevenience that occurs especially
> with high number of concurent users ....
>
> i don't know if there a problem with my code, or
> access...

There are several issues with your code, most of which have nothing to do
with your problem, but the correction of which will improve the performance,
maintainability and stability of your application. Here are some of the
issues:
1. Using the obsolete ODBC driver rather than the native Jet OLE DB
Provider - http://www.aspfaq.com/show.asp?id=2126
2. Using selstar (select *) in production code -
http://www.aspfaq.com/show.asp?id=2096
3. Using a cursor (recordset) to perform data modifications (this is the
major problem)
4. The use of + instead of & to perform concatenation which can lead to
unexpected results
6. Unnecessarily mixing server-side code with your html (not a performance
issue - strictly a maintenance issue)
7. Using dynamic sql, which can leave your site open to attacks from hackers
using sql injection (see links at end of message)
8. Using an expensive recordset to run a query that does not retrieve
records (the DELETE query) rather than simply using connect.execute

Here is how I would rewrite this page:

<% option explicit

Dim dbname, cnpath, connStr, sql, cn,cmd, email, title
email=Session("Email")
title = request.form(Title)
Dim N,Title, arParms
N=Session("TitlesNum")

sql = "Delete * from tbl_user where Email =?"
arParms=Array(email)

dbname = "userDb"
cnpath = "Data Source=" & Server.mappath(dbname)
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
cnpath
set cn= CreateObject("ADODB.Connection")
cn.open(connStr)
Set cmd=CreateObject("adodb.command")
With cmd
.CommandText=sql
Set .ActiveConnection = cn
.Execute ,arParms,129
End With

'******break*********************************************
At this point, your code ceases to make sense. You have just deleted all the
records from tbl_user where Email contained the email address stored in
Session. Now you run a query that retrieves all records where Email contains
that same address. This query should retrieve zero records. OK. Then you run
a loop in which you add N records, all containing the same title(!?), into
the recordset... without setting the email address! This makes no sense! I'm
not sure what your actual intent here is, but I will show you how to better
perform that action, as well as adding the email address to the table, which
is what I suspect you want to do, although I don't really know why you wish
to have N records containing the same email and title ...
'******end break*****************************************

sql = "Insert into tbl_user (Email, Title)" & _
"values(?,?)"
arParms=Array(email, title)
Set cmd=CreateObject("adodb.command")
With cmd
.CommandText=sql
Set .ActiveConnection = cn
End With

for i=1 To N
cmd.Execute ,arParms,129
NEXT

Dim msg
msg= "

Your preferences are now stored"
cn.close:set cn=nothing
%>




type="text/css">

Submit the Changes to the Web Site Database