c# - How do I update an an Access database from a GridView in Visual Studio?

Question

I'm trying to update an access database when using a Grid View in Visual Studio 2010 but not having any success. Let me try and explain what I have.

I have an access database with a table "tblConfirmedworkhours" which has fields "dateworked" & "confirmed". I can display the filtered table on my webpage with the edit/update links but the table won't update.

Option 1: I would like (if possible) is to not have to click on the edit & update buttons, just edit the data on the screen (I'm thinking of some kind of similar thing to a continuous form is MS Access and then hit some kind of save button (which (again if possible) would run an append query already created and stored in my access database "qryToHistory".

Option 2: Be able to use the edit/update buttons to change the data in the table.

Here is my current code:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
DataSourceID="AccessDataSource1"  Width="983px" 
    AutoGenerateDeleteButton="True" AutoGenerateEditButton="True">
<Columns>
   <asp:BoundField DataField="WorkHourID" Visible="false" 
        HeaderText="Timesheet ID" />
    <asp:BoundField DataField="EmpName" HeaderText="Employee" 
        SortExpression="EmpName" />
    <asp:BoundField DataField="dateworked" 
        HeaderText="Date" SortExpression="dateworked" ApplyFormatInEditMode="True">
    <ItemStyle HorizontalAlign="Center" />
    </asp:BoundField>
    <asp:CheckBoxField DataField="confirmed" HeaderText="Confirmed" 
        SortExpression="confirmed" Text="This is OK">
    <ItemStyle HorizontalAlign="Center" />
    </asp:CheckBoxField>
</Columns>
<EditRowStyle Width="500px" Wrap="True" />
<EmptyDataRowStyle Width="5000px" />
</asp:GridView>
<asp:AccessDataSource ID="AccessDataSource1" runat="server" 
DataFile="~/PayrollDirect.accdb" 
SelectCommand="SELECT [WorkHourID], [EmpName], [dateworked], [confirmed] FROM [tblConfirmedworkhours] WHERE (([CompanyID] = ?) AND ([confirmed] = ?)) ORDER BY [dateworked]"
UpdateCommand="UPDATE tblConfirmedworkhours SET dateworked=dateworked, confirmed=confirmed where WorkHourID=WorkHourID">
<SelectParameters>
    <asp:SessionParameter DefaultValue="0" Name="CompanyID" SessionField="UserID" Type="Int32" />
    <asp:Parameter DefaultValue="false" Name="confirmed" Type="Boolean" />
</SelectParameters>
<UpdateParameters>
    <asp:Parameter Name="dateworked"/>
    <asp:Parameter Name="confirmed"/>
</UpdateParameters>
</asp:AccessDataSource>

Is Option 1 a possibility, if so how.

If option 1 is not possible, how can I sort the problem of updating my table as in option 2.

I'm still learning Visual Studio so any and all help is gratefully appreciated.

Answer 1

I don't see an update command in your datasource. Are you trying to update it via the aspx page, or do you have a code behind page?

Try adding an UpdateCommand in the asp:accessDataSource. I suspect that at present, you are asking the tabel to update, when you haven't told it that a) It is allowed to and b) How it should update

After edit.

this is from the top of my head. But try adding in something like the following in. (Similar to the select)

  UpdateCommand="UPDATE tblConfirmedworkhours  SET dateWorked=@dateWorked, confirmed =@confirmed where EmpName=@empName">
  <UpdateParameters>
    <asp:Parameter Name="dateWorked" />
    <asp:Parameter Name="confirmed" />
  </UpdateParameters>

I don't have VS on this PC so I can't give an exact answer at the moment sorry. However, I think you may need to also have the employee number in your queries, as you may have 2 employees with the same name, and you need a unique reference to distinguish between them.