Re: Using DateTime with SQL

29 Mar 2006 8:47 AM

Herby

Help me ?????

29 Mar 2006 9:11 AM

Cor Ligthert [MVP]

Herby,

If this is about the DateTime string in an SQL script, don't do that. Use
parameters.

See the most simple sample in this on our website

http://www.vb-tips.com/default.aspx?ID=886bba68-8a2f-4b99-8f66-7139b8970071

I hope this helps,

Cor

29 Mar 2006 3:07 PM

Herby

I cannot always use DbParameters.
My application is highly dynamic sometimes i will be dealing with array
of source values and set membership SQL commands
E.g

SELECT * FROM Members WHERE MemberNo IN ( @membNo )

The variable membNo will have a different number of items when
executing the above, so its better to use a literal expression here

IN( 1,23,56,67,89, 9 )

Another time it could be

IN( 9 )

I need to support both Oracle and SQL servers, hopefully with common
code.

I have got over my initial problem by using a format specifier:
myTextDateInstance.ToString( "yyyy-MM-dd hh:mm:ss");

This gets the date part in the necessary format.
I can then add the {ts ' '} part via StringBuilder etc.

My problem now is the System.Data.OracleClient is throwing an exception
ORA-00911 Invalid character.
And its to do with the date part

WHERE effective_date <= {ts '1990-01-01 12:00:00'}

But this works fine when i run against the target database
independently.

So the Oracle driver does now not like the above format, but thats what
the Oracle DBMS is expecting it.
As that is the type of the column effective_date DATE.

Can anyone help me?