Date/time not updating

[0xC0000005]

I use this function to update a record in my database:

function UpdateConnection(name, ip, port, type, tz)
{
objConn = Server.CreateObject("ADODB.Connection");
objConn.Open("Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:/Inetpub/wwwroot/xxx/xxx.mdb;");

var d = new Date();
var strDate = FormatDateString(d);

sqlUpdate =
"UPDATE [Connections] SET " +
"[ip]='" + ip + "', " +
"[port]='" + port + "', " +
"[type]='" + type + "', " +
"[register]='" + strDate + "', " +
"[logout]='', " +
"[tz]='" + tz + "' " +
"WHERE name='" + name + "'";

objConn.Execute(sqlUpdate);
objConn.Close();
}
Note that one of the fields is the date/time of the update.

But when I view the database immediately after the update, the date has not been updated:

function ShowDatabase()
{
objConn = Server.CreateObject("ADODB.Connection");
objConn.Open("Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:/Inetpub/wwwroot/xxx/xxx.mdb;");
objRS = objConn.Execute("SELECT * FROM [Connections] ORDER BY [register]");

%>
<table border="1" width="100%">

<tr>
<td>Name</td>
<td>IP</td>
<td>Port</td>
<td>Type</td>
<td>Register</td>
<td>Logout</td>
<td>TimeZone</td>
</tr>

<%

try{ objRS.MoveFirst(); }
catch(e){ }

while( !objRS.EOF )
{%>
<tr>
<td> <%=objRS("name")%> </td>
<td> <%=objRS("ip")%> </td>
<td> <%=objRS("port")%> </td>
<td> <%=objRS("type")%> </td>
<td> <%=objRS("register")%> </td>
<td> <%=objRS("logout")%> </td>
<td> <%=objRS("tz")%> </td>
</tr>

<% objRS.MoveNext();
}
%>

</TABLE>

<%

objConn.Close();
}


Clients who use this page essentially 'ping' it every minute or so. If a client pings the page for the first time the date/time will update. But if the client now sends a second, or third... etc. ping the date/time does not update.

If a second client pings the page, it will behave the same way. it's first ping will update the date/time, but not successive pings until the first client has pinged again.

In other words, this is what happens:

CLIENT 1 PING 1: DATE/TIME UPDATED
CLIENT 1 PING 2: DATE/TIME NOT UPDATED
CLIENT 1 PING 3: DATE/TIME NOT UPDATED
CLIENT 1 PING 4: DATE/TIME NOT UPDATED

A different scenario:

CLIENT 1 PING 1: DATE/TIME UPDATED
CLIENT 1 PING 2: DATE/TIME NOT UPDATED
CLIENT 1 PING 3: DATE/TIME NOT UPDATED

CLIENT 2 PING 1: DATE/TIME UPDATED
CLIENT 2 PING 2: DATE/TIME NOT UPDATED
CLIENT 2 PING 3: DATE/TIME NOT UPDATED

CLIENT 1 PING 4: DATE/TIME UPDATED
CLIENT 2 PING 4: DATE/TIME UPDATED
CLIENT 1 PING 5: DATE/TIME UPDATED
CLIENT 2 PING 5: DATE/TIME UPDATED

[PeejAvery]

Why don't you just use a TIMESTAMP field? The server will automatically update it everytime that row is updated.

[0xC0000005]

Why don't you just use a TIMESTAMP field? The server will automatically update it everytime that row is updated.Since I'm relatively new to database work I will have to look into a TIMESTAMP field.

However, my question is much more generic. As you can see from my example, there are other fields, mostly text, and they don't get updated properly either. As it turns out, most of the fields stay the same each time the page is pinged - it's only the date/time that needs to be updated every time - that's why I used it as an example.

But occasionally the other fields do change as a result of the client setup procedure - and they don't update correctly either. So I am really looking for an answer as to why this happens and how I can correct it.

[davide++]

Hi all.

@PeejAvery: 0XC0000005 is using an Access database that doesn't support TIMESTAMP field (as SqlServer does).

@0XC0000005
A very simple way to set the system date (date + time) is using the Now() function; for example


sqlUpdate =
"UPDATE [Connections] SET " +
"[ip]='" + ip + "', " +
"[port]='" + port + "', " +
"[type]='" + type + "', " +
"[register]= Now(), "
"[logout]='', " +
"[tz]='" + tz + "' " +
"WHERE name='" + name + "'";



The field register must be a datetime field.
It's unnecessary to add the brackets when the fields don't have spaces.

Probably the main problem doesn't depend on the UPDATE operation; after the UPDATE the updated value remanis forever. Maybe this is a problem of server pages.

[PeejAvery]

@PeejAvery: 0XC0000005 is using an Access database that doesn't support TIMESTAMP field (as SqlServer does).
True. I jumped the gun thinking of other databases.

[0xC0000005]

Probably the main problem doesn't depend on the UPDATE operation; after the UPDATE the updated value remanis forever. Maybe this is a problem of server pages.
That's an explanation that I had thought about. In other words, the ASP is cached and so is Date(). Is that possible? How would I defeat something like that?

[0xC0000005]

It's looks like it is a cache problem. I added "&cacheKiller=n" (where n is a number that increments) and the problem was solved.

Is there some way I can do that automatically so that the caller isn't responsible for sending a unique query each time?