Solved

Passing Stored Procedure Parameter Variables between pages

Posted on 2010-11-08
13
914 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 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