How to work with generic date format

[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]

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]

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

[PIEBALDconsult]

I dunno. I avoid wizards; I prefer to maintain control. On the other hand, the closest I've come to that was writing an app to create a clone of an Ingres database in SQL Server -- which had no keys and hardly any indices, so I didn't bother copying them. Of course, I also avoid "Autonumber" fields, so you'll get little sympathy from me. :-D Here's what I currently have for doing that sort of thing:

protected virtual System.Collections.Generic.Dictionary<System.Type,System.String> TypeMap { get ; set ; }

...

public virtual int
CreateTable
(
string TableName
,
System.Data.IDataReader SchemaSource
)
{
return ( this.CreateTable ( TableName , SchemaSource.GetSchemaTable() ) ) ;
}

public virtual int
CreateTable
(
string TableName
,
System.Data.DataTable SchemaSource
)
{
if ( this.TypeMap == null )
{
throw ( new System.InvalidProgramException ( "You can't translate without a TypeMap" ) ) ;
}

System.Text.StringBuilder cmd = new System.Text.StringBuilder() ;

cmd.AppendFormat ( "CREATE TABLE \[{0}\] ( " , TableName ) ;

foreach
(
    System.Data.DataRow col
in
    SchemaSource.Rows
)
{
    if ( !this.TypeMap.ContainsKey ( (System.Type) col \[ "DataType" \] ) )
    {
        throw ( new System.InvalidProgramException
            ( "The TypeMap doesn't contain type " + col \[ "DataType" \].ToString() ) ) ;
    }

    cmd.AppendFormat
    (
        this.TypeMap \[ (System.Type) col \[ "DataType" \] \]
    ,
        col \[ "ColumnName" \]
    ,
        col \[ "ColumnSize" \]
    ,
        col \[ "NumericPrecision" \]
    ,
        col \[ "NumericScale" \]
    ,
        (bool) col \[ "AllowDBNull" \]?" NULL ":" NOT NULL "
    ) ;

    cmd.Append ( " , " ) ;
}

cmd.Replace ( "," , ")" , cmd.Length - 2  , 1 ) ;

return ( this.ExecuteNonQuery ( cmd.ToString() ) ) ;

}

I know I've used this method, but only to clone tables from SQL Server to Access, so I'm unsure if it will work the other way. Here's the TypeMap I have for Access:

        typemap = new System.Collections.Generic.Dictionary<System.Type,System.String>() ;
        
        typemap \[ typeof(Syst

[Richard Deeming]

The DateTime error is not a new problem, and is apparently "by design": https://connect.microsoft.com/VisualStudio/feedback/details/94377/oledbparameter-with-dbtype-datetime-throws-data-type-mismatch-in-criteria-expression[^]

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer