none
Multiple Parameters using Stored Procedure RRS feed

  • Question

  • I have a Form that asks for the Project Id, Begin Date, and End Date in order to query a stored procedure containing:

    WHERE  (PAprojid = @PAProjid AND dbo.PA30301.PADT >= @beginDate and dbo.PA30301.PADT <= @enddate)

    Once the Project Id and date interval (via DatePickers) is input, the Enter button is selected to open up a subform containing a DataGridView of the queried data using a TableAdapter/DataSet.  The DataGridView contains the correct Project Id information; however, I am having problems with the BeginDate and Enddate parameters in order to filter the data further.

    Here is my code:

    Public Class dbCharges
        Public Shared Function GetTimeCharges(ByVal projectid As String) As clsCharges
            Dim charges As New clsCharges
            Dim cnSQL As SqlConnection = dbLIMS.GetESSConnection
            'Select Command using Stored Proceduures.............
            Dim selectCommand As New SqlCommand()
            selectCommand.Connection = cnSQL
            selectCommand.CommandText = "usp_Add_PM_Time"
            selectCommand.CommandType = CommandType.StoredProcedure
            selectCommand.Parameters.AddWithValue("@PAProjid", projectid)
            Try
                cnSQL.Open()
                Dim reader As SqlDataReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow)
                reader.Close()
            Catch ex As SqlException
                Throw ex
            Finally
                cnSQL.Close()
            End Try
            Return charges
    End Function

    End Class

    Once again, this will fill the DataGridView with ALL the information for the queried Project Id, but I need to also filter by BeginDate to EndDate.  Would I be on the right track to modify as such?

    Dim selectCommand As New SqlCommand()
            selectCommand.Connection = cnSQL
            selectCommand.CommandText = "usp_Add_PM_Time"
            selectCommand.CommandType = CommandType.StoredProcedure
            selectCommand.Parameters.AddWithValue("@PAProjid", projectid)
            selectCommand.Parameters.AddWithValue("@BeginDate", frmC_TM_Projects.BeginDateTimePicker.Text)
            selectCommand.Parameters.AddWithValue("@EndDate", frmC_TM_Projects.EndDateTimePicker.Text)


    Wednesday, May 30, 2012 2:12 PM

Answers

  • Rather than use the .Text property, use the DateTimePicker.Value property, which is of type DateTime (I assume the parameters in your Stored Proc are datetime types, not char or varchar).

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    • Proposed as answer by Cor Ligthert Thursday, May 31, 2012 8:19 AM
    • Marked as answer by LHendren Thursday, May 31, 2012 12:59 PM
    Wednesday, May 30, 2012 3:56 PM

All replies