Wrong format for SQL Select Statement

Question

westsiderailway

9 Years Ago

Hello Everyone,

it seems that I cannot see the wood for the trees.... :-)

I am having problems with this line of code...

StateSQL = "select * from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'"

what I am trying to do is this

 StateSQL = "select * from petrol_table where fueldate >= '2013-12-16' and fueldate < '2013-12-22'"

The second line works, but the first line gives a conversion failed when converting date and/or time error.

Thanks for reading.

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If DateTimePicker1.Checked And DateTimePicker2.Checked = True Then
            Dim DTP1 As String = Me.DateTimePicker1.Value.ToString
            Dim DTP2 As String = Me.DateTimePicker2.Value.ToString
            TextBox1.Text = DTP1
            TextBox2.Text = DTP2
            If DTP1 > DTP2 Then
                MessageBox.Show("You MUST pick the same WEEK", "Important Message", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                'EXIT SUB, as the user has incorectly clicked on the wrong days
                Exit Sub
            End If
        End If


        Dim M12S As String = TextBox1.Text
        Dim M12F As String = TextBox2.Text

        'Open connection and Fill Data Table
        '====================================
        Dim StateSQL As String
        Dim ConString As String
        Dim Conn As SqlConnection
        Dim sqlCmd As SqlCommand
        Dim DAP As New SqlDataAdapter
        Dim ds As New DataSet
        Dim dt As New DataTable

        ConString = "Data Source=i7borgmatrix2\sqlexpress;Initial Catalog=Petrol_Costs;Integrated Security=True;"
        Conn = New SqlConnection(ConString)

        'select * from Petrol_Table where FuelDate >= '2014-02-10' and FuelDate < '2014-02-16';

        StateSQL = ("select * from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'")
        Conn.Open()
        sqlCmd = New SqlCommand(StateSQL, Conn)
        DAP.SelectCommand = sqlCmd
        DAP.Fill(ds, "PetrolCosts")
        ' dt.Rows.Clear()
        dt = ds.Tables("PetrolCosts")
        DAP.Dispose()
        sqlCmd.Dispose()
        Conn.Close()



        'DTRC = DataTable Row Count
        Dim DTRC As Integer = dt.Rows.Count

        TextBox3.Text = DTRC

        '===========================================================
        'If there is only 1 row or NONE then Load the ListviewBox
        'Otherwise Load the AVG,MIN,MAX Groupboxes
        '===========================================================
        If DTRC <= 1 Then
            'Load ListView with Data
            '=========================
            Dim LVitem As New ListViewItem
            ListView1.View = View.Details
            ListView1.GridLines = True
            ListView1.FullRowSelect = True
            ListView1.Items.Clear()

            For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
                ListView1.Columns.Add(ds.Tables(0).Columns(i).ColumnName, 75)
            Next

            LVitem.Text = dt.Rows(0).Item(0).ToString
            LVitem.SubItems.Add(dt.Rows(0).Item(1).ToString)
            LVitem.SubItems.Add(dt.Rows(0).Item(2).ToString)
            LVitem.SubItems.Add(dt.Rows(0).Item(3).ToString)
            LVitem.SubItems.Add(dt.Rows(0).Item(4).ToString)
            LVitem.SubItems.Add(dt.Rows(0).Item(5).ToString)
            LVitem.SubItems.Add(dt.Rows(0).Item(6).ToString)
            LVitem.SubItems.Add(dt.Rows(0).Item(7).ToString)
            LVitem.SubItems.Add(dt.Rows(0).Item(8).ToString)

            ListView1.Items.Add(LVitem)

        Else

            Dim constr1 As String = "Data Source=i7borgmatrix2\sqlexpress;Initial Catalog=Petrol_Costs;Integrated Security=True;"

            ds.Tables.Add(New DataTable("Table1"))
            ds.Tables.Add(New DataTable("Table2"))
            ds.Tables.Add(New DataTable("Table3"))
            ds.Tables.Add(New DataTable("Table4"))
            ds.Tables.Add(New DataTable("Table5"))
            ds.Tables.Add(New DataTable("Table6"))
            ds.Tables.Add(New DataTable("Table7"))
            ds.Tables.Add(New DataTable("Table8"))
            ds.Tables.Add(New DataTable("Table9"))
            '=========================
            'Get Average Price@Liter
            '=========================
            Using con As New SqlConnection
                con.ConnectionString = constr1
                con.Open()
                '========================
                'Get Average Price@Liter
                '=========================
                Using AD As SqlDataAdapter = New SqlDataAdapter("select AVG(Price_a_liter) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", con)
                    AD.Fill(ds.Tables("Table1"))
                End Using
                '==========================
                'Get Average Total_Liters
                '==========================
                Using AD As SqlDataAdapter = New SqlDataAdapter("select AVG(total_liters) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", con)
                    AD.Fill(ds.Tables("Table2"))
                End Using
                '==========================
                'Get Average Total_Dollars
                '==========================
                Using AD As SqlDataAdapter = New SqlDataAdapter("select AVG(total_dollars) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", con)
                    AD.Fill(ds.Tables("Table3"))
                End Using
                '=========================
                'Get MIN Price@Liter
                '=========================
                Using AD As SqlDataAdapter = New SqlDataAdapter("select MIN(Price_a_liter) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", con)
                    AD.Fill(ds.Tables("Table4"))
                End Using
                '==========================
                'Get MIN Total_Liters
                '==========================
                Using AD As SqlDataAdapter = New SqlDataAdapter("select MIN(total_liters) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", con)
                    AD.Fill(ds.Tables("Table5"))
                End Using
                '==========================
                'Get MIN Total_Dollars
                '==========================
                Using AD As SqlDataAdapter = New SqlDataAdapter("select MIN(total_dollars) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", con)
                    AD.Fill(ds.Tables("Table6"))
                End Using
                '=========================
                'Get MAX Price@Liter
                '=========================
                Using AD As SqlDataAdapter = New SqlDataAdapter("select MAX(Price_a_liter) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", con)
                    AD.Fill(ds.Tables("Table7"))
                End Using
                '==========================
                'Get MAX Total_Liters
                '==========================
                Using AD As SqlDataAdapter = New SqlDataAdapter("select MAX(total_liters) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", con)
                    AD.Fill(ds.Tables("Table8"))
                End Using
                '==========================
                'Get MAX Total_Dollars
                '==========================
                Using AD As SqlDataAdapter = New SqlDataAdapter("select MAX(total_dollars) from petrol_table where fueldate >= " & "'" & M12S & "'" & " and fueldate < " & "'" & M12F & "'", con)
                    AD.Fill(ds.Tables("Table9"))
                End Using
                con.Close()

            End Using



            If IsDBNull(ds.Tables("Table1").Rows(0).Item(0)) And IsDBNull(ds.Tables("Table2").Rows(0).Item(0)) And IsDBNull(ds.Tables("Table3").Rows(0).Item(0)) And _
               IsDBNull(ds.Tables("Table4").Rows(0).Item(0)) And IsDBNull(ds.Tables("Table5").Rows(0).Item(0)) And IsDBNull(ds.Tables("Table6").Rows(0).Item(0)) And _
               IsDBNull(ds.Tables("Table7").Rows(0).Item(0)) And IsDBNull(ds.Tables("Table8").Rows(0).Item(0)) And IsDBNull(ds.Tables("Table9").Rows(0).Item(0)) Then

                MessageBox.Show("NO DATA Availalbe!", "Select Another Month", MessageBoxButtons.OK, MessageBoxIcon.Information)

            Else
                Label51.Text = ds.Tables("Table1").Rows(0).Item(0)
                Label48.Text = Format(ds.Tables("Table2").Rows(0).Item(0), ".00")
                Label47.Text = FormatCurrency(ds.Tables("Table3").Rows(0).Item(0))

                Label24.Text = ds.Tables("Table4").Rows(0).Item(0)
                Label21.Text = ds.Tables("Table5").Rows(0).Item(0)
                Label20.Text = FormatCurrency(ds.Tables("Table6").Rows(0).Item(0))

                Label39.Text = ds.Tables("Table7").Rows(0).Item(0)
                Label36.Text = ds.Tables("Table8").Rows(0).Item(0)
                Label35.Text = FormatCurrency(ds.Tables("Table9").Rows(0).Item(0))
            End If
        End If

    End Sub

sql vb.net

Edited 9 Years Ago by westsiderailway because: adding full code

2 Contributors
4 Replies
319 Views
9 Hours Discussion Span
Latest Post 9 Years Ago Latest Post by westsiderailway

Reverend Jim 4,968 Hi, I'm Jim, one of DaniWeb's moderators.

9 Years Ago

Try

StateSQL = "SELECT * FROM petrol_table " &
           " WHERE fueldate >= @sdate    &
           "   AND fueldate <  @edate"
Conn.Open()
sqlCmd = New SqlCommand(StateSQL, Conn)
sqlCmd.Parameters.AddWithValue("@sdate", M12S)
sqlCmd.Parameters.AddWithValue("@edate", M12F)

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

westsiderailway · Answer 1 · 2015-07-05T05:00:56+00:00

Thanks Jim,

but still getting the same error.

score 0 · Answer 2 · 2015-07-05T11:18:06+00:00

Do

StateSQL = "SELECT * FROM petrol_table " &
           " WHERE fueldate >= " & "'" & M12S & "'" & 
           "   AND fueldate <  " & "'" & M12F & "'"
Debug.WriteLine(StateSQL)

and post the output here.

westsiderailway · Answer 3 · 2015-07-05T11:42:52+00:00

Thanks Jim,

After doing some research, and some debuging(walking through the code) have found out the problem was with the format of the DateTimePicker.

it was in the format of 16/12/2013
when is was needed to be 20131216

SQL was being very picky about the date format.

found the answer here
http://stackoverflow.com/questions/5792600/how-to-change-the-date-format-of-a-datetimepicker-in-vb-net

so I just changed the DateTimePicker to custom with this line

  Dim DTP1 As String = Me.DateTimePicker1.Value.ToString("yyyyMMdd")
            Dim DTP2 As String = Me.DateTimePicker2.Value.ToString("yyyyMMdd")

and everything is working. YEAH!! :-)