date in the sql query
I have an access db with two short date fields and in my vb.net application I need to
display in a dgw the data between two chosed values. Every time I try the "Data
type mismatch in criteria expression" appears.
I use two Datatimepicker formated as Short. In the database the field are also
formated as Short Date. What could be the reason for that?
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider = Microsoft.Jet.OleDb.4.0;Data Source=C:\Maint.mdb")
Dim cmd As New OleDb.OleDbCommand
Dim cmda As OleDb.OleDbDataAdapter
Dim dset As New DataSet
'Dim cb As OleDbCommandBuilder = New OleDb.OleDbCommandBuilder
Try
Dim query As String
query = "SELECT * FROM Sarcini WHERE DataInitiere between '" + DateTimePicker1.Value() + "' And '" + DateTimePicker2.Value() + "'"
cmda = New OleDbDataAdapter(query, con)
dset.Clear()
' cb.QuotePrefix = "[
' cb.QuoteSuffix = "]"
con.Open()
cmda.Fill(dset, "Sarcini")
Me.DataGridView1.DataSource = dset.Tables("Sarcini")
con.Close()
Catch ex As OleDb.OleDbException
MsgBox(ex.Message)
End Try
End Sub
[2792 byte] By [
manasia] at [2007-12-26]
Now the problem was solved only by half meaning that I use the code from below and on the DGW when I search from 2/11/2006 to 30/11/2006 I receive data field
not in the search domain like 29/10/2006. I don't know why and I use use regional option as English (UK) to have the date formated as dd/MM/yyyy.
I think that the sql query is only looking at the dd values and not to the MM. I need some advice to sole this!
Try
dset.Clear()
Dim query As String'query = "SELECT * FROM Sarcini WHERE DataInitiere between '" + DateTimePicker1.Value.ToString("dd/MM/yyyy") + "' And '" + DateTimePicker2.Value.ToString("dd/MM/yyyy") + "'" query = "SELECT * FROM Sarcini WHERE DataInitiere >= #" + DateTimePicker1.Value() + "# AND DataInitiere <= #" + DateTimePicker2.Value() + "#" cmda = New OleDbDataAdapter(query, con) con.Open()
cmda.Fill(dset, "Sarcini")
Me.DataGridView1.DataSource = dset.Tables("Sarcini")
Catch ex As OleDb.OleDbException
MsgBox(ex.Message)
End Try
con.Close()
I'm having a simular problem. I'm using an access database whit short dates. I get my dates from 2 DateTimePickers.
I use parameters, and I always get the same error. "The datatypes don't match in expression".
I've tried everything. Other formats, using strings in stead of dates, ... . But nothing works. It's realy frustrating. Can somebody help?
