Solved

Stored Procedure HELP

Posted on 2014-04-28
15
175 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 39

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

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 39

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 39

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 68

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 39

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Windows programmers of the C/C++ variety, how many of you realise that since Window 9x Microsoft has been lying to you about what constitutes Unicode (http://en.wikipedia.org/wiki/Unicode)? They will have you believe that Unicode requires you to use…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

747 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

12 Experts available now in Live!

Get 1:1 Help Now