|
 |
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
|
|
|
View Cart
