locked
passing Date selection to Sp RRS feed

  • Question

  • User279786496 posted

    I have 2 drop down lists, where i bind the data using web service from sql server database .
    Now in the front end i can see the data in the drop down list.
    Here is the procedure that i bind the data in 2 drop down list - start date and end date

    Alter procedure dbo.Dates
    select top 100 Desc as start date , Desc as end date from dbo. DateSelection

    data looks like this in the frontend Example - 
    Start date end date
    Wk 1 Jan 2010 Wk1 jan 2010
    Wk 2 Jan 2010 Wk2 Jan 2010

    Now the question is what ever dates i have selected in the drop down how can i pass this selection to below procedure where it has logic to filter.

    Idea is Employee Id's which are selected in the front end will look like this
    64747,84747,95858 etc....
    on clicking on the Move button it will ask to select date parameter selection start date and end date..

    I am trying to figure out how to pass date selection to the below procedure as well as Employee Id selection to below procedure..

    ALTER PROCEDURE dbo.Employee
    (
    @IdList NVARCHAR(3500)
    )

    DECLARE
    @SqlStatement NVARCHAR(4000) =
    N'
    INSERT INTO #Employee
    (
    EmployeeId 
    )
    SELECT 
    EmployeeId 
    FROM 
    dbo.Employee
    WHERE 
    EmployeeId IN (' + @IdList + N');
    ';

    IF ( OBJECT_ID('tempdb..#Employee') IS NOT NULL )
    DROP TABLE #Employee;

    CREATE TABLE #Employee
    (
    EmployeeId INTEGER NOT NULL
    )

    EXEC ( @SqlStatement )

    DROP TABLE #Employee;

    RETURN;
    END

    Monday, August 7, 2017 3:04 PM

All replies

  • User1168443798 posted

    Hi BerrySmith,

    >> I have 2 drop down lists, where i bind the data using web service from sql server database

    How did you bind drop down list to web service? How did you interop with database by web service?

    >> I am trying to figure out how to pass date selection to the below procedure as well as Employee Id selection to below procedure..

    Do you mean you have achieved passing Id from FrontEnd to procedure? If so, could you share us how did you achieve this?

    It would be helpful if you could share us a simple project to reproduce your issue.

    Best Regards,

    Edward

    Tuesday, August 8, 2017 3:28 AM
  • User-271186128 posted

    Hi BerrySmith,

    how can i pass this selection to below procedure where it has logic to filter.

    Suppose the stored procedure as below:

    CREATE PROCEDURE dbo.usp_GetReportsWithinRange
        @Start_Date smalldatetime, 
        @End_Date smalldatetime
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT  *  --You can define columns that you want to pull
        FROM    dbo.Sales_Report
        WHERE   Sales_Date >= @Start_Date AND
            Sales_Date <= @End_Date
    END
    GO

    First, you could get the DropDownList Property (SelectedItem.Text, SelectedItem.Value, SelectedValue).

    Then, refer to the following code to call the stored procedure:

                //get connection string
    string constr = ConfigurationManager.ConnectionStrings["MyTestDBConnStr2"].ToString(); DataTable dt = new DataTable(); using (SqlConnection con = new SqlConnection(constr)) {
    // the procedure name using (SqlCommand cmd = new SqlCommand("usp_GetReportsWithinRange", con)) { cmd.CommandType = CommandType.StoredProcedure; con.Open();
    //add parameters cmd.Parameters.AddWithValue("@Start_Date", startdate);
    cmd.Parameters.AddWithValue("@End_Date", enddate); SqlDataAdapter ada = new SqlDataAdapter(); ada.SelectCommand = cmd; ada.Fill(dt); } } GridView1.DataSource = dt; GridView1.DataBind();

    Best regards,
    Dillion

    Tuesday, August 15, 2017 9:28 AM