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=products_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.


.

Relevant Pages

  • Re: Query-based update failed because the row to update could not be found
    ... > pessimistic locking in ASP applications. ... >> Dim Conn, connection, rsProd ... > eliminate these lines (no recordset is needed): ... > quicker response by posting to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Temporary table with SQL Server 7.0
    ... Francois Houde wrote: ... access it with a recordset, ... Unable to use this connection to do this operation. ... Please reply to the newsgroup. ...
    (microsoft.public.data.ado)
  • Re: VB.NET ADO Recordset DataGridView and Access not in sync w/oth
    ... I had it mostly written in ADO.NET BEFORE I decided Recordset ... certain conditions it will change a field in the database. ... a classic COM ADODB connection and recordset via Interop, ... but this is a classic ADO newsgroup. ...
    (microsoft.public.data.ado)
  • Re: ADO recordset doesnt see DB changes
    ... Just wanted to let you know that by executing a Resyncof recordset 2, ... The way my app is set up there is only 1 connection path to ... the DB (that is I only open the database once) which both recordsets ... Please reply to the newsgroup. ...
    (microsoft.public.data.ado)
  • Re: Updating Access data using SQL / refresh time question
    ... As a test today, for one poarticular recordset, I changed from DAO to ADO to ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ...
    (microsoft.public.vb.database)