Hi,

I am having a problem getting an Access 2003 date/time field to update through a GridView Control.

Essentially the user updates the record which then applies Now() to the date accepted field.

When constructing the query in the 'Configure Data Source' / Update Statement/ Build Query button, I actually test the query in the Build Interface, and it works!!!!

When using the Web Page, it does not, it does nothing and returns the page with records unchanged unchanged.

Please help, I am new to asp and just not getting this at the moment.


ASP Code below;

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
DataKeyNames="job_id" DataSourceID="AccessDataSource1" EmptyDataText="There are no data records to display."
Font-Size="10px" ForeColor="#333333" GridLines="None">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="job_id" HeaderText="job_id" InsertVisible="False" ReadOnly="True"
SortExpression="job_id" />
<asp:BoundField DataField="job_title" HeaderText="job_title" SortExpression="job_title" />
<asp:BoundField DataField="job_description" HeaderText="job_description" SortExpression="job_description" />
<asp:BoundField DataField="job_location" HeaderText="job_location" SortExpression="job_location" />
<asp:BoundField DataField="job_initiator" HeaderText="job_initiator" SortExpression="job_initiator" />
<asp:BoundField DataField="job_provider" HeaderText="job_provider" SortExpression="job_provider" />
<asp:BoundField DataField="job_request_time" HeaderText="job_request_time" SortExpression="job_request_time" />
<asp:BoundField DataField="job_accepted_time" HeaderText="job_accepted_time" SortExpression="job_accepted_time" />
</Columns>
<RowStyle BackColor="#EFF3FB" />
<EditRowStyle BackColor="#2461BF" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/botanic_skies_db.mdb"
DeleteCommand="DELETE FROM `maintenance` WHERE `job_id` = ?" InsertCommand="INSERT INTO `maintenance` (`job_id`, `job_title`, `job_description`, `job_location`, `job_initiator`, `job_provider`, `job_request_time`, `job_accepted_time`, `job_finish_time`, `job_initiator_comments`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

SelectCommand="SELECT job_id, job_title, job_description, job_location, job_initiator, job_provider, job_request_time, job_accepted_time, job_finish_time, job_initiator_comments FROM maintenance WHERE (job_accepted_time IS NULL)"

UpdateCommand="UPDATE maintenance SET job_accepted_time = NOW() WHERE (job_id = ?)">

<DeleteParameters>
<asp:Parameter Name="job_id" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="job_title" Type="String" />
<asp:Parameter Name="job_description" Type="String" />
<asp:Parameter Name="job_location" Type="String" />
<asp:Parameter Name="job_initiator" Type="Int32" />
<asp:Parameter Name="job_provider" Type="Int32" />
<asp:Parameter Name="job_request_time" Type="DateTime" />
<asp:Parameter Name="job_accepted_time" Type="DateTime" />
<asp:Parameter Name="job_finish_time" Type="String" />
<asp:Parameter Name="job_initiator_comments" Type="String" />
<asp:Parameter Name="job_id" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="job_id" Type="Int32" />
<asp:Parameter Name="job_title" Type="String" />
<asp:Parameter Name="job_description" Type="String" />
<asp:Parameter Name="job_location" Type="String" />
<asp:Parameter Name="job_initiator" Type="Int32" />
<asp:Parameter Name="job_provider" Type="Int32" />
<asp:Parameter Name="job_request_time" Type="DateTime" />
<asp:Parameter Name="job_accepted_time" Type="String" />
<asp:Parameter Name="job_finish_time" Type="String" />
<asp:Parameter Name="job_initiator_comments" Type="String" />
</InsertParameters>
</asp:AccessDataSource>

OK,



I now have a better understanding of the problem.



I have declared a value called theDate which is a string variable that contains the following:



<%

Dim theDate As String

theDate = CStr(Now)
Response.Write(theDate)

%>



This provides me with (I believe) the Current Date/Time as a string - It outputs the value to the screen fine.



I want the value in job_accepted to be updated with the value of theDate. I use the following UPDATE Statement:



UpdateCommand="UPDATE maintenance SET job_title = ?, job_description = ?, job_location = ?, job_initiator = ?, job_provider = ?, job_request_time = ?,job_accepted_time = '%&theDate&%', job_finish_time = ?, job_initiator_comments = ? WHERE (job_id = ?)">

I would like to stress :

that the UPDATE statement works perfectly, EXCEPT when it is trying to load the variable theDate, I DO NOT have a problem updating records now, just getting the value of theDate into the field (I would be happy just to use Now() but that doesn't seem to work either.
That if i put simple text in its place that it works fine and updates the record with the text .
I have tried setting the database and the update paramaeters as both String and Date/Time types but still no joy.
I think I must have the format string wrong or something.

So I am hoping someone can tell me how to format this:

job_accepted_time = [SOMETHING THAT LETS ME INPUT THE CURRENT DATE/TIME EITHER FROM NOW() or theDate]

so it works.



Thanks again.

First question...
Why are you storing datetime values as strings?!
Set it to datetime and then you have a couple of ways of doing this.
Via code:
Or you can simply set the default value of the field in Access tobe Now() and then remove it from your update statements entirely!