Re: Select statement for boolean and/or DateTime values?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance



Olaf Krause wrote:
Hi together,

What I'm currently using is something like

IDbCommand
cmd=AdoProviderFactory.GetCommand(command,m_DB.Connection,m_DB.ProviderType);
IDataReader reader = cmd.ExecuteReader();

There was no way for you to know it (except maybe by browsing through some
of the previous questions in this newsgroup before posting yours - always a
recommended practice) , but this is a classic ADO newsgroup. ADO.Net bears
very little resemblance to classic ADO so, while you may be lucky enough to
find a dotnet-knowledgeable person here who can answer your question, you
can eliminate the luck factor by posting your question to a group where
those dotnet-knowledgeable people hang out. I suggest
microsoft.public.dotnet.framework.adonet.

However, some of your questions have the same answers in both ADO and
ADO.Net, so read on.

here cmd is an Ole, ODBC (e.g. MSAccess), SqLite or SqlCommand
depending on the Provider, command is a string like

SELECT * FROM table WHERE booleancolumn= 'false'AND
datetimecolumn='20.10.2021 10:43:25'

The above works for an SQL Server, means reader contains the row I'm
looking for

It does?? SQL Server does not have a boolean datatype, and even if it did,
you cannot compare a boolean with a string?? Are you sure "booleancolumn"
isn't actually a varchar column? The closest SQL Server has to a boolean
datatype is the bit datatype, which holds 0,1 or Null. If you utilize
parameters, ADO.Net will translate a boolean value into either 0 or 1.
You should also use a standard datetime format, such as ISO: yyyymmdd
hh:nn:ss will always work, at least in SQL Server. But again, utilizing
parameters will relieve you of the necessity of worrying about date formats.

. But for the rest the select statement seems not to work
as expected.

Access (Jet) DOES have a boolean datatype, but, it requires a nonstandard
date format: either US (mm/dd/yyyy) or tyyyy-mm-dd.

Again, if you utilize parameters, values will be passed correctly to the Jet
engine

I thought it should be simple but now I'm looking for a common
solution since several days reading dozends of google hits etc...

So my questions are:

* As there a way to create a common sql statement that works for all
above database providers? If not how should the statement lookm like
for SQLite and/or MSAccess? Sorry might be a newbie here ;)
* Or maybe there in another alternative to query for the row I'm
looking for?

No, there will be differences in SQL dialectst depending on the database.
However, utilizing parameters will help.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.



Relevant Pages

  • Re: 0 or 1 vs True or False
    ... > In SQL Server, the bit data type should not be considered to be boolean. ... the boolean datatype can be True oe False. ... >> errors because the values extracted from a datarow return True or False. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Yes/No insted of True/False
    ... > Not in SQL Server because there is no Boolean datatype in SQL Server. ... Maybe so, but ADO, which is used by most client applications, does convert ...
    (microsoft.public.sqlserver.programming)
  • Re: Cursors... whats the alternative?
    ... Use meaningful codes that will make sense for the data you are representing ... There is no Boolean datatype in SQL Server and there can sometimes be ...
    (microsoft.public.sqlserver.programming)
  • Re: Error "SQL Server does not allow remote connections"
    ... The application could not connect to the sql server db. ... network is blocking the connection. ... SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 ... integratedSecurity, SqlConnection owningObject) +737554 ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Sql 2005: SSIS - How to convert "N/Y" CHAR(1) to byte type column
    ... below) to represent boolean types. ... i.e. an expression that takes a charconstruction_weld column value from the flat file and converts it to a bit type that is compatible with the construction_weld column in the wl_well_casing_liner table. ... > Before we go further on the issue, I'd like to know which data type you> want to use in SQL server, do you use any tinyint? ... I'm totally unfamiliar the grammar of SSIS expressions> and I ...
    (microsoft.public.sqlserver.dts)