p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: Forcing a table to update immediately


Message #1 by "Peter Foti" <pdf@b...> on Tue, 12 Dec 2000 22:00:26 -0000
Hi,

The problem I am having is this:  I want to look at a bunch of items, and

see if they are already in my database.  If not, then I want to add them. 

If they are already in my database, then I want to move on to the next

item.  The problem I'm having is that after adding an item, the database

does not seem to update itself quick enough, and I end up adding the same

item 10 or 11 times (assuming I see the same item over and over).  The

count is zero until about 10 items, at which point it has added the new

item about 10 times and the count says 10.  Is there anything I can do to

tell this to update immediately?  Also, this database is a MS Access

database.



Here is a snippet of the ASP code.  This is located inside a loop and the

variables f and ef may change (but in this example, they happen to be

same).





   ' Add to the file table

   SQLstr = "SELECT COUNT(*) AS dcount FROM CFGFILES WHERE

CFGFILENAME="&sql_quote(f)&" AND ENCCFGFILENAME="&sql_quote(ef)

   set cRS = dConn.Execute(SQLstr, , adCmdText)

   

   ' The first time through the loop, a new item is found, and count is

   ' accurately reported as zero

   Response.Write("<hr>Count = "&cRS("dcount") & "<hr>")

   if cRS("dcount") = 0 then

   ' If this isn't already in the database, add it now

   set fRS = Server.CreateObject("ADODB.Recordset")

   fRS.Open "CFGFILES", i_cntStr, adOpenKeyset, adLockPessimistic,

adCmdTable

   fRS.AddNew

   fRS.Fields("CFGFILENAME") = f

   fRS.Fields("ENCCFGFILENAME") = ef

   fRS.Update



   ' Here, I wanted to see what was in the table.  Sure enough, my new

   ' entry is in there.  But next time through the loop I wont see it	

   fRS.MoveFirst

   do while not fRS.EOF

      Response.Write("<hr>"&fRS("CFGFILEID") & vbTab & fRS("CFGFILENAME") &

vbTab & fRS("ENCCFGFILENAME") & "<br>")

      fRS.MoveNext

   loop



   fRS.Close

   Set fRS = Nothing





--- 

FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND

INSIGHTS IN YOUR INBOX!

Get the latest and best C++, Visual C++, Java, Visual Basic, and XML tips, tools, and 

developments from the experts.  Sign up for one or more of EarthWeb?s

FREE IT newsletters at http://www.earthweb.com today!  

---

You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')

To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com

Message #2 by "Ken Schaefer" <ken@a...> on Wed, 13 Dec 2000 10:50:34 +1100
a) Start by not using the Recordset object - use SQL statements instead -

they're faster.



b) You are opening an adOpenForwardOnly cursor - this *does not* requery the

database when you do a .movefirst since you are not repopulating the

recordset. You need to read up on the different cursor types that are

available.





Thirdly, if you're still having problems after making the necessary changes,

look here:



http://support.microsoft.com/support/kb/articles/Q245/6/76.ASP

Records Newly Inserted into Access Database Are Not Immediately Available

When Reselecting

Cheers

Ken



----- Original Message -----

From: "Peter Foti" <pdf@b...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, December 13, 2000 5:21 PM

Subject: [asp_databases] Forcing a table to update immediately





> Hi,

> The problem I am having is this:  I want to look at a bunch of items, and

> see if they are already in my database.  If not, then I want to add them.

> If they are already in my database, then I want to move on to the next

> item.  The problem I'm having is that after adding an item, the database

> does not seem to update itself quick enough, and I end up adding the same

> item 10 or 11 times (assuming I see the same item over and over).  The

> count is zero until about 10 items, at which point it has added the new

> item about 10 times and the count says 10.  Is there anything I can do to

> tell this to update immediately?  Also, this database is a MS Access

> database.

>

> Here is a snippet of the ASP code.  This is located inside a loop and the

> variables f and ef may change (but in this example, they happen to be

> same).

>

>

>    ' Add to the file table

>    SQLstr = "SELECT COUNT(*) AS dcount FROM CFGFILES WHERE

> CFGFILENAME="&sql_quote(f)&" AND ENCCFGFILENAME="&sql_quote(ef)

>    set cRS = dConn.Execute(SQLstr, , adCmdText)

>

>    ' The first time through the loop, a new item is found, and count is

>    ' accurately reported as zero

>    Response.Write("<hr>Count = "&cRS("dcount") & "<hr>")

>    if cRS("dcount") = 0 then

>    ' If this isn't already in the database, add it now

>    set fRS = Server.CreateObject("ADODB.Recordset")

>    fRS.Open "CFGFILES", i_cntStr, adOpenKeyset, adLockPessimistic,

> adCmdTable

>    fRS.AddNew

>    fRS.Fields("CFGFILENAME") = f

>    fRS.Fields("ENCCFGFILENAME") = ef

>    fRS.Update

>

>    ' Here, I wanted to see what was in the table.  Sure enough, my new

>    ' entry is in there.  But next time through the loop I wont see it

>    fRS.MoveFirst

>    do while not fRS.EOF

>       Response.Write("<hr>"&fRS("CFGFILEID") & vbTab & fRS("CFGFILENAME")

&

> vbTab & fRS("ENCCFGFILENAME") & "<br>")

>       fRS.MoveNext

>    loop

>

>    fRS.Close

>    Set fRS = Nothing





--- 

FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND

INSIGHTS IN YOUR INBOX!

Get the latest and best C++, Visual C++, Java, Visual Basic, and XML tips, tools, and 

developments from the experts.  Sign up for one or more of EarthWeb?s

FREE IT newsletters at http://www.earthweb.com today!  

---

You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')

To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com

Message #3 by pdf@b... on Wed, 13 Dec 2000 10:38:14 -0500

Hi Ken,



Thanks for the info.  Do you know where I could find some more info on cursor

types?



Thanks,

Peter











"Ken Schaefer" <ken@a...> on 12/12/2000 06:50:34 PM



Please respond to "ASP Databases" <asp_databases@p...>



To:   "ASP Databases" <asp_databases@p...>

cc:    (bcc: Peter Foti)



Subject:  [asp_databases] Re: Forcing a table to update immediately







a) Start by not using the Recordset object - use SQL statements instead -

they're faster.



b) You are opening an adOpenForwardOnly cursor - this *does not* requery the

database when you do a .movefirst since you are not repopulating the

recordset. You need to read up on the different cursor types that are

available.





Thirdly, if you're still having problems after making the necessary changes,

look here:



http://support.microsoft.com/support/kb/articles/Q245/6/76.ASP

Records Newly Inserted into Access Database Are Not Immediately Available

When Reselecting

Cheers

Ken



----- Original Message -----

From: "Peter Foti" <pdf@b...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, December 13, 2000 5:21 PM

Subject: [asp_databases] Forcing a table to update immediately





> Hi,

> The problem I am having is this:  I want to look at a bunch of items, and

> see if they are already in my database.  If not, then I want to add them.

> If they are already in my database, then I want to move on to the next

> item.  The problem I'm having is that after adding an item, the database

> does not seem to update itself quick enough, and I end up adding the same

> item 10 or 11 times (assuming I see the same item over and over).  The

> count is zero until about 10 items, at which point it has added the new

> item about 10 times and the count says 10.  Is there anything I can do to

> tell this to update immediately?  Also, this database is a MS Access

> database.

>

> Here is a snippet of the ASP code.  This is located inside a loop and the

> variables f and ef may change (but in this example, they happen to be

> same).

>

>

>    ' Add to the file table

>    SQLstr = "SELECT COUNT(*) AS dcount FROM CFGFILES WHERE

> CFGFILENAME="&sql_quote(f)&" AND ENCCFGFILENAME="&sql_quote(ef)

>    set cRS = dConn.Execute(SQLstr, , adCmdText)

>

>    ' The first time through the loop, a new item is found, and count is

>    ' accurately reported as zero

>    Response.Write("<hr>Count = "&cRS("dcount") & "<hr>")

>    if cRS("dcount") = 0 then

>    ' If this isn't already in the database, add it now

>    set fRS = Server.CreateObject("ADODB.Recordset")

>    fRS.Open "CFGFILES", i_cntStr, adOpenKeyset, adLockPessimistic,

> adCmdTable

>    fRS.AddNew

>    fRS.Fields("CFGFILENAME") = f

>    fRS.Fields("ENCCFGFILENAME") = ef

>    fRS.Update

>

>    ' Here, I wanted to see what was in the table.  Sure enough, my new

>    ' entry is in there.  But next time through the loop I wont see it

>    fRS.MoveFirst

>    do while not fRS.EOF

>       Response.Write("<hr>"&fRS("CFGFILEID") & vbTab & fRS("CFGFILENAME")

&

> vbTab & fRS("ENCCFGFILENAME") & "<br>")

>       fRS.MoveNext

>    loop

>

>    fRS.Close

>    Set fRS = Nothing











--- 

NEED TECHNICAL TIPS, TOOLS, AND INSIGHTS?  Is FREE okay?

Visit EarthWeb for the latest in IT Management, Software Development, 

Web Development, Networking & Communications, and Hardware & Systems.  

Click on http://www.earthweb.com for FREE articles, tutorials,

and discussions from the experts.

---

You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')

To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com


  Return to Index