How to know if record exist

[gracine]

Hi Guys, I try to update a record if exist or create it if not.

Here's my code.

strSQL = "SELECT count(*) FROM Table1 "
Set m_RS = m_DB.Execute(strSQL)

If (m_RS.Fields(0).Value) = 0 then

...insert
else
...update
end if

My question is : is there a way to execute the update query and see if one record has been updated and if not I excecute insert query. I would like to avoid running a query only to get count...

I tried the m_RS.recordcount but no success.

Any help is welcome
thanks

 

[swampBoogie]

sql = "update ... "
connect.execute sql,rows,128
if rows = 0 then 'not updated

 

[SemperFiDownUnda]

to explain Swamps explanation the 2nd parameter of the .Execute method is a long that will recieve the number of rows effected by the query. The last parameter is for good measure. It is the adExecuteNoRecords which just means that ADO won't set up to recieve a recordset back from the executed statement.


Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[BogdanMBM]

I think I have a better idea. So far, your answers use to much network traffic. First you see if it is the case of an update or of an insert; then you get back the answer from the server (+1 network traffic), then yuo create the ADO.recordset on the client's machine (useless memory usage); then you tell the server what to do (+ 1 another network traffic). This is not a good approach...
I may add that between the repeated communications with the SQL server ( lets say in case of a slow connection <the internet>), your first response from the SQL server can become absolete (another one can insert the record meenwhile).
My idea would be:


if exists (select * from Table1 where <your condition>)
begin
update Table1 set <your columns> = <your values> where <your condition>
end
else
begin
insert into Table1(<your columns>) values (<your values>)
end

This way you have only one network trafic (NOT 3), you do NOT have to instantiate any ADO.Recordset on the client's machine and you are protected from other user's enerference in your update/insert. You can even encapsulate it into a transaction, chek for errors, make a rollback (if an error ocures).

Hope I've been helpful,
Bogdan Muresan.

 

[SemperFiDownUnda]

WHAT?
.Execute method with the adExecuteNoRecords doesn't do anything with a recordset

I would agree that a stored proc would be a better option.





Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first