[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 884
  • Last Modified:

Update Access database Date field from GridView fails

This question was also asked here: http://www.experts-exchange.com/Microsoft/Development/.NET/Visual_Studio_2008/Q_23508375.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

0
davewalter1
Asked:
davewalter1
  • 7
  • 7
1 Solution
 
arhameCommented:
<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.
0
 
arhameCommented:
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.
0
 
davewalter1Author Commented:
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.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
davewalter1Author Commented:
So, nobody has a clue about this one?
0
 
arhameCommented:
Well, since if you hard code a number into your updateCommand then we can eliminate it being a problem with your datasource.  One thing I noticed is your column is named DateInstall, yet your update command and your textbox all seem to bind to DateInstall, yet you set your update command to @InstallDate (opposite).  Try making them all the same syntax, and make sure to use the BIND I talked about earlier not Eval.
0
 
davewalter1Author Commented:
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

0
 
arhameCommented:
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

0
 
davewalter1Author Commented:
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

0
 
arhameCommented:
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"

0
 
davewalter1Author Commented:
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.
0
 
davewalter1Author Commented:
Any reason why I can't award you these points, too?
0
 
arhameCommented:
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.

:)
0
 
davewalter1Author Commented:
Well, then I'm happy to award these points as well!
0
 
arhameCommented:
Thanks :)  Adding a link to the other post just incase somebody looks at this post later on for help:

http://www.experts-exchange.com/Microsoft/Development/.NET/Visual_Studio_2008/Q_23529977.html
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now