Problem with query dates

  • Thread starter Thread starter Henrique
  • Start date Start date
H

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
 
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
Click to expand...

Date style of what?
I really need to fix this.Seabra
Click to expand...

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 & "'"
Click to expand...

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)
Click to expand...

Why not SqlConnection?
 
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
 
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
Click to expand...

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

<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
 
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?
Click to expand...
Click to expand...
 
Back
Top