Question DateTimePicker works only for date> 12 .... please help

[desy]

Hi, I need your help, because the whole of today I just spent to find the solution but still can't get properly. It works well for the date >12 only

My purpose is to import excel file into Datagridview with a selection on date field before upload
My Excel file just consists of 3 columns: InvoiceNbr, InvoiceDate, UpdatedBy
I use DTP to filter the date and my DateTimePicker format is set into SHORT in property

My code for selection is:

MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= d:\\testprint\test1.xlsx ; extended Properties= Excel 12.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet1$] where InvDate = #" & DateTimePicker1.Value.ToShortDateString & "#", MyConnection)
MyCommand.TableMappings.Add("Table", "Net-informations.com")
​

dataset = New System.Data.DataSet
MyCommand.Fill(dataset)
DataGridView1.DataSource = dataset.Tables(0)

MyConnection.Close()

I'm trying to change CultureInfo into en-GB, but maybe it is not properly define as I'm really newbie in VB .net

This is my code for setting CultureInfo:

Imports System.Globalization
​

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
​

My.Application.ChangeCulture("en-Gb")
My.Application.ChangeUICulture("en-Gb")
DateTimePicker1.Format = DateTimePickerFormat.Short
​

End Sub
​

I think the problem comes from format en-US, but I don't know how to set in in DTP and selection

Thanks for the help in advance

 

[jmcilhinney]

Just do it properly in the first place and you won't have to worry about cultures. NEVER use string concatenation to insert values into SQL code. ALWAYS use parameters. Do that and things like date format will never be an issue. Follow the Blog link in my signature below and read my post on Parameters In ADO.NET to learn why and how to use parameters.

 

[desy]

Thanks jmcilhinney for your prompt reply
After focusing working on parameter rather than thinking about cultures, now it works with a simple changes:
Dim ddate As Date = Date.Parse(DateTimePicker1.Value.ToString())

MyCommand = New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet1$] where InvDate = #" & Format(ddate, "MM/dd/yyyy") & "#", MyConnection)


I realized that the selection always work with the default format date. When we want to show different format on UI, then it will be different thing

 

[jmcilhinney]

Thanks jmcilhinney for your prompt reply
After focusing working on parameter rather than thinking about cultures, now it works with a simple changes:
Dim ddate As Date = Date.Parse(DateTimePicker1.Value.ToString())

MyCommand = New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet1$] where InvDate = #" & Format(ddate, "MM/dd/yyyy") & "#", MyConnection)


I realized that the selection always work with the default format date. When we want to show different format on UI, then it will be different thing

Um, nope. What exactly is this supposed to be achieving?

Dim ddate As Date = Date.Parse(DateTimePicker1.Value.ToString())

That accomplishes exactly nothing. As for this:

MyCommand = New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet1$] where InvDate = #" & Format(ddate, "MM/dd/yyyy") & "#", MyConnection)

It's still not using parameters so any focusing you did also accomplished nothing. Did you just read part of my blog post, i.e. the part that shows what NOT to do?