Stored Procedure HELP

I need this stored Procedure to get the value from the dropdownlist (WSID) pass that value to another table (foreigned keyed).  I then need to see the FirstName and LastName of the correspnding participants that linc to the WSID.

Tables: i have two - Workshop and Participants
WSID - Prim key in Workshop
WorkshopID - foreign key to WSID in Participants

On my webpage - the user picks from a drop down list the WSID.  then hits submit.  On the button click I need the stored procedure to take the correct WSID and show in the gridview the correct Participants attending that WSID.

Here is the stored procedure I have so far- As you guessed it DOESN'T work.  Any Help thanks


ALTER PROCEDURE dbo.StoredProcedure7
(@WS_number int)
AS
	SELECT Participants.FirstName, Participants.LastName, Participants.WorkshopID, Workshop.WSID
FROM Participants
INNER JOIN Workshop on Participants.WorkshopID = Workshop.WSID

WHERE Workshop.WSID =  @WS_number
Return
smokey1920Asked:
Who is Participating?
 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
You need a select parameter:  

<asp:SqlDataSource ID="SqlDataSource2" runat="server"
        ConnectionString="<%$ ConnectionStrings:RecordsConnectionString %>"
        SelectCommand="SELECT [FirstName], [LastName] FROM [Participants] where workshopID = @WSID">
<SelectParameters>
        <asp:ControlParameter ControlID="ddlWorkShops" Name="WSID"
            PropertyName="SelectedValue"
            ConvertEmptyStringToNull="true" />
    </SelectParameters>
    </asp:SqlDataSource>



and then on the dropdownlist set autopostback to true.

No need for the submit button.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
ALTER PROCEDURE dbo.StoredProcedure7
(@WS_number int)
AS
      SELECT FirstName, LastName, WorkshopID
FROM Participants
WHERE WorkshopID =  @WS_number


IT's most likely in how you're passing the parameter though.  If you run this in Management studio do you get the correct values?

Posting your code may help.
0
 
smokey1920Author Commented:
It doesnt do anything.  NO RESULTS.   Here is my code for the gridview and DataSource:
Now you see my stored procedure - what am I missing?



<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
        DataSourceID="SqlDataSource2">
        <Columns>
            <asp:BoundField DataField="FirstName" HeaderText="FirstName"
                SortExpression="FirstName" />
            <asp:BoundField DataField="LastName" HeaderText="LastName"
                SortExpression="LastName" />
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server"
        ConnectionString="<%$ ConnectionStrings:RecordsConnectionString %>"
        SelectCommand="SELECT [FirstName], [LastName] FROM [Participants]">
    </asp:SqlDataSource>
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
smokey1920Author Commented:
The problem I am not getting past- is the fact that my page has a dropdown list.  That list pulls the WSID from the workshops - from my workshop table.  That part is ok.  But when I click the submit button Nothing shows up.  There is no erros but no data.  I need to be able to select from the dropdown list the WSID.  Then when I hit submit- I need my stored procedure to go get the names of the participants that correspond to the WSID chosen.  Does that make sense?
0
 
smokey1920Author Commented:
So do i change my stored procedure code?   And omit the submit button?
0
 
smokey1920Author Commented:
When i select an id from the dropdown menu i have to click something to process that correct?  Sorry this is new to me.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
AutoPostback says any time my value is changed automatically go back to the server for processing.

By setting that to true they don't have to make an extra click.
0
 
smokey1920Author Commented:
ok i put this code in place of my current data source - however now the dropdownlist does not show the WSID in the list to choose from.  That value comes from Workshop table.  this is all confusing.  sorry
0
 
smokey1920Author Commented:
I cant figure out why this code doesnt work for me.  However i noticed that the list now shows nothing.  I had made a page that has a gridview in it.  I think you are telling me i can do this all in one page.  I have it set to goto another page once the selection is clicked.  So the code i gave you was from the gridview page.  Here is the actual "button click " page:


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        #form1
        {
            font-weight: 700;
            font-size: large;
            text-align: center;
            margin-left: 40px;
        }
   
        .style1
        {
            font-size: large;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <br />
    <br />
    <strong><span class="style1">
    <asp:Image ID="Image1" runat="server" Height="97px" ImageUrl="~/logo.gif"
        Width="140px" />
    <br />
    <br />
    </span>
        </strong>
        <br />
    CHOOSE FROM THE LIST WHICH WORKSHOP YOU WANT TO VIEW
    PARTICIPANTS<br />
    <br />
    <br />
    <br />
    <asp:DropDownList ID="DropDownList1" runat="server"
        DataSourceID="SqlDataSource1" DataTextField="WSID" DataValueField="WSID"
        AutoPostBack="True">
    </asp:DropDownList>


    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:RecordsConnectionString %>"
        SelectCommand="SELECT [WSID] FROM [Workshop]"></asp:SqlDataSource>
    <br />
    <br />
    <asp:Label ID="lblStatus" runat="server"></asp:Label>
    <br />
    <br />
    <br />
    <br />
    <br />
    <asp:Button ID="Button1" runat="server" PostBackUrl="~/WebForm1.aspx"
        Text="SUBMIT" />
    <br />
    <br />
    <br />
    <br />
    <asp:Panel ID="Panel1" runat="server" Height="84px" style="margin-left: 317px"
        Width="919px">
        <asp:Menu ID="Menu2" runat="server" Font-Size="Large"
        Font-Underline="True" Orientation="Horizontal">
            <DynamicHoverStyle Font-Bold="True" />
            <DynamicSelectedStyle Font-Bold="True" />
            <Items>
                <asp:MenuItem NavigateUrl="~/Home.aspx" Text="HOME" Value="HOME"></asp:MenuItem>
                <asp:MenuItem NavigateUrl="~/createworkshop.aspx"
                Text="CREATE WORKSHOP" Value="CREATE WORKSHOP"></asp:MenuItem>
                <asp:MenuItem NavigateUrl="~/partenroll.aspx"
                Text="ENROLL" Value="ENROLL"></asp:MenuItem>
                <asp:MenuItem NavigateUrl="~/listparticipant.aspx"
                Text="FIND PARTICIPANTS" Value="FIND PARTICIPANTS"></asp:MenuItem>
                <asp:MenuItem NavigateUrl="~/participantform.aspx"
                Text="SHOW WORKSHOP ENROLLED" Value="FIND WORKSHOP"></asp:MenuItem>
            </Items>
            <StaticSelectedStyle Font-Bold="False" />
        </asp:Menu>
    </asp:Panel>
    <br />
    <br />
    <br />
    <br />
    <br />
    <br />
    </form>
</body>
</html>
0
 
smokey1920Author Commented:
am i suppose to have two data sources on this page: one for the dropdown list and one for a gridview?
0
 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
Yep.  

The first datasource is paramaterless, the secondone has a control paramater based on it.  

I'm not sure if that's the best way to go about it, but if you want to stick with the datasource design that's how I would do it.

(I usually do all of this from the code behind).
0
 
adeel289Commented:
use this stored procedure

ALTER PROCEDURE dbo.StoredProcedure
@WS_number int
AS
SELECT FirstName, LastName, WorkshopID,
(case when 1=1 then (select top 1 WSID from Workshop where objPart.WorkshopID = WSID) else 0 end) as WSID

FROM Participants objPart

it will work. Implement it and let me know its working or not?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
http:#a40028033 combined with
http:#a40029643 Is the correct answer.

The OP wanted to have a stored procedure pull based on a parameter, using a sql data source.  28033 shows how to do that 29643 confirms that the OP was supposed to have two datasources, one for the WSID and the other for the participants.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.