Solved

Passing Stored Procedure Parameter Variables between pages

Posted on 2010-11-08
13
908 Views
Last Modified: 2012-05-10
I have an application that is passing parameters from page to page. Example:

PAGE 1 - Search Page

<dropDownList1 ID="college">  --> performs cross-page posting to the colleges page
<dropDownList2 ID="program">  --> performs cross-page posting to the programs page
<dropDownList3 ID="degree"> --> performs cross-page posting to the degree page

PAGE 2 - Colleges Page

Receives two parameters  @type and @collegeID
I am trying to grab the values from the search page using the PreviousPage.FindControl("variablename"), and populate those valies into the parameters for the stored procedure.

What I am not successfully doing is populating the parameters for the "dsPrograms" SqlDataSource on Page_Load.  My system is not recognizing "dsPrograms" as a valid SqlDataSource ID.  It will accept "SqlDataSource", but since I have 3 of them (see below), I assumed one would use the specific id to identify which parameters are being populated?

Maybe I'm just getting the syntax wrong?
<asp:SqlDataSource ID="dsCollege" runat="server" 

        ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" 

        SelectCommand="sp_collegeRecord" SelectCommandType="StoredProcedure">

        <SelectParameters>

            <asp:Parameter DefaultValue="UG" Name="type" Type="String" />

        </SelectParameters>

    </asp:SqlDataSource>

    

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

        ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" 

        SelectCommand="sp_allPrograms" 

        SelectCommandType="StoredProcedure">

        <SelectParameters>

            <asp:Parameter Name="type" Type="String" />

            <asp:Parameter Name="collegeID" Type="Int32" />

        </SelectParameters>

    </asp:SqlDataSource>



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

        ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" 

        SelectCommand="sp_allDegrees" SelectCommandType="StoredProcedure">

        <SelectParameters>

            <asp:Parameter DefaultValue="UG" Name="type" Type="String" />

        </SelectParameters>

    </asp:SqlDataSource>

Open in new window

0
Comment
Question by:vcbertini
  • 6
  • 5
  • 2
13 Comments
 
LVL 9

Expert Comment

by:puru1981
ID: 34086288
I would like to know why you need the datasources??
0
 

Author Comment

by:vcbertini
ID: 34086372
The datasources on the first page are populating the drop-down boxes with their options.
The datasources on the second page populate the record as follows:

College  (datasource 1)
College information (datasource 1)

>> First Program Offered (datasource 2 (dependent on ID from DS1))
>>>> Degree Offered  (datasource 3 (dependent on ID from DS2))
>>>> Degree Offered (datasource 3 (dependent on ID from DS2))
>>>> Degree Offered (datasource 3 (dependent on ID from DS2))

>> Second Program Offered (datasource 2 (dependent on ID from DS1))
>>>> Degree Offered  (datasource 3 (dependent on ID from DS2))
>>>> Degree Offered (datasource 3 (dependent on ID from DS2))
>>>> Degree Offered (datasource 3 (dependent on ID from DS2))

etc...
0
 
LVL 9

Expert Comment

by:puru1981
ID: 34086476
In that case i think you need to read the value from drop downs not the datasource.
0
 

Author Comment

by:vcbertini
ID: 34086574
Whatever the person chooses in the initial Drop-down (on the search page), passes an ID through to the 2nd page. I need that ID to pull the relevant data for that record on to the 2nd page.

For example if someone chooses "College of Performing Arts", I need to pull that ID from the initial page, and put it in to the parameter for the three datasources on the 2nd page that will:

1. Bring back the record for the information about the College of Performing arts. (dataset 1 based ON ID passed from previous page)
2. Bring back the programs offered by the college of performing arts (dataset 2 based on ID passed from previous page)
3. Bring back the degrees offered in those programs (dataset 3 based on dataset 2)
0
 
LVL 9

Expert Comment

by:puru1981
ID: 34086713
are you able to get the drop down containing the "College of Performing Arts" in the SelectedText field of the drop down.

if yes then you can get the value of SelectedValue to get the ID. i think after that you can perform your task.
0
 

Author Comment

by:vcbertini
ID: 34086740
Yes, I can retrieve the value, I just can't seem to get it to populate the parameter for the stored procedure, so I can retrieve my new dataset on the 2nd page.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 9

Expert Comment

by:puru1981
ID: 34086890
can you share the code for the same?
0
 

Author Comment

by:vcbertini
ID: 34086934
I don't reallly have any code written for this to share with you. Everything I have tried has failed miserably and isn't worth sharing (junk code). I posted a question on here in hopes that someone had some success with this and had a suggestion. The rest of the page isn't written yet because I can't get past the point where the stored procedure parameter gets populated (on page load).
0
 
LVL 9

Expert Comment

by:puru1981
ID: 34087034
on the page load try to set the parameters of the sql datasource. i hope it will work.
0
 

Author Comment

by:vcbertini
ID: 34087145
I was trying to do that, but I wasn't getting the syntax right. I was trying to use the ID of the sql data source instance so I was setting the correct parameter, but it wasn't letting me do that.
0
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 34089728
You don't want anything to do with those data sources (they represent all the data, and have nothing to do with which single item is selected in the drop down).
In my example below, Page1.aspx has a drop down listing colleges; the drop down displays the college name, and uses the college id as a value (each item in the drop down has text it displays, and a value the user doesn't see).
When the button is clicked the user is redirected to Page2.aspx?SelectedCollege=n where n is the value of whatever college is selected in the drop down (the part of the URL after the question mark is the query string).
The data source on Page2.aspx has a select command of SELECT * FROM [Degrees] WHERE ([CollegeId] = @CollegeId), and the <SelectParameters> child element has an <asp:QueryStringParameter> which automatically gets the value of SelectedCollege from the query string and assigns it to the @CollegeId SQL select parameter.

<!-- Page1.aspx -->

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



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">



<script runat="server">

	private void NextPageButton_Click(object sender, EventArgs e)

	{

		// Pass the selected value from the drop down

		// as a URL querystring parameter to the next page

		Response.Redirect(String.Format(

			"Page2.aspx?SelectedCollege={0}", CollegesDropDown.SelectedValue));

	}

</script>



<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

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

    <div>

		<asp:SqlDataSource ID="CollegesDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnStr %>"

			SelectCommand="SELECT * FROM [Colleges]" />

		<asp:DropDownList ID="CollegesDropDown" runat="server" DataSourceID="CollegesDataSource"

			DataValueField="CollegeId" DataTextField="Collegename" />

		<br />

		<asp:LinkButton ID="NextPageButton" runat="server" Text="Go To Page 2"

			OnClick="NextPageButton_Click" />

    </div>

    </form>

</body>

</html>

Open in new window

0
 
LVL 33

Accepted Solution

by:
Todd Gerbert earned 500 total points
ID: 34089733
Here's Page2.aspx
<!-- Page2.aspx -->



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



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">



<script runat="server">



	</script>



<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

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

    <div>

		<asp:SqlDataSource ID="DegreesDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnStr %>"

			SelectCommand="SELECT * FROM [Degrees] WHERE ([CollegeId] = @CollegeId)">

				<SelectParameters>

					<asp:QueryStringParameter Name="CollegeId" QueryStringField="SelectedCollege" Type="Int32" />

				</SelectParameters>

		</asp:SqlDataSource>

		<asp:DropDownList ID="DegreesDropDown" runat="server" DataSourceID="DegreesDataSource"

			DataValueField="DegreeId" DataTextField="DegreeName" />

    </div>

    </form>

</body>

</html>

Open in new window

0
 

Author Closing Comment

by:vcbertini
ID: 34121785
I had to work the solution into my call to a stored procedure, but ultimately got it to work.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now