SQL Select Statement for date range in VB.NET

Good Afternoon,

I need some help with my VB.NET code using a SQL SELECT statement to export records to an excel file.

export history
Exporting all records is working correctly, but I can't figure out the SQL statement to do a date range to export.

See line 33 of my code below, this is what I cant figure out. The xls file is being created but there is no data in it. I can confirm that I do have records for the date range I selected (March 1st to March 29th).

Imports System.Data.OleDb
Imports System.IO


Public Class frmExport

    Private Sub btnExportCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportCancel.Click

        Me.Close()

    End Sub

    Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click

        Dim conn As New OleDbConnection
        Dim cmd As New OleDbCommand
        Dim sSQL As String = String.Empty

        Try

            'get connection string declared in the modFunctions.vb and assing it to conn variable
            conn = New OleDbConnection(sConnString)
            conn.Open()

            If rdoExportSBT.Checked = True And rdoExportAll.Checked = True Then

                sSQL = "SELECT ChqNum, ChqDate, Amount, PayTo, Reference, DebitAcc, CreditAcc, Currency, PayeeBank, Unit INTO [Excel 12.0;DATABASE=C:\SBTTest.xls].[EXPORT] FROM tblSBTHist"
                cmd = New OleDbCommand(sSQL, conn)
                cmd.ExecuteNonQuery()

            ElseIf rdoExportSBT.Checked = True And rdoExportRange.Checked = True Then

                sSQL = "SELECT ChqNum, ChqDate, Amount, PayTo, Reference, DebitAcc, CreditAcc, Currency, PayeeBank, Unit INTO [Excel 12.0;DATABASE=C:\SBTTest.xls].[EXPORT] FROM tblSBTHist WHERE ChqDate BETWEEN" & "'" & dtpExportFrom.Value & "'" & "and" & "'" & dtpExportTo.Value & "'"
                cmd = New OleDbCommand(sSQL, conn)
                cmd.ExecuteNonQuery()

            ElseIf rdoExportBSBIL.Checked = True And rdoExportAll.Checked = True Then

                sSQL = "SELECT ChqNum, ChqDate, Amount, PayTo, Reference, DebitAcc, CreditAcc, Currency, PayeeBank, Unit INTO [Excel 12.0;DATABASE=" & txtExportPath.Text & "\" & "BSBILTest.xls].[EXPORT] FROM tblBSBILHist"
                cmd = New OleDbCommand(sSQL, conn)
                cmd.ExecuteNonQuery()

                'ElseIf rdoExportBSBIL.Checked = True And rdoExportRange.Checked = True Then

                '    sSQL = "SELECT ChqNum, ChqDate, Amount, PayTo, Reference, DebitAcc, CreditAcc, Currency, PayeeBank, Unit INTO [Excel 12.0;DATABASE=" & txtExportPath.Text & "\" & "BSBILTest.xls].[EXPORT] FROM tblBSBILHist"
                '    cmd = New OleDbCommand(sSQL, conn)
                '    cmd.ExecuteNonQuery()

            Else

                MsgBox("Please select a unit to export.")

            End If

        Catch ex As Exception

        Finally

            conn.Close()

        End Try

    End Sub
End Class

Open in new window


Kindly advise.

Regards,
K
LVL 1
KevinInformation TechnologyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
Is it because you have no privileges to write at the root of the C: drive?
0
Éric MoreauSenior .Net ConsultantCommented:
also have you tried to hardcode dates just for test?

BETWEEN '2018-01-01' and '2018-12-31'

Open in new window

0
KevinInformation TechnologyAuthor Commented:
@Éric Moreau - I do have privileges to write to C: (local admin), plus my code to export all data (lines 27 and 39), exports the data correctly to a xls file.

Any other ideas?
0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Éric MoreauSenior .Net ConsultantCommented:
you saw my comment about hard coding dates?
0
KevinInformation TechnologyAuthor Commented:
yep. will give it a try in a few and advise of my results.
0
Nitin SontakkeDeveloperCommented:
In addition, I am surprised to see that dtpExportFrom.Value is not giving error, as to the best of my knowledge, the value property's data type is DateTime. Is implicit conversion happening here by calling the .ToString(). If yes, it is very much expected to go wrong.

Should use explicitly as follows, instead:

dtpExportFrom.Value.ToString("yyyyMMdd")

Furthermore, please clarify if your column in table is just date or datetime.
0
Éric MoreauSenior .Net ConsultantCommented:
>>In addition, I am surprised to see that dtpExportFrom.Value is not giving error,

surely because Kevin is using & to concatenate which automatically convert to strings!
0
KevinInformation TechnologyAuthor Commented:
ok so hardcoding it with the below is creating the file, but file is still empty:

BETWEEN '2018-03-01' and '2018-03-28'"

Open in new window


Also tried by month, day, year, but same result.
BETWEEN '03-01-2018' and '03-28-2018'"

Open in new window


Also tried using slashes instead of dashes and experience same result:
BETWEEN '03/01/2018' and '03/28/2018'"
BETWEEN '2018/03/01' and '2018/03/28'"

Open in new window


Below is screenshot of ChqDate column in table

table
table properties
0
Nitin SontakkeDeveloperCommented:
Always mention database....

You should try enclosing dates in # if it is MS-Access, if I remember it right from whatever I used to do several years back!
0
Éric MoreauSenior .Net ConsultantCommented:
Access!!!

sSQL = "SELECT ChqNum, ChqDate, Amount, PayTo, Reference, DebitAcc, CreditAcc, Currency, PayeeBank, Unit INTO [Excel 12.0;DATABASE=C:\SBTTest.xls].[EXPORT] FROM tblSBTHist WHERE ChqDate BETWEEN #" & dtpExportFrom.Value.Tostring("yyyy/MM/dd")  & "# and #" & dtpExportTo.Value.Tostring("yyyy/MM/dd") & "#"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KevinInformation TechnologyAuthor Commented:
Apologies for not mentioning what database I was using in initial post.

I appreciate very much your assistance. All looks good after including the '#'.

Thanks.
K
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.