From: Henrique on 11 Jun 2010 13:09 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
From: Armin Zingler on 11 Jun 2010 14:04 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
From: Cor Ligthert[MVP] on 13 Jun 2010 02:54 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 "Henrique" <Henrique(a)discussions.microsoft.com> wrote in message news:54DBD271-4590-4553-9F97-E8506B46CE74(a)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 > > >
From: Chris Dunaway on 29 Jun 2010 16:44 On Jun 11, 12:09 pm, Henrique <Henri...(a)discussions.microsoft.com> wrote: > 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 > <snip> > 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
|
Pages: 1 Prev: Inner class problem. Next: Create click event (keyboard key down) on Panel. |