Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Passing Stored Procedure Parameter Variables between pages

Posted on 2010-11-08
13
Medium Priority
?
918 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

604 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