|
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.
|
|
|
|
|
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[ ^]
|
|
|
|
|
Dalek Dave wrote: I like that!
This ain't bloody Facebook (yet)!
|
|
|
|
|
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.
|
|
|
|
|
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";
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?
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Do I smell a Tip/trick or article in the making?
Good to see you again.
Cheers,
Peter
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
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 wrote: those stupid OleDbTypes/SqlTypes enums the parameters need.
The what?
|
|
|
|
|
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).
|
|
|
|
|
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.)
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
Thanks for the confirmation and the suggestions, I'm good now.
|
|
|
|
|
Luc Pattyn wrote: I'm good now
Don't sell yourself short.
|
|
|
|
|
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.
|
|
|
|
|
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.
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(System.String) ] = " [{0}] TEXT({1}) " ;
typemap [ typeof(System.Boolean) ] = " [{0}] YESNO " ;
typemap [ typeof(System.Int32) ] = " [{0}] INT " ;
typemap [ typeof(System.DateTime) ] = " [{0}] DATETIME " ;
typemap [ typeof(System.Guid) ] = " [{0}] UNIQUEIDENTIFIER " ;
I hope it helps, or at least makes you laugh. I picture Hercule ROTFLMAOing.
modified on Thursday, August 4, 2011 12:52 AM
|
|
|
|
|
|
Hello,
I am trying to setup a RegEx expresion to check a text box for numeric values but also have the possibility to have a < or > symbol. For example the input could be 25.1 or > 20 or < 50, etc. How can I allow for the < or > symbol?
Thanks
|
|
|
|
|
There is a Regular Expressions forum.
How about something as simple as: ^(\s|\d|\.|<|>)*$ (not tested).
|
|
|
|
|
If you don't want to allow any spaces, try ^[<>]?\d+\.?\d*$ That scans as
^ beginning of string
[<>]? < or >, zero or one times
\d+ one or more digits
\.? zero or one decimal points
\d* zero or more digits
$ end of string
Ran a few cases past Expresso and they look OK. If you want to allow scientific notation, etc, grab the examples from Expresso (see our Free Tools forum for a link) and splice in the [<>]? bit. This one won't allow .3 or >.6 - it requires a digit before a decimal point, but a few minutes with Expresso will get you anywhere.
Cheers,
Peter
ps As the previous answer indicated, the Regular Expressions forum would be more appropriate next time.
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
Manfred R. Bihy: "Looks as if OP is learning resistant."
|
|
|
|
|
Thanks everyone for the suggestions. They have helped get started but the criteria has changed. I now need to support the following conditions:
1. a value of N/A only in the field => N/A
2. a numeric value with decimal points => 24.231
3. a numeric value that can begin with < character => < 12.1, or < .5
4. a numeric value that can begin with > character => > 27.2, or > .5
|
|
|
|
|