Hello,
Best to use a DateTimePicker unless you are using some form of validation on the TextBoxes to make sure they are dates.
The following shows how to get a date range where the sheet's first row is data, not column names. Column 1 is type date, column 2 string. Since when HDR in the connection string is NO column names are Fn so A would be F1, B would be F2 etc so for this demo I alias the columns.
Code:
Module ExcelModule
Public Sub DemoDateRange()
Dim Builder As New OleDb.OleDbConnectionStringBuilder With
{
.Provider = "Microsoft.ACE.OLEDB.12.0",
.DataSource = IO.Path.Combine(Application.StartupPath, "MyFile1.xlsx")
}
Builder.Add("Extended Properties", "Excel 12.0; HDR=No;")
Dim SheetName As String = "Sheet1"
Dim dt As New DataTable
Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString}
Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
cmd.CommandText =
<SQL>
SELECT F1 As TheDate, F2 As SomeColumn
FROM [<%= SheetName %>$]
WHERE F1 BETWEEN @StartDate AND @EndDate
</SQL>.Value
cmd.Parameters.Add(
New OleDb.OleDbParameter With
{
.ParameterName = "StartDate", .DbType = DbType.Date,
.Value = "1/30/2013"
}
)
cmd.Parameters.Add(
New OleDb.OleDbParameter With
{
.ParameterName = "EndDate", .DbType = DbType.Date,
.Value = "4/5/2013"
}
)
cn.Open()
dt.Load(cmd.ExecuteReader)
End Using
End Using
End Sub
End Module
Reply With Quote
