...

View Full Version : How to check if the Database was update



xavatar
08-31-2007, 04:05 PM
HI,

Im trying to create a program that updates a record on my database. It works just fine. However, it always displays "Record was updated!" even if the filters in my SQL statement were not satisfied. I know that i need to create another condition to check whether my SQL statement was satisfied but i dont know where to start and where to put it... Thanks in advance...

here is my code.... database is access 2003...

<%


set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\Documents and Settings\CTSLab\Desktop\lab webby\webby 2\labapp.mdb"

sql="UPDATE borrowTable SET "
sql=sql & "timeReturned='" & Request.Form("timeReturned") & "'"
sql=sql & " WHERE userName='" & Request.Form("userName") & "' and userPassword='" & Request.Form("userPassword") & "'"





on error resume next
conn.Execute sql, resaffected
if err<>0 then
response.write("No update permissions!")
else
response.write("Record was updated!")
end if

conn.close


%>

Daemonspyre
08-31-2007, 04:54 PM
I am not sure that Access does this.

The best thing that you can do is to add a TIMESTAMP field onto your records, and set the default to NOW(). That way, you can query the database to look at which records have an updated TIMESTAMP field and do a count there.

EXAMPLE:

SELECT COUNT(RecordID) FROM your_table WHERE LastEdit >= (NOW()-1);

That tells the database to count the updated records with a LastEdit timestamp of NOW() minus 1 second.

xavatar
08-31-2007, 05:08 PM
thanks for you reply...

i understand what you are asking me to do... but can you give me more details on how to do it? like where to insert SELECT COUNT(RecordID) FROM your_table WHERE LastEdit >= (NOW()-1); and how to display the result of that query...

thanks a lot....

Daemonspyre
08-31-2007, 05:17 PM
Once you have added the field onto your database records, then you would do this:



'on error resume next
conn.Execute sql, resaffected
if err<>0 then
response.write("No update permissions!")
else
Set rs = Server.CreateObject("ADODB.Recordset")
rs.open "SELECT COUNT(RecordID) AS 'RecAffected' FROM borrowTable WHERE LastEdit >= (NOW()-1);", conn
recordsAffected = rs("RecAffected")
rs.close
Set rs = nothing
'
response.write(recordsAffected & " records were updated!")
end if

conn.close


I don't know what your RecordID field is called, nor your 'lastedit' field, so make sure that you update those when you paste this into your code.

xavatar
08-31-2007, 06:38 PM
Thank you very much... this is how my code looks like now....



<%


set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\Documents and Settings\CTSLab\Desktop\lab webby\webby 2\labapp.mdb"

sql="UPDATE borrowTable SET "
sql=sql & "timeReturned='" & Request.Form("timeReturned") & "'"
sql=sql & "timeStamp='" & Now() & "'"
sql=sql & " WHERE userName='" & Request.Form("userName") & "' and userPassword='" & Request.Form("userPassword") & "'"





'on error resume next
conn.Execute sql, resaffected
if err<>0 then
response.write("No update permissions!")
else
Set rs = Server.CreateObject("ADODB.Recordset")
rs.open "SELECT COUNT(timeReturned) AS 'RecAffected' FROM borrowTable WHERE timeStamp >= (NOW()-1);", conn
recordsAffected = rs("RecAffected")
rs.close
Set rs = nothing
'
response.write(recordsAffected & " records were updated!")
end if

conn.close


%>


....
Now im getting this error message....

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression ''11'timeStamp='9/14/2007 1:23:41 AM''.
/CTs2/returnsystem.asp, line 206

....

what i did was i created a new field in my database called timeStamp.... data type is time/date...

I am trying to update only 1 field at a time....
the field that i am trying to update is the timeReturned field.... at the same time, im am putting a time stamp value on the field timeStamp (as per your advice earlier)...

the concept of the whole program is much like that of a library system where you borrow and return books and you update the database every transaction...

I pray for your patience... Thanks!!!

Spudhead
08-31-2007, 08:18 PM
I've got to be misunderstanding something here, you're calling:

conn.Execute sql, resaffected

If there was at least one record that matched the criteria in your SQL statement, and the attempt to update was successful, then resaffected will contain the number of records that were successfully updated. So, unless I'm missing something obvious (hey, it's Friday), then all you need to do is:



resaffected = 0
on error resume next
conn.Execute sql, resaffected
if err<>0 then
response.write("No update permissions!")
else
if resaffected > 0 then
response.write("Record was updated!")
else
response.write("No matching records")
end if
end if



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum