locked
Retrieving Values from Stored Procedure to populate parameter values for select function RRS feed

  • Question

  • User-1901014284 posted

    Hi,

    I have 2 select queries, the first retrieving dates from table 1 using a stored procedure. The second retrieving values from a second table between the dates that have been retrieved from the stored procedure. I have the below code but am unable to populate the parameters within the select query using the textboxes as the results are not displaying within the textbox even though the stored procedure has populated the textboxes with the correct values.

    Please see below my code:

    Query to run stored procedure within C#:

    using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
    using (var command = sqlConn.CreateCommand())
    {

    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.CommandText = "HealthandSafetySelectContractDates";

    Int32 ConClientName;

    Int32.TryParse(Client_IDTextBox.Text, out ConClientName);
    command.Parameters.Add("@Client_Name", SqlDbType.Int).Value = ConClientName;

    sqlConn.Open();
    using (var reader = command.ExecuteReader())
    {
    if (reader.Read())
    {
    HSContractStartDateTextBox.Text = reader["Contract_Start_Date"].ToString();
    HSContractEndDateTextBox.Text = reader["Contract_End_Date"].ToString();
    }
    reader.Close();
    reader.Dispose();
    sqlConn.Close();
    }
    }
    }
    }

    Stored Procedure Select Query:

    ALTER PROCEDURE [dbo].[HealthandSafetySelectContractDates]
    @Client_Name int

    AS

    SELECT Contract_Start_Date, Contract_End_Date, crownfordServices, serviceLevels, Indemnified, Status, Client_Name, id
    FROM Customer_Contracts
    WHERE Client_Name = @Client_Name AND CrownfordServices = 3 AND Status = 1 ORDER BY Contract_Start_Date ASC

    Second Select Query within C# Code:

    string query = "SELECT isnull(SUM(Work_Time_DurationMinutes),0) FROM Expense_Entry_Form ef " +
    "LEFT JOIN Customer_Contracts cc ON (ef.Client_ID = cc.Client_Name) " +
    "WHERE Location = '1' AND Client_ID = @ClientId AND ContractServiceLevel = '3' AND Expense_Date BETWEEN @ContractStart AND @ContractEnd";
    SqlCommand sqlCmd = new SqlCommand(query, sqlConnection);
    using (System.Data.IDbCommand command = new System.Data.OleDb.OleDbCommand(query, DBconn))

    sqlConnection.Open();
    {
    sqlCmd.Parameters.AddWithValue("@ClientId", Request.QueryString["id"]);
    cmd.Parameters.Add("@ContractStart", SqlDbType.Date).Value = HSContractStartDateTextBox.Text;
    cmd.Parameters.Add("@ContractEnd", SqlDbType.Date).Value = HSContractEndDateTextBox.Text;
    object result = sqlCmd.ExecuteScalar();
    HealthandSafetyDurationMins.Text = Convert.ToString(result);
    }
    sqlConnection.Close();

    Any Assistance would be greatly appreciated.

    Many thanks

    Jonny

    Monday, June 25, 2018 12:25 PM

All replies

  • User-330142929 posted

    Hi Jonnygareth3,

    According to your description, the first code snippets populate two textboxes by calling a stored procedure. The second code snippets use the values of last two textboxes to populate a select query.

    Because the code is incomplete and I don't have enough code to infer and restore your scene, At last I failed to reproduce your problem. I suggest that you could use F9 to add breakpoints, debug the second code snippets to verify that it is correct to get the value from textbox, and then use the generated query string to see if the SqlCommand was executed correctly. You could also execute the select string directly on SQL Server ManageStudio to determine if there is a problem with the SQL statement.

    Please feel free to let me know if you have any question.

    Best Regards,

    Abraham

    Tuesday, June 26, 2018 7:44 AM
  • User-1901014284 posted

    Thank you Abraham,

    I have debugged the solution numerous times but still cannot understand why the textbox are not populating. My first query using the stored procedure pulls the value into the textboxes using the execute reader and the parameters below:

    HSContractStartDateTextBox.Text = reader["Contract_Start_Date"].ToString();
    HSContractEndDateTextBox.Text = reader["Contract_End_Date"].ToString();

    But when checking the second select query within the C# code the parameter values (@ContractStart and @ ContractEnd do not have any value even though the parameters should be populated using the textboxes as shown below:

    cmd.Parameters.Add("@ContractStart", SqlDbType.Date).Value = HSContractStartDateTextBox.Text;
    cmd.Parameters.Add("@ContractEnd", SqlDbType.Date).Value = HSContractEndDateTextBox.Text;

    Could the fact that I am running the stored procedure on the page load and the second select query under a Private Void which is loaded under if (!this.IsPostBack)

    Would this be a why the textboxes are not populated in the second select query?

    Wednesday, June 27, 2018 9:26 AM
  • User-330142929 posted

    Hi Jonnygareth30,

    According your description,

    I have the below code but am unable to populate the parameters within the select query using the textboxes as the results are not displaying within the textbox even though the stored procedure has populated the textboxes with the correct values.

    You mean that the SqlDataReader has fetched the value while it doesn’t display in the textbox?

    If this is indeed the case, I suggest you check if the updatepanel interferes with the text value filling method. As far as I know, if the textbox is outside of updatepanel, and the event is triggered inside of the updatepanel, in some cases, it will cause your problem.

    Case 1.

          <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
            <div id="div1">
            </div>
            <asp:UpdatePanel ID="UpdatePanel1" runat="server">
                <ContentTemplate>
                    <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
                </ContentTemplate>
            </asp:UpdatePanel>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

    Code Behind.

            protected void Button1_Click(object sender, EventArgs e)
            {
                this.TextBox1.Text = "acbde";
            }

    Case 2:

           <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
            <div id="div1">
            </div>
            <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
                <ContentTemplate>
                    <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
                </ContentTemplate>
            </asp:UpdatePanel>
            <asp:UpdatePanel ID="UpdatePanel2" runat="server" UpdateMode="Conditional">
                <ContentTemplate>
                    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                </ContentTemplate>
            </asp:UpdatePanel>

    In addition, there is a possibility that these values are modified or overwritten elsewhere. Please check the rest of the code.

    Feel free to let me know if you have any question.

    Best Regards,

    Abraham

    Thursday, June 28, 2018 2:15 AM
  • User-1901014284 posted

    Hi Abraham,

    Thank you very much for your help, the issue has now been resolved by completely removing the SP and using a join to access the tables data I required.

    Again than you very much for all your help. It has been greatly appreciated.

    Thanks

    Jonny

    Thursday, June 28, 2018 11:57 AM