How to work with generic date format

Gali1978

hi i have C# program that work with access database. (i have datetime field in access - Short Date) How to make sure that the program will not crash with the wrong date format ? for example: in my computer the date in this format: dd/MM/yyyy and if i run my program on computer that has this format: MM/dd/yyyy the program will crash or will show me abnormal result is there any generic solution ? can i get any C# code for this ? thanks in advance

realJSOP

This might help http://msdn.microsoft.com/en-us/library/w2sa9yss.aspx[^]

".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass." - Dale Earnhardt, 1997

Luc Pattyn

You can and should get rid of all regional setting stuff and avoid all string representations for dates, use the proper data types in your code AND in your database. This implies you use SQL parameters, not literal data. For MS Access, use OleDbParameter (which requires the ? symbolic name in the SQL statement, where other databases would allow real symbolic names for the parameters; the question marks and parameters are synchronized in chronological order). Example:

string SQL="UPDATE [references] SET name=?,address=?,zip_code=?,date=? WHERE referenceID = 1115224993"

OleDbCommand cmd=new OleDbCommand(...);
...
cmd.Parameters.Add("date", OleDbType.Date).Value=DateTime.Today;
...

See, no date strings at all. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Dalek Dave

I like that! Nice to see you around Luc.

------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave CCC Link[^] Trolls[^]

Luc Pattyn

Dalek Dave wrote:

I like that!

This ain't bloody Facebook (yet)! :mad:

Luc Pattyn [My Articles] Nil Volentibus Arduum

PIEBALDconsult

I have found that you can use @name in place of ?, which allows one to use a parameter more than once, but they still have to be in typographical order.

Luc Pattyn

I am assuming you mean the following would be good:

string SQL="UPDATE tableName SET anInt=@anInt, aString=@aString, anotherInt=@anInt, aDate=@aDate WHERE ..."
OleDbCommand cmd=new OleDbCommand(...);
cmd.Parameters.Add("@anInt", OleDbType.Int).Value=12;
cmd.Parameters.Add("@aString", OleDbType.VarChar).Value="abc";
// no need to give the third parameter as it re-uses the first one
cmd.Parameters.Add("@aDate", OleDbType.Date).Value=DateTime.Today;

as the parameters are given in the order of first use. But then that limitation seems pretty weird, as now they use parameter names to handle duplicate uses, but not for general parameter identification? That doesn't make much sense to me. If they would use names all the way (not hard at all), it would work like T-SQL and others!? Maybe it depends on the OleDb version, where older ones need "?" and newer ones do it the proper way? Just guessing. Do you have any reference? :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Luc Pattyn

I just performed a simple experiment with @name and it works just fine, so now my code for Access (using Provider=Microsoft.Jet.OLEDB.4.0) and for SQLServer looks pretty much the same, except for those stupid OleDbTypes/SqlTypes enums the parameters need. [ADDED] The MSDN doc[^] doesn't mention named parameters in a positive way, all it says is: The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters. [/ADDED] :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

modified on Monday, August 1, 2011 11:05 PM

Peter_in_2780 · modified on Monday, August 1, 2011 11:05 PM

Do I smell a Tip/trick or article in the making? Good to see you again. :) Cheers, Peter

Software rusts. Simon Stephenson, ca 1994.

Luc Pattyn

Peter_in_2780 wrote:

Do I smell a Tip/trick or article in the making?

Not really. I don't have any reference to this, and I tend not to rely on, nor propagate, undocumented ways that seem to work but aren't promised to work.

Peter_in_2780 wrote:

Good to see you again

Thanks. I have reduced my presence, I'm visiting and posting sporadically now. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

PIEBALDconsult

I think both uses of @anInt have to be consecutive, but it has been a while since I last experimented with it.

Luc Pattyn wrote:

Do you have any reference?

Just experience.

PIEBALDconsult · modified on Monday, August 1, 2011 11:05 PM

Luc Pattyn wrote:

those stupid OleDbTypes/SqlTypes enums the parameters need.

The what?

Luc Pattyn

cmd.Parameters.Add("@aDate", OleDbType.Date).Value=DateTime.Today;

these force me to duplicate a lot of code when I need to support more than one database type (Access and SQLServer). :doh:

Luc Pattyn [My Articles] Nil Volentibus Arduum

PIEBALDconsult

I don't do it that way. I never set the type; it seems unnecessary. Here's how I create parameters currently:

protected virtual System.Data.IDbDataParameter
AddParameter
(
string Name
,
object Value
)
{
System.Data.IDbDataParameter result = this.command.CreateParameter() ;

result.ParameterName = Name ;

result.Value = Value ;

this.command.Parameters.Add ( result ) ;

return ( result ) ;

}

(I hope to pubish my latest data access library before the end of the year.)

Luc Pattyn

Thanks Piebald, great idea of using interfaces here, it allowed me to reduce my code a lot. I got it all working with a single IDbConnection and IDbCommand (serving either OleDb/Access or Sql/SQLServer), except for one issue: Access doesn't want to store DateTime values in Date/Time fields when using IDbCommand and CreateParameter, not even when I force the field type (with result.DBType, which is generic, not OleDb-specific). I'm getting a generic message about a criteria datatype mismatch, and all Google turns up is adding or removing quotes, which I don't have and don't want. So I reverted to using cmd.Parameters.Add(name,type).Value=val which is target specific but works fine. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

PIEBALDconsult

Glad to be of service.

Luc Pattyn wrote:

interfaces here, it allowed me to reduce my code

That's what they're for.

Luc Pattyn wrote:

Access doesn't want to store DateTime

Huh, that's odd. I guess I haven't tried it. I'll give it a shot and see whether or not I can replicate it.

PIEBALDconsult

Well, I certainly was able to replicate it. In my derived class for OleDb, I have added:

protected override System.Data.IDbDataParameter
AddParameter
(
string Name
,
object Value
)
{
System.Data.OleDb.OleDbParameter result =
(System.Data.OleDb.OleDbParameter) base.AddParameter ( Name , Value ) ;

if ( Value is System.DateTime )
{
    result.OleDbType = System.Data.OleDb.OleDbType.Date ;
}

return ( result ) ;

}

Which solved the immediate problem, but I suppose there are others out there waiting to pounce.

Luc Pattyn

Thanks for the confirmation and the suggestions, I'm good now. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

PIEBALDconsult

Luc Pattyn wrote:

I'm good now

Don't sell yourself short. :-D

Luc Pattyn

Since you asked, here is my next obstacle: I have a legacy Access database I want to convert to something more modern, probably SQLServer Express. The built-in "Upgrade Wizard" produced a working database, except for the primary keys: all fields of type "Autonumber" became regular integer fields, without "Identity ON". I know I could convert each table individually, adding a column, marking it identity, then force it to accept the existing values, finally removing and renaming columns. But that is so messy and cumbersome. Do you have any suggestions? or did I miss something? TIA.

Luc Pattyn [My Articles] Nil Volentibus Arduum