How to know if UPDATE, INSERT or DELETE succeed

[Serge Myrand]

Hi,

Suppose that, "Set Rs = cmd.Execute" perform a DELETE command. How
can'I check if the command succeed without to requery the table seeking
for the involved record?

Rs.GetRows result in an error message like ' cannot operate on a closed
object
Where is the number of DELETE, UPDATED or INSERTED record?

thank's in advance
serge

[Serge Myrand]

Hi,

Here is what I did. It works find for all commands DELETE, UPDATE and INSERT.
The following is the DELETE routine. I use Access 2000 with ADO 2.7 and JET
4.0 sp8 . I put all the SQL in my ASP files. I just want to know how to get
the result of the command. thank's

sub DeleteBasketItems
dim Conn
dim Cmd
dim Rs
dim sSQL
dim prm
sSQL = "DELETE * FROM BASKET " & _
"WHERE CLIENT_NO=? AND BASKET_NO = 0"
set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString="Provider=Microsoft.Jet.OLED B.4.0;Data Source=" & _
Server.MapPath("Data\WEB_PROD.mdb") & ";Persist Security
Info=True"
conn.CursorLocation = adUseClient
conn.Open
set cmd= Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = Conn
cmd.CommandText = sSQL
cmd.CommandType = adCmdText
set prm = cmd.CreateParameter("@prmCustNo", adChar, adParamInput, 13)
cmd.Parameters.Append prm
cmd.Parameters("@prmCustNo").Value = Session("ClientNo")
Set Rs = cmd.Execute
end sub

"Aaron [SQL Server MVP]" wrote:

> > Suppose that, "Set Rs = cmd.Execute" perform a DELETE command.

>
> Why on earth would you use a recordset for that?
>

> > How
> > can'I check if the command succeed without to requery the table seeking
> > for the involved record?

>
> If you are using SQL Server (you forgot to tell us what product you're
> using), you can use a stored procedure to perform the delete, then check
> @@ROWCOUNT.
>
> You should NOT be looping through a recordset object to perform updates or
> deletes.
>
> --
> [url]http://www.aspfaq.com/[/url]
> (Reverse address to reply.)

[Aaron [SQL Server MVP]]

> Suppose that, "Set Rs = cmd.Execute" perform a DELETE command.

Why on earth would you use a recordset for that?

> How
> can'I check if the command succeed without to requery the table seeking
> for the involved record?

If you are using SQL Server (you forgot to tell us what product you're
using), you can use a stored procedure to perform the delete, then check
@@ROWCOUNT.

You should NOT be looping through a recordset object to perform updates or
deletes.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Serge Myrand]

Thank you very much, I am learning all this stuff and you guys are very kinds
and helpfull. It's a big change in language for me.

serge

"Bob Barrows [MVP]" wrote:

> Serge Myrand wrote:

> > Hi,
> >
> > Here is what I did. It works find for all commands DELETE, UPDATE and
> > INSERT.

> <snip>

> > Set Rs = cmd.Execute

>
> And here is what you should have done:
>
> dim lRecs
> cmd.Execute lRecs,,128
> Response.Write lRecs & " records were affected by this query"
>
> As Aaron said, it is very wasteful of resources to create a recordset when
> the query you are running is not intended to return records.
>
> The "128" argument (adExecuteNoRecords) tells the Command object not to
> build a recordset object behind the scenes when the Execute command is,
> well, er, executed. :-)
>
> HTH,
> 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.

[Bob Barrows [MVP]]

Serge Myrand wrote:

> Hi,
>
> Here is what I did. It works find for all commands DELETE, UPDATE and
> INSERT.

<snip>

> Set Rs = cmd.Execute

And here is what you should have done:

dim lRecs
cmd.Execute lRecs,,128
Response.Write lRecs & " records were affected by this query"

As Aaron said, it is very wasteful of resources to create a recordset when
the query you are running is not intended to return records.

The "128" argument (adExecuteNoRecords) tells the Command object not to
build a recordset object behind the scenes when the Execute command is,
well, er, executed. :-)

HTH,
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.

[David C. Holley]

I might be mistaken, but is it safe to say that if an error is NOT
thrown, the command succeeded?

David H

Serge Myrand wrote:

> Hi,
>
> Suppose that, "Set Rs = cmd.Execute" perform a DELETE command. How
> can'I check if the command succeed without to requery the table seeking
> for the involved record?
>
> Rs.GetRows result in an error message like ' cannot operate on a closed
> object
> Where is the number of DELETE, UPDATED or INSERTED record?
>
> thank's in advance
> serge
>

[Bob Barrows [MVP]]

No.
If no records match the criteria provided for the operation, the operation
will not throw an error, but the operation will not affect any records.

Bob Barrows

David C. Holley wrote:

> I might be mistaken, but is it safe to say that if an error is NOT
> thrown, the command succeeded?
>
> David H
>
> Serge Myrand wrote:

>> Hi,
>>
>> Suppose that, "Set Rs = cmd.Execute" perform a DELETE command. How
>> can'I check if the command succeed without to requery the table
>> seeking for the involved record?
>>
>> Rs.GetRows result in an error message like ' cannot operate on a
>> closed object
>> Where is the number of DELETE, UPDATED or INSERTED record?
>>
>> thank's in advance
>> serge

--
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"

[Aaron [SQL Server MVP]]

>I might be mistaken, but is it safe to say that if an error is NOT thrown,

>the command succeeded?

For an INSERT, probably. For the others, it really depends on how you
define "succeeded". Consider these statements:

UPDATE tbl SET col='blat' WHERE 1 = 2

DELETE tbl WHERE 1 = 2

INSERT tbl2(col) SELECT col FROM tbl1 WHERE 1 = 2

None of these statements generated an error, but none of them affected any
rows, either.

A