Problem with query dates

[Henrique]

Hi

My records date range in the database is Betwene 20/05/20010 and
22/05/20010 .

When my dateObject has a date of 24/05/2010 00:00:00, is shown me all the
records less than this date, OK. But if I change the month, this date to
24/04/2010 00:00:00 shows the same records. That is even if I change the
month the records that appear are always less than the 24 days without
following month. There lies my problem.

Note: My date style is dd-mm-yyyy and time is hh: mm: ss

I really need to fix this.Seabra



Dim Q1, SQL As String
Dim DateTime1 As Date
Dim ConnString As String = "Provider=SQLOLEDB;Data
Source=HENRIQUE-PC;Initial Catalog=Winergest;Integrated Security=SSPI"
Dim dateString = DateTimePicker1.Text & " " & DateTimePicker3.Text
Dim dateObject As DateTime = DateTime.ParseExact(dateString, "dd-MM-yyyy
HH:mm:ss", New Globalization.CultureInfo("pt-PT"))

DateTime1 = dateObject
Q1 =" SELECT contadores.Data_Hora as Data_Hora, "

Q1 = Q1 +"ID_Contador as Contador,Nome as Nome, E_SVazio as H_SVazio,
E_Vazio as H_Vazio, E_Cheias as H_Cheias, E_Ponta as H_Ponta, "

Q1 = Q1 +"(E_SVazio+ E_Vazio+ E_Cheias+ E_Ponta) as Total "

Q1 = Q1 +", Convert(varchar, contadores.Data_Hora, 105) as horas"

Q1 = Q1 & " FROM Contadores JOIN DadosContadores ON Contadores.ID_Contador =
DadosContadores.ID "

Q1 = Q1 & " WHERE Convert(varchar(10), contadores.Data_Hora, 104) < '" &
dateObject & "'"

Q1 = Q1 & " AND E_SVazio<>0 AND E_Vazio<>0 AND E_Cheias<>0 AND E_Ponta<>0 "

Q1 = Q1 & "ORDER BY "

Q1 = Q1 & "contadores.Data_Hora Asc "

TextBox1.Text = Q1

Using Con As New OleDb.OleDbConnection(ConnString)
Con.Open()

Dim Tabela As New DataTable()
SQL = Q1

Dim Da As New OleDb.OleDbDataAdapter(SQL, Con)
Dim ocb As New OleDbCommandBuilder(Da)
Da.Fill(Tabela)
DataGridView1.DataSource = Tabela
End Using

 

[Armin Zingler]

Am 11.06.2010 19:09, schrieb Henrique:

Hi

My records date range in the database is Betwene 20/05/20010 and
22/05/20010 .

When my dateObject has a date of 24/05/2010 00:00:00, is shown me all the
records less than this date, OK. But if I change the month, this date to
24/04/2010 00:00:00 shows the same records. That is even if I change the
month the records that appear are always less than the 24 days without
following month. There lies my problem.

Note: My date style is dd-mm-yyyy and time is hh: mm: ss

Date style of what?

I really need to fix this.Seabra

Use the OleDbCommand's Parameters property instead of concatinating
Strings. Mind the correct data types.

Q1 = Q1 & " WHERE Convert(varchar(10), contadores.Data_Hora, 104) < '" &
dateObject & "'"

Why do you convert to a string? The < operator is also defined for the
date field type - I guessing it's type is date.

Using Con As New OleDb.OleDbConnection(ConnString)

Why not SqlConnection?

 

[Cor Ligthert[MVP]]

Why so difficult,

Change in this sample SQLClient to OleDB.

http://www.vb-tips.com/SQLParameters.aspx

Be aware that the whole EU uses the same date time format, with the
exception of UK and Eire where a 12 hour clock is used.

(The punctuation can be different, with what I've seen here the most
exceptional Norway)

Cor

 

[Chris Dunaway]

Hi

My records date range in the database is  Betwene 20/05/20010 and  
22/05/20010 .

When my dateObject has a date of 24/05/2010 00:00:00, is shown me all the
records less than this date, OK. But if I change the month, this date to
24/04/2010 00:00:00 shows the same records. That is even if I change the
month the records that appear are always less than the 24 days without
following month. There lies my problem.

Note: My date style is dd-mm-yyyy and time is hh: mm: ss

Dim dateString = DateTimePicker1.Text & " " & DateTimePicker3.Text
Dim dateObject As DateTime = DateTime.ParseExact(dateString, "dd-MM-yyyy
HH:mm:ss", New Globalization.CultureInfo("pt-PT"))

<snip>

Why convert your date to a string and then back to a date?? Just use
the Value property:

Dim dateObject As DateTime = DateTimePicker.Value

And then do as Armin says and use Parameters and not string
concatenation to build your query. You will be much happier!

Chris

 

[liam webber]

Here you can find some date parametr samples

http://vb.net-informations.com/crystal-report/vb.net_crystal_report_parameter_date.htm

for more on crystal reports just go to :

http://vb.net-informations.com/crystal-report/vb.net_crystal_reports_tutorials.htm

liam

Hi

My records date range in the database is Betwene 20/05/20010 and
22/05/20010 .

When my dateObject has a date of 24/05/2010 00:00:00, is shown me all the
records less than this date, OK. But if I change the month, this date to
24/04/2010 00:00:00 shows the same records. That is even if I change the
month the records that appear are always less than the 24 days without
following month. There lies my problem.

Note: My date style is dd-mm-yyyy and time is hh: mm: ss

I really need to fix this.Seabra



Dim Q1, SQL As String
Dim DateTime1 As Date
Dim ConnString As String = "Provider=SQLOLEDB;Data
Source=HENRIQUE-PC;Initial Catalog=Winergest;Integrated Security=SSPI"
Dim dateString = DateTimePicker1.Text & " " & DateTimePicker3.Text
Dim dateObject As DateTime = DateTime.ParseExact(dateString, "dd-MM-yyyy
HH:mm:ss", New Globalization.CultureInfo("pt-PT"))

DateTime1 = dateObject
Q1 =" SELECT contadores.Data_Hora as Data_Hora, "

Q1 = Q1 +"ID_Contador as Contador,Nome as Nome, E_SVazio as H_SVazio,
E_Vazio as H_Vazio, E_Cheias as H_Cheias, E_Ponta as H_Ponta, "

Q1 = Q1 +"(E_SVazio+ E_Vazio+ E_Cheias+ E_Ponta) as Total "

Q1 = Q1 +", Convert(varchar, contadores.Data_Hora, 105) as horas"

Q1 = Q1 & " FROM Contadores JOIN DadosContadores ON Contadores.ID_Contador =
DadosContadores.ID "

Q1 = Q1 & " WHERE Convert(varchar(10), contadores.Data_Hora, 104) < '" &
dateObject & "'"

Q1 = Q1 & " AND E_SVazio<>0 AND E_Vazio<>0 AND E_Cheias<>0 AND E_Ponta<>0 "

Q1 = Q1 & "ORDER BY "

Q1 = Q1 & "contadores.Data_Hora Asc "

TextBox1.Text = Q1

Using Con As New OleDb.OleDbConnection(ConnString)
Con.Open()

Dim Tabela As New DataTable()
SQL = Q1

Dim Da As New OleDb.OleDbDataAdapter(SQL, Con)
Dim ocb As New OleDbCommandBuilder(Da)
Da.Fill(Tabela)
DataGridView1.DataSource = Tabela
End Using

09, schrieb Henrique:

Date style of what?


Use the OleDbCommand's Parameters property instead of concatinating
Strings. Mind the correct data types.


Why do you convert to a string? The < operator is also defined for the
date field type - I guessing it is type is date.



Why not SqlConnection?