No results searching - Access Database

[shaolinazul]

Hi... I am searching and filtering data in an Access Database from vb.net, everything works fine if I use criteria like a number or string, BUT if I try to filter data by Date Column, I get no results in the datagrid..... what's wrong ?

 

It accepts the date format because if I put something different (like "Hello") it shows error...

 

I also have tried with a MonthCalendar control with the 'same' results...

 

Thanks in Advance....

[Robby]

Surround the date string with the pound sign (#) .

[shaolinazul]

nop.... I already tried with # and No results....

[Robby]

What does your code look like?

[shaolinazul]

'OleDbDataAdapter1

'

Me.OleDbDataAdapter1.SelectCommand = Me.OleDbSelectCommand1

Me.OleDbDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "4-1", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("Hora Inicio", "Hora Inicio"), New System.Data.Common.DataColumnMapping("Hora Fin", "Hora Fin"), New System.Data.Common.DataColumnMapping("Clase", "Clase")})})

'

'OleDbSelectCommand1

'

Me.OleDbSelectCommand1.CommandText = "SELECT [Hora Inicio], [Hora Fin], Clase FROM [4-1] WHERE (Fecha LIKE ?)"

Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1

Me.OleDbSelectCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Fecha", System.Data.OleDb.OleDbType.DBDate, 0, "Fecha"))

'

'OleDbConnection1

'

Me.OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documen" & _

"ts and Settings\SHAOLIN\Mis documentos\domo.mdb;Mode=Share Deny None;Extended Pr" & _

"operties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Da" & _

"tabase Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet" & _

" OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:" & _

"New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encryp" & _

"t Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact " & _

"Without Replica Repair=False;Jet OLEDB:SFP=False"

 

.

more code...

.

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click

Dim date11 As Date

date11 = MonthCalendar1.SelectionStart

Dim date12 As String = "#" & date11 & "#"

Me.OleDbDataAdapter1.SelectCommand.Parameters(0).Value = date12

Label4.Text = Me.OleDbDataAdapter1.SelectCommand.Parameters(0).Value

Me.DataSet11.Clear()

Me.OleDbDataAdapter1.Fill(Me.DataSet11)

End Sub

[shaolinazul]

Ok thanks Robby, I solved it.....

 

the problem was with the format in the selectcommand:

 

Me.OleDbSelectCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Fecha", System.Data.OleDb.OleDbType.DBDate, 0, "Fecha"))

 

This is the right way for my app... :

 

Me.OleDbSelectCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Fecha", System.Data.OleDb.OleDbType.Date, 0, "Fecha"))

[Larry dot NET]

Hi... I am searching and filtering data in an Access Database from vb.net' date=' everything works fine if I use criteria like a number or string, BUT if I try to filter data by Date Column, I get no results in the datagrid..... what's wrong ? [/quote']

 

Hi, actually I'm having the same problems, but the error occurs in my SQL statement, I'm sure. Here's what I'm working with:

 

This is the date selected from the MonthCalendar picker:

 

Dim DateSelected As String = Format(Me.dtpCalendarPicker.SelectionEnd, "mm/dd/yyyy")

 

 

and in my search string:

 

cmdCommand.CommandText = "SELECT QuoteHistory.QuoteNo, QuoteHistory.QuoteDate, QuoteHistory.CustomerName, QuoteHistory.SalesPersonName FROM QuoteHistory WHERE ((QuoteHistory.QuoteDate) = #" & DateSelected & "#)"

 

 

Anybody got any insight into the problem? This is a simple thing, I'm sure!

 

Thanks!

[Joe Mamma]

1. Use parameters.

2. Dates are actually floating point numbers, the integer is a day number from a zero day, 1/1/1900, I think, and the decimal is the part of the day. for example

 

1/1/1900 12:00 AM = 0.0

1/1/1900 12:00 PM = 0.5

 

so what does this mean? your dates may have fractional time parts and your equality wont work.

 

 

use 'select from mytable where adate >= ? and ? > adate'

 

set the first parameter to the day you are looking for, the second to the day after

 

notice the selection uses '>' operators, and no 'not' or '<'. this is because 'not' and '<' will not use indexes but cause table search.