How to Create a Stored Procedure for Two Tables ?

Question

Hi,

        I have Created Three Tables .First two tables i bind it to 2 different dropdowns and the third table i have some data. I want to create a Stored Procedure .Depending UpOn selection of two ddls the data should be populated to listbox(i.e Data frm 3rd Table).Please tell the sp for it

Thanks.

Answer 1

Hi,

where will you get the data to load the ListBox?. Is it from the third table directly without any creteria from the ddl1 and ddl2???

Answer 2

Can u tell Without using the dropdowns how to bind it using the tables only.Plz

Answer 3

Hi,

You have the two drop downs and already you binded with them.

Create the ListBox in ASP.Net and now you want to bind the third table based on the two dropdown selection.

 <asp:ListBox ID="lstBackupfiles" runat="server" Height="236px" Width="354px"></asp:ListBox>


Create the stored procedure

CREATE PROCEDURE Proc_FillListBox

@ddl1ID INT,

@ddl2ID INT

AS

BEGIN

SET NOCOUNT ON

    SELECT * FROM thirdTable WHERE col1=@ddl1ID AND col2=@ddl2ID

END

Execute it in your sql server query window.

Write the code in the code file:

 SqlConnection sqlConnection = new SqlConnection();

            sqlConnection.ConnectionString = @"Data Source=SQLEXPRESS;Initial Catalog=Master;Integrated Security=true;";

            sqlConnection.Open();

            SqlCommand sqlCommand = new SqlCommand();

            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlCommand.Connection = sqlConnection;

            sqlCommand.CommandText = "Proc_FillListBox";

            sqlCommand.Parameters.Add("@ddl1ID", SqlDbType.Int).Value = Convert.ToInt32(ddl1.SelectedItem.Value.ToString());

            sqlCommand.Parameters.Add("@ddl2ID", SqlDbType.Int).Value = Convert.ToInt32(ddl2.SelectedItem.Value.ToString());

            SqlDataAdapter sqldataAdapter = new SqlDataAdapter(sqlCommand);

            DataSet dataSet = new DataSet();

            sqldataAdapter.Fill(dataSet);

            //Fill the ListBox

            lstBackupfiles.DataSource = dataSet.Tables[0];

            lstBackupfiles.DataBind();