Solved

Stored Procedure HELP

Posted on 2014-04-28
15
181 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Join Problem 2 33
Help Parsing a String with SQL Syntax 23 33
sql query questions 2 25
SQL Query with WHERE clause for an entire day 5 23
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

863 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

26 Experts available now in Live!

Get 1:1 Help Now