ASP ACCESS UPDATE QUERY

[apple17]

I have a page where the user is updating a participant's information. The participant may or may not already have a record for a particular set of data from the webpage. Rather than doing a SELECT to see if there is data (most of the time there will be), then do an UPDate or INSERT, I was thinking it would be better to do the UPDATE and then if it's not successful, do an INSERT.

Does this make sense? If so, how do I test the return result of the UPDATE query?

Thanks!

 

[DotNetGnat]

update statement will always be successful if the record is found or not(assuming the sql statement is correctly structured)

what i mean is..lets say you have something like this

Update mytable set Name = 'blah' where recordid = 25

now if the recordid 25 exists the above query will work fine and it sets the Name to blah

if the recordid doesnt exists then the update query would work fine without complaining any errors and without updating any records...so there is no way you can tell the differnce...

so i would suggest doing a select statement first and then

if rs.bof and rs.eof then
'record not found
'go ahead and insert the new record
else
'record found
'go ahead and do the update
end if

-DNG

 

[Tarwn]

Actually, whether or not the update modifies records, it will still return the number of rows it affacted. So you could capture the value for number of rows affected and use it to determine if you need to follow it up with an insert.
Example:

Dim num, sqlstr
sqlstr = "UPDATE blah blah blah"
MyConnection.Execute sqlstr, num
If num = 0 Then
   sqlstr = "INSERT blah blah blah"
   MyConnection.Execute sqlstr
End If

-T