Queries from Access

[=?Utf-8?B?SmFtZXM=?=]

Hello all

I am having a odd problem, I have a page that lists users (GridView), this
list links through to another page when you click on the name. The second
page lists all phone calls they received during the previous month
(GridView). I have done this by a Querystring passing SupportId from the
first to the second page.

The second page GridView data source is based on an SQL statement:
"WHERE (supportid = ?) AND
((Right(Left([datetaken],8),5))=Right(D ateAdd("m",-1,Date()),5))"

the [datetaken] field is the date and time the call was received.

This works fine on my computer running it under a local host. However, when
I copy it to my IIS server V6, it does not show any records on the second
page.

Everything else works fine, the list on the first page works ok, even the
second page loads fine but it says No Calls.

Does anyone have a clue why? I have tried almost everything I can think off
but nothing makes a difference.
Many thanks in advance

James

[=?Utf-8?B?SmFtZXM=?=]

Hi

I set up a linked connection to it and it worked fine. the lock file
appeared as well.

If I open up the database the lock file appears and the query shows all the
records it should.

"Mark Rae" wrote:

"James" <Ja***@discussi ons.microsoft.c omwrote in message
news:07******** *************** ***********@mic rosoft.com...

Yes they are both accessing the same database, when I renamed the database
they both had errors saying they could not find it.

I also just deleted one of the names from the database and it dissapeard
from the first page where it lists the names in the table so it is.

I can't think why this is happening

Me neither...

Are you able to query this database through any other means...? E.g. a full
copy of Access, or something like Excel which can query Jet databases (Get
External Data...)

If so, what happens if you run the query manually...?

[Mark Rae]

"James" <Ja***@discussi ons.microsoft.c omwrote in message
news:0A******** *************** ***********@mic rosoft.com...

I set up a linked connection to it and it worked fine. the lock file
appeared as well.

If I open up the database the lock file appears and the query shows all
the
records it should.

In which case, you need to apply the Sherlock Holmes logic which says that,
once you have eliminated the impossible, whatever is left (however
improbable) must be the answer...

If you have a web page which should be querying a Jet database but, when it
runs, the corresponding locking database doesn't appear and the Last
Accessed date of the Jet database is not updated, then at that precise
moment it isn't querying the database - it just can't be, however improbable
you find this to be...

If you rename the database immediately prior to accessing the page in
question, what happens...?

[=?Utf-8?B?SmFtZXM=?=]

Hi

Well nothing happens, but if i click refresh it has an errror message. I
have custom errors off but I am guessing it is that it cannot find the
database.

The first page that works ok also has the error message so they must be both
accessing the same database.

"Mark Rae" wrote:

"James" <Ja***@discussi ons.microsoft.c omwrote in message
news:0A******** *************** ***********@mic rosoft.com...

I set up a linked connection to it and it worked fine. the lock file
appeared as well.

If I open up the database the lock file appears and the query shows all
the
records it should.

In which case, you need to apply the Sherlock Holmes logic which says that,
once you have eliminated the impossible, whatever is left (however
improbable) must be the answer...

If you have a web page which should be querying a Jet database but, when it
runs, the corresponding locking database doesn't appear and the Last
Accessed date of the Jet database is not updated, then at that precise
moment it isn't querying the database - it just can't be, however improbable
you find this to be...

If you rename the database immediately prior to accessing the page in
question, what happens...?

[Mark Rae]

"James" <Ja***@discussi ons.microsoft.c omwrote in message
news:CB******** *************** ***********@mic rosoft.com...

Well nothing happens,

There's your problem, then!

but if i click refresh it has an errror message. I have custom errors off
but I am
guessing it is that it cannot find the database.

Probably, but only you can tell... :-)

The first page that works ok also has the error message so they must be
both
accessing the same database.

No - they may both be configured to access the same database, but something
in your code is bypassing the database query, otherwise you'd get an
error...

[Patrice]

I would suggest to use a dummy criteria and to display
((Right(Left([datetaken],8),5)) and Right(DateAdd(" m",-1,Date()),5)) to see
if this the value you expect.

In particular keep in mind that a date to string conversion is dependent on
the national language you are using. Either make sure you use the language
you need. My personal preference would be to compare date values
(datetaken>=Dat eAdd("m",-1,Date() or something similar))

---
Patrice

"James" <Ja***@discussi ons.microsoft.c oma écrit dans le message de news:
20************* *************** **...icrosof t.com...

Hello all

I am having a odd problem, I have a page that lists users (GridView), this
list links through to another page when you click on the name. The second
page lists all phone calls they received during the previous month
(GridView). I have done this by a Querystring passing SupportId from the
first to the second page.

The second page GridView data source is based on an SQL statement:
"WHERE (supportid = ?) AND
((Right(Left([datetaken],8),5))=Right(D ateAdd("m",-1,Date()),5))"

the [datetaken] field is the date and time the call was received.

This works fine on my computer running it under a local host. However,
when
I copy it to my IIS server V6, it does not show any records on the second
page.

Everything else works fine, the list on the first page works ok, even the
second page loads fine but it says No Calls.

Does anyone have a clue why? I have tried almost everything I can think
off
but nothing makes a difference.
Many thanks in advance

James

[Mark Rae]

"Patrice" <http://www.chez.com/scribe/wrote in message
news:%2******** ********@TK2MSF TNGP05.phx.gbl. ..

>I would suggest to use a dummy criteria and to display
((Right(Left ([datetaken],8),5)) and Right(DateAdd(" m",-1,Date()),5)) to see
if this the value you expect.

We've already established that the database is not even being queried at
this point, so changing the criterion won't help at all...

[=?Utf-8?B?SmFtZXM=?=]

Hi

Ok tried editing the datasource, this is the full SQL statement for the
datagrid:

SELECT supportmember.s upportid, supportmember.d atetaken, icb.number,
icb.firstname, icb.lastname, Right(Left([datetaken],8),5) AS Expr1
FROM supportmember INNER JOIN icb ON supportmember.m emid = icb.memid
WHERE ((supportid = ?) AND (supportmember. datetaken Like "*/07*"))
ORDER BY supportmember.d atetaken DESC

Now if I remove
AND (supportmember. datetaken Like "*/07*")
It works fine by bringing up the list with the matching supportid.

So it must be that addition about the datetaken that is causing it to find
nothing. I have tried things like *07* or whatever just to try and get it to
find something in that field. I know the data is there as I can open the
query in access using the same SQL code (but putting in a supportid manualy)
and it shows a list.

It is accessing the correct database as I amended the data and put some test
figures in and they appeared properly on the first page, and the second one
without the Datetaken bit in the WHERE clause.

Am I doing the AND bit wrong? I have run out of ideas :)

Thanks for your help so far though its been great.

James
"Mark Rae" wrote:

"Patrice" <http://www.chez.com/scribe/wrote in message
news:%2******** ********@TK2MSF TNGP05.phx.gbl. ..

I would suggest to use a dummy criteria and to display
((Right(Left([datetaken],8),5)) and Right(DateAdd(" m",-1,Date()),5)) to see
if this the value you expect.

We've already established that the database is not even being queried at
this point, so changing the criterion won't help at all...

[=?Utf-8?B?SmFtZXM=?=]

Hello again,

Ok it must be something wrong with my AND statement I have just tried
amending it to:

SELECT supportmember.s upportid, supportmember.d atetaken, icb.number,
icb.firstname, icb.lastname, Right(Left([datetaken],8),5) AS Expr1
FROM supportmember INNER JOIN icb ON supportmember.m emid = icb.memid
WHERE ((supportid = ?) AND (icb.lastname Like "*"))
ORDER BY supportmember.d atetaken DESC

So it should show all records with the matching supportid (usualy about 40
records) as the Lastname will always match that requirement. But it still
shows no records.

"James" wrote:

Hi

Ok tried editing the datasource, this is the full SQL statement for the
datagrid:

SELECT supportmember.s upportid, supportmember.d atetaken, icb.number,
icb.firstname, icb.lastname, Right(Left([datetaken],8),5) AS Expr1
FROM supportmember INNER JOIN icb ON supportmember.m emid = icb.memid
WHERE ((supportid = ?) AND (supportmember. datetaken Like "*/07*"))
ORDER BY supportmember.d atetaken DESC

Now if I remove
AND (supportmember. datetaken Like "*/07*")
It works fine by bringing up the list with the matching supportid.

So it must be that addition about the datetaken that is causing it to find
nothing. I have tried things like *07* or whatever just to try and get it to
find something in that field. I know the data is there as I can open the
query in access using the same SQL code (but putting in a supportid manualy)
and it shows a list.

It is accessing the correct database as I amended the data and put some test
figures in and they appeared properly on the first page, and the second one
without the Datetaken bit in the WHERE clause.

Am I doing the AND bit wrong? I have run out of ideas :)

Thanks for your help so far though its been great.

James
"Mark Rae" wrote:

"Patrice" <http://www.chez.com/scribe/wrote in message
news:%2******** ********@TK2MSF TNGP05.phx.gbl. ..

>I would suggest to use a dummy criteria and to display
>((Right(Left ([datetaken],8),5)) and Right(DateAdd(" m",-1,Date()),5)) to see
>if this the value you expect.

We've already established that the database is not even being queried at
this point, so changing the criterion won't help at all...

[=?Utf-8?B?SmFtZXM=?=]

Hello

I've Done it!!!!! The problem was that UK and USA dates are different, so I
had to add code to format both dates into UK before comparing them. My
computer it worked fine as its UK same as data, but ASP.Net is obviously USA
so when on the server it was looking for a day instead of a month.

Final code is:

SELECT supportmember.s upportid, supportmember.d atetaken, icb.[number],
icb.firstname, icb.lastname, RIGHT (LEFT (supportmember. datetaken, 8), 5) AS
Expr1 FROM (supportmember INNER JOIN icb ON supportmember.m emid = icb.memid)
WHERE (supportmember. supportid = ?) AND
((Right(Left(Fo rmat([datetaken],"dd/mm/yy"),8),5))=Rig ht(Format(DateA dd("m",-1,Date()),"dd/mm/yy"),5)) ORDER BY supportmember.d atetaken DESC

Wow this one confused me for days.

Many thanks for your help, keep up the good work.

Cheers
James

"James" wrote:

Hello all

I am having a odd problem, I have a page that lists users (GridView), this
list links through to another page when you click on the name. The second
page lists all phone calls they received during the previous month
(GridView). I have done this by a Querystring passing SupportId from the
first to the second page.

The second page GridView data source is based on an SQL statement:
"WHERE (supportid = ?) AND
((Right(Left([datetaken],8),5))=Right(D ateAdd("m",-1,Date()),5))"

the [datetaken] field is the date and time the call was received.

This works fine on my computer running it under a local host. However, when
I copy it to my IIS server V6, it does not show any records on the second
page.

Everything else works fine, the list on the first page works ok, even the
second page loads fine but it says No Calls.

Does anyone have a clue why? I have tried almost everything I can think off
but nothing makes a difference.
Many thanks in advance

James

[Mark Rae]

"James" <Ja***@discussi ons.microsoft.c omwrote in message
news:50******** *************** ***********@mic rosoft.com...

I've Done it!!!!! The problem was that UK and USA dates are different, so
I
had to add code to format both dates into UK before comparing them. My
computer it worked fine as its UK same as data, but ASP.Net is obviously
USA
so when on the server it was looking for a day instead of a month.

Wow this one confused me for days.

Many thanks for your help, keep up the good work.

Hurrah! Well done for finally figuring it out... :-)