Weird DataReader Problem

[indyrob]

This is a puzzling issue...

The following code works:

Dim sql0A As String = "select date, (endmiles - startmiles) as totalmiles from workorders where date = @p1 order by startmiles"
        Dim SQL0B As String = "SELECT DATE, (ENDMILES - STARTMILES) AS TOTALMILES FROM WORKORDERS WHERE DATE = @P1 ORDER BY STARTMILES DESC"

        Dim cmdread0 As New SqlCeCommand(sql0A, con)
        Dim CMDREADREV0 As New SqlCeCommand(SQL0B, con)

        cmdread0.Parameters.Add("@p1", CDate(Me.DateTimePicker1.Text).ToShortDateString)
        CMDREADREV0.Parameters.Add("@P1", CDate(Me.DateTimePicker1.Text).ToShortDateString)

        con.Open()

        'DO THE FIRST RECORD------------------------

        Dim rdr0 As SqlCeDataReader = cmdread0.ExecuteReader


        For h = 1 To 1

            rdr0.Read()

            Dim dt0 = rdr0.Item(0)
            Dim TM0 = rdr0.Item(1)

            If CheckBox1.CheckState = CheckState.Checked Then
                Label14.Text = dt0
                Label15.Text = TM0
            End If

        Next


        ' NOW DO THE LAST RECORD ---------------------------------

        Dim RDRREV0 As SqlCeDataReader = CMDREADREV0.ExecuteReader


        For i = 1 To 1

            RDRREV0.Read()

            Dim TMR0 = RDRREV0.Item(1)
            If CheckBox1.CheckState = CheckState.Checked Then
                Label16.Text = TMR0
            End If



        Next

        con.Close()
        rdr0.Dispose()
        RDRREV0.Dispose()

Immediately following that I have this bit of code which returns an error stating that "Data conversion failed. [ OLE DB status value (if known) = 2 ]:"

The line "rdr1.read()" is highlighted.

'---------WEEK ENDING -1 -------------
        Dim SQL1A As String = "select date, (endmiles - startmiles) as totalmiles from workorders where date = (@p1 - 1) order by startmiles"
        Dim SQL1B As String = "SELECT DATE, (ENDMILES - STARTMILES) AS TOTALMILES FROM WORKORDERS WHERE DATE = (@P1 - 1) ORDER BY STARTMILES DESC"

        Dim cmdread1 As New SqlCeCommand(SQL1A, con)
        Dim CMDREADREV1 As New SqlCeCommand(SQL1B, con)

        cmdread1.Parameters.Add("@p1", CDate(Me.DateTimePicker1.Text).ToShortDateString)
        CMDREADREV1.Parameters.Add("@P1", CDate(Me.DateTimePicker1.Text).ToShortDateString)


        'DO THE FIRST RECORD------------------------

        con.Open()

        Dim rdr1 As SqlCeDataReader = cmdread1.ExecuteReader

        For j = 1 To 1

            rdr1.Read()

            Dim dt1 = rdr1.Item(0)
            Dim tm1 = rdr1.Item(1)

            If CheckBox1.CheckState = CheckState.Checked Then
                Label17.Text = dt1
                Label18.Text = tm1
            End If

        Next


        ' NOW DO THE LAST RECORD ---------------------------------

        Dim RDRREV1 As SqlCeDataReader = CMDREADREV1.ExecuteReader


        For k = 1 To 1

            RDRREV1.Read()

            Dim TMR1 = RDRREV1.Item(1)
            If CheckBox1.CheckState = CheckState.Checked Then
                Label20.Text = TMR1
            End If

        Next

Is it something to do with my date parameter? The SQL works using the query wizards. I've tried parsing the date too.

Thanks for any input on this!

IndyRob

 

[indyrob]

It's definitely in my parameter. @p1 - 1

I'll toy around with it and try to find a solution and post back.

 

[indyrob]

Resolved...

cmdread1.Parameters.Add("@p1", DateTime.Parse(DateTimePicker1.Text).AddDays(-1))
        CMDREADREV1.Parameters.Add("@P1", DateTime.Parse(DateTimePicker1.Text).AddDays(-1))

using "AddDays(-1)"

Sorry to clog up the forums!

 

[indyrob]

The corrected code is as follows:

Instead of using the SQL (@p1-1) I used (@p1) and used AddDays(-x). This works because I'm only filling in labels.

I also went with the ExecuteResultSet option Scrollable so I could test whether or not the reader HasRows.

Dim sql0A As String = "select date, (endmiles - startmiles) as totalmiles from workorders where date = @p1 order by startmiles"
        Dim SQL0B As String = "SELECT DATE, (ENDMILES - STARTMILES) AS TOTALMILES FROM WORKORDERS WHERE DATE = @P1 ORDER BY STARTMILES DESC"

        Dim cmdread0 As New SqlCeCommand(sql0A, con)
        Dim CMDREADREV0 As New SqlCeCommand(SQL0B, con)

        cmdread0.Parameters.Add("@p1", CDate(Me.DateTimePicker1.Text).ToShortDateString)
        CMDREADREV0.Parameters.Add("@P1", CDate(Me.DateTimePicker1.Text).ToShortDateString)

        con.Open()

        'DO THE FIRST RECORD------------------------

        Dim rdr0 As SqlCeDataReader = cmdread0.ExecuteResultSet(ResultSetOptions.Scrollable)


        If rdr0.HasRows = True Then


            For i = 1 To 1

                rdr0.Read()

                Dim dt0 = rdr0.Item(0)
                Dim TM0 = rdr0.Item(1)

                If CheckBox1.CheckState = CheckState.Checked Then
                    Label14.Text = (CDate(Me.DateTimePicker1.Text).ToShortDateString)
                    Label15.Text = TM0
                End If

            Next

        End If


        ' NOW DO THE LAST RECORD ---------------------------------

        Dim RDRREV0 As SqlCeDataReader = CMDREADREV0.ExecuteResultSet(ResultSetOptions.Scrollable)


        If RDRREV0.HasRows = True Then

            For I = 1 To 1

                RDRREV0.Read()

                Dim TMR0 = RDRREV0.Item(1)
                If CheckBox1.CheckState = CheckState.Checked Then
                    Label16.Text = TMR0
                End If



            Next

        End If


        con.Close()
        rdr0.Dispose()
        RDRREV0.Dispose()

And the second batch of code reflecting the changes...

'---------WEEK ENDING -1 -------------
        Dim SQL1A As String = "select date, (endmiles - startmiles) as totalmiles from workorders where date = (@p1) order by startmiles"
        Dim SQL1B As String = "SELECT DATE, (ENDMILES - STARTMILES) AS TOTALMILES FROM WORKORDERS WHERE DATE = (@P1) ORDER BY STARTMILES DESC"

        Dim cmdread1 As New SqlCeCommand(SQL1A, con)
        Dim CMDREADREV1 As New SqlCeCommand(SQL1B, con)

        cmdread1.Parameters.Add("@p1", DateTime.Parse(DateTimePicker1.Text).AddDays(-1))
        CMDREADREV1.Parameters.Add("@P1", DateTime.Parse(DateTimePicker1.Text).AddDays(-1))


        'DO THE FIRST RECORD------------------------

        con.Open()

        Dim rdr1 As SqlCeDataReader = cmdread1.ExecuteResultSet(ResultSetOptions.Scrollable)

        If rdr1.HasRows = True Then

            For I = 1 To 1

                rdr1.Read()

                Dim dt1 = rdr1.Item(0)
                Dim tm1 = rdr1.Item(1)

                If CheckBox1.CheckState = CheckState.Checked Then
                    Label17.Text = DateTime.Parse(DateTimePicker1.Text).AddDays(-1).ToShortDateString
                    Label18.Text = tm1
                End If


            Next
        End If


        ' NOW DO THE LAST RECORD ---------------------------------

        Dim RDRREV1 As SqlCeDataReader = CMDREADREV1.ExecuteResultSet(ResultSetOptions.Scrollable)

        If RDRREV1.HasRows = True Then
            For I = 1 To 1

                RDRREV1.Read()

                Dim TMR1 = RDRREV1.Item(1)
                If CheckBox1.CheckState = CheckState.Checked Then
                    Label20.Text = TMR1
                End If

            Next

        End If