update problem

I am using an Insert command in my asp code that Inserts a rows in my
database.
After the Insert command i create a recordset and ask for all the rows in
the table that i aplied the insert command
Some times the rows i get are correct but sometimes the database is not
updated by the time the recordset is opened.

Is there any way to wait until the table is updated with the new rows and
then open the
recordset?

<%
Dim Cmd_insord
set Cmd_insord = Server.CreateObject("ADODB.Command")
Cmd_insord.ActiveConnection = mystring
Cmd_insord.CommandText = "INSERT INTO PP3 (order_status) VALUES (0) "
Cmd_insord.CommandType = 1
Cmd_insord.CommandTimeout = 0
Cmd_insord.Prepared = true
Cmd_insord.Execute() 'here i execute the insert command
%>
<%
Dim RS_ORDER
Set RS_ORDER = Server.CreateObject("ADODB.Recordset")
RS_ORDER.ActiveConnection = MM_connDUgallery_STRING
RS_ORDER.Source = "SELECT * FROM PP3 WHERE order_status=0"
RS_ORDER.CursorType = 0
RS_ORDER.CursorLocation = 2
RS_ORDER.LockType = 1
RS_ORDER.Open() ' when the program reaches here the PP3 table may not
allready contain the new added records
%>

I am using access and dsnless conection

Re: update problem

xarrisx wrote:
> I am using an Insert command in my asp code that Inserts a rows in my
> database.
> After the Insert command i create a recordset and ask for all the
> rows in the table that i aplied the insert command
> Some times the rows i get are correct but sometimes the database is
> not updated by the time the recordset is opened.
>
> Is there any way to wait until the table is updated with the new rows
> and then open the
> recordset?

> I am using access and dsnless conection

The connection type does not matter. However, if you are not using the
native Jet OLEDB provider, I suggest you do so. See
www.able-consulting.com/ado_conn.htm for examples.

You are falling victim to a performance enhancement in Jet: update-caching.
I'm a little surprised about this as you are not using a cursor to do your
insert. However, see these KB articles:
http://support.microsoft.com/?kbid=240317
http://support.microsoft.com/kb/200300


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"

Re: update problem

"Bob Barrows [MVP]" wrote in message
news:ONjBtfRsEHA.2316@TK2MSFTNGP12.phx.gbl...
> xarrisx wrote:
> > I am using an Insert command in my asp code that Inserts a rows in my
> > database.
> > After the Insert command i create a recordset and ask for all the
> > rows in the table that i aplied the insert command
> > Some times the rows i get are correct but sometimes the database is
> > not updated by the time the recordset is opened.
> >
> > Is there any way to wait until the table is updated with the new rows
> > and then open the
> > recordset?
>
> > I am using access and dsnless conection
>
> The connection type does not matter. However, if you are not using the
> native Jet OLEDB provider, I suggest you do so. See
> www.able-consulting.com/ado_conn.htm for examples.
>
> You are falling victim to a performance enhancement in Jet:
update-caching.
> I'm a little surprised about this as you are not using a cursor to do your
> insert. However, see these KB articles:
> http://support.microsoft.com/?kbid=240317
> http://support.microsoft.com/kb/200300
>
>
> 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"
>
>
thanks for the link
http://support.microsoft.com/?kbid=240317

Searching for more info on "JetEngine object's RefreshCache Method" i found
the following:

I adjust this setting to the registry

Go HKEY_LOCAL_MACHINE/Software/Microsoft/Jet/4.0/Engines/Jet/4. 0/
ImplicitCommitSync should be set to yes
UserCommitSync should be set to yes.


As it mentions it has some performance penalty but it works fine

I suppose this affects all web sites in my web server. I am now looking for
a function to add in my asp code that will immediately refresh the
read-cache after the insert in my current connection.