Problem with query dates

11 Jun 2010 5:09 PM

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

11 Jun 2010 6:04 PM

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?

--
Armin

13 Jun 2010 6:54 AM

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

Show quoteHide quote

"Henrique" <Henri***@discussions.microsoft.com> wrote in message
news:54DBD271-4590-4553-9F97-E8506B46CE74@microsoft.com...
> 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
>
>
>