Determine if SQL update really updated ASP

[d2g]

Hello, I'm sure this is a simple one.. Ive done alot with record sets but we are rewriting some code to make it more efficient and we are replacing our old code with single SQL Update statments.

In this following example how would I determine if a record was updated or not. Using ASP

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DSN=SOMEDSN"
oConn.CursorLocation = 3
strSQL = "UPDATE testtable SET TESTVAR = 'testentry' where PKID = 12"
oConn.Execute strSQL

I have tried using a variable called sqlselected, setting it to 0 before the execute, then using

oConn.Execute strSQL, sqlselected

then the sqlselected variable i thought would give me the number of rows updated, however its still returning 0 even though it is updating the record in the database.

I know PHP has some built in functions for mysql to show affected rows, but im trying to do this in asp

thanks

D2

 

[d2g]

Ok - Figured some of this out myslef, just posting for others to reference.

oConn.Execute strSQL, sqlselected

the variable sqlselected will show the number of rows affected.. The problem i came across was that if the sql update contains the exact same data thats already in the database the sqlselected variable will return 0, because mysql determined it didn't have to update that row.

So if I read all of this correctly i'll have to use another select sql statement before after this one in order to determine if the record or records exist in the first place?

Thanks,

D2G

 

[steven290]

asp doesn't have that feature i would execute a select statement with the same criteria then use some sort of rowcount

 

[d2g]

Yeah this is what im having to do, basicly run a select statment first, then determine if i need to do an update or insert depending of if the record already exists or not.

I did discover another issue however, which is when you do an insert statment, how would i get the ID column value of the new record created in asp and mysql?

 

[steven290]

nice learned something new

 

[steven290]

try something like this

objConn.execute "insert into test (yourfield) Values ('Hello')"
set rs = objconn.execute("select @@Identity")


response.Write rs(0)

 

[d2g]

thanks! That does the trick, so does using

set rs = objconn.execute("select LAST_INSERT_ID()")

Response.Write rs(0)

Now onto the next! Thanks!

 

[steven290]

thanks for the advise