How To Set SessionParameter And QueryStringParameter Both In SelectParameters Of SQLDataSource.
Mar 14, 2008
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CollegeDatabaseConnectionString %>"
SelectCommand="SELECT employee_datails.* FROM employee_datails WHERE memberid !=@id OR memberid OR memberid==uid">
<SelectParameters>
<asp:SessionParameter Name="id" SessionField="userid" />
<asp:QueryStringParameter Name="uid" QueryStringField="memberid" />
</SelectParameters>
</asp:SqlDataSource>
I tried like this but it is not working. It takes at a time one sessionparameter or querystringparameter.
I want if profile.aspx--> then i want session parameter in select query
If profile.aspx?id=12432---> then i want querystringparameter in select query.
Hello, I am sure there is a way to do this programmatically, but it is just not sinking in yet on how to go about this. I have a page where I use a dropdownlist that goes into a gridview and then the selected item of the gridview into the detailsview (typical explain seen online). So, when selecting from the dropdownlist, the gridview uses a controlparameter for the selectparameter to display the appropriate data in the gridview (and so on for the detailslist). My question is - I wanted to use this same page when coming in from a different page that would utilize querystring. So, the parameter in the querystring would be for what to filter on in the gridview, so the dropdownlist (and that associated controlparameter) should be ignored. Any suggestions on how to go about this? I assume there is some check of some sort I should in the code (like if querystring is present, use this querystringparameter, else use the controlparameter), but I am not sure exactly what I should check for and how to set up these parameters programmatically. Thanks, Jennifer
Hi, I am using a grid view with parameter defined in dropdown list. the asp code is: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Pubs %>" SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors] WHERE ([state] = @state)" DeleteCommand="DELETE FROM [authors] WHERE [au_id] = @au_id"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="state" PropertyName="SelectedValue" Type="String" /></SelectParameters> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Pubs %>" SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors] WHERE ([state] = @state)" DeleteCommand="DELETE FROM [authors] WHERE [au_id] = @au_id"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="state" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource> </asp:SqlDataSource> I would like to to set the parameter assignment in my VB code in Page_Load methodWhen i am using: SqlDataSource1.SelectParameters("state").DefaultValue = DropDownList1.Text SqlDataSource1.SelectCommand = "SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors] WHERE ([state] = @state)"I get error message saying: Object reference not set to an instance of an object. Any ideas how I can set the select parameters (in my VB code) to use the dropdown list I have in my page? Thanks, Uzi
Hi All, I'm trying to set the SelectParameters of SqlDataSource from the code behind. This is my query: SELECT * FROM [UploadSessions] WHERE ([OwnerID] = @OwnerID)And I need to set the value of the @OwnerID at the code behind since it is stored in an object. How can I do it?? Thanks in advance Bar
How to pass a null to SelectParameters in SqlDataSource? The type of "CreateDate" is DateTime, the following code can be run correctly! SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue = "2006-11-12"; now I hope to pass null value to the Parameter "CreateDate", but the following 3 section codes don't work! SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue = string.Empty; or SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue =null; or SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue = "";
Hi everybody, I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005 Stored procedure:
Alter PROCEDURE [dbo].[sp_GetIdeaByCategory] @CatId <span class="kwd">int</span> = 0 AS BEGIN SET NOCOUNT ON;
Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc End
I am trying to set the SessionField from the codebehind, but it's not picking it up. What is the proper syntax here? Can I assign SessionField like this? I don't get an error but the Session is not read <asp:SqlDataSource ID="SqlDataSource1" runat="server" CancelSelectOnNullParameter="false" ConnectionString="<%$ ConnectionStrings:MedIT %>" SelectCommand="usp_customer_get" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:SessionParameter Name="customerID" SessionField="<%= sessionCustomerID %>" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>
Hello, In <asp:SqlDataSource> I need to do a request, something like that: SelectCommand="Select Fecha,FijoKwh,Kwh,FijoAgua,Litro From Precios Where NInstalacion=@instal">@instal is a variable that I need to get from Session State, it is an integer. I have been looking throughtout the theory but I haven´t understood how to link this variable to this Session.Could anyone help me with this?Thank you.
Hi, I'm having problems with the querystring parameter in a SQLDataSource, I think the SelectCommand is not getting the value of the Querystringparameter, here is the code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE '%' + @nombre + '%'"> <SelectParameters> <asp:QueryStringParameter Name="Nombre" QueryStringField="Nombre"/> </SelectParameters> </asp:SqlDataSource> I've tried many things like... "SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE @nombre" "SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE '%' & @nombre & '%'" "SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE '%' + @nombre + '%'" And nothing work, where's the problem?, I send the value of the SelectCommand of the DataSource and the @name is not replaced by any value.
How do you specify a NULL DefaultValue for a SQLDataSource QueryStringParameter? I've tried DefaultValue="" and DefaultValue="NULL" among other things, but nothing works, and I haven't found MSDN documentation on this. I have a stored procedure which takes two input parameters as follows:
EXEC @return_value = [dbo].[heading_detail] @site_id = 1, @heading_id = NULL I'm using a SQLDataSource control to execute the stored procedure. I should get a 15 row result set, but instead I get zero rows when I have a null @heading_id value. Here is my ASP code: <asp:SqlDataSource ID="HeadingDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>" SelectCommand="heading_detail" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:SessionParameter Name="site_number" Type="Int32" DefaultValue="1" SessionField="site_number" /> <asp:QueryStringParameter Name="heading_id" Type="Int32" DefaultValue="" QueryStringField="heading_id" /> </SelectParameters> </asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Ales %>" SelectCommand="Select * From HSeason" > <SelectParameters> <asp:SessionParameter Name="hotelID" SessionField="hotelID"/> <asp:SessionParameter Name="dtdate1" SessionField="dtdate1" Type="DateTime"/> </SelectParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" DataSourceID="SqlDataSource1" runat="server"> </asp:GridView>When I delete <asp:SessionParameter Name="dtdate1" SessionField="dtdate1" Type="DateTime"/>everythig works I am defining protected void Page_Load(object sender, EventArgs e) { Session["hotelID"] = "1"; Session["dtdate_1"] = DateTime.Now; }What could be the problem the datetime session just doesnt work??? Even when I dont use it at all in SelectCommand
Hi there!Basicly I only want to retrieve the value of the field "FirstTime" from a record as shown below:1 2 Dim ProdUserID As String = "samurai" 3 4 Dim LoginSource As New SqlDataSource() 5 6 LoginSource.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString1").ToString() 7 8 LoginSource.SelectCommandType = SqlDataSourceCommandType.Text 9 LoginSource.SelectCommand = "SELECT FROM aspnet_Users (FirstTime) VALUES (@FirstTime) WHERE UserName=@ProdUserID" 10 11 Dim SeExiste As Boolean = LoginSource.SelectParameters.GetValues("@FirstTime") 12 This is the error reported:Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30455: Argument not specified for parameter 'control' of 'Public Function GetValues(context As System.Web.HttpContext, control As System.Web.UI.Control) As System.Collections.Specialized.IOrderedDictionary'. Please can you answer in the same logical schema? I'm a newbie so if you may, describe the process step by step. It would be easier for me to understand.Thanks in advance.
What am I forgetting to do? I am setting the SELECT parameter 'techcode' in the code behind trying to paqss it as the ZWOP in my SQL Select statement but it is not getting to the SQl SelectParameters statement. Do I need to place a TextBox control on the ASPX page to hold it? ERROR: Exception Details: System.InvalidOperationException: Could not find control 'techcode' in ControlParameter 'ZWOP'. CODE BEHINDint techcode;protected void Page_Load(object sender, EventArgs e){ techcode = 11; } ASPX CODE. . . SelectCommand="SELECT [ZL_ID], [complete], [error], [Zip], [ZipCity], [ZipState], [ZWOP] FROM ZipData WHERE ([complete] = 0 OR [complete] IS NULL ) AND [ZWOP] = @techcode ORDER BY Zip" <SelectParameters> <asp:ControlParameter ControlID="techcode" Name="ZWOP" PropertyName="SelectedValue" Type="Int16" /> </SelectParameters>
What im looking to do is put a checkbox on my page and if the box is checked have it add a where clause to the sql statement. The most i have seen with selectparameters is for it to basically take the value of the given control (or whatever) and dump it in to a query. In this case what i would like to do is if the box is not checked it has my basic query run Select * from Table and if the box is checked i would like it to run Select * from table where status='completed' basically a check box that shows only completed items. wasnt sure if i could do this with just the sqldatasource control and some selectparameters or if i had to write some code. Thanks Justin
Hi , I am trying to write a report generator by simply using a form and then a gridview that query's the database based on what the user selects on the form. Anyways, I update the SQL parameters similar to below code. I just want to know how i can tell the parameter to get ALL values from the parameter instead of a specific value. For example if the user wanted to see ALL the records for CustomerName what would i set the parameter default value to? I tried the asterisk but that did not work. Please guide me....Thanks! MattSqlDataSource1.SelectParameters["CustomerName"].DefaultValue = "some value";<asp:SqlDataSource ... > <SelectParameters> <asp:Parameter Name="CustomerName" /> </SelectParameters></asp:SqlDataSource>
Hi, I am using Visual Web Developer 2005 Express Edition. I am trying to SELECT three information fields from a table when the Page_Load take place (so I select the info on the fly). The refering page, sends the spesific record id as "Articleid", that looks typically like this: "http://localhost:1424/BelaBela/accom_Contents.aspx?Articleid=2". I need to extract the "Article=2" so that I can access record 2 (in this example). How do I define the SelectParameters or QueryStingField on the fly so that I can define the WHERE part of my query (see code below). If I remove the WHERE portion, then it works, but it seem to return the very last record in the database, and if I include it, then I get an error "Must declare the scalar variable @resortid". How do I programatically set it up so that @resortid contains the value that is associated with "Articleid"? My code is below. Thank you for your advise! RegardsJan/******************************************************************************* * RETRIEVE INFORMATION FROM DATABASE *******************************************************************************/ // specify the data source string connContStr = ConfigurationManager.ConnectionStrings["tourism_connect1"].ConnectionString; SqlConnection myConn = new SqlConnection(connContStr);
// define the command query String query = "SELECT resortid, TourismGrading, resortHits FROM Resorts WHERE ([resortid] = @resortid)"; SqlCommand myCommand = new SqlCommand(query, myConn);
// open the connection and instantiate a datareader myConn.Open(); SqlDataReader myReader = myCommand.ExecuteReader();
// loop thru the reader while (myReader.Read()) { Label5.Text = myReader.GetInt32(0).ToString(); Label6.Text = myReader.GetInt32(1).ToString(); Label7.Text = myReader.GetInt32(2).ToString(); }
// close the reader and the connection myReader.Close(); myConn.Close();
Hi,I have a GridView which is bound to a SqlDataSource that connects to Oracle. Here's the code: <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:OracleConnectionString %>" ProviderName="<%$ ConnectionStrings:OracleConnectionString.ProviderName %>" SelectCommand="SELECT QUIZ.TITLE FROM QUIZ WHERE (QUIZ.USERNAME = @UserName)"> <SelectParameters> <asp:SessionParameter Name="UserName" SessionField="currentUser" Type="String" /> </SelectParameters> </asp:SqlDataSource> As you can see I'm trying to pass the value of the "currentUser" session variable to the query. I get an error message "ORA-xxx Illegal name/variable". Where am I going wrong? I tested the connection by placing a specific value instead of the "@UserName" and it worked.
Column1 in table 1 has a range from 1 to 5 (int) A CheckboxList displays these 5 items. When checked (for example 1 and 4) I want a gridview with sqldatasource=sqlProducts to display the records where column1 values 1 or 4. When I use the code below I only get the records where column1 values 1.... <asp:SQLDataSource id="sqlProducts" Runat="Server" SelectCommand="Select * From Table1 where Column1 IN (@CBLchecked_items)" ConnectionString="<%$ ConnectionStrings:pubs %>"> <SelectParameters> <asp:ControlParameter Name="CBLchecked_items" ControlID="CBL" propertyname="SelectedValue" type="String" /> </SelectParameters></asp:SQLDataSource>
I Have This StoredProcedure ALTER PROCEDURE dbo.Default_Edit1 ( @ID int, @Family Nvarchar (100) OutPut ) AS SELECT @Family=Family FROM Table1 where (ID=@ID) RETURN I went to use SqlDataSource1.SelectParameters to Return Data Ho to get and set @ID , @Family with SelectParameters ?
I have a simple gridview that loads on page load. It uses an on page sqldatasource declaration in which there's a parameter in which value is already available in cookies. I added an asp:HiddenField and set that value on PageLoad() to the value of the cookies. I then set a FormParameter in the sqldatasource mapped to that hidden field. However that appears to have no effect at all. I'm guessing the sqldatasource will only use the form field after postback.
Hi everyone, I am building a search page and am a bit stuck. The page has an sqldatasource with no select command and i am passing the select command and parameters depending on the optionsm selected. If a tick box is ticked and a drop down selected i need to add a parameter to the datasource and generate the select command. then if 1 and or 2 trext boxes are filled in, add the 1 or 2 select parameters then generate the select command. The select command is correct as far as i can tell , but the parameters are not being passed to the datasource so it doesn't select anything. my code behind is in the page load handler and at the moment only adds the parameters in one place so i could test. Does anyone have any idea what i have done wrong?
Cheers Chris CODE BEHINDProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim SQLstr As String
If cb_Today.Checked = True And dd_Area.SelectedValue.ToString <> "" ThenLocManSearch.SelectParameters.Add("Area", TypeCode.String, dd_Area.SelectedValue.ToString()) SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%" & dd_Area.SelectedValue.ToString() & "%') AND ([available] LIKE '%" & Date.Today & "%') AND ([viewable] = 'True')" ElseIf cb_Today.Checked = False And dd_Area.SelectedValue.ToString <> "" Then SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%" & dd_Area.SelectedValue.ToString() & "%') " ElseIf txt_FName.Text <> "" And txt_LName.Text <> "" And cb_Today.Checked = False And dd_Area.SelectedValue.ToString = "" Then SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([FirstName] LIKE '%" & txt_FName.Text.ToString() & "%') and [LastName] LIKE '%" & txt_LName.Text.ToString() & "%')" ElseIf txt_LName.Text <> "" And cb_Today.Checked = False And dd_Area.SelectedValue.ToString = "" Then SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([LastName] LIKE '%" & txt_LName.Text.ToString() & "%')" ElseIf txt_FName.Text <> "" And cb_Today.Checked = False And dd_Area.SelectedValue.ToString = "" Then SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([FirstName] LIKE '%" & txt_FName.Text.ToString() & "%')" End If Response.Write(SQLstr) LocManSearch.SelectCommand = SQLstr End Sub
Hello to all coding members! I'm building my first ever complete website in the ASP.net VB language and I have to say, I'm pretty happy that I was able to learn from this website and to gain some ground. Data retrieval and editing from tables and databases was very intuitive with the Visual Web Developer IDE. I even learnt simple ways to implement (and to script a bit of) login pages to make my visitors feel "at home" in my website.However, when I wanted to implement simple Business Logic into one of my ASP.net page, i happen to stumble upon a block.Business Logic: To have the server go through the whole table of storybook entries submitted by many users, but only pull out the storybook entry(s) that belong to the logged-on user.Hence, let me drill down to my specific questions:
I will be using VB. Coming from a JavaScript genre, my concern is: will putting the Business Logic code into a code-behind page (.vb) or dumping it onto the page itself have any errors or effects?
What is the C# code I use to do this? I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.
i am using visual web developer 2005 with SQL Express 2005 with VB as the code behindi have one database and three tables in itfor manipulating each table i am using separate SqlDataSource() is it sufficient to use one SqlDataSource() for manipulating all the three tables ? i am manipulating all the tables in the same page only please help me
I have an asp:sqldatasource which is bound to a gridviewIn addition to this I would like it to a) see if there is a specif row/ item in it (ie item_id = 10 for any of the rows it has received) as I conditionally want to show another item outside of the gridview subject to if it is in the gridview or notb) show the sum of all the values within a certain column of returned rowsMany thanks
How do I get the result of this select into a variableDim sqldsFindUserId As SqlDataSource = New SqlDataSource sqldsFindUserId.ConnectionString = ConfigurationManager.ConnectionStrings("bluConnectionString2").ToString sqldsFindUserId.SelectCommandType = SqlDataSourceCommandType.Text Dim myUserIdCmd As String = "select pkUser from tblUsers where strDisplayName='" + myDisplayname + "" sqldsFindUserId.SelectCommand = myUserIdCmd ' The result of this select statement is to be stored in a variable, how do I do it?
hi all, in all my 2.0 learnings and books i keep coming across the page element <asp:sqldatasource>. I have always (in 1.1) used server side connections and adapters to bind my Sql datasets to any control needed. Now that im learning 2.0 im finding it difficult to understand using control on the page to bind my data. Can someone explain the benefits of using this data source? Ideally i would like to keep my data access layer separate from my presentation layer but i'd really like to understand why this method seems so popular. thanks in advance, mcm
Hi, I am new to ASP.NET 2.0 and I am trying to use VWB to bind my web site to a SQL Express edition. I used SQLDataSource to specify the .mdf file so I can connect to my tables but when I click on the advanced button to generate the Insert, Update, Delete SQL I find it grayed out and it cannot be clicked. I looked into several tutorials online and I couldn't find the problem, can anyone explain what I am missing or doing wrong? Any suggestion is very appreciated. Thanks
SELECT * FROM [CONTACTS] WHERE @ddl_value LIKE '%@txt_value%'
Why doesn't this not working I am using SQLdatasource control to bind a gridview If my query is wrong then what might be the correct one to work with like operator in the sqldatasource Can any ony help me!
Hello All, I have quick question .. In my aspx page i have gridview and Sql DataSource object as you can see <asp:sqldatasource id="SqlDataSource1" runat="server" ></asp:sqldatasource>
<asp:gridview id="GridView1" runat="server" allowpaging="True" allowsorting="True" autogeneratecolumns="False" datasourceid="SqlDataSource1"> <columns> <asp:boundfield datafield="breakdownid" headertext="breakdownid" insertvisible="False" readonly="True" sortexpression="breakdownid" /> <asp:boundfield datafield="ticketno" headertext="ticketno" sortexpression="ticketno" /> <asp:boundfield datafield="systemtype" headertext="systemtype" sortexpression="systemtype" /> <asp:boundfield datafield="break_date" headertext="break_date" readonly="True" sortexpression="break_date" /> <asp:boundfield datafield="subject" headertext="subject" sortexpression="subject" /> <asp:boundfield datafield="status" headertext="status" sortexpression="status" /> <asp:boundfield datafield="prioritylevel" headertext="prioritylevel" sortexpression="prioritylevel" /> <asp:boundfield datafield="mobiletype" headertext="mobiletype" sortexpression="mobiletype" /> </columns> </asp:gridview> In codebehind file i call a function to get data from database. what i want is to bind the result to the sqlDataSourse not the gridview. I need to have the SqlDataSourse thier.. Any help please
Sub Page_load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load If Not Page.IsPostBack Then Dim objReader As New Dynamic.Reportsdemo SqlDataSource1 = .objReader.Get_Tickets(1, 0, "all")
