asked on

Update Access database Date field from GridView fails

This question was also asked here: https://www.experts-exchange.com/questions/23508375/Format-Date-for-ASP-Net-UpdateCommand-to-Access-2003-database.html

I'm trying to update a date field in Access 2003 using a Textbox control in a GridView. I can get the date to display properly in the GridView, but it won't update the field in the database. If I hard code a date into the UpdateCommand statement, that value will get inserted, but using the value from the TextBox control results in "No value given for one or more required parameters." I know it's something to do with the way I'm referring to the TextBox control in the UpdateCommand, but I can't figure out what's wrong.

Below is my AccessDataSource control, and the salient section of the GridView control. I believe the problem isn't in the GridView because, without the InstallDate EditItemTemplate, the GridView update function works perfectly. If I then hard code a date value in the UpdateCommand string and enable the InstallDate edit TextBox, that date will post properly to the database.

So my conclusion is that the problem is in the UpdateCommand, and in how the field is specified in the UpdateCommand, because if I hard code an invalid value in the UpdateCommand and try to post it, I get a "Data Type Mismatch..." error, but a field name (@InstallDate) coded into the UpdateCommand and a valid value supplied by the GridView control results in "No value given for one or more required parameters." That would tend to argue that Access is not liking "@InstallDate." At least that's what I've come to think.

<asp:AccessDataSource ID="SubscriptionDataSource" runat="server" DataFile="\\database.mdb"
        SelectCommand="SELECT [IDSubscription], [FirstName], [LastName], [StudentAddress], [WallboxSerial], [TVServiceLevel], [DateRequest], [DateInstall] FROM [TigerTVSubscriptions] ORDER BY [StudentAddress]"
        UpdateCommand="UPDATE [TigerTVSubscriptions] SET TVServiceLevel=@TVServiceLevel, DateInstall=[@InstallDate] WHERE IDSubscription=@IDSubscription"
        DeleteCommand="DELETE FROM [TigerTVSubscriptions] WHERE IDSubscription=@IDSubscription">        
    </asp:AccessDataSource>   
    
    <asp:AccessDataSource ID="ServiceLevelDataSource" runat="server" DataFile="\\netatlas\networkdb\NetworkDB Backend\networkdb_be.mdb"
        SelectCommand="SELECT [TVServiceLevel] FROM [TigerTVServiceLevel]">
    </asp:AccessDataSource>
 
 
        <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">    
 
                <ItemTemplate>
                    <asp:Label Runat="server" ID="LabelDateInstall"><%#String.Format("{0:MM-dd-yyyy}", Eval("DateInstall"))%></asp:Label>
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:TextBox runat="server" id="InstallDate" Text='<%# Eval("DateInstall", "{0:dd/MM/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="InstallDate" ErrorMessage = "Date must be in the form MM/DD/YYYY" Text="*" />
                </EditItemTemplate>                
            </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>

Open in new window

arhame

<asp:TextBox runat="server" id="InstallDate" Text='<%# Eval("DateInstall", "{0:dd/MM/yyyy}")%>' />
to
<asp:TextBox runat="server" id="InstallDate" Text='<%# Bind("DateInstall", "{0:dd/MM/yyyy}")%>' />

Let me know if it's still not working and I'll dig deeper into your code.

arhame

Weird, I must have pasted over my initial comments.

One thing I can see is that you're using Eval instead of Bind - Eval is used to just view data I'm pretty sure, if you want to do two way databinding (retrieving and updating) you need to use bind.

davewalter1

ASKER

I thought of that, too. I think you're right about the difference between Eval and Bind, and I tried both. Doesn't do it. With either one, I'm not throwing an error, but a valid date seems to change to a null and post a null to the database. Very annoying, and I know it's something simple.

davewalter1

ASKER

So, nobody has a clue about this one?

ASKER CERTIFIED SOLUTION

arhame

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

davewalter1

ASKER

Thanks for your response!

Everything says "DateInstall" now. I'm just providing two updateable fields and, at the moment, the updates aren't happening, whether I hard code a date value or not. The entire code is below. Doesn't seem to matter if it's Bind or Eval. I don't know what is going on.

<%@ 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">TigerTV Subscribers</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]='10-10-2010' 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">    
 
                <ItemTemplate>
                    <asp:Label Runat="server" ID="LabelDateInstall"><%#Eval("DateInstall", "{0:MM/dd/yyyy}")%></asp:Label>
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:TextBox runat="server" id="DateInstall" Text='<%# Bind("DateInstall", "{0:dd/MM/yyyy}")%>' HtmlEncode="false" />
                    <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>                
            </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>

Open in new window

arhame

It's been a long time since I've used an access database - however I looked up one of the first applicatiosn I built and it's using a different syntax than you. Your syntax is what I'm familiar with using SQLDatasources.... but I am wondering now that I look at my syntax (that works) if that might be where the problem is.

Instead of using @InstallDate and the likes - in my application it used question marks. So check out the syntax and try using it. I wish I had a definite yes or no answer on what exactly is wrong, but hopefully I can try something you didn't think of and it'll get it working.

Here is the syntax on my accessdatasource:

<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/admin/tok.mdb"
                                                            SelectCommand="SELECT [ID], [Comments], [Picture], [Category] FROM [tok] WHERE ([Category] = ?)" DeleteCommand="DELETE FROM [tok] WHERE [ID] = ?" 
InsertCommand="INSERT INTO [tok] ([ID], [Comments], [Picture], [Category]) VALUES (?, ?, ?, ?)" 
UpdateCommand="UPDATE tok SET Comments = ?, Category = ? WHERE (ID = ?)">
                                                            <DeleteParameters>
                                                                <asp:Parameter Name="ID" Type="Int32" />
                                                            </DeleteParameters>
                                                            <UpdateParameters>
                                                                <asp:Parameter Name="Comments" Type="String" />
                                                                <asp:Parameter Name="Category" Type="String" />
                                                                <asp:Parameter Name="ID" Type="Int32" />
                                                            </UpdateParameters>
                                                            <SelectParameters>
                                                                <asp:ControlParameter ControlID="DropDownList1" Name="Category" PropertyName="SelectedValue"
                                                                    Type="String" />
                                                            </SelectParameters>
                                                            <InsertParameters>
                                                                <asp:Parameter Name="ID" Type="Int32" />
                                                                <asp:Parameter Name="Comments" Type="String" />
                                                                <asp:Parameter Name="Picture" Type="String" />
                                                                <asp:Parameter Name="Category" Type="String" />
                                                            </InsertParameters>
                                                            </asp:AccessDataSource>

Open in new window

davewalter1

ASKER

If I remove the UpdateParameters and everything related to updating the DateInstall field, leaving me with just the DropDown control to update, the update command works. When I put the DateInstall stuff back in without the UpdateParameters, the browser returns "Data Type Mismatch in Criteria Expression."

With the DateInstall stuff and just the DateInstall UpdateParameter as below, still "Data Type Mismatch..."

It has got to be something with the way I'm formatting the date. I can't figure anything else...

        <UpdateParameters>        
            <asp:Parameter Name="DateInstall" Type="DateTime" />
        </UpdateParameters>

Open in new window

arhame

Interesting......

Or rather, Frustrating......

Okay, well I've resorted to using Google. One thing I've noticed in all update commands in access they surround the date by #'s - for example:

UpdateCommand="UPDATE [TigerTVSubscriptions] SET TVServiceLevel=@TVServiceLevel, [DateInstall]=#10-10-2010# WHERE IDSubscription=@IDSubscription"

davewalter1

ASKER

Yep. Saw that, too. Tried the pound signs. Tried question marks, @ signs, no @ signs, brackets...everything except voodoo, or the right thing!

And frustrating is a nice term for it!

This -- #@DateInstall# returns a syntax error. '#@DateInstall#' doesn't throw an error, but doesn't update, either. '#DateInstall#' throws a syntax error. Hardcoding the date in with '#10-10-2010#' doesn't throw an error and doesn't update either...and neither does the same thing without the quotes. Tried removing the UpdateParameters, adding "HtmlEncode='false'" to the editable TextBox control, then removed it (saw that someplace on Google :-).

It does not seem like it's a ridiculous concept to have a dotnet form request a date and then post that date to even something as primitive as an Access database. Yet it seems insurmountable.

davewalter1

ASKER

Any reason why I can't award you these points, too?

arhame

Nope. I've seen it done where people will (when a question doesn't get answered) just post another question with more points and just a link to the other question and award both to the person who solves it.

:)

davewalter1

ASKER

Well, then I'm happy to award these points as well!

arhame

Thanks :) Adding a link to the other post just incase somebody looks at this post later on for help:

https://www.experts-exchange.com/questions/23529977/Formatiing-dates-for-ASP-Net-update-query-in-Access.html