[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Update Access database Date field from GridView fails

Posted on 2008-06-24
14
Medium Priority
?
882 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

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

Accepted Solution

by:
arhame earned 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

650 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