SQL DELETE STATEMENT problem with returned rowcount

Question

I have a statement like this:

DELETE TABLENAME WHERE COLUMN = NUMBER; SELECT @@ROWCOUNT AS RC;

if i run it from the code (classic asp) i get Error:Item cannot be found in the collection corresponding to the requested name or ordinal.

the DB is sql server 2005 express.

if i run this line in query analyzer it works!

the column i refer to has a constraint to another table in the db

do you guys know where to stat looking for the fix for it?

UPDATE - please try to explain this now

the first line is the connection string we had on the regular servers, when we needed to upload the code to a new server with sql 2005 express we changed to the second one cause the first one didn't work for us (i tried in the servername local ip: 127.0.0.1) and the second was the actual server name. now i have changed the first one to connect to the server name and it works. even deleteing and retreving the @@rowcount. but now it feels like a slower connection so the website work slow!

any idea for making it better?

ConnectionString =  "Server=**ServerName**;Driver={SQL Server};UID=**Username**; PWD=**password**;database=**dbname**;Data Provider=SQLOLEDB;Network Library=DBMSSOCN;"

ConnectionString = "PROVIDER=SQLOLEDB; Data Source =**servername**; Trusted_Connection=Yes; Initial Catalog=**dbname**;User Id=**username**; Password=**password**;"

Can you post the ASP code, this error generally means that a field cannot be found in the data that came back from the query — Sparky, Sep 28 '11 at 05:04
I can't because it is part of alot of functions and classes. anyway i tried retriving the @@rowcount (without the as part) so it will be just column 0 but didn't work — Y.G.J, Sep 28 '11 at 05:12

score 2 · Answer 1 · answered Sep 28 '11 at 06:00

2

If you want to count the number of affected rows, you could use this instead:

conn.Execute("DELETE FROM TABLENAME WHERE COLUMN = " & number, rows_affected)

Response.Write rows_affected

answered Sep 28 '11 at 06:00

stealthyninja

10,343
11
51
59

1

this will work, as long as connection object's CursorType is set to AdOpenKeyset. – Dale C. Anderson Sep 28 '11 at 15:15

score 1 · Answer 2 · answered Sep 28 '11 at 05:06

1

Add SET NOCOUNT ON; as without that another recordset is returned causing the issue

SET NOCOUNT ON;DELETE TABLENAME WHERE COLUMN = NUMBER; SELECT @@ROWCOUNT AS RC;

answered Sep 28 '11 at 05:06

amit_g

30,880
8
61
118

this is what executing: returnedValue = m_Conn.Execute(strSql).NextRecordset.Fields(strReturnedValueName).Value so it will execute and not return the value (this code works in hundreds of our websites but now on a new server it is not working – Y.G.J Sep 28 '11 at 05:22
If you are already using NextRecordset, you don't need NOCOUNT ON. Is the record getting deleted? – amit_g Sep 28 '11 at 05:49
if i remove th nocount on i get the first error... look at the edited quesion for a new thing that happend – Y.G.J Sep 28 '11 at 05:55

score 0 · Answer 3 · answered Sep 28 '11 at 05:18

@@ Rowcount is a T-SQL call that can only run from the database. You can instantiate it in a stored proc and then call the delete and return the @@rowcount from the stored proc.

This is for ASP.NET but instead of writting this in the page load just handle it in the header

http://support.microsoft.com/kb/306574

score 0 · Answer 4 · answered Sep 28 '11 at 05:59

This code assumes that there are two record sets in the query, the first probably being the row count (why people are suggesting SET NOCOUNT ON).

m_Conn.Execute(strSql).NextRecordset.Fields(strReturnedValueName).Value

so it will execute and not return the value (this code works in hundreds of our websites but now on a new server it is not working –

When you run the code on this server, does it show the number of rows? If not, the something is turning on NOCOUNT, so your ASP code is not getting the expected number of recordset objects back. Rather than assume you will always get two recordsets back, I would loop through the recordset until the returned recordset is empty, something along these lines. The code below is not tested, it basically grabs the first field from each record set until the last one. This way, regardless of the setting of NOCOUNT, you should always get the value from your final SQL statement

DIM rs 
set rs=Server.CreateObject("ADODB.recordset")
set rs = m_Conn.Execute(strSql)
do while rs is not nothing
  val = rs(0).value
  rs = rs.NextRecordSet
enddo

SQL DELETE STATEMENT problem with returned rowcount

4 Answers4