Solved

Stored Procedure HELP

Posted on 2014-04-28
15
183 Views
Last Modified: 2014-11-18
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
0
Comment
Question by:smokey1920
15 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40027731
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
 

Author Comment

by:smokey1920
ID: 40027888
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
 

Author Comment

by:smokey1920
ID: 40027896
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 40028033
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
 

Author Comment

by:smokey1920
ID: 40028252
So do i change my stored procedure code?   And omit the submit button?
0
 

Author Comment

by:smokey1920
ID: 40028258
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40028272
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
 

Author Comment

by:smokey1920
ID: 40028401
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
 

Author Comment

by:smokey1920
ID: 40028414
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
 

Author Comment

by:smokey1920
ID: 40028415
am i suppose to have two data sources on this page: one for the dropdown list and one for a gridview?
0
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 500 total points
ID: 40029643
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
 

Expert Comment

by:adeel289
ID: 40041496
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 40435236
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40435237
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Server 2012 management. 5 25
C# Desktop Application 3 35
SSRS Enable Remote Errors 4 26
Need help with a Stored Proc on Sql Server 2012 4 11
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

809 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