Solved

Update Access database Date field from GridView fails

Posted on 2008-06-24
14
868 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
  • 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now