Solved

Update Access database Date field from GridView fails

Posted on 2008-06-24
14
873 Views
Last Modified: 2013-11-26
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
Comment
Question by:davewalter1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 8

Expert Comment

by:arhame
ID: 21857197
<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
 
LVL 8

Expert Comment

by:arhame
ID: 21857204
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
 

Author Comment

by:davewalter1
ID: 21857277
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
Industry Leaders: 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!

 

Author Comment

by:davewalter1
ID: 21907181
So, nobody has a clue about this one?
0
 
LVL 8

Accepted Solution

by:
arhame earned 500 total points
ID: 21907434
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
 

Author Comment

by:davewalter1
ID: 21907636
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
 
LVL 8

Expert Comment

by:arhame
ID: 21907761
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
 

Author Comment

by:davewalter1
ID: 21908244
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
 
LVL 8

Expert Comment

by:arhame
ID: 21908461
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
 

Author Comment

by:davewalter1
ID: 21909176
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
 

Author Comment

by:davewalter1
ID: 21917928
Any reason why I can't award you these points, too?
0
 
LVL 8

Expert Comment

by:arhame
ID: 21918382
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
 

Author Closing Comment

by:davewalter1
ID: 31470167
Well, then I'm happy to award these points as well!
0
 
LVL 8

Expert Comment

by:arhame
ID: 21925559
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

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question