German to Access / SQL Server

[Robse]

I'm pretty frustrated here, been trying this for days...
All I want is for german users to be able to enter dates that should be stored in SQL Server. The dates are in the following format "dd.mm.yyyy". I always get error messages like this one: Syntax error in date in query expression '#23.01.2004 00:07:00#'. - using Access for now.

I've tried lots of methods (Thread.CultureInfo, CDate(), Formatting DateTimeStyles) but here is my latest:

Dim dTermin As DateTime
dTermin = DateTime.Parse(txtTermin.Text)

sSQL = "INSERT INTO Termin (Termin) VALUES ("
sSQL = sSQL & "#" & dTermin & "#)"

That also gives me an error. If I hardcode a date like "10-04-2004" then it works, however. It's an Access 2000 db and there are two fields: ID (Autonumber) and Termin (ShortDate). Using Jet OLEDB 4.0 Provider.

Does anybody have a simple example?

 

[chiph]

You're confusing how a date is displayed vs. how it is stored.

It should be displayed in the format that the user expects it to be, based on their configuration (CultureInfo, control panel settings, etc).

Also, when the user enters a date value, they expect to enter it in the format they're accustomed to. You need to use a control which supports this (datetime picker, drop-down boxes, masked-edit control, etc). The goal is to convert what they give you into a DateTime variable.

You would then pass that DateTime value to the code which does the database work. The ADO.NET Parameter object understands DateTimes natively. If you decide not to use Parameter objects (and I strongly urge you to -- Google on "SQL Injection"), then you can call .ToString() on it and you'll get a string which your database will understand.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[Robse]

No this was just for testing, the finished version will use Stored Procedures. I'm not interested in how the data is displayed right now, this is just for entering dates and I think you're right I should store them in native format (I think that's ISO) - so how can I convert a string into an USA SQL Server compliant DateTime type?

 

[chiph]

Use a DateTime picker control. It will return you a DateTime variable, which you can then pass to your data layer.

In your GUI:

Dim dTermin As DateTime  ' variable
Dim MyDateTimePicker As DateTimePicker ' Visual component

public Sub btnSave_click
  ' The date-time picker handles the messy user interface
  ' part.
  dTermin = MyDateTimePicker.Value;

  ' Send to data layer

End Sub

In your data layer:

Dim MySqlConnection As SqlConnection
Dim MySqlCommand As SqlCommand

MySqlCommand = New SqlCommand(MyUpdateQuery, MySqlConnection)
MySqlCommand.Parameters.Add(("@dTerminDate", SqlDbType.DateTime).Value = dTermin
MySqlCommand.ExecuteNonQuery

Note that I'm a C# guy, not a VB.Net guy, so my syntax may be off.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[Robse]

Thanks for your replies, but I don't want to use the Datepicker or linked Dropdowns - that's totally unnecessary.
There must be a nice .NET way to convert a string from a textbox into native SQL server format, provided it can be converted to a valid date?

 

[chiph]

Sorry, there isn't.

Since people can enter dates in so many ways, there does not (and never will) exist a nice clean way to convert a string to a DateTime value. The least you can do is try and call DateTime.Parse on the GUI side to see if you get anything reasonable.

But the problem of European vs. US date formats (dd/mm/yy vs. mm/dd/yy) means there is no way to tell Jan 2nd from Feb 1st. Plays hell with data consistency!

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first