I'm using a GridVeiw with and Access database. I want to allow users to select a date and then update the GridView to show only data entered after that date.
So my question is, how do I modify the SQL used by the datagrid? I'm using a control parameter as part of my AccessDataSource and I think that is what I'm having trouble figuring out how to include.
Here is my datasource, with the control parameter in the the GridView. The GridView works fine with these initial settings settings.<asp:AccessDataSource ID="AccessDataSourceForwView" runat="server" ConflictDetection="CompareAllValues"
DataFile="~/App_Data/HelpChanges.mdb"
DeleteCommand="DELETE FROM [HelpChangeLog] WHERE [ChangeID] = ? AND [CHMName] = ? AND [DateChanged] = ? AND [ControlOrProcedure] = ? AND [Owner] = ? AND [DescriptionOfChange] = ?"
InsertCommand="INSERT INTO [HelpChangeLog] ([CHMName], [DateChanged], [ControlOrProcedure], [Owner], [DescriptionOfChange]) VALUES (?, ?, ?, ?, ?)"
OldValuesParameterFormatString="original_{0}"
SelectCommand="SELECT * FROM [HelpChangeLog] WHERE ([ChangeID] = ?) ORDER BY [DateChanged] DESC"
UpdateCommand="UPDATE [HelpChangeLog] SET [CHMName] = ?, [DateChanged] = ?, [ControlOrProcedure] = ?, [Owner] = ?, [DescriptionOfChange] = ? WHERE [ChangeID] = ? AND [CHMName] = ? AND [DateChanged] = ? AND [ControlOrProcedure] = ? AND [Owner] = ? AND [DescriptionOfChange] = ?">
<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="ChangeID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
....</asp:AccessDataSource>
This the part of my codebehind page where I try to programatically change the SQL. (The SQL here is actually still the same, I haven't changed it yet - I just want to see if I can get this working as is, then I'll figure out the correct modified SQL to use.)AccessDataSource1.SelectCommand = "SELECT * FROM [HelpChangeLog] WHERE ([ChangeID] = ?) ORDER BY [DateChanged] DESC"
AccessDataSource1.UpdateCommand = "UPDATE [HelpChangeLog] SET [CHMName] = ?, [DateChanged] = ?, [ControlOrProcedure] = ?, [Owner] = ?, [DescriptionOfChange] = ? WHERE [ChangeID] = ? AND [CHMName] = ? AND [DateChanged] = ? AND [ControlOrProcedure] = ? AND [Owner] = ? AND [DescriptionOfChange] = ?"
AccessDataSource1.InsertCommand = "INSERT INTO [HelpChangeLog] ([CHMName], [DateChanged], [ControlOrProcedure], [Owner], [DescriptionOfChange]) VALUES (?, ?, ?, ?, ?)"
AccessDataSource1.DeleteCommand = "DELETE FROM [HelpChangeLog] WHERE [ChangeID] = ? AND [CHMName] = ? AND [DateChanged] = ? AND [ControlOrProcedure] = ? AND [Owner] = ? AND [DescriptionOfChange] = ?"
This is the error I get when I try to run the code. I think the problem is that I haven't taken into account the control parameter - how do I do that?:
Server Error in '/OnlineHelpChangeLog' Application.
--------------------------------------------------------------------------------No value given for one or more required parameters.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[OleDbException (0x80040e10): No value given for one or more required parameters.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +65
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +181
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +307
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +77
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +188
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +122
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +29
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +183
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +308
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +152
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2868
System.Web.UI.WebControls.AccessDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +179
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +84
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +154
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +99
System.Web.UI.WebControls.GridView.DataBind() +24
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +91
System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e) +33
System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +74
System.Web.UI.Control.PreRenderRecursiveInternal() +148
System.Web.UI.Control.PreRenderRecursiveInternal() +233
System.Web.UI.Control.PreRenderRecursiveInternal() +233
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4437
![]() |
0 |
![]() |
I tried using the following code, but it also gives an error. But am I on the right track here?
AccessDataSource1.SelectCommand =
"SELECT * FROM [HelpChangeLog] WHERE ([ChangeID] = ?) ORDER BY [DateChanged] DESC"AccessDataSource1.UpdateCommand =
"UPDATE [HelpChangeLog] SET [CHMName] = ?, [DateChanged] = ?, [ControlOrProcedure] = ?, [Owner] = ?, [DescriptionOfChange] = ? WHERE [ChangeID] = ? AND [CHMName] = ? AND [DateChanged] = ? AND [ControlOrProcedure] = ? AND [Owner] = ? AND [DescriptionOfChange] = ?"AccessDataSource1.InsertCommand =
"INSERT INTO [HelpChangeLog] ([CHMName], [DateChanged], [ControlOrProcedure], [Owner], [DescriptionOfChange]) VALUES (?, ?, ?, ?, ?)"AccessDataSource1.DeleteCommand =
"DELETE FROM [HelpChangeLog] WHERE [ChangeID] = ? AND [CHMName] = ? AND [DateChanged] = ? AND [ControlOrProcedure] = ? AND [Owner] = ? AND [DescriptionOfChange] = ?"GridView1.DataSource = AccessDataSource1
GridView1.DataBind()
The error I get hapens at runtime, and the debugger points to the line 'GridView1.DataBind()'
Here is the error text:
"Both DataSource and DataSourceID are defined on 'GridView1'. Remove one definition."
![]() |
0 |
![]() |
If you have a textbox with id of TextBox1 ( not in the gridview ) in which you enter your date the Select string would be:
Select * FROM HelpChangeLog WHERE DateChanged >= ? ORDER BY DateChanged
Your Select Parameters would be:
<asp:ControlParameter ControlId="TextBox1" Name="DateChange" PropertyName="Text" Type="DateTime" />
You don't need any code or to modify the Select SQL, every time the user enters a different date in TextBox1 ( as long as autopostback is set on text box ) gridview will update. Your select statement at the moment is selecting on ChangeID which I assume is the unique key for your table
There are a number of errors in your code as far as I can see:
The control parameter you are using is pointing at GridView1 selected value
When you set the GridView datasource this needs to be a dataset. If you are using an AccessDataSource you use GridView1.DataSourceId="AccessDataSource1" - although you don't need to do this if using control paramters correctly
Hope that helps, Tim
![]() |
0 |
![]() |
Thanks, Tim! With your help I got it working now.
(I mistakenly gave the AccessDataSource for the FormView instead of the GridView - so even more thanks for helping me in spite of my mistake.)
Here's my working code, if anyone is curious:
<asp:AccessDataSource ID="AccessDataSource1" runat="server" ConflictDetection="CompareAllValues" DataFile="xxx.mdb"
DeleteCommand="DELETE FROM [HelpChangeLog] WHERE [ChangeID] = ? AND [CHMName] = ? AND [DateChanged] = ? AND [ControlOrProcedure] = ? AND [Owner] = ? AND [DescriptionOfChange] = ?"
InsertCommand="INSERT INTO [HelpChangeLog] ([CHMName], [DateChanged], [ControlOrProcedure], [Owner], [DescriptionOfChange]) VALUES (?, ?, ?, ?, ?)"
OldValuesParameterFormatString="original_{0}"
SelectCommand="Select * FROM [HelpChangeLog] WHERE [DateChanged] >= ? ORDER BY [DateChanged] DESC"
UpdateCommand="UPDATE [HelpChangeLog] SET [CHMName] = ?, [DateChanged] = ?, [ControlOrProcedure] = ?, [Owner] = ?, [DescriptionOfChange] = ? WHERE [ChangeID] = ? AND [CHMName] = ? AND [DateChanged] = ? AND [ControlOrProcedure] = ? AND [Owner] = ? AND [DescriptionOfChange] = ?">
<SelectParameters>
<asp:ControlParameter ControlID="begindateTextBox" Name="DateChanged" PropertyName="Text"
Type="DateTime" />
</SelectParameters>
..........</
asp:AccessDataSource>
![]() |
0 |
![]() |