Fixed it before but now not working

[acepeda]

Hey guys!

Im having a little problem with my datetimepicker!Im making a personal payroll system,where I want to add employee's daily hours!for example:

Employee name:Marcus Veron
IdPayroll:032012
Date|Hours

19/07/2012 | 8
20/07/2012 | 8
21/07/2012 | 7

Well im having problem quering the date field...cause in my datetimepicker i have it custom formated to dd/MM/yyyy but in the database its MM/dd/yyyy.I guess thats where the problem is.When I run this query "Select * From WorkedHours where EmployeeID="employee ID" and PayrollID="Payroll ID" and Date="dtphours.text".

I want it to look if the date exist then update the hours if not then create new registry.How do I do this?I have tried formating the dtp but still it doesnt read the date!!please Help!!

[TnTinMN]

I looked at your previous thread and the advice you were given should have worked if it was implemented correctly. So, please show your actual code where you define your Select query. This should be easy to correct for you if we have the relevant information.

Also, what type of database are you using?(SQLServer, Access, Oracle, MySql etc.)

[acepeda]

It did work last time but my PC crashed and I guess it dint save the actual form.Im using Access as my Database.What im trying to do is lookup a date by using a datetimepicker and search my database to see if the employee has reported hours to his name in that specific date,if not then create an entry.

But my problem is the Its not reading the datetimepicker value correctly.

Here's the code that im having problems with:

    Dim SearchComand As String
    Dim Leer As OleDbDataReader

   SearchComand = "SELECT * From LaborHours where Date=(" + Me.DTPDate.Text + ")and EmployeeID=Val(" & EmployeeID.Text & ")and PayrollID=val(" & PayrollID.Text & ")"
    Comando = New OleDbCommand(ComandoBuscar, Conexion)
    Leer = Comando.ExecuteReader() 'Leyendo el resultado del select

[TnTinMN]

Give this a try:

SearchComand = "SELECT * From LaborHours where Date= #" & Me.DTPDate.Value.Date.ToString(System.Globalization.CultureInfo.InvariantCulture.DateTimeFormat.ShortDatePattern) & "# and EmployeeID=Val(" & EmployeeID.Text & ") and PayrollID=val(" & PayrollID.Text & ")"

This assumes that the DateTime value from Access does not have a time component (usually safe assumption). For Access, you need to bracket the date with "#" characters (i.e. #1/20/2013# for 20 Jan 2013). It needs to be in Microsoft's InvariantCulture.ShortDatePattern (i.e. USA standards) format (mm/dd/yyyy) to ensure that there is no time component.

[acepeda]

Nope!Still it doesnt update.It just adds a new entry

[tinstaafl]

btw you can still access the answer on your old thread. Here's the answer that worked before.

change the format string, TempDateString = Format(dtphours.text, "MM/dd/yyyy"). Also you'll have to pass TempdateString to the database not dtphours.text