|
 |
asp_databases thread: Database Update
Message #1 by Chris Neale <Chris.Neale@s...> on Thu, 14 Sep 2000 11:26:11 +0100
|
|
In an Access 97 database being accessed using ASP (running on PWS) and ADO I
keep getting the error message;
Microsoft Cursor Engine error '80040e38'
The specified row could not be located for updating: Some values may have
been changed since it was last read.
I think this is because I'm trying to change the information in the database
using rsRecords.Fields("field") = strNewvalue, and then using a MoveNext to
go to the next record.. How am I meant to update anything if the script
crashes when I put in a new value? (Its crashes on both MoveNext and Update)
Chris
Chaos! Panic! Disaster! (My work here is done)
Chris Neale. Web/Wap Developer
Chris.neale@s... <mailto:Chris.neale@s...>
www.sparkresponse.co.uk
Message #2 by ckoski@w... on Thu, 14 Sep 2000 08:41:48 -0400
|
|
Chris,
have you considered trying SQL via ADO? it's not as object oriented as the
method you are using, but issuing an UPDATE sql statement works with less
headaches... unless you don't actually know SQL, then you'll need to learn
that first.
Cory
----- Original Message -----
From: "Chris Neale" <Chris.Neale@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, September 14, 2000 6:26 AM
Subject: [asp_databases] Database Update
> In an Access 97 database being accessed using ASP (running on PWS) and ADO
I
> keep getting the error message;
>
> Microsoft Cursor Engine error '80040e38'
> The specified row could not be located for updating: Some values may have
> been changed since it was last read.
>
> I think this is because I'm trying to change the information in the
database
> using rsRecords.Fields("field") = strNewvalue, and then using a MoveNext
to
> go to the next record.. How am I meant to update anything if the script
> crashes when I put in a new value? (Its crashes on both MoveNext and
Update)
>
> Chris
>
> Chaos! Panic! Disaster! (My work here is done)
> Chris Neale. Web/Wap Developer
> Chris.neale@s... <mailto:Chris.neale@s...>
> www.sparkresponse.co.uk
>
>
Message #3 by Chris Neale <Chris.Neale@s...> on Thu, 14 Sep 2000 13:55:43 +0100
|
|
Trust me, I'd much rather do this using SQL, unfortunately its having to be
done in Access coz its for our intranet and the box running the server isn't
even slightly up to hosting SQL Server.
As it is, if I comment all the lines that change the data coming out of the
recordset the recordset.update statement doesn't give an error. Only if I
actually alter something. And even then it will change the first record.
It's a right pain..
Chris
Chaos! Panic! Disaster! (My work here is done)
Chris Neale. Web/Wap Developer
Chris.neale@s... <mailto:Chris.neale@s...>
www.sparkresponse.co.uk
-----Original Message-----
From: ckoski@w... [SMTP:ckoski@w...]
Sent: Thursday, September 14, 2000 1:42 PM
To: ASP Databases
Subject: [asp_databases] Re: Database Update
Chris,
have you considered trying SQL via ADO? it's not as object oriented
as the
method you are using, but issuing an UPDATE sql statement works with
less
headaches... unless you don't actually know SQL, then you'll need to
learn
that first.
Cory
----- Original Message -----
From: "Chris Neale" <Chris.Neale@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, September 14, 2000 6:26 AM
Subject: [asp_databases] Database Update
> In an Access 97 database being accessed using ASP (running on PWS)
and ADO
I
> keep getting the error message;
>
> Microsoft Cursor Engine error '80040e38'
> The specified row could not be located for updating: Some values
may have
> been changed since it was last read.
>
> I think this is because I'm trying to change the information in
the
database
> using rsRecords.Fields("field") = strNewvalue, and then using a
MoveNext
to
> go to the next record.. How am I meant to update anything if the
script
> crashes when I put in a new value? (Its crashes on both MoveNext
and
Update)
>
> Chris
>
> Chaos! Panic! Disaster! (My work here is done)
> Chris Neale. Web/Wap Developer
> Chris.neale@s...
<mailto:Chris.neale@s...>
> www.sparkresponse.co.uk
>
>
Message #4 by Imar Spaanjaars <Imar@S...> on Thu, 14 Sep 2000 14:48:21 +0200
|
|
Are you supplying the right datatype for the field?? Remember, VB script
uses variants, but the database expects a specific type.
Otherwise, posting some code is always a quick way to solve any problem.
Imar
At 11:26 AM 9/14/2000 +0100, you wrote:
>In an Access 97 database being accessed using ASP (running on PWS) and ADO I
>keep getting the error message;
>
>Microsoft Cursor Engine error '80040e38'
>The specified row could not be located for updating: Some values may have
>been changed since it was last read.
>
>I think this is because I'm trying to change the information in the database
>using rsRecords.Fields("field") = strNewvalue, and then using a MoveNext to
>go to the next record.. How am I meant to update anything if the script
>crashes when I put in a new value? (Its crashes on both MoveNext and Update)
>
>Chris
>
>Chaos! Panic! Disaster! (My work here is done)
>Chris Neale. Web/Wap Developer
>Chris.neale@s... <mailto:Chris.neale@s...>
>www.sparkresponse.co.uk
>
>
>
Message #5 by "Mark Morgan" <mark.morgan@v...> on Thu, 14 Sep 2000 13:58:57 +0100
|
|
Chris,
You can still use SQL and ADO with Access, I think SQL is a better way to
go since you can port the code to run against any relational database with
just a few tweaks if you dump Access in the future.
Mark.
-----Original Message-----
From: Chris Neale [mailto:Chris.Neale@s...]
Sent: 14 September 2000 13:56
To: ASP Databases
Subject: [asp_databases] Re: Database Update
Trust me, I'd much rather do this using SQL, unfortunately its having to be
done in Access coz its for our intranet and the box running the server isn't
even slightly up to hosting SQL Server.
As it is, if I comment all the lines that change the data coming out of the
recordset the recordset.update statement doesn't give an error. Only if I
actually alter something. And even then it will change the first record.
It's a right pain..
Chris
Chaos! Panic! Disaster! (My work here is done)
Chris Neale. Web/Wap Developer
Chris.neale@s... <mailto:Chris.neale@s...>
www.sparkresponse.co.uk
-----Original Message-----
From: ckoski@w... [SMTP:ckoski@w...]
Sent: Thursday, September 14, 2000 1:42 PM
To: ASP Databases
Subject: [asp_databases] Re: Database Update
Chris,
have you considered trying SQL via ADO? it's not as object oriented
as the
method you are using, but issuing an UPDATE sql statement works with
less
headaches... unless you don't actually know SQL, then you'll need to
learn
that first.
Cory
----- Original Message -----
From: "Chris Neale" <Chris.Neale@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, September 14, 2000 6:26 AM
Subject: [asp_databases] Database Update
> In an Access 97 database being accessed using ASP (running on PWS)
and ADO
I
> keep getting the error message;
>
> Microsoft Cursor Engine error '80040e38'
> The specified row could not be located for updating: Some values
may have
> been changed since it was last read.
>
> I think this is because I'm trying to change the information in
the
database
> using rsRecords.Fields("field") = strNewvalue, and then using a
MoveNext
to
> go to the next record.. How am I meant to update anything if the
script
> crashes when I put in a new value? (Its crashes on both MoveNext
and
Update)
>
> Chris
>
> Chaos! Panic! Disaster! (My work here is done)
> Chris Neale. Web/Wap Developer
> Chris.neale@s...
<mailto:Chris.neale@s...>
> www.sparkresponse.co.uk
>
Message #6 by Chris Neale <Chris.Neale@s...> on Thu, 14 Sep 2000 14:06:17 +0100
|
|
All the data types are fine.
set rsModify = server.CreateObject ("ADODB.Recordset")
rsModify.CursorLocation = adUseClient
rsModify.Open "Select * from BOOKINGS where ser_num=" &
Request("bookingID"),conChris,adOpenKeyset,adLockOptimistic
if len(trim(Request.Form("update"))) > 0 then
rsModify.fields("reason")
trim(Request.Form("reason"))
rsModify.Update
end if
If I comment out the rsModify line it doesn't give an error..
Chris
Chaos! Panic! Disaster! (My work here is done)
Chris Neale. Web/Wap Developer
Chris.neale@s... <mailto:Chris.neale@s...>
www.sparkresponse.co.uk
-----Original Message-----
From: Imar Spaanjaars [SMTP:Imar@S...]
Sent: Thursday, September 14, 2000 1:48 PM
To: ASP Databases
Subject: [asp_databases] Re: Database Update
Are you supplying the right datatype for the field?? Remember, VB
script
uses variants, but the database expects a specific type.
Otherwise, posting some code is always a quick way to solve any
problem.
Imar
At 11:26 AM 9/14/2000 +0100, you wrote:
>In an Access 97 database being accessed using ASP (running on PWS)
and ADO I
>keep getting the error message;
>
>Microsoft Cursor Engine error '80040e38'
>The specified row could not be located for updating: Some values
may have
>been changed since it was last read.
>
>I think this is because I'm trying to change the information in the
database
>using rsRecords.Fields("field") = strNewvalue, and then using a
MoveNext to
>go to the next record.. How am I meant to update anything if the
script
>crashes when I put in a new value? (Its crashes on both MoveNext
and Update)
>
>Chris
>
>Chaos! Panic! Disaster! (My work here is done)
>Chris Neale. Web/Wap Developer
>Chris.neale@s...
<mailto:Chris.neale@s...>
>www.sparkresponse.co.uk
>
>
>
Message #7 by ckoski@w... on Thu, 14 Sep 2000 09:13:20 -0400
|
|
My comments were meant that you can use SQL statements to talk to an Access
DB via ADO. You don't need SQL Server to do that...
----- Original Message -----
From: "Chris Neale" <Chris.Neale@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, September 14, 2000 8:55 AM
Subject: [asp_databases] Re: Database Update
> Trust me, I'd much rather do this using SQL, unfortunately its having to
be
> done in Access coz its for our intranet and the box running the server
isn't
> even slightly up to hosting SQL Server.
>
> As it is, if I comment all the lines that change the data coming out of
the
> recordset the recordset.update statement doesn't give an error. Only if I
> actually alter something. And even then it will change the first record.
> It's a right pain..
>
> Chris
>
> Chaos! Panic! Disaster! (My work here is done)
> Chris Neale. Web/Wap Developer
> Chris.neale@s... <mailto:Chris.neale@s...>
> www.sparkresponse.co.uk
>
> -----Original Message-----
> From: ckoski@w... [SMTP:ckoski@w...]
> Sent: Thursday, September 14, 2000 1:42 PM
> To: ASP Databases
> Subject: [asp_databases] Re: Database Update
>
> Chris,
>
> have you considered trying SQL via ADO? it's not as object oriented
> as the
> method you are using, but issuing an UPDATE sql statement works with
> less
> headaches... unless you don't actually know SQL, then you'll need to
> learn
> that first.
>
> Cory
>
> ----- Original Message -----
> From: "Chris Neale" <Chris.Neale@s...>
> To: "ASP Databases" <asp_databases@p...>
> Sent: Thursday, September 14, 2000 6:26 AM
> Subject: [asp_databases] Database Update
>
>
> > In an Access 97 database being accessed using ASP (running on PWS)
> and ADO
> I
> > keep getting the error message;
> >
> > Microsoft Cursor Engine error '80040e38'
> > The specified row could not be located for updating: Some values
> may have
> > been changed since it was last read.
> >
> > I think this is because I'm trying to change the information in
> the
> database
> > using rsRecords.Fields("field") = strNewvalue, and then using a
> MoveNext
> to
> > go to the next record.. How am I meant to update anything if the
> script
> > crashes when I put in a new value? (Its crashes on both MoveNext
> and
> Update)
> >
> > Chris
> >
> > Chaos! Panic! Disaster! (My work here is done)
> > Chris Neale. Web/Wap Developer
> > Chris.neale@s...
> <mailto:Chris.neale@s...>
> > www.sparkresponse.co.uk
> >
> >
>
>
>
>
Message #8 by Chris Neale <Chris.Neale@s...> on Thu, 14 Sep 2000 14:15:31 +0100
|
|
Woohoo! Fixed the problem..
Fix:
Change connection string from the proper Access driver
(conChris.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" &
"Dbq=d:\inetpub\wwwroot\roombookings.mdb;" & "Uid=Admin;" & "Pwd=;") to the
MS Jet driver instead (
conChris.open("Provider=Microsoft.Jet.OLEDB.4.0.0;Data Source=" &
Server.MapPath("roombookings.mdb"))). Quite why the Access driver doesn't
give you a proper interface to an Access database is beyond me..
Chris
Chaos! Panic! Disaster! (My work here is done)
Chris Neale. Web/Wap Developer
Chris.neale@s... <mailto:Chris.neale@s...>
www.sparkresponse.co.uk
-----Original Message-----
From: Imar Spaanjaars [SMTP:Imar@S...]
Sent: Thursday, September 14, 2000 1:48 PM
To: ASP Databases
Subject: [asp_databases] Re: Database Update
Are you supplying the right datatype for the field?? Remember, VB
script
uses variants, but the database expects a specific type.
Otherwise, posting some code is always a quick way to solve any
problem.
Imar
At 11:26 AM 9/14/2000 +0100, you wrote:
>In an Access 97 database being accessed using ASP (running on PWS)
and ADO I
>keep getting the error message;
>
>Microsoft Cursor Engine error '80040e38'
>The specified row could not be located for updating: Some values
may have
>been changed since it was last read.
>
>I think this is because I'm trying to change the information in the
database
>using rsRecords.Fields("field") = strNewvalue, and then using a
MoveNext to
>go to the next record.. How am I meant to update anything if the
script
>crashes when I put in a new value? (Its crashes on both MoveNext
and Update)
>
>Chris
>
>Chaos! Panic! Disaster! (My work here is done)
>Chris Neale. Web/Wap Developer
>Chris.neale@s...
<mailto:Chris.neale@s...>
>www.sparkresponse.co.uk
>
>
>
Message #9 by Imar Spaanjaars <Imar@S...> on Thu, 14 Sep 2000 15:18:23 +0200
|
|
You might have already checked this, but I am just checking whether you
checked or not ;-)
Is your recordset .EOF? I guess not, but you should check this
Does Request.Form("reason") have a value?? If not, does the column reason
allow nulls or empty strings??
Did you try rsModify.fields(reason").value?? This shouldn't make a
difference, but still, you never know.
Imar
At 02:06 PM 9/14/2000 +0100, you wrote:
>All the data types are fine.
>
> set rsModify = server.CreateObject ("ADODB.Recordset")
> rsModify.CursorLocation = adUseClient
> rsModify.Open "Select * from BOOKINGS where ser_num=" &
>Request("bookingID"),conChris,adOpenKeyset,adLockOptimistic
>
> if len(trim(Request.Form("update"))) > 0 then
> rsModify.fields("reason")
>trim(Request.Form("reason"))
> rsModify.Update
> end if
>
>If I comment out the rsModify line it doesn't give an error..
Message #10 by Chris Neale <Chris.Neale@s...> on Thu, 14 Sep 2000 14:38:43 +0100
|
|
Umm.. yeah.. I know. I meant that we can't use SQL server. The Access/ADO
thing is just something annoying that I have to suffer for a while. As it is
it was the driver that was throwing me. See a different post.
Chris
Chaos! Panic! Disaster! (My work here is done)
Chris Neale. Web/Wap Developer
Chris.neale@s... <mailto:Chris.neale@s...>
www.sparkresponse.co.uk
-----Original Message-----
From: Mark Morgan [SMTP:mark.morgan@v...]
Sent: Thursday, September 14, 2000 1:59 PM
To: ASP Databases
Subject: [asp_databases] Re: Database Update
Chris,
You can still use SQL and ADO with Access, I think SQL is a
better way to
go since you can port the code to run against any relational
database with
just a few tweaks if you dump Access in the future.
Mark.
-----Original Message-----
From: Chris Neale [mailto:Chris.Neale@s...]
Sent: 14 September 2000 13:56
To: ASP Databases
Subject: [asp_databases] Re: Database Update
Trust me, I'd much rather do this using SQL, unfortunately its
having to be
done in Access coz its for our intranet and the box running the
server isn't
even slightly up to hosting SQL Server.
As it is, if I comment all the lines that change the data coming out
of the
recordset the recordset.update statement doesn't give an error. Only
if I
actually alter something. And even then it will change the first
record.
It's a right pain..
Chris
Chaos! Panic! Disaster! (My work here is done)
Chris Neale. Web/Wap Developer
Chris.neale@s...
<mailto:Chris.neale@s...>
www.sparkresponse.co.uk
-----Original Message-----
From: ckoski@w... [SMTP:ckoski@w...]
Sent: Thursday, September 14, 2000 1:42 PM
To: ASP Databases
Subject: [asp_databases] Re: Database Update
Chris,
have you considered trying SQL via ADO? it's not as object
oriented
as the
method you are using, but issuing an UPDATE sql statement
works with
less
headaches... unless you don't actually know SQL, then you'll
need to
learn
that first.
Cory
----- Original Message -----
From: "Chris Neale" <Chris.Neale@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, September 14, 2000 6:26 AM
Subject: [asp_databases] Database Update
> In an Access 97 database being accessed using ASP (running
on PWS)
and ADO
I
> keep getting the error message;
>
> Microsoft Cursor Engine error '80040e38'
> The specified row could not be located for updating: Some
values
may have
> been changed since it was last read.
>
> I think this is because I'm trying to change the
information in
the
database
> using rsRecords.Fields("field") = strNewvalue, and then
using a
MoveNext
to
> go to the next record.. How am I meant to update anything
if the
script
> crashes when I put in a new value? (Its crashes on both
MoveNext
and
Update)
>
> Chris
>
> Chaos! Panic! Disaster! (My work here is done)
> Chris Neale. Web/Wap Developer
> Chris.neale@s...
<mailto:Chris.neale@s...>
> www.sparkresponse.co.uk
>
Message #11 by Chris Neale <Chris.Neale@s...> on Thu, 14 Sep 2000 15:02:15 +0100
|
|
What else would you use? As far as I know SQL statements are the only way to
get stuff out of an ADO connection? Can you use, for example, QBE instead?
Chris
Chaos! Panic! Disaster! (My work here is done)
Chris Neale. Web/Wap Developer
Chris.neale@s... <mailto:Chris.neale@s...>
www.sparkresponse.co.uk
-----Original Message-----
From: ckoski@w... [SMTP:ckoski@w...]
Sent: Thursday, September 14, 2000 2:13 PM
To: ASP Databases
Subject: [asp_databases] Re: Database Update
My comments were meant that you can use SQL statements to talk to an
Access
DB via ADO. You don't need SQL Server to do that...
----- Original Message -----
From: "Chris Neale" <Chris.Neale@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, September 14, 2000 8:55 AM
Subject: [asp_databases] Re: Database Update
> Trust me, I'd much rather do this using SQL, unfortunately its
having to
be
> done in Access coz its for our intranet and the box running the
server
isn't
> even slightly up to hosting SQL Server.
>
> As it is, if I comment all the lines that change the data coming
out of
the
> recordset the recordset.update statement doesn't give an error.
Only if I
> actually alter something. And even then it will change the first
record.
> It's a right pain..
>
> Chris
>
> Chaos! Panic! Disaster! (My work here is done)
> Chris Neale. Web/Wap Developer
> Chris.neale@s...
<mailto:Chris.neale@s...>
> www.sparkresponse.co.uk
>
> -----Original Message-----
> From: ckoski@w... [SMTP:ckoski@w...]
> Sent: Thursday, September 14, 2000 1:42 PM
> To: ASP Databases
> Subject: [asp_databases] Re: Database Update
>
> Chris,
>
> have you considered trying SQL via ADO? it's not as object
oriented
> as the
> method you are using, but issuing an UPDATE sql statement works
with
> less
> headaches... unless you don't actually know SQL, then you'll need
to
> learn
> that first.
>
> Cory
>
> ----- Original Message -----
> From: "Chris Neale" <Chris.Neale@s...>
> To: "ASP Databases" <asp_databases@p...>
> Sent: Thursday, September 14, 2000 6:26 AM
> Subject: [asp_databases] Database Update
>
>
> > In an Access 97 database being accessed using ASP (running on
PWS)
> and ADO
> I
> > keep getting the error message;
> >
> > Microsoft Cursor Engine error '80040e38'
> > The specified row could not be located for updating: Some values
> may have
> > been changed since it was last read.
> >
> > I think this is because I'm trying to change the information in
> the
> database
> > using rsRecords.Fields("field") = strNewvalue, and then using a
> MoveNext
> to
> > go to the next record.. How am I meant to update anything if the
> script
> > crashes when I put in a new value? (Its crashes on both MoveNext
> and
> Update)
> >
> > Chris
> >
> > Chaos! Panic! Disaster! (My work here is done)
> > Chris Neale. Web/Wap Developer
> > Chris.neale@s...
> <mailto:Chris.neale@s...>
> > www.sparkresponse.co.uk
> >
> >
>
>
>
>
Message #12 by Imar Spaanjaars <Imar@S...> on Thu, 14 Sep 2000 16:30:13 +0200
|
|
I think what he means is that you can use SQL statements to update and
delete records as well.
Take this example:
Dim sSQL
Dim objUpdate
sSQL = UPDATE aTable SET aColumn = '" & sAValue & "' WHERE aColumnID = " &
Request.Form("txtID")
Set objUpdate = Server.CreateObject("ADODB.Command")
objUpdate.ActiveConnection = Application("securityLayer_ConnectionString")
objUpdate.CommandText = sSQL
objUpdate.Execute,, adCmdText OR adExecuteNorecords
Set objUpdate = Nothing
The benefit of this method is that it performs way faster than using the
AddNew and Update methods of an ADO recordset.
Even faster would be to call a stored procedure with some parameters.
Check out
http://msdn.microsoft.com/library/psdk/dasdk/impr8l2m.htm
The section "Use Cursor-Based Updating Only If Needed" is what you might be
interested in.
Also on www.asptoday.com there are some excellent articles related to SQL /
ADO performance.
Hope this helps.
Imar
At 03:02 PM 9/14/2000 +0100, you wrote:
>What else would you use? As far as I know SQL statements are the only way to
>get stuff out of an ADO connection? Can you use, for example, QBE instead?
>
>Chris
|
|
|
View Cart
