Update Record Base in Date & Time VB.NET

[GAiXz]

Good Day Programmers ^_^

i need a help...

how do i Update the record based on the Date & Time??

here's the code:

Private Sub Timer2_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer2.Tick
        lblTimeOfDay.Text = Format(TimeOfDay, "hh:mm:ss tt")
        UpdateItem()
    End Sub

    Private Function UpdateItem()
        Dim xSQL As New System.Text.StringBuilder
        xSQL.AppendLine("UPDATE tblClientRecord SET Status=@Stat WHERE DateOfReturn=#" & DTParrive.Text.Trim & "# AND TimeOfReturn>='" & lblTimeOfDay.Text.Trim & "' ")
        Try
            Using cn As New OleDbConnection(ConString)
                cn.Open()
                Dim cmd As New OleDbCommand(xSQL.ToString, cn)
                cmd.Parameters.AddWithValue("@Stat", lblStatus.Text.Trim)

                cmd.ExecuteNonQuery()
                cmd.Dispose()
                Return True
            End Using
        Catch ex As Exception
            MessageBox.Show("ERROR3:" & ex.Message.ToString, My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return False
        End Try
    End Function

the problem is... it's updating but not on time...
it's updating in advance...
i just want to update it in the date & time that the record has
and i'm thinking that it' may affect the other record's that not on time to update

thanks in advance for someone will help me ^_^

:-/

[Oxiegen]

Your best bet would be to use the CONVERT function to convert DateOfReturn into a varchar with a specific format.

xSQL.AppendLine("UPDATE tblClientRecord SET Status=@Stat WHERE CONVERT(VarChar, DateOfReturn, <format>)='" & DTParrive.Text.Trim & "' AND CONVERT(VarChar, TimeOfReturn, <format>)>='" & lblTimeOfDay.Text.Trim & "' ")

Where <format> is any of preset CONVERT integer formats: http://msdn.microsoft.com/en-us/library/ms187928.aspx

That way you can define whatever format will be best suited to match what's in the database with what the datetimepicker and label contains.

[M.Waqas Aslam]

trye this query

"update tablename set status=@status where DateOfReturn='" & datetimepicker.value.date & "' and AND TimeOfReturn >= '" & datetimepicker1.value.time & "'"

hope this will solve your problem .

Regards