assign SQL to datasource dynamically


I have a default SQL statement assigned to the datasource: select * from customers. But when I want to dynamically change this at runtime, e.g.: accessdatasource.selectcommand="select * from categories where custCity="&  lblCity.Text (lblcity is in this case NY of which there are plenty fields in my DB) it appears that after the postback this command is lost, the gridview linked to the datasource disappears from the screen....

This is my problem and this is what I heard before as an answer:
"You only specify the SelectCommand when the button is pressed. When you don't press the button and do other postback, the AccessDataSource SelectCommand is not set. Make sure you set a default SelectCommand for the AccessDataSource. The SelectCommand will not be saved into any state so it will not keep the state during a postback."

1.   So is it possible to dynamically assign a selectcommand to the datasource?

2.   if not: is there is another way to display data dynamically in customized tables on which im able to sort and page?

please mark answers as 'Answered' and post back solutions when you figure stuff out that isnt in the post already.
9/24/2005 10:40:06 AM 72751 articles. 3 followers. Follow

6 Replies

Similar Articles

[PageSpeed] 45

The SelectCommand of the Data source control will not be added to any state. So you need to set it on every postback. If you for example only set the SelectCommand when a button is pressed, the GridView will be empty everytime you do a postback becuase the SelectCommnd isn't set.

So in your case, you must specify a SelectCommand even when the button isn't pressed.
/Fredrik Normén - fredrikn @ twitter

Microsoft MVP, MCSD, MCAD, MCT

My Blog
9/26/2005 5:18:52 AM
I have specified a default select command, but the thing is I want to change this at runtime, is it possible to dynamically change this command? How can I make sure the gridview is filled (sorted or paged) after a postback?
This thing is really driving me crazy, but it HAS to be possible right?

please mark answers as 'Answered' and post back solutions when you figure stuff out that isnt in the post already.
9/26/2005 6:48:34 AM
Hello Peter.

well, I've built a small sample over the northwind database that shows how you acn do that. I've written it here and haven't done any testing on it (so it may have one or more things that don't work as expected). Notice that you have to 1) mantain the current select command between postbacks and 2) update the sort expression and page index when you change the select command:

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">

<script runat="server">

void P(object sender, EventArgs args)


Button bt = (Button)sender;

if (string.CompareOrdinal(bt.ID, "customers") == 0)


ViewState["sql"] = "select * from customers";




ViewState["sql"] = "select * from categories";




void RebindData()


source.SelectCommand = ViewState["sql"].ToString();

grid.PageIndex = 0;

grid.Sort("", SortDirection.Ascending);


protected override void OnLoad(EventArgs e)



if (ViewState["sql"]!= null && !string.IsNullOrEmpty(ViewState["sql"].ToString()))


source.SelectCommand = ViewState["sql"].ToString();




<html xmlns="" >

<head runat="server">

<title>Untitled Page</title>



<form id="form1" runat="server">

<asp:SqlDataSource runat="server" ID="source"

ConnectionString="data source=.;Integrated Security=SSPI;Initial Catalog=Northwind;"

SelectCommand="select * from customers" />

<asp:GridView runat="server" ID="grid" DataSourceID="source" AllowSorting="true"

AllowPaging="true" PageSize="3" PageIndex="0" />

<asp:Button runat="server" id="customers" Text="Customers" OnClick="P" />

<asp:Button runat="server" id="categories" Text="Categories" OnClick="P" />




Luis Abreu
EN blog:
9/26/2005 10:19:01 AM
No need to rewrite, it works as far as I can see now! :)
Okay, but apparently there's another issue:
I have several dropdownboxes which are parameters for my search query: age, gender etc.
When the user searches I want to fire THAT query on the database and show the results in the grid (and ofcourse be able to page that grid). That means  I have my query (oledbcommand) and have to assign it to the datasource select command which in turn provides it to the gridview.
(Ofcourse) I cannot simply fill the selectcommand of the datasource as the viewstate is not maintained and therefore paging on the results will be can I fix that? (bearing in mind that I use OleDB for my command, and I do that because it handles my date fields for me perfectly)

please mark answers as 'Answered' and post back solutions when you figure stuff out that isnt in the post already.
9/26/2005 4:38:52 PM

well, you can use parameters for the query and configure them so that they get their values from the controls. in ohter words, you'll have to use selectparameters and add the controlparameters to that collection...
Luis Abreu
EN blog:
9/27/2005 11:19:53 AM
Could you provide me with an example please?
please mark answers as 'Answered' and post back solutions when you figure stuff out that isnt in the post already.
9/27/2005 3:37:39 PM

Similar Artilces:

gridview, access datasource, dynamic assignment sql statement
I have a gridview "gvTable" and a datasource "accessdatasource1".When I load the page all the data from the table "Models" is displayed.When I click the Categories button I want to have the gridview display the rows from my "Categories" table (which contains columns: ID and CategoryName), on pages of each 5 rows in length.It does display all the info in the right amount of rows.HOWEVER: when I click on the next page or column header to sort I get the error:"System.IndexOutOfRangeException: Cannot find column CategoryName"Here's all my code: <asp:GridView ID="gvTable" runat="server" Allow...

Dynamically assigned events to Dynamic controls
Ok I have seen this happen to many people. I have read the suggestions and feel I am still missing something. The created imagebuttons do not fire the assigned btnImg_Click method although the page does post back. Here is my code for reference. public class Navigation : System.Web.UI.UserControl { private Security _security; private PrivilegeDS _privileges; private void Page_Load(object sender, System.EventArgs e) { _security = (Security)HttpContext.Current.Session["SECURITY"]; _privileges = _security.GetUserPriviledges(); crea...

Is it possible to assign datasource dynamically at runtime to crystal report(.rpt) with VB.NET 2.0??
Hi experts, Can any one suggest me Is it possible to assign datasource dynamically at runtime to crystal report(.rpt) with VB.NET 2.0??I can only use Datasets in my case!! Typed-datasets are column-fixed right!!! But my data I assign is dynamic???? I actually gave a typed dataset having datatable with 4 fixed column  as datasource to crystal report .rpt at design timeBelow is my piece of code    Dim mydt As DataTable = New DataTable            schemadt = DummyTable() 'Function to retrieve Data TableDim r...

Dynamic Controls Within Dynamic Controls
Ok, So here's what i'm trying to do: I have the first set of controls, on the page which are dynamicly added, i'll just show simplified code, do to the complex nature of the full thing, so this is part one: private void Page_Load(object sender, System.EventArgs e) { if (!Page.IsPostBack) Label1.Text = ""; for (int i = 0; i < 10; i++) { Button li = new Button(); li.Text = "Click Me - " + i.ToString(); li.ID = i.ToString();   li.Click +=new EventHandler(li_Click); PlaceHolder1.Controls.Add(li); PlaceHolder1.Controls.Add(new LiteralControl("<br/>"));   } } N...

Dynamic Slave Controls
I am working a solution to enhance slave controls. I have created a proof of concept based on the 2.04 code base that shows how to load slave controls without switching to the admin view. I have included a sample module that contains 3 view controls. Each control has a row of buttons that can be used to load one of the other 2 controls. (and no I am not using panels.) The default control has a drop down of other instances of the dynamic control that exist on the current tab. Using the module communicator you can raise an event from one control that tells another instance of the module what c...

postback from dynamic controls to create more dynamic controls
Hi, Could any one help me?  I have a masterpage with dynamically created list boxes.  When the user selects an item from the listbox the selected value will be used to dynamically create a set of controls in the contentplaceholder.  The problem i have is that the item selected from the list box is posted back and i do not know what the selected item is until the life cycle is complete.  During the post back is there a way to findout which listbox control performed a post back and its value before i get to the init part of the content page where i woul...

Dynamic control and SQL
Here is an practical issue: I loaded a text and IDs of dynamical buttons from the DataBase. Is it better to keep it this way every time on postback, or to store information in the session, as it releases multiply connections with SQL server, but takes server's resources more. Can somebody explain to me differences between few ways to do it. Thanks people! Hi,kikiriki Ok I understand what u r asking that suppose first time u have fetch the data from database and then bind that to a dropdownlist .Then suppose there is button and its click event will cause the postback....

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...

dynamically assigning style sheet styles to dynamically created controls (e.g. table cells)
Hi,Dynamically adding controls, say table cells, to a page.The table cells need to be formatted.Can be done, like so: tablecell1.width = 100; tablecell1.height = ...; //do for all attributes, for all table cellsWorks fine. But. If you have many html objects which have to be formatted then this creates a lot of html mark up which slows down downloads, e.g.:<td height = ... width = ... bgcolor = ... color = ... = ... /> Is there a way to dynamically assign a style sheet style instead so that the html looks something like:<td style = stylesheet1.style1 />Where style 1 is ...

Dynamic Controls, Placeholder, Retrieve Information from Dynamic Controls
I have been searching forever. Here is my problem. I have form of 3 panels of information for car insurance information. 1st panel is general information but I need to retrieve the NUMBER OF FAMILY members to see how many names I need for my next panel that is dynamically generated based upon my drop down list selection. I can generate panel 2 with the number of names I need but the problem is then posting the data from the textboxes to the next page. ERROR: Object reference not set to an instance of an object. I try to retrieve from a dynamic box txt1, txt2, etc. I have trie...

Data presentation control in another Data presentation control
 Hello,I want to make a menu with datalist or gridview control.Basically,the menu will be like this,Categories     Sub Categories.For example, for COMPUTING category there will be NOTEBOOKS DESKTOPS etc.There will be a image for CATEGORIES and linkbuttons with Subcategories.If I put a datalist inside a datalist and a link button inside the second datalist visually it satisfies my need.However,CommandArguments come from database to linkbutton.Andwhen one of these command buttons is cliked it must go to proper page.I dont know how to wrie an event_handler ,specifically, for...

Help with dynamic controls and retrieving data from dynamic controls
I am new to web development. My requirement is Text boxes get generated dynamically based on user input on click of a button called Ok after user enters values to these textboxes when user clicks on submit button ,values user entered to these textboxes have to be retrieved for further calculation I am generating text boxes in the ok button click event how to retrieve value user enters to these n where the procedure for that to be placed here u need to give name to id of every texbox in a specugic patterns so than u can know next time.   static int i = 0;protected...

Dynamically Loaded Control Wants to Change The Dynamically Loaded Control
I have been playing around with dynamically loading controls and AJAX and have the following:An UpdatePanel has a PlaceHolder which which contains a dynamically loaded control.  The control that is dynamically loaded has a button that is supposed to change the control that was loaded.  So after the link button is clicked on DynamicControlA, DynamicControlB should now be loaded where DynamicControlA used to be. Is there anything in the framework to do this?  If not, any ideas on how to tackle? I did it with Panels.  Made 5 panels all the same size, and toggled visi...

Loading controls dynamically based of previously dynamically created control
Hi All, Heres the deal. I have a hierarchal or tree structure of data that needs to be represented in dropdownlists (ddl). The root is a single ddl and the items in the ddl are children of the root. When a child is selected I need to add another ddl to the control collection with it's children as listitems. Its like dropdownlist chaining without knowing the order of the dropdownlists. Now I know I can use the Page.OnInit method to add the ddl, but since viewstate is not loaded until after OnInit is called I don't have access to the selected child. Right know I am getting around this by u...

Web resources about - assign SQL to datasource dynamically -

Placebase team at Apple file “Schematic Maps” patent dynamically detailing important data
In a report from Computerworld way back in 2009, we learned that Apple had quietly acquired Maps API company Placebase. Then, founder of Placebase ...

Apple's Lightning port dynamically assigns pins to allow for reversible use
... a closer look at how Apple's new Lightning connector works, and has come to the conclusion that the 8 pins on each side of the plug are dynamically ...

In a dynamically typed language, is it a bad idea to return different data types?
In some cases, returning different types from a single function works.

Urlbox Launches Screenshots-as-a-Service Platform to Dynamically Update Galleries
... changes. Launched yesterday, Urlbox allows anyone with a gallery displaying website screenshots to automate the process and keep them dynamically ... launcher dynamically adapts to what you’re doing right now
... phone according to what you’re doing. Perform a search for ‘football’ and your background will change to the NFL logo. A launcher that dynamically ...

A YouTube Experiment Resizes the Player Dynamically
There's a new YouTube experiment that changes the video player's size, depending on the browser window's size. When resizing Chrome's window, ...

Apple Granted “Dynamically Changing Cursor for User Interface” Patent, Originally Filed in 2003
... a utility patent that covers the use of the mouse cursor that changes according to the context of the task it is engaged in. Called a “Dynamically ...

Atmel's FPSLIC II Dynamically Reconfigurable SoC Supports "Silicon-Sharing" For Peripherals & Interfaces ...
Silicon-Sharing Fits 200K Gate Design into 40K Gate FPSLIC II's, Cuts Power Drain by 97% Launcher Wants to Dynamically Change Phones Based on Location, Interests, and Mental State ...
What if your phone’s launcher dynamically changed based upon interests, your location, or topics you had in mind at a specific moment? A new ...

Resources last updated: 11/29/2015 6:50:49 PM