Global Date Filter for DataAdapter?

[Toby]

I've a DataAdapter with:

SELECT ID, Employee, [Date], Period_End_Date , Job, Description, Exported,
Units, Cost_Code, Category, Class, Chargeout_Level , PayID, Rate
FROM tblTimeEntry
WHERE (Employee LIKE ?) AND (Period_End_Dat e = ?)
ORDER BY [Date]

I then get my Data by the following:

' Select Data Based upon Current Week Ending and Employee ID
daUserData.Sele ctCommand.Param eters.Add("@Emp loyee", OleDbType.Char. Char, 10)
daUserData.Sele ctCommand.Param eters.Add("@MyD ateField ", OleDbType.Date)
daUserData.Sele ctCommand.Param eters(0).Value = strUserID
daUserData.Sele ctCommand.Param eters(1).Value = WeekEndDate
daUserData.Fill (DsUserData.tbl TimeEntry)

with the 'date' variable WeekEndDate I choose a 'specific' date.

What I'd like to know, is what do I use as a 'global' search for ALL dates
using the above?

"%" and "*" of course do not work as I have to search a 'DATE'.

Anyone know?

Thanks

Toby

[Val Mazur]

Hi Toby,

You cannot use any wildcards for the dates. What you could do here is to use
SQL statement without date condition or use doubled condition for the date
and pass range of the dates, which basically covers everything. Something
like

SELECT ID, Employee, [Date], Period_End_Date , Job, Description,
Exported,
Units, Cost_Code, Category, Class, Chargeout_Level , PayID, Rate
FROM tblTimeEntry
WHERE (Employee LIKE ?) AND Period_End_Date >= ? AND Period_End_Date <=
?
ORDER BY [Date]

daUserData.Sele ctCommand.Param eters.Add("@Emp loyee", OleDbType.Char. Char,
10)
daUserData.Sele ctCommand.Param eters.Add("@MyD ateField1", OleDbType.Date)
daUserData.Sele ctCommand.Param eters.Add("@MyD ateField2", OleDbType.Date)
daUserData.Sele ctCommand.Param eters(0).Value = strUserID
daUserData.Sele ctCommand.Param eters(1).Value = WeekStartDate
daUserData.Sele ctCommand.Param eters(2).Value = WeekEndDate
daUserData.Fill (DsUserData.tbl TimeEntry)

In case wehn you need to check for one date, you could pass it as a value
for both parameters

--
Val Mazur
Microsoft MVP
Check Virus Alert, stay updated
http://www.microsoft.com/security/incident/blast.asp
"Toby" <pl****@PostTo. NGs> wrote in message
news:tr******** *************** *********@4ax.c om...

I've a DataAdapter with:

SELECT ID, Employee, [Date], Period_End_Date , Job, Description, Exported, Units, Cost_Code, Category, Class, Chargeout_Level , PayID, Rate
FROM tblTimeEntry
WHERE (Employee LIKE ?) AND (Period_End_Dat e = ?)
ORDER BY [Date]

I then get my Data by the following:

' Select Data Based upon Current Week Ending and Employee ID
daUserData.Sele ctCommand.Param eters.Add("@Emp loyee", OleDbType.Char. Char, 10) daUserData.Sele ctCommand.Param eters.Add("@MyD ateField ", OleDbType.Date) daUserData.Sele ctCommand.Param eters(0).Value = strUserID
daUserData.Sele ctCommand.Param eters(1).Value = WeekEndDate
daUserData.Fill (DsUserData.tbl TimeEntry)

with the 'date' variable WeekEndDate I choose a 'specific' date.

What I'd like to know, is what do I use as a 'global' search for ALL dates
using the above?

"%" and "*" of course do not work as I have to search a 'DATE'.

Anyone know?

Thanks

Toby

[Toby]

On Sat, 8 Nov 2003 23:12:44 -0500, in microsoft.publi c.data.ado you wrote:

You cannot use any wildcards for the dates. What you could do here is to use
SQL statement without date condition or use doubled condition for the date
daUserData.Sele ctCommand.Param eters(1).Value = WeekStartDate
daUserData.Sele ctCommand.Param eters(2).Value = WeekEndDate

Okay... I recall seeing this start-end date once. But didn't realize that it
was my only option.

Thanks!

I'll take it from here on!

Toby

[Roy Fine]

"Val Mazur" <gr******@hotma il.com> wrote in message
news:Oy******** *****@TK2MSFTNG P10.phx.gbl...

Hi Toby,

You cannot use any wildcards for the dates. What you could do here is to use SQL statement without date condition or use doubled condition for the date
and pass range of the dates, which basically covers everything. Something
like

SELECT ID, Employee, [Date], Period_End_Date , Job, Description,
Exported,
Units, Cost_Code, Category, Class, Chargeout_Level , PayID, Rate
FROM tblTimeEntry
WHERE (Employee LIKE ?) AND Period_End_Date >= ? AND Period_End_Date <= ?
ORDER BY [Date]

If your SQL dialect supports it, consider changing the two tests for the
date criteria to a single test using the BETWEEN clause. Something like
this :

AND Period_End_Date BETWEEN ? AND ?

regards
roy fine

[Toby]

With Deft Fingers, "Roy Fine" <rl****@twt.obf uscate.net> wrote:

If your SQL dialect supports it, consider changing the two tests for the
date criteria to a single test using the BETWEEN clause. Something like
I'm using OLE and an Access DB file.
AND Period_End_Date BETWEEN ? AND ?

I'll see if this works too.

Thanks!

Toby

[Val Mazur]

Hi,

Yes, it supports BETWEEN

--
Val Mazur
Microsoft MVP
Check Virus Alert, stay updated
http://www.microsoft.com/security/incident/blast.asp
"Toby" <pl****@PostTo. NGs> wrote in message
news:1r******** *************** *********@4ax.c om...

With Deft Fingers, "Roy Fine" <rl****@twt.obf uscate.net> wrote:

If your SQL dialect supports it, consider changing the two tests for the
date criteria to a single test using the BETWEEN clause. Something like

I'm using OLE and an Access DB file.

AND Period_End_Date BETWEEN ? AND ?

I'll see if this works too.

Thanks!

Toby

[Toby]

>Yes, it supports BETWEEN

What I found interested (not sure if this is correct)... is that now with the
WeekStart and WeekEnd search... my loading of the application has increased (5
seconds or so out of 10 normally).

Would this be 'typical'?

Toby

[Val Mazur]

Hi Toby,

If you do not have indexes created for those fields, then yes, you could
expect it. You should build indexes on a fields, involved into search by
WHERE clause

--
Val Mazur
Microsoft MVP
Check Virus Alert, stay updated
http://www.microsoft.com/security/incident/blast.asp
"Toby" <pl****@PostTo. NGs> wrote in message
news:v1******** *************** *********@4ax.c om...

Yes, it supports BETWEEN
What I found interested (not sure if this is correct)... is that now with

the WeekStart and WeekEnd search... my loading of the application has increased (5 seconds or so out of 10 normally).

Would this be 'typical'?

Toby

[Toby]

>If you do not have indexes created for those fields, then yes, you could

expect it. You should build indexes on a fields, involved into search by
WHERE clause

Indexes? Hmmm... not sure by what you mean.

Sorry... I've only been using DBs for about 1-1/2 months now. And while I've
learnt a lot... I'm still out of my depth here sometimes :(

Toby

[Val Mazur]

Hi,

Index allows you to boost selection from the table. When you create index,
for the specific field(s), then database maintains it to provide faster
search. I would suggest you to read about databases structure and how to
create indexes. This is not a five minutes topic. If you do not create
appropriate indexes, then you should expect slow selection from the database

--
Val Mazur
Microsoft MVP
Check Virus Alert, stay updated
http://www.microsoft.com/security/incident/blast.asp
"Toby" <pl****@PostTo. NGs> wrote in message
news:pq******** *************** *********@4ax.c om...

If you do not have indexes created for those fields, then yes, you could
expect it. You should build indexes on a fields, involved into search by
WHERE clause
Indexes? Hmmm... not sure by what you mean.

Sorry... I've only been using DBs for about 1-1/2 months now. And while

I've learnt a lot... I'm still out of my depth here sometimes :(

Toby