Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
Good morning everyone.
I am trying to update an .accdb (Access 2007)
One of the rows in table is a date :
Date Type = Date/Time Format = ShortDate
 
This is being updated from a VB2010.Net application using a SQL Update statement
 
 
 Ssql = "UPDATE Purchase_Order SET [Po_Rev_No] =  " & lbl_Revision_Number.Text & _
                                            ", [Po_Owner] = '" & GlobalVariables.UserInit & "'" & _
                                            ", [Po_Type] = '" & cbo_Type.Text & "'" & _
                                          ", [Po_Proj_No] = '" & ProjNo & "'" & _
                                         ", [Po_Supplier] = '" & cbo_Supplier.Text & "'" & _
                                          ", [Po_Contact] = '" & cbo_Contact.Text & "'" & _
                                         ", [Po_Currency] = '" & theCurrency & "'" & _
                                 ", [Po_Delivery_Address] = '" & GlobalVariables.DeliveryAddr & "'" & _
                                    ", [Po_Delivery_From] = '" & cbo_Delivery_From.Text & "'" & _
                               ", [Po_Delivery_From_Date] = #" & dtp_Delivery_Date_From.Text & "#" & _
                                      ", [Po_Delivery_To] = '" & cbo_Delivery_To.Text & "'" & _
                                 ", [Po_Delivery_To_Date] = #" & dtp_Delivery_Date_To.Text & "#" & _
                                      ", [Po_Settle_Disc] =  " & mxt_Settlement_Discount.Text & _
                                        ", [Po_Pay_Terms] = '" & cbo_Payment_Terms.Text & "'" & _
                                            ", [Po_Notes] = '" & rxt_Notes.Text & "'" & _
                                        ", [Po_Incoterms] = '" & cbo_Incoterm.Text & "'" & _
                                 ", [Po_Inco_Named_Place] = '" & cbo_Inco_Named_Place.Text & "'" & _
                                       ", [Po_Inco_Place] = '" & cbo_Inco_Place.Text & "'" & _
                                   ", [Po_Submitted_Date] = #" & Today & "#" & _
                                        ", [Po_Auth_Date] = Null" & _
                                          ", [Po_Auth_By] = '" & AuthBy & "'" & _
                                           ", [Po_No_Vat] =  " & chk_No_Vat.Checked & " " & _
                                      " WHERE [Po_Number] = '" & lbl_Order_Number.Text & "'"
 
            da = New OleDb.OleDbDataAdapter(Ssql, cn)
            Try
                da.UpdateCommand = New OleDbCommand(Ssql, cn)
                da.UpdateCommand.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox("Notify Darrell Immediately - PO Master not updated")
                MsgBox(ex.ToString)
                Exit Sub
            End Try
            ds.Clear()
            da.Dispose()
 
The table item '[Po_Delivery_From_Date]' contains the value from a DateTimePicker.
If the date = 19/01/2013 (IE 19 January 2013) the database is updated with 19/01/2013.
If the date = 04/02/2013 (IE 4 February 2013) the database is updated with 02/04/2013 (Ie 2 April 2013) <--- THIS IS THE PROBLEM
 
In my Region and Language settings the short date = dd/mm/yyyy.
 
Can anyone please tell me why this is happening
 
Thanks in advance
 
Darrell
Posted 5-Feb-13 1:24am
Edited 5-Feb-13 2:19am
CHill6067K
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Always be explicit with dates when writing to databases to avoid issues like this. Unfortunately most of the tools we use these days seem to assume all programmers live in the US Poke tongue | ;-P
 
To get around the problem replace the line
dtp_Delivery_Date_From.Text
with
dtp_Delivery_Date_From.Value.ToString("dd-MMM-yyyy")
  Permalink  
Comments
Darrell de Wet at 5-Feb-13 8:08am
   
Hi Chill
Thanks for the quick response. I tried your suggestion and got the same result so I swapped the dd and MM around ie("MM/dd/yyyy") and the database now contains the date as i need it.
 
This makes no sense to me as my (Win 7) Regiona and Language settings are dd/mm/yyyy for short date.
 
I also tried to run my original SQL statement in Ms Access and had the same original problem. When i tried with your chganges (and swapped the dd & mm) around it achieved the desired result.
 
the database contains the date as i need it but it thinks it in in format MM/DD/YYYY and i am concerned that it is going to come back and bite me sometime.
 
Any thoughts?
CHill60 at 5-Feb-13 8:37am
   
Did you spot that I had 3 'M's in the format? I.e. It should look like "4 FEB 2013" - this technique usually works. I did a bit of digging around and some other forums have suggested setting the format on the column in Access - I'm not convinced though as internally the dates are stored as numbers, but it might be worth a try
Darrell de Wet at 5-Feb-13 8:44am
   
Ahh good point about the 3 M's - i did not notice that. I'll give it a try and let you know. I can just convert from MMM to MM when i read it back. This problem is driving me nuts.
Thanks for your support.
CHill60 at 5-Feb-13 8:46am
   
No problem. You should find when you read it back that it's in short format so no conversion *should* be required - I say that but you never know with Access!! :-)
Darrell de Wet at 5-Feb-13 8:49am
   
You are a star !!! Works !!!
Exactly as you said - no change required when I read it back.
Thank you sooo much

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 587
1 Sergey Alexandrovich Kryukov 519
2 Maciej Los 305
3 BillWoodruff 250
4 Mathew Soji 195
0 OriginalGriff 7,356
1 Sergey Alexandrovich Kryukov 6,777
2 DamithSL 5,461
3 Manas Bhardwaj 4,916
4 Maciej Los 4,475


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 5 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100