DataSource Controls :: Dynamically Changing SqlDataSource.SelectCommand From FormView.ModeChanged?
Nov 17, 2010
In an asp.net 3.5 web app, I have a page with the following markup/code (this is the brief version, but I believe I got all the relevant info).
[Code]....
The form contains 2 different record types, accessed using the paging controls, depending on the ParentSR field; a parent SR will be something like '1234' while its sub-record will be something like '1234.1'. There are only a few differences in the fields used between the 2 different record types and that is why they are stored in the same table. However, the activity depends on the record type. When browsing from the parent record to the sub-record, the itemtemplate loads fine (notice in the formview's datasource
query, that the sractivity_id field is being pulled along with the subquery value). But when going to edit the record, the page throws an error:
System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.ArgumentOutOfRangeException: 'ddlActivity' has a SelectedValue which is invalid because it does not exist in the list of items.
If i click 'back' and retry, it may take 3 attempts, but then the edittemplate will load just fine. I should also point out that this error does not always occur. I've been trying to figure this out for a couple months and it is driving me nuts, mainly due to the fact that I can't reproduce the error in development--it seems to only occurr in production. Anyone with any thoughts on what is going on?
There are cases where I have to change this query dynamically at runtime, so I do the following:
SqlDataSource1.SelectCommand = sql; // 'sql' is the new query GridView1.PageIndex = 0; GridView1.EditIndex = -1; GridView1.SelectedIndex = -1; GridView1.DataBind(); updatePanel.Update();
This works just fine actually, but when I click the pagination controls, the result set defaults back to the SelectCommand defined in the SqlDataSource1.
I have a SqlDataSource setup in the markup on my page. After some new requirements, I am trying to find a way to modify the SelectCommand before the data is filled for the first time. The SelectCommand below will now be used by default since we'll always have the first param. I am faced with a need to modify the SelectCommand though, adding a second session parameter along with it. I tried to override the OnInit method but it didn't seem to help...it still used the default query. Is there a best way to accomplish this? Here is the section of markup I am referring to:
everytime I modify an SQLdatasource using VS wizards, I have to dettach the mdf in VS's server exporer, then hit refresh in the browser, then get the applicaiton is offline error message please remove some pia file, but i usually just count to 12 and then hit refresh again and the page renders properlyhad scoured the forum on more than one ocasstiion, think it has something to do with the Admin rights within SQL, Ive tried the SQL server surface configuration tool, and add administrator stuff, but not sure if i should add (user to provision) something like the asp.net thing, or network services or ??also cant figure out where to view which administrators are actually on SQL server,
In my application, I am using DataList. In Item Template I added One GridView and SqlDataSource to populate data in GridView. DataList having one DataKey field, I want to pass this DataKey Value to the SqlDataSource as Parameter.
a FormView that is linked to SqlDataSource1a Repeater control (inside the formview) that is linked to SqlDataSource2Thus representing a simple "1 to many" or "parent/child" relationship in the database!qlDataSource1 is simple in that it just retrieves a straight set of records from a table and they are displayed back in the FormView (nothing special happening there).SqlDataSource2 has parameters that filter the contents of the repeater based on the record that is shown in the Formview. This would normally be simple enough to set up by setting the parameter to the formview.selectedvalue property.HOWEVER - THE PROBLEM ARISES...The formview has multiple DataKeyNames because the table that the SqlDataSource1 links to has a compound key. So to filter the Repeater correctlySqlDataSource2 needs to map 3 parameters to 3 data key values in the formview.Any ideas how to do this?I've tried setting the SqlDataSource2 parameters to:
With asp 2.0 and vwd 2005, I have a page with a radiobuttonlist, textbox and gridview with edit/delete. Using the radiobuttonlist, the user selects a search type and then enters the criteria in the text box. the click event of a submit button checks the rbl.selectedindex and chooses a sqldatasource.selectcommand with the where clause getting its criteria from the textbox. The gridview loads the data, but the edit/delete are not working. Code and relevant markup is below
I've already spent hours in searching through the forum to be able to do the following:I have a SQL datasource defined with a checkbox in it. Purpose is to update one column (userid) behind the table for only that row when the checkbox is selected.This is the code I'm currently having:
My users have "demanded" to have an export function to their gridviews, and since they're running from usercontrols there's no way to modify the response stream. My workaround is to create an aspx to run in a hidden iframe. To do that, and to make it modular, I'm trying to dynamically create everything from the querystring. This *seems* to work just fine until I bind my sqldatasource to the dummy gridview. I then get "Procedure or function 'xxx' expects parameter '@NumberResults', which was not supplied."
I have a DDL with 2 options: "Trainer" & "Observer." I'd like to use the selection from that DDL to reference a field in the SqlDataSource SelectCommand.
There are 2 fields in the table that may be referenced in the SelectCommand:
"Trainer_Oracle_ID" & "Observer_Oracle_ID." I'm trying to do something like:
WHERE @Oracle_ID_Type = <%=DropDownList_Oracle_ID_Type.Text%>_Oracle_ID
i.e. User selects "Trainer" and the SelectCommand references the field called:
I have an ASP.NET 3.5 web form with a DropDownList bound to a table of company names in a database. I also have a data bound GridView which I would like to update with data from the database depending on the company name selected in the DropDownList, so that the SelectCommand for the GridView's SqlDataSource is:
SELECT Registration, Telephone, Profile FROM {CompanyName}_VehicleData
Where {CompanyName} is whatever is selected in the DropDownList. I've used the Command and Parameter Editor to create a ControlParameter pointing to the SelectedValue of the DropDownList, but I don't know how to write the SelectCommand query to concatenate the parameter to '_VehicleData'.
I am trying to pass a variable within a selectcommand statment in ASP.NET and I have researched online how to do it but I can't seem to find a way to do it...What I am trying to do is write a code for a web page that will display a page with useful links on it where the links are separated into different topics and where everything is dynamically grabbed from a database. This is what I have so far:
My SelectCommand string in my asp:SqlDataSource is very long. Is there a way I can chop it up into different strings on several newlines to make it more readable?
I have a question related to <asp:SqlDataSource's SelectCommand. How to pass a querystring like <%# Eval("PhotoID") %> in the selectCommand. The whole frontend code is attached. I put
<%# Eval("PhotoID") %> and the photoid gets printed in the page. <%# Eval("PhotoID") %> is the current displayed picture's PhotoID.
I want to pass this PhotoID or I want to pass <%# Eval("PhotoID") %> to the select command dynamically. I have tried the following approaches:
1) <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:aspnetdb %>" SelectCommand="SELECT UserName, commentDesc, uID FROM photo_comment_view Where pID = @pID"> <SelectParameters><asp:Parameter Name="pID" DefaultValue="7" /></SelectParameters> </asp:SqlDataSource> The above works but it gives only information about PhotoID 7, but there are other photos; How to set pID's value to <%# Eval("PhotoID") %> in ASP .NET's frontend page? then I also tried,
2) <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:aspnetdb %>" SelectCommand="SELECT UserName, commentDesc, uID FROM photo_comment_view Where pID = <%#'Eval("PhotoID') %>"></asp:SqlDataSource> Gives Parser error: Parser Error Message: The server tag is not well formed.
What I should do so that I can pass current PhotoID to SelectCommand? The frontend code:
My SqlDataSource has no selectCommand defined in the .aspx page. Instead I define it in the code behind dynamically depending on some parameters that the user selects and then clicks a button to perform the query (custom search filters basically). The problem is that when I click on a different page in my dataPager, or I click to sort any of the columns the gridview becomes empty when the page refreshes. I did a check and the selectCommand becomes empty when the page posts back. Is there a way to prevent this from happening?
I've got a gridview which works fine. I use dropdownlists which are filled with distinct values of the columns. So when I select an item in a drompdownlist the gridview refreshes based on the selected value. When I have a column which uses string values, I can start the filter with a default % value to use in my select paramaters. But when I have a date column, I can't use that. I use a datasource for the gridview and I prefer to leave codebehind out if this is not needed. Now I use >= in my gridview select to show all rows. but I want to use only = to make the filter work correct when I select a date in the ddl and use the selectedvalue in the gridview selectCommand
<asp:DropDownList ID="ddlUpdateDatum" DataSourceID="dsPopulateUpdateDatum" AutoPostBack="true" DataValueField="UPDATEDATE" runat="server" CssClass="selectinput" AppendDataBoundItems="true"> <asp:ListItem Text="All" Value="01/01/1900"></asp:ListItem> </asp:DropDownList> <asp:SqlDataSource ID="dsPopulateUpdateDatum" runat="server" ConnectionString="<%$ ConnectionStrings:GAMSYSTEMSqlServer %>" SelectCommand="SELECT DISTINCT(UPDATEDATE) FROM [messages] ORDER BY UPDATEDATE ASC "> </asp:SqlDataSource> And I use this in the gridview datasource: <asp:SqlDataSource runat="server" ID="dsGridview" ConnectionString="<%$ ConnectionStrings:DBSqlServer %>" SelectCommand = "SELECT * FROM [messages] WHERE AND UpdateDatum >= @UpdateDatePar ORDER BY UpdateDatumDESC" > <SelectParameters> <asp:ControlParameter Name="UpdateDatePar" ControlID="ddlUpdateDate" PropertyName="SelectedValue" Type="string" /> </SelectParameters>
I have a page has Listview and Datapager inside UpdatePanel. When I refresh the new SQL results and bind by pressing link buton outside of the update panel, I get correct results. (get new SQLselect statment, rebind to listview, update updatepanel)
However, the SelectCommand is not bound right or some other reason, if I press datapager, I won't get no results.
I'm guessing it's caused by not passing new dynamic selectcommand since my datapager and listview works first update.
