SQL Server - How To Get Number Of Rows Updated

If I issue an

UPDATE tablename SET fieldname = newvalue

in the Query window in SQL Server 2005 Management Studio, it tells me how many rows were updated.

Ditto, if I do the equivalent of SQLExec() programatically in either Visual FoxPro or .NET, I also get back the number of rows updated.

However, I can't find a way to do this with WinDev's SQLExec().

Any ideas please?

TIA, Kevin

Nobody????/

The following piece of code might be what you want:-

bUpdate = HExecuteSQLQuery("updateAudit3",hCheckDuplicates, sql)

if bUpdate then
rowsaffected=HNbRec("updateAudit3", hNotLocking)
else
.....

Scrap that! Got this example from somewhere quite a long time ago, but it doesnt seem to work.

Off the top of my head... have you looked at SQLInfo()?

In the help I found that SQLinfo only lets you know the nbr of affected rows for a SELECT query, and only if the result is browsed by SQLfirst/SQLcol;
Then SQL.NbLine results the number of rows.

So for an UPDATE query I supose you have to perform a SELECT COUNT(*) with the same WHERE clause before performing the update query, to know the number of rows that will be affected.

Thanks for the feedback folks. Your replies seem to echo what I have found.

Since the SQLInfo() works on a SELECT but not an UPDATE, but I know that SQL Server returns the count in both cases I should report this to PC Soft as a bug?

Kevin

For my updates, I use SQL Stored Procs, and return @ROWSAFFECTED as a parameter. Its only a workaround though.

Quote
Ben
For my updates, I use SQL Stored Procs, and return @ROWSAFFECTED as a parameter. Its only a workaround though.

Yes, I can see that would work. Unfortunately, I need to implement it as SQL from the business tier rather than SPs.

Kevin

Hello

instead of insert with autoIncrement column you need to do a select @@identity to know the news key insert you can use select @@rowcount for having the row update inserted or deleted

in you case :
SQLExec("UpdateClient where client_ID > 1000 ........)
SQLExec("select @@rowcount")

then the SQLCol(maReq,1) give you the number of row updated

it work fine with SQLServer Express 200(

Best regards
Firetox

Quote
Firetox
in you case :
SQLExec("UpdateClient where client_ID > 1000 ........)
SQLExec("select @@rowcount")

then the SQLCol(maReq,1) give you the number of row updated

it work fine with SQLServer Express 200(

/quote]

Can you please show me all of the code in this area - I keep getting WinDev errors (the SQLExec() needs a second parameter, I think you need to put something else before SQLCol() and what is maReq1).

TIA, Kevin

hello

nbUpdate is integer

SQLExec("UpdateClient where client_ID > 1000 ","MaReq")
SQLferme("MaReq")
SQLExec("select @@rowcount","Mareq")
SQLPremier("MaReq")
if not SQL.EnDehors then nbUpdate = SQLCol("maReq",1)
SQLferme("MaReq")

don"t tested but it like this
With F1 on SQLexec you have exemple

Best regards

Thanks again. Unfortunately nbUpdate still returns 0 even when I know it is updating a record.

Kevin

try this

nbUpdate is integer

SQLExec("UpdateClient where client_ID > 1000 ;select @@rowcount","MaReq")
SQLExec("","Mareq")
SQLPremier("MaReq")
if not SQL.EnDehors then nbUpdate = SQLCol("maReq",1)
SQLferme("MaReq")

with SQL2005 Studio this query work :

UpdateClient where client_ID > 1000 ;
select @@rowcount

when i execute this in SQLServer Studio i have
5000

Regards

It will not work in WD:
You cannot execute more than one query in one "sqlExec" (no 'scripts')


iQtty is int
sqlName is string = "MyQRY"
sqlReq is string = "UPDATE CUST SET NAME = 'Petersen' "
sqlWhere is string = "WHERE NAME = 'Johnson' "

sqlCount is string = "SELECT COUNT(*) FROM CUST "


IF NOT sqlExec(sqlCount+sqlWhere,sqlName) THEN
//do some error procedure
END

//I always use the 'Fetch' way to query around...
IF sqlFetch(sqlname) = 0 THEN
iQtty = sqlGetCol(sqlName,1)
ELSE
//do some error procedure
END

//'Main' query :
IF NOT sqlExec(sqlReq+sqlWhere,sqlName) THEN
//do some error procedure
END

This allways will work on any ansi SQL based DB;
Way make things difficult if they can be so easy ?

If qtty affected rows is so important for you, make a procedure that allways results it.

qttyUpdated = MyUpdateQRY(_TableName,_Set,_WhereClause) or whathever.

and in a transaction Two query ?

because this code work fine with MSSQL4WD i use it for a long time

CONVSQL:MySQLtransaction(CONVSQL:mySQLDebut,1)
CONVSQL:mySQLexec("Update Client set fax=2 where client_ID > 1000",1)
CONVSQL:MySQLFerme(1)
CONVSQL:mySQLexec("select @@rowcount",1)
CONVSQL:mysqlpremier(1)
SI PAS CONVSQL:mysqlendehors ALORS Trace("nbLigne Updated : "+CONVSQL:mySQLLitCol(1,1))
CONVSQL:mysqlferme(1)
CONVSQL:MySQLtransaction(CONVSQL:mySQLFin,1)


i have in trace : nbLigne Updated : 4000
Best regards

Quote
This allways will work on any ansi SQL based DB;
Way make things difficult if they can be so easy ?

only if stored procedure are supported and for avoiding having stored procedure on server. i worked with some people who didn't want stored procedures because they don't want to have a DBA and customer is king !!

best regards

Sorry ??

The above code is windev code, sending some queries to the DB by sqlExec(), no stored procedures involved. All sent queries are standard ansi SQL.

The example 'MyUpdateQuery' is ment to be a Windev procedure. If you'd prefer OO, you can make a method instead. In that case :NbrRecAffected could be a member of your clDBaseHandling class.

Leo.

Hello

yes but in your code you do and select count(*) for having the result of number of rows concerned for the update. that cost in time for executing a count(*) from a DB which have Million lines.

i use this code and i can say you that count(*) cost very much time and for avoiding this i search a code for geting number row updated. this my solution and it work fine since 2 years with a simply code

in fact i have modified mSQL4WD for doing this query when mySQLExec have a query with no select word in text and charge a member in the class with the reult

Best regard

Guys, thanks very much for the discussions. Perhaps I should summarise my requirements more clearly:

1. I want to do this dynamic SQL through SQLExec() in middle tier objects. I do not want to use Stored Procedures.

2. I don't want to double query every time, which rules out the additional COUNT(*).

3. I basically want to issue a SQLExec("Update....", "Qry1") and get back the number of rows affected. SQL Server returns this value, and I can certainly get it using similar code in VFP or .NET (this project requires the use of WinDev).

4. Can you please explain what mSQL4WD is - I have looked it up in the Help and cannot find any reference to it?

5. FWIW, I have this morning upgraded to WD12 - same error. I have also sent a bug report to PC Soft, but am still waiting for an answer.

Regards,
Kevin

Hello

MSSQL4WD is an alternative acces to SQLServer dataBase
you don"t need the analyse i th project but you can exec query on the database

the command MySQLExec like SQLExec can execute query but there is somme change then you can execute query with limit because the command accept limit wor like "select * from client limit 10,20 for having the 20 lines after the 10 th

there are several command to read column, have the description of a table, execute stored procedure (with cursor answer) etc....

the project example is on SQLManagerX.com
you need a class (msSQL4WD.wdc) and a dll (mSSQL4WD.dll) in the exe directory

for example

Declaration of object

AccesDatabase is a c_MSSQL4WD()

Connection

LOCAL
	v_retCode is boolean
v_retCode = MSSQL:mySQLConnecte("Provider=SQLOLEDB;server=FREDERIC\SQLEXPRESS;UID=sa;PWD=fred;database=testSQLserver;")
	
IF not (v_retCode) THEN Erreur("Impossible de se connecter à la base de donnée 'test'.", MSSQL:mySQLGetErrorMessage())

execute query and read

LOCAL
	retCode is boolean
	custom_ID is int
	custom_Name is string
	custom_date is string

retCode = MSSQL:mySQLExec("SELECT NumEntree, NumFournisseur, DateEntree FROM ENTREES limit 10,20",0)

IF (retCode) THEN
	MSSQL:mySQLPremier(0)
	WHILE (NOT MSSQL:mySQLEnDehors)
		custom_ID = MSSQL:mySQLLitCol(0, 1)
		custom_Name = MSSQL:mySQLLitCol(0, 2)
		custom_date = MSSQL:mySQLLitCol(0, 3)
		trace( custom_ID + TAB + custom_Name + TAB + custom_date)
		MSSQL:mySQLSuivant(0)
	END
ELSE
	Info(MSSQL:mySQLGetErrorMessage())	
END
MSSQL:mySQLFerme(0)

exec query and get result n a table

LOCAL
	retCode is boolean

TableSupprimeTout(TABLE1)
retCode = MSSQL:mySQLExec("SELECT NumEntree, NumFournisseur, DateEntree FROM ENTREES order by NumEntree desc", 0)
IF (retCode) THEN  MSSQL:mySQLTable(0, "TABLE1")
MSSQL:mySQLFerme(0)

having table description :

description est une chaîne
IF  MSSQL:MySQLDecritTable(table_adox, ma_description) THEN Info (Remplace(description,"/", ""+RC+""))

Best regards
Firetox

Do I understand (from a combination of my own French reading and a Babelfish translation which seems to generate some strange words) that MSSQL4WD is some sort of WinDev wrapper around a .NET class library?

Is it like WinDev in having both English and French method names, or just the French like you show above?

Kevin

yes if you want
alternative acces have been created for having acces to dataBase with API level (with constructor dll) or for MSSQL ADO acces.

the method name are french but if you want you can call mySQLPremier how you want like MySQLFirst the call for the dll is the only thing you can't change (it's not true because you have the C++ source in the donload)

when you download the acces you can modify it as you want it"s free and openSource

Hi Folks,

For the sake of completeness for anybody following this thread, I have now heard back from PC Soft. In essence they said "what a good idea, we will consider putting it in a future version of WinDev".

Hmmmm - I still think it is a basic oversight.

Kevin