ExecNonQuery

[stefanobusetto]

if you execute an update
is there any way to know how many rows has been updated?
thanks

 

[Mahares]

When you did an UPDATE, you probably had a WHERE clause in your SQL statement. Use part of it to find the number of updated records. Perhaps you are thinking of a better way than the below :

dim SQL1 as SQL 'in process global

Dim txt as string  'globals
Dim UpdatedRecos as Int   'globals

txt="UPDATE MyTable SET City= 'Rome' WHERE Country='Italy'"
SQL1.ExecNonQuery(txt)

txt="SELECT count(*) FROM myTable WHERE City= 'Rome' AND Country= 'Italy'"
UpdatedRecs=SQL1.ExecQuerySingleResult(txt)
msgbox(UpdatedRecs,"Number of updated records.")

 

[stefanobusetto]

so you know there is a record with the given key
but you are not sure you updated it

 

[Mahares]

I am not sure if there is a better way, unless you make a copy of the original table and compare it to the updated one to know for sure. That is too much work though. I am cutious too. If anyone can help this biggie, it is probably someone like Klaus.

 

[stefanobusetto]

the best solution i can think of is
ExecNonQuery returns the number of rows affected
by the sql statement
thanks anyway

 

[Mahares]

I did not think ExecNonQuery returns any rows in a SQLite table, since the UPDATE is an action query. How would you then display the number of rows using ExecNonQuery?

 

[stefanobusetto]

Not the rows but how many rows have been affected

 

[Mahares]

If you can show the code that can do it, that is great. If not, perhaps someone else can point me to the proper place, where can get some guidance. If you are referring to a rowcount, I have only seen it in reference to a CURSOR, like in Cursor1.rowcount

 

[mc73]

You can add a field named say 'updated'. Then put its value to 1. After updating, check the records' count with updated=1. When finished with the query, set updated to 0.

 

[eps]

Do a select count query before the update...

 

[Mahares]

@mc73: That is clever. However, you are talking about altering the table by adding a new field(column). If having to calculate the number of updated records is a frequent task. That would be the way to do it. Below is how I see the code then:

Dim SQL1 as SQL 'in process global
Dim txt as string  'globals
Dim UpdatedRecos as Int   'globals

txt="UPDATE MyTable SET City= 'Rome', MyUpdate =1  WHERE Country='Italy'"
SQL1.ExecNonQuery(txt)

txt="SELECT count(*) FROM myTable WHERE MyUpdate = 1" 
UpdatedRecs=SQL1.ExecQuerySingleResult(txt)
Msgbox(UpdatedRecs,"Number of updated records.")

txt="UPDATE MyTable SET MyUpdate =0  WHERE MyUpdate = 1" 
SQL1.ExecNonQuery(txt)

 

[eps]

No need for "myupdate" column, and not sure why you're assigning the sql to txt, just exec it.

Just do a select count query, which has matching where clauses to the update, then do the update.

 

[stefanobusetto]

the idea is good
but in a multi user environment if you want to be sure
you have updated the record you must at least add a couple
of fields last_user and last_datetime
something like a timestamp
i did it for same applications
but it is a little bit too complex
thanks anyway

 

[eps]

the idea is good
but in a multi user environment if you want to be sure
you have updated the record you must at least add a couple
of fields last_user and last_datetime
something like a timestamp
i did it for same applications
but it is a little bit too complex
thanks anyway

I wasn't aware this was a multi user environment... It's the first time you've mentioned this. If you ask the wrong question or don't give enough information you'll invariably get the wrong answer!

 

[Ricky D]

ExecNonQuery returns an int - the number of rows affected by the INSERT, UPDATE & DELETE commands

Dim updated As Int
updated = ExecNonQuery("UPDATE Table SET field1=value1 WHERE Field2=value2")

 

[klaus]

ExecNonQuery returns an int - the number of rows affected by the INSERT, UPDATE & DELETE commands

No, ExecNonQuery returns void.

But you can use the changes() query below:

Dim changes As Int
changes = SQL1.ExecQuerySingleResult("SELECT changes() FROM " & DBTableName)

Best regards.

 

[Mahares]

Klaus: You are an amazing machine. I was wondering when you are going to get on this. I tested it and it works. And above all , it is a very simple solution. Although I am not the original issuer of this thread, I have benefited from it immensely due to the willingness of the forum users to offer candid and different points of view.

 

[klaus]

I was wondering when you are going to get on this.

Not really complicated.
- Ricky's answer looked wrong to me
- I looked at the SQL library documentation, and saw returns void
- Tested it in one of my programs, confirmed my thought
- I looked at the SQLite documentation, found the changes() function
- Tested it in my program to prove that it works
- Posted the answer.

That's it.

Best regards.

 

[Erel]

I looked at the SQLite documentation, found the changes() function

Nice one. I wasn't familiar with this keyword.

 

[NJDude]

For future reference, a list of SQLite Functions and some very informative website about SQLite