SQL DELETE STATEMENT problem with returned rowcount

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?

ConnectionStr开发者_运维百科ing =  "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**;"

---

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

---

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;

---

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

---

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