Update of AccessDatasource does nothing but has no error

Question

My UPDATE command fails to change any date in the table while very similar SELECT and DELETE commands work. When I change the UpdateParameters to invalid choices, the code behind command throws an error, buy when the parameters are correct, nothing hapens.

Code behind to activate DELETE (which works)

protected void Button2_Click(object sender, EventArgs e)
{
    this.AccessDataSource6.Delete();
}

Code behind to activate UPDATE (which does seem to have any effect on the data)

protected void Button1_Click(object sender, EventArgs e)
{
    this.AccessDataSource6.Update();
}

The AccessDatasource, its SQL commands and parameters

   <asp:AccessDataSource ID="AccessDataSource6" runat="server" DataFile="~/App_Data/ASPNetDB.mdb" 
    SelectCommand="SELECT [PracticeDate], 
                          [StartTime], 
                          [EndTime], 
                          [Division], 
                          [TeamStr], 
                          [FieldName] 
    FROM [vw_fbScheduleFull]
    WHERE (([LocationID] = ?) 
       AND ([DayName] = ?) 
       AND ([PracticeDate] &gt;= ?) 
       AND ([PracticeDate] &lt;= ?) 
       AND ([StartTime] = ?))
    ORDER BY [PracticeDate], [FieldName]"

    UpdateCommand="UPDATE fbPracticeSlot 
    SET StartTime = ?, EndTime = ?
    WHERE ID IN (
       SELECT [PracticeSlotID] 
       FROM [vw_fbScheduleFull] 
       WHERE (([LocationID] = ?) 
          AND ([DayName] = ?) 
          AND ([PracticeDate] &gt;= ?) 
          AND ([PracticeDate] &lt;= ?) 
          AND ([StartTime] = ?))
       )" 

   DeleteCommand="DELETE FROM fbPracticeSlot 
    WHERE ID IN (
       SELECT [PracticeSlotID] 
       FROM [vw_fbScheduleFull]
       WHERE (([LocationID] = ?) 
          AND ([DayName] = ?) 
          AND ([PracticeDate] &gt;= ?) 
          AND ([PracticeDate] &lt;= ?) 
          AND ([StartTime] = ?)) 
       )">

<SelectParameters>
       <asp:ControlParameter ControlID="DropDownListLocation" Name="LocationID" PropertyName="SelectedValue" Type="Int32" />
       <asp:ControlParameter ControlID="DropDownListDOW" Name="DayName" PropertyName="SelectedValue" Type="String" />
       <asp:ControlParameter ControlID="DropDownListStartDate" Name="PracticeDate" PropertyName="SelectedValue" Type="DateTime" />
       <asp:ControlParameter ControlID="DropDownListEndDate" Name="PracticeDate2" PropertyName="SelectedValue" Type="DateTime" />
       <asp:ControlParameter ControlID="DropDownListStartTime" Name="StartTime" PropertyName="SelectedValue" Type="DateTime" />
</SelectParameters>

<UpdateParameters>
       <asp:ControlParameter ControlID="DropDownListNewStart" Name="NewStartTime" PropertyName="SelectedValue" Type="DateTime" />                  
       <asp:ControlParameter ControlID="DropDownListNewEnd" Name="NewEndTime" PropertyName="SelectedValue" Type="DateTime" />
       <asp:ControlParameter ControlID="DropDownListLocation" Name="LocationID" PropertyName="SelectedValue" Type="Int32" />
       <asp:ControlParameter ControlID="DropDownListDOW" Name="DayName" PropertyName="SelectedValue" Type="String" />
       <asp:ControlParameter ControlID="DropDownListStartDate" Name="PracticeDate" PropertyName="SelectedValue" Type="DateTime" />
       <asp:ControlParameter ControlID="DropDownListEndDate" Name="PracticeDate2" PropertyName="SelectedValue" Type="DateTime" />
       <asp:ControlParameter ControlID="DropDownListStartTime" Name="StartTime" PropertyName="SelectedValue" Type="DateTime" />
</UpdateParameters>

<DeleteParameters>
       <asp:ControlParameter ControlID="DropDownListLocation" Name="LocationID" PropertyName="SelectedValue" Type="Int32" />
       <asp:ControlParameter ControlID="DropDownListDOW" Name="DayName" PropertyName="SelectedValue" Type="String" />
       <asp:ControlParameter ControlID="DropDownListStartDate" Name="PracticeDate" PropertyName="SelectedValue" Type="DateTime" />
       <asp:ControlParameter ControlID="DropDownListEndDate" Name="PracticeDate2" PropertyName="SelectedValue" Type="DateTime" />
       <asp:ControlParameter ControlID="DropDownListStartTime" Name="StartTime" PropertyName="SelectedValue" Type="DateTime" />
</DeleteParameters>

</asp:AccessDataSource>

Please help me understand why the update is not changing data.

It seems that I either need to write UpdateCommands with hard coded parameters for the values to change or write a DeleteCommand and InsertCommand for each update that I want to perform. Please help me find some way to avoid that sort of kludge.

Answer 1

Usually a valid SQL Update statement fails without error because no records match your WHERE condition. Have you set a breakpoint on the Button_Click event and looked at the value of the parameters?

Answer 2

I might get downvoted for this useless answer but I couldn't help noticing that you are using xml constants for the symbols > and < within your UPDATE clause....Do a response write on the UpdateCommand to verify that the sql is valid, copy that SQL and paste it into Access and see if it is accepted by Access itself? Sorry if this sounds lame and stupid *sniff*

Hope this helps, Best regards, Tom.

Answer 3

Try and catch the Updating event, to see what is the SQL that gets executed when you run AccessDataSource6.Update();

Inside the event, you can get hold of the underlying SQL by looking at the instance of SQLDataSourceCommandEventArgs's Command property.

That should be the starting point.
If the SQL looks OK, try running that on your MDB to see, if it raises any error at all.

Answer 4

This may not be the answer, I don't use the data adpaters like this much, but I have noticed in the past that the order of the parameters being passed to a query against an access database matters.

Your list of update parameters does not match the order of the parameters in your update query - this may just be your issue (providing you've done the above and checked that there is something that matches your where clause).

Answer 5

First, after you update the database, what do you do? I have run into an issue where the access db is in your project and you it is automatically set to copy always to the output directory. If you run the code and then restart it, the access db is blown away. Just a thought, but one to make sure you are not doing.

Answer 6

From reading your question and all the follow-up I can see you've covered just about all the bases. I do have one thought, and it's really a shot in the dark. Perhaps the Access parser doesn't recognize the target columns as belonging to the table you're updating. Have you tried:

    UpdateCommand="UPDATE fbPracticeSlot 
    SET fbPracticeSlot.StartTime = ?, fbPracticeSlot.EndTime = ?
    WHERE ID IN (
    SELECT [PracticeSlotID] 
    FROM [vw_fbScheduleFull] 
    WHERE (([LocationID] = ?) 
      AND ([DayName] = ?) 
      AND ([PracticeDate] >= ?) 
      AND ([PracticeDate] <= ?) 
      AND ([StartTime] = ?))
   )"

Since Access supports multi-table updates, it is possible that the parser is confused because of the presence of a column named StartTime inside of the nested select.

In any case, it seems like you've tried everything else. Good luck!