How to change the SQL used for the GridView, when using a control parameter in the datasource

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
hapax_legomenon
7/17/2006 3:22:47 PM
asp.net.access-datasource 4679 articles. 0 followers. Follow

3 Replies
1218 Views

Similar Articles

[PageSpeed] 41

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
hapax_legomenon
7/17/2006 4:41:57 PM

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
cashmore
7/17/2006 10:26:02 PM

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
hapax_legomenon
7/18/2006 4:14:24 PM
Reply:

Similar Artilces:

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

code for Custom paging without using sqldatasource control and sql datasource control for gridview...........
 code for Custom paging without using sqldatasource control and sql datasource control for gridview...........i need code for how to show all pagenumbers in gridview with custom paging in gridview..............It is very urgent................................. T.Ashok Kumar  suppose that we have a table like thisCountryId  identity intCountryTitle    nvarchar(50)you can use this sql command for paging the dataUSE [CyberOffice]GO/****** Object:  StoredProcedure [dbo].[CountryGetListPaged]    Script Date: 09/17/2008 14:01:06 ******/SET A...

Problem using two gridviews when using selected value of 1st to control datasource of 2nd
 I have a page with 2 gridviews. When you select a row in the first gridview, the DataKey of that row gets passed to the second gridview, which then displays a list of related records. I've read at http://www.asp.net/learn/data-access/tutorial-08-cs.aspx that there can be issues around page lifecycle timing that can affect what I'm doing so I've tried to follow the instructions there. However, I'm still having problems.Here's the flow of what I want to happen:a) User clicks row in gridview1.b) Postback occurs.c) gridview1 is selected, gridview2 shows records relating...

Can I use a Formula Field or Report Parameter in my SQL Command when using a Command as my datasource?
Instead of having the database tables as my datasource, I'm creating a command (double clicking the Add Command Node under my server connection in the database expert) and using that for the fields on my report. I need to be able to use a formula field (or even a report parameter) in that command SQL so that I can pass dates that will change at runtime to my SELECT statement. Is that possible? Yeah it is possible but you have to preset the filters in your report and then pass the information (i.e. dates) as parameters. To learn how to do this there is an article on www.aspalliance.com/...

Trying to use the FAQ " Sorting and paging in the GridView control when not using data source controls "Sorting and paging in the GridView control when not using..."
and I am not getting the results I had hoped for.  I keep getting a "Compiler Error Message: CS0123: No overload for 'AdhocJobGridView_PageIndexChanged' matches delegate 'System.EventHandler'".   Here is my event handling... protected void AdhocJobGridView_PageIndexChanged(object sender, GridViewPageEventArgs e){ try { AdhocJobGridView.DataSource = wsBaseInfoArray; AdhocJobGridView.PageIndex = e.NewPageIndex; AdhocJobGridView.DataBind(); }catch (Exception ex) {throw ex; }   Here is my aspx... <asp:GridView ID="AdhocJobGridView...

Custom Datasource Controls Vs. Object Datasource Controls
Hi folks,I am a web developer developing pages in the project. I need to integrate the front end pages with the backend API. Active directory is being used as the database system for our project. I only use the API and the methods offered by it to interact with the data.For all the operations on the data, I have methods that I can call from the API (viz. Create(), Delete(), Modify() etc.). For connecting the data to my frontend data controls (viz. GridView, DropDownList etc.) I can use Object Datasource Control. I also read some articles about Custom Datasource controls. Now, I am having a d...

Change SQL in datasource using Dropdownlist
I want to have a Dropdownlist on a Form with selectable values:- Query1, Query 2, Query 3. I have a gridview with datasource selecting and displaying record based on a SQL statement. I want on select dropdownlist, if Query1 is selected value, that SQL select statement changes to "Select A,B,C etc"if Query2 is selected value, that SQL select statement changes to "Select D,E,F etc"if Query3 is selected value, that SQL select statement changes to "Select H,I,J etc" How to code this in VB ? Pls help. ThanksThanks in advance for the help. I will try to credit the ...

Which select parameter to use for GridView DataSource?
I have transfered the values of two TextBox controls from the previous page by using the following code below. public void Page_Load(object s, EventArgs e){     if (PreviousPage != null)     {     TextBox ctrlCourseNumber = (TextBox)PreviousPage.FindControl("ctrlCourseNumber");         TextBox ctrlLastNameOfProfessor = (TextBox)PreviousPage.FindControl("ctrlLastNameOfProfessor");      }} I would like to use these values for the select parameter of...

Gridview Filter using Dropdown where dropdown is populated from a distinct list in the Gridview
I have a report page that shows the user who accessed pages, when they accessed it, and the page they accessed.  I have it attached to some dates, so they can easily filter the search down.  What I want to do is to further filter the list with a dropdown of users, so they can see which user viewed what pages over a certain time frame.  I know how to query the database for a list of users and then populate the dropdown with all the users...then attach that dropdown to the sqldatasource for the gridview to filter it down by user.   But, what if I just want to popu...

Cannot use same sql datasource for formview
Hello, I have problem that is confusing me... I am using a gridview on page1 that when selected navigates to page2 queried by the ID. On page2 i have a gridview which uses a sqldatasource using the query parameter of the ID taken from Page1. I have this working, but when i try to replace the gridview with a formview (with the same sqldatasource) i get the following error: 'System.Data.DataRowView' does not contain a property with the name 'publicationID' Why can i not use the same sqldatasource in the formview??  Kind regards, Hi, Where you getting that error? Can...

How to achieve this using SQL with Access mdb as datasource ?
I have an Access table like this:- Name, Value, Effective ============ John,12, 1 Jan 2008 John, 14, 1 Jun 2008 John, 16, 1 Jan 2009I want add an end date to make it like:- Name, Value, Effective, Enddate ============ John,12, 1 Jan 2008, 31 May 2008 John, 14, 1 Jun 2008, 31 Dec 2008 ie what I need is to add enddate as additional field which is the Start date of next record minus 1. Is this possible ? Thanks in advance for the help. I will try to credit the ones who helped but most important is we really do sincerely thanks to all who have helped. Pls help........... Thanks....

Use a page property as a datasource control parameter
Hi,How can I hook up a property I've added to my codefile page as a parameter in a datasource control.Ask and it will be given to you; seek and you will find; knock and the door will be opened to you. Luke 11:9...

5 datasource using one gridview control
Hi: I have a dropdown list, according to user select different item value, such as: male, female, country... then showing the gridview. At the moment, I have 5 gridviews (all have same coloums and format from one table) to show the result (infact using hide and show panels), I feel that is not good way, too many gridviews! I am think using one gridview to show the result according to which item is selected. I feel hard to handle this because it needs binding the datasauce dynamically. How to set the DataSourceID dynamically? it can save a lot of lines of auto generated code...

Binding GridView without using an SQL datasource
I am currently working on porting a solid VB6 app to ASP 2.0 (yeah a big jump). The app has a tier structure and is heavly dependent on the business layer. Due to that i cannot use simple ASP direct Databinding and need to use binding to datasets. I have started experimenting to get some traction and I cannot get the simplest example to work !! here is some test code. Here is what happens that is very puzzling to me. Upon hitting "edit" the event gets fired and the browser displays a blank page. I even tried to persist the DataSet object as a private class variable but that became...

Web resources about - How to change the SQL used for the GridView, when using a control parameter in the datasource - asp.net.access-datasource

Datasource - Wikipedia, the free encyclopedia
A DataSource object has properties that can be modified when necessary. For example, if the data source is moved to a different server, the property ...

Inverness Graham Acquires DataSource
Inverness Graham , a lower middle market private equity firm headquartered in suburban Philadelphia, has acquired DataSource , a print supply ...

SmartGlance for iPad for iPad on the iTunes App Store
Read reviews, get customer ratings, see screenshots, and learn more about SmartGlance for iPad on the App Store. Download SmartGlance for iPad ...

Chaitanya Pandit (@chaitanyapandit) on Twitter
Sign in Sign up To bring you Twitter, we and our partners use cookies on our and other websites. Cookies help personalize Twitter content, tailor ...

Data source - Wikipedia, the free encyclopedia
... Data source A data source is any of the following types of sources for (mostly) digitized data: a database in the Java software platform, datasource ...

2ndQuadrant - PostgreSQL expertise from specialists with a source code level understanding of RDBMS ...
PostgreSQL expertise from specialists with a source code level understanding of RDBMS PostgreSQL Planets Gabriele’s PlanetPostgreSQL Gianni’s ...

Tagged entries for CLOUD COMPUTING
Alan Williamson's output as a Java Champion, Blog-City Architect, BlueDragon Creator, Author, Speaker and Internet Guru

Private equity deals
... of biometric identity management systems, applications and services. www.crossmatch.com Inverness Graham Investment has acquired DataSource ...

C# C Sharp and Tutorials on C# Friends.com
Learn the c# langauge to build web applications using our online tutorials with live demos. Participate in our forums and learn from others. ...

JavaScript UI Library, Ajax Components & HTML5 Framework - DHTMLX
DHTMLX offers a rich JavaScript library, UI components & HTML5 mobile framework. Build impressive web apps for both desktop and mobile devices. ...

Resources last updated: 12/12/2015 7:50:24 AM