Query-based update failed because the row to update could not be found

I am getting the following error on the rsProd.Update line at the end of the
sample code I have provided.

"Query-based update failed because the row to update could not be found"
I have tried everything I could think of but cannot solve this problem. can
anyone help? I am new to MYSQL and I must be missing something.



Dim Conn, connection. rsProd, intQuant

set Conn = Server.CreateObject("ADODB.Connection")
Conn.CursorLocation = adUseServer

connection =
"driver={MySQL};server=webhostserver;database=products_datab ase;uid=user;pwd
=password;option=32"
Conn.open (connection)

set rsProd = Server.CreateObject("ADODB.Recordset")
rsProd.CursorLocation = adUseServer

rsProd.Open "SELECT * FROM itemsOrdered WHERE orderID="& intOrderID, _
Conn, adOpenDynamic, adLockPessimistic, adCmdText

if intQuant <> "" then
if intQuant = 0 then
rsProd.Delete
else
rsProd("quantity") = intQuant
end if
end if

rsProd.Update
rsProd.MoveNext

Re: Query-based update failed because the row to update could not be found

#1. DO NOT use "Select * ..."

http://aspfaq.com/show.asp?id=3D2096

#2. Check for EOF

rsProd.Open "SELECT * FROM itemsOrdered WHERE orderID=3D"& intOrderID, _
Conn, adOpenDynamic, adLockPessimistic, adCmdText
If rsProd.EOF Then
Response.Write "Error: Record Does Not Exist": _
rsProd.Close: Response.End
else
if intQuant <> "" then
if intQuant =3D 0 then
rsProd.Delete
else
rsProd("quantity") =3D intQuant
end if
end if
rsProd.Update
end if
rsProd.close
--=20
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"Craig" wrote in message =
news:UeydnSYTU_qS7sTfRVn-tQ@golden.net...
> I am getting the following error on the rsProd.Update line at the end =
of the
> sample code I have provided.
>=20
> "Query-based update failed because the row to update could not be =
found"
> I have tried everything I could think of but cannot solve this =
problem. can
> anyone help? I am new to MYSQL and I must be missing something.
>=20
>=20
>=20
> Dim Conn, connection. rsProd, intQuant
>=20
> set Conn =3D Server.CreateObject("ADODB.Connection")
> Conn.CursorLocation =3D adUseServer
>=20
> connection =3D
> =
"driver=3D{MySQL};server=3Dwebhostserver;database=3Dproducts _database;uid=
=3Duser;pwd
> =3Dpassword;option=3D32"
> Conn.open (connection)
>=20
> set rsProd =3D Server.CreateObject("ADODB.Recordset")
> rsProd.CursorLocation =3D adUseServer
>=20
> rsProd.Open "SELECT * FROM itemsOrdered WHERE orderID=3D"& intOrderID, =
_
> Conn, adOpenDynamic, adLockPessimistic, adCmdText
>=20
> if intQuant <> "" then
> if intQuant =3D 0 then
> rsProd.Delete
> else
> rsProd("quantity") =3D intQuant
> end if
> end if
>=20
> rsProd.Update
> rsProd.MoveNext
>=20
>=20

Re: Query-based update failed because the row to update could not be found

Still doesn't work, even after doing the check for EOF.

Anyone have any other suggestion?


"Steven Burn" wrote in message
news:%23FPeg7fPFHA.2356@TK2MSFTNGP14.phx.gbl...
#1. DO NOT use "Select * ..."

http://aspfaq.com/show.asp?id=2096

#2. Check for EOF

rsProd.Open "SELECT * FROM itemsOrdered WHERE orderID="& intOrderID, _
Conn, adOpenDynamic, adLockPessimistic, adCmdText
If rsProd.EOF Then
Response.Write "Error: Record Does Not Exist": _
rsProd.Close: Response.End
else
if intQuant <> "" then
if intQuant = 0 then
rsProd.Delete
else
rsProd("quantity") = intQuant
end if
end if
rsProd.Update
end if
rsProd.close
--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"Craig" wrote in message
news:UeydnSYTU_qS7sTfRVn-tQ@golden.net...
> I am getting the following error on the rsProd.Update line at the end of
the
> sample code I have provided.
>
> "Query-based update failed because the row to update could not be found"
> I have tried everything I could think of but cannot solve this problem.
can
> anyone help? I am new to MYSQL and I must be missing something.
>
>
>
> Dim Conn, connection. rsProd, intQuant
>
> set Conn = Server.CreateObject("ADODB.Connection")
> Conn.CursorLocation = adUseServer
>
> connection =
>
"driver={MySQL};server=webhostserver;database=products_datab ase;uid=user;pwd
> =password;option=32"
> Conn.open (connection)
>
> set rsProd = Server.CreateObject("ADODB.Recordset")
> rsProd.CursorLocation = adUseServer
>
> rsProd.Open "SELECT * FROM itemsOrdered WHERE orderID="& intOrderID, _
> Conn, adOpenDynamic, adLockPessimistic, adCmdText
>
> if intQuant <> "" then
> if intQuant = 0 then
> rsProd.Delete
> else
> rsProd("quantity") = intQuant
> end if
> end if
>
> rsProd.Update
> rsProd.MoveNext
>
>

Re: Query-based update failed because the row to update could not be found

Craig wrote:
> I am getting the following error on the rsProd.Update line at the end
> of the sample code I have provided.
>
> "Query-based update failed because the row to update could not be
> found"


Have you defined a primary key on your itemsOrdered table? The lack of a
primary key is the most common cause of this error.

You can avoid this error completely by using sql to update your table
instead of a cursor*:

dim conn, cmd, sSQL, arParms

if intQuant <> "" then
if intQuant = 0 then
sSQL = "DELETE FROM itemsOrdered " & _
"WHERE orderID = ?"
arParms = Array(intOrderID)
else
sSQL = "UPDATE itemsOrdered SET quantity =? " & _

"WHERE orderID = ?"
arParms = Array(intQuant, intOrderID)
end if

conn.open ...
set cmd=createobject("adodb.command")
cmd.CommandText= sSQL
cmd.Execute ,arParms,129
set cmd=nothing
conn.close:set conn=nothing
end if


HTH,
Bob Barrows
*In ASP, recordsets (cursors) should only be used to retrieve read-only data
for display. All data modifications should be done via sql queries.


--
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: Query-based update failed because the row to update could not be found

Yes, the table does have a primary key.

After applying the code suggested...using the array...I now get the
following error message:

The connection cannot be used to perform this operation. It is either closed
or invalid in this context.


"Bob Barrows [MVP]" wrote in message
news:uM6661gPFHA.3184@TK2MSFTNGP10.phx.gbl...
> Craig wrote:
> > I am getting the following error on the rsProd.Update line at the end
> > of the sample code I have provided.
> >
> > "Query-based update failed because the row to update could not be
> > found"
>
>
> Have you defined a primary key on your itemsOrdered table? The lack of a
> primary key is the most common cause of this error.
>
> You can avoid this error completely by using sql to update your table
> instead of a cursor*:
>
> dim conn, cmd, sSQL, arParms
>
> if intQuant <> "" then
> if intQuant = 0 then
> sSQL = "DELETE FROM itemsOrdered " & _
> "WHERE orderID = ?"
> arParms = Array(intOrderID)
> else
> sSQL = "UPDATE itemsOrdered SET quantity =? " & _
>
> "WHERE orderID = ?"
> arParms = Array(intQuant, intOrderID)
> end if
>
> conn.open ...
> set cmd=createobject("adodb.command")
> cmd.CommandText= sSQL
> cmd.Execute ,arParms,129
> set cmd=nothing
> conn.close:set conn=nothing
> end if
>
>
> HTH,
> Bob Barrows
> *In ASP, recordsets (cursors) should only be used to retrieve read-only
data
> for display. All data modifications should be done via sql queries.
>
>
> --
> 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: Query-based update failed because the row to update could not be found

I forgot to include the line

Set cmd.ActiveConnection = Conn

It should go just before the cmd.Execute ... line.


My Bad
Bob Barrows

Craig wrote:
> Yes, the table does have a primary key.
>
> After applying the code suggested...using the array...I now get the
> following error message:
>
> The connection cannot be used to perform this operation. It is either
> closed or invalid in this context.
>
>
> "Bob Barrows [MVP]" wrote in message
> news:uM6661gPFHA.3184@TK2MSFTNGP10.phx.gbl...
>> Craig wrote:
>>> I am getting the following error on the rsProd.Update line at the
>>> end
>>> of the sample code I have provided.
>>>
>>> "Query-based update failed because the row to update could not be
>>> found"
>>
>>
>> Have you defined a primary key on your itemsOrdered table? The lack
>> of a primary key is the most common cause of this error.
>>
>> You can avoid this error completely by using sql to update your table
>> instead of a cursor*:
>>
>> dim conn, cmd, sSQL, arParms
>>
>> if intQuant <> "" then
>> if intQuant = 0 then
>> sSQL = "DELETE FROM itemsOrdered " & _
>> "WHERE orderID = ?"
>> arParms = Array(intOrderID)
>> else
>> sSQL = "UPDATE itemsOrdered SET quantity =? " & _
>>
>> "WHERE orderID = ?"
>> arParms = Array(intQuant, intOrderID)
>> end if
>>
>> conn.open ...
>> set cmd=createobject("adodb.command")
>> cmd.CommandText= sSQL
>> cmd.Execute ,arParms,129
>> set cmd=nothing
>> conn.close:set conn=nothing
>> end if
>>
>>
>> HTH,
>> Bob Barrows
>> *In ASP, recordsets (cursors) should only be used to retrieve
>> read-only data for display. All data modifications should be done
>> via sql queries.
>>
>>
>> --
>> 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"

--
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: Query-based update failed because the row to update could not be found

Thanks Bob.

Unfortunately now I get another error: Unknown column 'intQuant' in 'field
list'

So far my research has come up blank on why this error is occuring


"Bob Barrows [MVP]" wrote in message
news:ePu9hvoPFHA.1088@TK2MSFTNGP14.phx.gbl...
> I forgot to include the line
>
> Set cmd.ActiveConnection = Conn
>
> It should go just before the cmd.Execute ... line.
>
>
> My Bad
> Bob Barrows
>
> Craig wrote:
> > Yes, the table does have a primary key.
> >
> > After applying the code suggested...using the array...I now get the
> > following error message:
> >
> > The connection cannot be used to perform this operation. It is either
> > closed or invalid in this context.
> >
> >
> > "Bob Barrows [MVP]" wrote in message
> > news:uM6661gPFHA.3184@TK2MSFTNGP10.phx.gbl...
> >> Craig wrote:
> >>> I am getting the following error on the rsProd.Update line at the
> >>> end
> >>> of the sample code I have provided.
> >>>
> >>> "Query-based update failed because the row to update could not be
> >>> found"
> >>
> >>
> >> Have you defined a primary key on your itemsOrdered table? The lack
> >> of a primary key is the most common cause of this error.
> >>
> >> You can avoid this error completely by using sql to update your table
> >> instead of a cursor*:
> >>
> >> dim conn, cmd, sSQL, arParms
> >>
> >> if intQuant <> "" then
> >> if intQuant = 0 then
> >> sSQL = "DELETE FROM itemsOrdered " & _
> >> "WHERE orderID = ?"
> >> arParms = Array(intOrderID)
> >> else
> >> sSQL = "UPDATE itemsOrdered SET quantity =? " & _
> >>
> >> "WHERE orderID = ?"
> >> arParms = Array(intQuant, intOrderID)
> >> end if
> >>
> >> conn.open ...
> >> set cmd=createobject("adodb.command")
> >> cmd.CommandText= sSQL
> >> cmd.Execute ,arParms,129
> >> set cmd=nothing
> >> conn.close:set conn=nothing
> >> end if
> >>
> >>
> >> HTH,
> >> Bob Barrows
> >> *In ASP, recordsets (cursors) should only be used to retrieve
> >> read-only data for display. All data modifications should be done
> >> via sql queries.
> >>
> >>
> >> --
> >> 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"
>
> --
> 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: Query-based update failed because the row to update could not be found

Show me your new code. I did not include the word "intQuant" in either sql
statement I suggested. Just to verify, the code should look like this:

dim conn, cmd, sSQL, arParms

if intQuant <> "" then
'slight improvement:
intQuant = CInt(intQuant)
if intQuant = 0 then
sSQL = "DELETE FROM itemsOrdered " & _
"WHERE orderID = ?"
arParms = Array(intOrderID)
else
sSQL = "UPDATE itemsOrdered SET quantity =? " & _
"WHERE orderID = ?"
arParms = Array(intQuant, intOrderID)
end if

conn.open ...
set cmd=createobject("adodb.command")
cmd.CommandText= sSQL
Set cmd.ActiveConnection = conn
cmd.Execute ,arParms,129
set cmd=nothing
conn.close:set conn=nothing
end if

Bob Barrows

Craig wrote:
> Thanks Bob.
>
> Unfortunately now I get another error: Unknown column 'intQuant' in
> 'field list'
>
> So far my research has come up blank on why this error is occuring
>
>
> "Bob Barrows [MVP]" wrote in message
> news:ePu9hvoPFHA.1088@TK2MSFTNGP14.phx.gbl...
>> I forgot to include the line
>>
>> Set cmd.ActiveConnection = Conn
>>
>> It should go just before the cmd.Execute ... line.
>>
>>
>> My Bad
>> Bob Barrows
>>
>> Craig wrote:
>>> Yes, the table does have a primary key.
>>>
>>> After applying the code suggested...using the array...I now get the
>>> following error message:
>>>
>>> The connection cannot be used to perform this operation. It is
>>> either closed or invalid in this context.
>>>
>>>
>>> "Bob Barrows [MVP]" wrote in message
>>> news:uM6661gPFHA.3184@TK2MSFTNGP10.phx.gbl...
>>>> Craig wrote:
>>>>> I am getting the following error on the rsProd.Update line at the
>>>>> end
>>>>> of the sample code I have provided.
>>>>>
>>>>> "Query-based update failed because the row to update could not be
>>>>> found"
>>>>
>>>>
>>>> Have you defined a primary key on your itemsOrdered table? The lack
>>>> of a primary key is the most common cause of this error.
>>>>
>>>> You can avoid this error completely by using sql to update your
>>>> table instead of a cursor*:
>>>>
>>>> dim conn, cmd, sSQL, arParms
>>>>
>>>> if intQuant <> "" then
>>>> if intQuant = 0 then
>>>> sSQL = "DELETE FROM itemsOrdered " & _
>>>> "WHERE orderID = ?"
>>>> arParms = Array(intOrderID)
>>>> else
>>>> sSQL = "UPDATE itemsOrdered SET quantity =? " & _
>>>> "WHERE orderID = ?"
>>>> arParms = Array(intQuant, intOrderID)
>>>> end if
>>>>
>>>> conn.open ...
>>>> set cmd=createobject("adodb.command")
>>>> cmd.CommandText= sSQL
>>>> cmd.Execute ,arParms,129
>>>> set cmd=nothing
>>>> conn.close:set conn=nothing
>>>> end if
>>>>
>>>>
>>>> HTH,
>>>> Bob Barrows
>>>> *In ASP, recordsets (cursors) should only be used to retrieve
>>>> read-only data for display. All data modifications should be done
>>>> via sql queries.
>>>>
>>>>
>>>> --
>>>> 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"
>>
>> --
>> 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"

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Query-based update failed because the row to update could not be found

Here's the current code. Error is happening on the cmd.Execute line:


Dim Conn, connection, rsProd

set Conn = Server.CreateObject("ADODB.Connection")
Conn.CursorLocation = adUseServer

connection =
"driver={MySQL};server=sqlc2.megasqlservers.com;database=pro ducts_premieres_
ca;uid=dbm.premieres.ca;pwd=roger;option=32"
Conn.open (connection)

set rsProd = Server.CreateObject("ADODB.Recordset")
rsProd.CursorLocation = adUseServer

rsProd.Open "SELECT tallyitems, orderID, productID, quantity FROM
itemsOrdered WHERE orderID="& intOrderID, _
Conn, adOpenDynamic, adLockPessimistic, adCmdText

Dim cmd, sSQL, arParms

if intQuant <> "" then
if intQuant = 0 then
sSQL = "DELETE FROM itemsOrdered " & _
"WHERE orderID = " & intOrderID
arParms = Array(intOrderID)
else
sSQL = "UPDATE itemsOrdered SET quantity = intQuant " & _
"WHERE orderID = " & intOrderID
arParms = Array(intQuant, intOrderID)
end if

set cmd=createobject("adodb.command")
cmd.CommandText= sSQL
Set cmd.ActiveConnection = Conn
cmd.Execute ,arParms,129
set conn=nothing
end if


"Bob Barrows [MVP]" wrote in message
news:%23ExtbKqPFHA.3704@TK2MSFTNGP12.phx.gbl...
> Show me your new code. I did not include the word "intQuant" in either sql
> statement I suggested. Just to verify, the code should look like this:
>
> dim conn, cmd, sSQL, arParms
>
> if intQuant <> "" then
> 'slight improvement:
> intQuant = CInt(intQuant)
> if intQuant = 0 then
> sSQL = "DELETE FROM itemsOrdered " & _
> "WHERE orderID = ?"
> arParms = Array(intOrderID)
> else
> sSQL = "UPDATE itemsOrdered SET quantity =? " & _
> "WHERE orderID = ?"
> arParms = Array(intQuant, intOrderID)
> end if
>
> conn.open ...
> set cmd=createobject("adodb.command")
> cmd.CommandText= sSQL
> Set cmd.ActiveConnection = conn
> cmd.Execute ,arParms,129
> set cmd=nothing
> conn.close:set conn=nothing
> end if
>
> Bob Barrows
>
> Craig wrote:
> > Thanks Bob.
> >
> > Unfortunately now I get another error: Unknown column 'intQuant' in
> > 'field list'
> >
> > So far my research has come up blank on why this error is occuring
> >
> >
> > "Bob Barrows [MVP]" wrote in message
> > news:ePu9hvoPFHA.1088@TK2MSFTNGP14.phx.gbl...
> >> I forgot to include the line
> >>
> >> Set cmd.ActiveConnection = Conn
> >>
> >> It should go just before the cmd.Execute ... line.
> >>
> >>
> >> My Bad
> >> Bob Barrows
> >>
> >> Craig wrote:
> >>> Yes, the table does have a primary key.
> >>>
> >>> After applying the code suggested...using the array...I now get the
> >>> following error message:
> >>>
> >>> The connection cannot be used to perform this operation. It is
> >>> either closed or invalid in this context.
> >>>
> >>>
> >>> "Bob Barrows [MVP]" wrote in message
> >>> news:uM6661gPFHA.3184@TK2MSFTNGP10.phx.gbl...
> >>>> Craig wrote:
> >>>>> I am getting the following error on the rsProd.Update line at the
> >>>>> end
> >>>>> of the sample code I have provided.
> >>>>>
> >>>>> "Query-based update failed because the row to update could not be
> >>>>> found"
> >>>>
> >>>>
> >>>> Have you defined a primary key on your itemsOrdered table? The lack
> >>>> of a primary key is the most common cause of this error.
> >>>>
> >>>> You can avoid this error completely by using sql to update your
> >>>> table instead of a cursor*:
> >>>>
> >>>> dim conn, cmd, sSQL, arParms
> >>>>
> >>>> if intQuant <> "" then
> >>>> if intQuant = 0 then
> >>>> sSQL = "DELETE FROM itemsOrdered " & _
> >>>> "WHERE orderID = ?"
> >>>> arParms = Array(intOrderID)
> >>>> else
> >>>> sSQL = "UPDATE itemsOrdered SET quantity =? " & _
> >>>> "WHERE orderID = ?"
> >>>> arParms = Array(intQuant, intOrderID)
> >>>> end if
> >>>>
> >>>> conn.open ...
> >>>> set cmd=createobject("adodb.command")
> >>>> cmd.CommandText= sSQL
> >>>> cmd.Execute ,arParms,129
> >>>> set cmd=nothing
> >>>> conn.close:set conn=nothing
> >>>> end if
> >>>>
> >>>>
> >>>> HTH,
> >>>> Bob Barrows
> >>>> *In ASP, recordsets (cursors) should only be used to retrieve
> >>>> read-only data for display. All data modifications should be done
> >>>> via sql queries.
> >>>>
> >>>>
> >>>> --
> >>>> 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"
> >>
> >> --
> >> 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"
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: Query-based update failed because the row to update could not be found

Why are you still opening a recordset? With pessimistic locking?? Avoid
pessimistic locking in ASP applications.

You did not read my code example carefully enough. (or you decided I had
done something wrong ... )

See below for the corrections:

Craig wrote:
> Here's the current code. Error is happening on the cmd.Execute line:
>
>
> Dim Conn, connection, rsProd
>
> set Conn = Server.CreateObject("ADODB.Connection")
> Conn.CursorLocation = adUseServer
>
> connection =
>
"driver={MySQL};server=sqlc2.megasqlservers.com;database=pro ducts_premieres_
> ca;uid=dbm.premieres.ca;pwd=roger;option=32"
> Conn.open (connection)
>

eliminate these lines (no recordset is needed):
************************************************************ *
> set rsProd = Server.CreateObject("ADODB.Recordset")
> rsProd.CursorLocation = adUseServer
>
> rsProd.Open "SELECT tallyitems, orderID, productID, quantity FROM
> itemsOrdered WHERE orderID="& intOrderID, _
> Conn, adOpenDynamic, adLockPessimistic, adCmdText
>
************************************************************

> Dim cmd, sSQL, arParms
>
> if intQuant <> "" then

Again, a slight improvement:
intQuant = CInt(intQuant)

> if intQuant = 0 then
> sSQL = "DELETE FROM itemsOrdered " & _
> "WHERE orderID = " & intOrderID

This should read:
"WHERE orderID = ?"

> arParms = Array(intOrderID)
> else
> sSQL = "UPDATE itemsOrdered SET quantity = intQuant "
> & _ "WHERE orderID = " & intOrderID

This should read:
sSQL = "UPDATE itemsOrdered SET quantity = ?"
& _ "WHERE orderID = ?"

The question marks are called "parameter placeholders". The values in the
arParms array will be substituted for the placeholders when the command is
executed.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Query-based update failed because the row to update could not be found

Sorry, I did not read the code fully. I have it working now...Thanks a bunch


"Bob Barrows [MVP]" wrote in message
news:OdGn8wqPFHA.1564@TK2MSFTNGP14.phx.gbl...
> Why are you still opening a recordset? With pessimistic locking?? Avoid
> pessimistic locking in ASP applications.
>
> You did not read my code example carefully enough. (or you decided I had
> done something wrong ... )
>
> See below for the corrections:
>
> Craig wrote:
> > Here's the current code. Error is happening on the cmd.Execute line:
> >
> >
> > Dim Conn, connection, rsProd
> >
> > set Conn = Server.CreateObject("ADODB.Connection")
> > Conn.CursorLocation = adUseServer
> >
> > connection =
> >
>
"driver={MySQL};server=sqlc2.megasqlservers.com;database=pro ducts_premieres_
> > ca;uid=dbm.premieres.ca;pwd=roger;option=32"
> > Conn.open (connection)
> >
>
> eliminate these lines (no recordset is needed):
> ************************************************************ *
> > set rsProd = Server.CreateObject("ADODB.Recordset")
> > rsProd.CursorLocation = adUseServer
> >
> > rsProd.Open "SELECT tallyitems, orderID, productID, quantity FROM
> > itemsOrdered WHERE orderID="& intOrderID, _
> > Conn, adOpenDynamic, adLockPessimistic, adCmdText
> >
> ************************************************************
>
> > Dim cmd, sSQL, arParms
> >
> > if intQuant <> "" then
>
> Again, a slight improvement:
> intQuant = CInt(intQuant)
>
> > if intQuant = 0 then
> > sSQL = "DELETE FROM itemsOrdered " & _
> > "WHERE orderID = " & intOrderID
>
> This should read:
> "WHERE orderID = ?"
>
> > arParms = Array(intOrderID)
> > else
> > sSQL = "UPDATE itemsOrdered SET quantity = intQuant "
> > & _ "WHERE orderID = " & intOrderID
>
> This should read:
> sSQL = "UPDATE itemsOrdered SET quantity = ?"
> & _ "WHERE orderID = ?"
>
> The question marks are called "parameter placeholders". The values in the
> arParms array will be substituted for the placeholders when the command is
> executed.
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>