Queries from Access

[Guest]

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(DateAdd("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]

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

It might be helpful if you

a) posted your code

b) told the group what you've already tried

c) posted any error message / EventLog entry you're getting.

Presumably, the account that your site is running under has read-write
permissions not only on the Jet database itself but also on the folder where
the Jet database resides so that it can create the associated locking
database (*.ldw)...?

 

[Guest]

Hello,

I did post my code, "WHERE (supportid = ?) AND
((Right(Left([datetaken],8),5))=Right(DateAdd("m",-1,Date()),5))"

That is the SQL part of the select statement. The rest is just

SELECT supportid, datetaken, [number], firstname, lastname FROM memberlist

I also said that I do not get any error messages, it just says that there
are 0 records.

As the first page works fine, (that is just based on a simple SELECT from a
table) I am assuming there is no problem with the connection or Read Write to
the database. I have checked this anyway.

I have set the folder to full read write permissions for everyone just to
test it and it still does not work, including the internet guest account.
However I do notice that the lock file is not appearing.

Other than that I am stumped as to what to look for.

Any ideas would be great.

James

 

[Mark Rae]

I did post my code, "WHERE (supportid = ?) AND
((Right(Left([datetaken],8),5))=Right(DateAdd("m",-1,Date()),5))"

That is the SQL part of the select statement. The rest is just

SELECT supportid, datetaken, [number], firstname, lastname FROM memberlist

Well, that's your SQL statement certainly...

I also said that I do not get any error messages

Actually, you didn't, but no matter...

As the first page works fine, (that is just based on a simple SELECT from
a
table) I am assuming there is no problem with the connection or Read Write
to
the database. I have checked this anyway.
OK.

However I do notice that the lock file is not appearing.

Ah... Well, it's been a while since I did any Jet database work but I've
never known a situation when the corresponding ldb file isn't created,
especially on a network share... If you interrogate the file properties of
the Jet database itself, is its Accessed date being updated every time it is
queried...?

 

[Guest]

Hi

No the last accessed date does not change. So I thought maybe my link was
wrong so I changed the name of the Db and it came up with an error message it
could not find it so I know its looking at the right one.

Just why does it work for one page but not the other?

I did post my code, "WHERE (supportid = ?) AND
((Right(Left([datetaken],8),5))=Right(DateAdd("m",-1,Date()),5))"

That is the SQL part of the select statement. The rest is just

SELECT supportid, datetaken, [number], firstname, lastname FROM memberlist

Well, that's your SQL statement certainly...

I also said that I do not get any error messages

Actually, you didn't, but no matter...

As the first page works fine, (that is just based on a simple SELECT from
a
table) I am assuming there is no problem with the connection or Read Write
to
the database. I have checked this anyway.
OK.

However I do notice that the lock file is not appearing.

Ah... Well, it's been a while since I did any Jet database work but I've
never known a situation when the corresponding ldb file isn't created,
especially on a network share... If you interrogate the file properties of
the Jet database itself, is its Accessed date being updated every time it is
queried...?

 

[Mark Rae]

No the last accessed date does not change. So I thought maybe my link was
wrong so I changed the name of the Db and it came up with an error message
it
could not find it so I know its looking at the right one.

Every time a Jet database is queried, its Last Accessed date will definitely
change - I can't think of any situation where this wouldn't be the case...

Just why does it work for one page but not the other?

Could it be that the two pages are not in fact pointing at the same
database...? Different connection string, maybe...?

 

[Guest]

Hi

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

 

[Guest]

Hi

Could it be something to do with the IIS server, do I have to install
anything before it can understand my where statement?

 

[Mark Rae]

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]

Could it be something to do with the IIS server, do I have to install
anything before it can understand my where statement?

???

 

[Guest]

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]

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...?

 

[Guest]

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]

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>=DateAdd("m",-1,Date() or something similar))

 

[Mark Rae]

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...

 

[Guest]

Hi

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

SELECT supportmember.supportid, supportmember.datetaken, icb.number,
icb.firstname, icb.lastname, Right(Left([datetaken],8),5) AS Expr1
FROM supportmember INNER JOIN icb ON supportmember.memid = icb.memid
WHERE ((supportid = ?) AND (supportmember.datetaken Like "*/07*"))
ORDER BY supportmember.datetaken 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

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...

 

[Guest]

Hello again,

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

SELECT supportmember.supportid, supportmember.datetaken, icb.number,
icb.firstname, icb.lastname, Right(Left([datetaken],8),5) AS Expr1
FROM supportmember INNER JOIN icb ON supportmember.memid = icb.memid
WHERE ((supportid = ?) AND (icb.lastname Like "*"))
ORDER BY supportmember.datetaken 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.

Hi

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

SELECT supportmember.supportid, supportmember.datetaken, icb.number,
icb.firstname, icb.lastname, Right(Left([datetaken],8),5) AS Expr1
FROM supportmember INNER JOIN icb ON supportmember.memid = icb.memid
WHERE ((supportid = ?) AND (supportmember.datetaken Like "*/07*"))
ORDER BY supportmember.datetaken 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

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...

 

[Guest]

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.supportid, supportmember.datetaken, icb.[number],
icb.firstname, icb.lastname, RIGHT (LEFT (supportmember.datetaken, 8), 5) AS
Expr1 FROM (supportmember INNER JOIN icb ON supportmember.memid = icb.memid)
WHERE (supportmember.supportid = ?) AND
((Right(Left(Format([datetaken],"dd/mm/yy"),8),5))=Right(Format(DateAdd("m",-1,Date()),"dd/mm/yy"),5)) ORDER BY supportmember.datetaken DESC

Wow this one confused me for days.

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

Cheers
James

 

[Mark Rae]

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...