Formatiing dates for ASP.Net update query in Access

This seems stupid and maybe it isn't the answer, but just to rule it out... put brackets around @InstallDate in your UPDATE statement:

UpdateCommand="UPDATE [TigerTVSubscriptions] SET TVServiceLevel=@TVServiceLevel, [DateInstall]=[@InstallDate] WHERE IDSubscription=@IDSubscription"

If you create an update query in Access in sql view it auto-inserts the brackets around the input parameter when the query is saved.  Sooo... when in Rome...

Yup, I've trried that. I think I've tried nearly every variation of brackets and pound signs conceivable.

Actually, your suggestion wasn't stupid at all. This situation is stupid :-)

It's as if using asp.net to update a date field in Access is something nobody's ever done successfully before. Like landing a man on Mars or something...

Ouch, still working on this one Dave?

Okay I'm going to set up a simple test in an access database with a string column and a datetime column and see if I have the same problem updating the datetime column.

Okay back (did ya miss me?)

Dave - it works for me :(  I set up a table called DateTime, set a primaryID, a DateTime column (set to date time data type), and String column (set to text).  I added a Gridview and a AccessDataSource.  Enabled editing and had it automatically generate the update parameters.  Works out of the box :(  I'll post my simple code to show you the syntax it's using to work - and I'll look over your code again.... there has to be a typo or something simple we're overlooking. It works... just not in your situation.  Crazy.  I'm using Visual Studio 2005, and I had it do all the syntax work... the main difference I see is it uses ?'s instead of @DateInstall or whatever you have in yours.

But I'll look through your code one last time and see if I can find something.  But the update works with the following code:

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/admin/tok.mdb"
        DeleteCommand="DELETE FROM [DateTime] WHERE [ID] = ?" 
        InsertCommand="INSERT INTO [DateTime] ([ID], [DateTime], [String]) VALUES (?, ?, ?)"
        SelectCommand="SELECT [ID], [DateTime], [String] FROM [DateTime]"
        UpdateCommand="UPDATE [DateTime] SET [DateTime] = ?, [String] = ? WHERE [ID] = ?">
        <UpdateParameters>
            <asp:Parameter Name="DateTime" Type="DateTime" />
            <asp:Parameter Name="String" Type="String" />
            <asp:Parameter Name="ID" Type="Int32" />
        </UpdateParameters>
        </asp:AccessDataSource>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
        DataSourceID="AccessDataSource1">
        <Columns>
            <asp:CommandField ShowEditButton="True" />
            <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
                SortExpression="ID" />
            <asp:BoundField DataField="DateTime" HeaderText="DateTime" SortExpression="DateTime" />
            <asp:BoundField DataField="String" HeaderText="String" SortExpression="String" />
        </Columns>
    </asp:GridView>

Select allOpen in new window

One thing I'm still confused about Dave - is why you have [DateInstall]=@InstallDate

You have the words reversed.  Then in the Update parameter you refer to it as Name="InstallDate"

BUT Then down in your Bind("DateInstall" - It's back to DateInstall!  Why are two one way, and two the other?  InstallDate or DateInstall - one or the other :P

Yes, I saw that, too. Fixed it. Don't remember why I had it that way. Probably something I tried in one of the myriad attempts to get this to work. I set up a table like your test table and I'm going to try your simple code to see if that works in my case. I did try question marks instead of the @fieldname things -- didn't matter either....

Thanks for persisting!

Okay, let me know the results of using that code.  Obviously it added the time, but when I'd edit it from today's date to 1/1/1 it'd update the database with 01/01/2001 12:00:00 etc.  But it did update.

I also tried removing the EditItemTemplate code and all of that and just using a BoundField to simplify matters, and that fails, too. At the moment, with question marks, it's not throwing an error on update, but it's not updating, either.

Dave: can you post the entire GridView markup?  I'd like to see all of your column definitions.  I have a suspicion that something's getting mixed around in the parameters.

Yeah, that's something I just got done trying too - converting the simple test fields in my test to a template and it still worked, was wondering if that was what was messing with it.

If you would, post your entire code for the accessdatasource and the gridview as it is now.  Since I'm sure you've made a billion changes trying to figure things out since you originally posted.

Yes I was thinking that too. Here it is the way I'm looking at it right now...

<%@ Page Language="VB" MasterPageFile= "~/PageDatabase.master" EnableEventValidation = "false" AutoEventWireup="false" title="TigerTV Subscribers" %>
 
<%@ Register Assembly="CustomControls" Namespace="CustomControls.Grid" TagPrefix="cc1" %>
 
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="System.Web.UI" %>
<%@ Import Namespace="System.Text.RegularExpressions" %>
 
<script language="VB" runat="server">
    Sub Display_Message(ByVal Src As Object, ByVal Args As GridViewDeletedEventArgs)
 
        EditMSG.Text = "Record deleted"
        Beep()
 
    End Sub
</script>
 
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
 
<asp:Panel ID="Panel1" runat="server">
 
<div id="graybanner"> 
  <h3 id="pageName">Edit TigerTV Subscriptions</h3>
  <div id="instructions">
    <ul>
	    <li>Default sort order is by last name</li>	
	    <li>Click column headings to sort</li>	
	</ul>
  </div>
</div>
 
<div id="summary">
    <asp:AccessDataSource ID="SubscriptionDataSource" runat="server" DataFile="\\database.mdb"
        SelectCommand="SELECT [IDSubscription], [FirstName], [LastName], [StudentAddress], [WallboxSerial], [TVServiceLevel], [DateRequest], [DateInstall] FROM [TigerTVSubscriptions] ORDER BY [LastName]"
        UpdateCommand="UPDATE TigerTVSubscriptions SET TVServiceLevel= @TVServiceLevel, DateInstall= @DateInstall WHERE IDSubscription= @IDSubscription"
        DeleteCommand="DELETE FROM [TigerTVSubscriptions] WHERE IDSubscription=@IDSubscription">
        <UpdateParameters>        
            <asp:Parameter Name="TVServiceLevel" Type="String" />
            <asp:Parameter Name="InstallDate" Type="DateTime" />
            <asp:Parameter Name="IDSubscription" Type="Int32" />
        </UpdateParameters>              
    </asp:AccessDataSource>   
    
    <asp:AccessDataSource ID="ServiceLevelDataSource" runat="server" DataFile="\\database.mdb"
        SelectCommand="SELECT [TVServiceLevel] FROM [TigerTVServiceLevel]">
    </asp:AccessDataSource>
    
    <asp:Label id="EditMSG" EnableViewState="False" ForeColor="Red" Runat="Server"/>
    
    <cc1:xGrid ID="GridView1" runat="server"
        AutoGenerateEditButton="false"
        AutoGenerateDeleteButton="false"
        EditRowStyle-BackColor="#FFFFC0"   
        OnRowDeleted="Display_Message"
        DataSourceID="SubscriptionDataSource" 
        DataKeyNames="IDSubscription"          
        AllowPaging="true" 
        PageSize="15" 
        AllowSorting="True" 
        AutoGenerateColumns="False"
        BorderColor="#CCCCCC" 
        BorderWidth="1px" 
        CellPadding="3" 
        Font-Names="Arial" 
        Font-Size="12px" 
        EnableRowClick="false" 
        IsFiltered="false"
        MouseOverColor="#FFFFC0" 
        AscImage="/graphics/up.gif" 
        DescImage="/graphics/down.gif">
        <Columns>
        
           <asp:TemplateField HeaderText="Edit"
                ItemStyle-VerticalAlign="Top">
           <ItemTemplate>
                <asp:Button ID="Button1" CommandName="Edit" Text="Edit" Font-Size="8pt" Width="45px" Runat="Server"/>
           </ItemTemplate>
                <EditItemTemplate>
                    <asp:Button ID="Button2" CommandName="Update" Text="Update" Font-Size="8pt" Width="45px" Runat="Server" CausesValidation="true" />
                    <asp:Button ID="Button3" CommandName="Cancel" Text="Cancel" Font-Size="8pt" Width="45px" Runat="Server" CausesValidation="false"/>
                    <asp:Button ID="Button4" CommandName="Delete" Text="Delete" Font-Size="8pt" Width="45px" Runat="Server" CausesValidation="false"                        
                        OnClientClick="return confirm('Are you sure you want to delete this record?');"/>
                </EditItemTemplate>
           </asp:TemplateField>
        
            <asp:BoundField DataField="DateRequest" DataFormatString="{0:MM-dd-yyyy}" HeaderText="Requested" SortExpression="DateRequest" ItemStyle-Wrap="false" ReadOnly="True" />  
            
            <asp:TemplateField SortExpression="DateInstall" HeaderText="Installed">    
                <EditItemTemplate>
                    <asp:TextBox runat="server" id="DateInstall" Text='<%# Eval("DateInstall", "{0:MM/dd/yyyy}")%>'/>
                    <asp:RegularExpressionValidator runat="server" id="RegularExpressionValidator1" ValidationExpression="(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)\d\d" SetFocusOnError="true" ControlToValidate="DateInstall" ErrorMessage = "Date must be in the form MM/DD/YYYY" Text="*" />
                </EditItemTemplate>   
                <ItemTemplate>
                    <asp:Label Runat="server" ID="LabelDateInstall" Text='<%#Bind("DateInstall", "{0:MM/dd/yyyy}")%>' />
                </ItemTemplate>               
            </asp:TemplateField>                 
            
            <asp:BoundField DataField="LastName" HeaderText="Last Name" SortExpression="LastName" ItemStyle-Wrap="false" ReadOnly="true" />
            <asp:BoundField DataField="FirstName" HeaderText="First Name" SortExpression="FirstName" ItemStyle-Wrap="false" ReadOnly="true" />            
            <asp:BoundField DataField="StudentAddress" HeaderText="Address" SortExpression="StudentAddress" ItemStyle-Wrap="false" ReadOnly="true" /> 
            
            <asp:TemplateField SortExpression="TVServiceLevel" HeaderText="Service Level">
            <EditItemTemplate>
                <asp:DropDownList ID="SelectServiceLevel" DataSourceID="ServiceLevelDataSource" runat="server" DataValueField="TVServiceLevel" SelectedValue='<%# Bind("TVServiceLevel") %>'>  
                </asp:DropDownList>       
            </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label Runat="server" Text='<%# Eval("TVServiceLevel")%>' ID="LabelTVServiceLevel"></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>   
            
            <asp:BoundField DataField="WallboxSerial" HeaderText="Wallbox" SortExpression="WallboxSerial" ItemStyle-Wrap="false" ReadOnly="true" />  
            
        </Columns>
        <EditRowStyle Wrap="False" />
        <HeaderStyle CssClass="greenhead" HorizontalAlign="Left" />
        <AlternatingRowStyle BackColor="Honeydew" Font-Overline="False" />
        <PagerSettings Mode="NumericFirstLast" />  
    </cc1:xGrid>
 
</div> 
 
<asp:ValidationSummary ID="ValidationSummary"
        ShowMessageBox="true"
        ShowSummary="false"
        HeaderText="Please note:"
        EnableClientScript="true"
        runat="server"/>  
 
</asp:Panel>
 
</asp:Content>

Select allOpen in new window

<asp:Parameter Name="InstallDate" Type="DateTime" />
 is still backwards, everything else is DateInstall

Ah, yes. Thanks. I keep removing and replacing those parameters, and I must have cut and pasted them from an older iteration. Makes no difference, though...

I gotcha - even with switching the edit item template to BIND?  

Okay, I'm going ot try to replicate your database and use your code and figure this out.  It's personal now, lol.  Give me a few.

Hey! The damn thing worked! I know I had previously had that coded as BIND, though. One other thing I did was to change the field definition in the table. I had it set as Date/Time with formatting "General Date." But when I set up a test table like yours, there's no default formatting, so I removed that from my table. Wonder if that had anything to do with it.

So it seems I have a solution, and very grateful I am! But what should I "Accept as Solution" for this question?

Probably multiple things were wrong to be honest.  I think we'd fix one thing (the dateinstall, installdate for instance) but then it'd still have eval instead of bind.  And by the time we got to fixing bind - maybe you had pasted back the backwards typing of dateinstall.

I don't *think* the formatting should matter.  Easy way to test would be add it back and see if it still doesn't work :)

Thankfully it's working!  This was driving me nuts, really should have been a cut and dry solution!

Well, I really appreciate your help, man. It was driving me nuts, too. Certainly should have been a simple thing. You're right, though, definitely multiple things wrong, and me fixing one and not the other...sheesh.

Once again, I really appreciate your hanging in with me.

Thanks, man. I feel like a great weight has been lifted :-)