Changes to DRW - URGENT HELP NEEDED

  • Thread starter Thread starter Mettá
  • Start date Start date
M

Mettá

I have what seems like a real problem

I had a drop down DRW using DISTINCT to show all records containing matching
records from a separate record set. SO one table containing various records.
Say Names, Description, County, etc It has worked fine for over two years!

The drop down did show all distinct "County" records where there was a
"Description" containing specific text.

When I modify the sql with Distinct as;

SELECT DISTINCT County FROM XTableName WHERE (Description LIKE
'%::Description::%') ORDER BY County ASC

is no longer working but if I revert to the standard query of

SELECT * FROM XTabeName WHERE (Description LIKE '%::Description::%') ORDER
BY County ASC

It works but of course I get duplicates in the drop down.

Can anyone shed any light on why this might be happening.

Thanks
M
 
Metta,

You need to do a Response.Write on the SQL statement to see what is or is not being pass to the
query statesmen.

Example:

sql = "SELECT DISTINCT County FROM XTableName WHERE (Description LIKE '%::Description::%') ORDER BY
County ASC"

Response.Write sql

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
 
Hi
Can you say more about how to do this?

The following simply produces syntax errors

<%sql = SELECT DISTINCT County FROM XTableName WHERE (Description LIKE
'%::Description::%') ORDER BY
County ASC %>

<%Response.Write sql%>

Thanks
M
 
Can you paste your complete sql statement and then I can add the Response.Write for you to test.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
 
How strange - the only thing that strikes me is description being a reserved
word, you might try bracketing it
SELECT DISTINCT County
FROM XTableName
WHERE [Description] LIKE '%::Description::%'
ORDER BY County ASC

What exactly do you mean not working? Returning duplicates or no data atall?

Jon
Microsoft MVP - FP
 
The statement is

SELECT DISTINCT pc1 FROM dbNAME WHERE (County ='::County::') ORDER BY pc1
ASC

This is one of the simpler queries, the other slightly longer one is

SELECT DISTINCT pc1 FROM dbNAME WHERE (County = '::County::' AND
Description LIKE '%::Description::%') ORDER BY pc1 ASC



Thanks for any help you can offer

M


Thomas A. Rowe said:
Can you paste your complete sql statement and then I can add the
Response.Write for you to test.
 
Hi Jon

I have almost gone mad with this problem! I have tried bracketing
[Description] without success

The drop down works without the DISTINCT, but the moment I put the distinct
bit in it comes back empty. It all used to work and now it does not, I am
at a real loss to know what might be the cause or how to resolve it, this is
happening locally and on the remote live site.

SELECT * FROM dbNAME WHERE (Description LIKE '%::Description::%') ORDER BY
County ASC

This Works but of course shows multiple matching entries

SELECT DISTINCT County FROM dbNAME WHERE ([Description] LIKE
'%::Description::%') ORDER BY County ASC

Comes back empty with or without square bracket.

Regards
M

Jon Spivey said:
How strange - the only thing that strikes me is description being a reserved
word, you might try bracketing it
SELECT DISTINCT County
FROM XTableName
WHERE [Description] LIKE '%::Description::%'
ORDER BY County ASC

What exactly do you mean not working? Returning duplicates or no data atall?

Jon
Microsoft MVP - FP


Mettá said:
I have what seems like a real problem

I had a drop down DRW using DISTINCT to show all records containing
matching records from a separate record set. SO one table containing
various records. Say Names, Description, County, etc It has worked
fine for over two years!

The drop down did show all distinct "County" records where there was a
"Description" containing specific text.

When I modify the sql with Distinct as;

SELECT DISTINCT County FROM XTableName WHERE (Description LIKE
'%::Description::%') ORDER BY County ASC

is no longer working but if I revert to the standard query of

SELECT * FROM XTabeName WHERE (Description LIKE '%::Description::%')
ORDER BY County ASC

It works but of course I get duplicates in the drop down.

Can anyone shed any light on why this might be happening.

Thanks
M
 
http://www.metta.org.uk/type.asp?description=Yoga

Is a temporary solution which works really badly but at least it does
something although the post code "pc1" is not correct in the main result.

Thanks
M



Jon Spivey said:
How strange - the only thing that strikes me is description being a reserved
word, you might try bracketing it
SELECT DISTINCT County
FROM XTableName
WHERE [Description] LIKE '%::Description::%'
ORDER BY County ASC

What exactly do you mean not working? Returning duplicates or no data atall?

Jon
Microsoft MVP - FP


Mettá said:
I have what seems like a real problem

I had a drop down DRW using DISTINCT to show all records containing
matching records from a separate record set. SO one table containing
various records. Say Names, Description, County, etc It has worked
fine for over two years!

The drop down did show all distinct "County" records where there was a
"Description" containing specific text.

When I modify the sql with Distinct as;

SELECT DISTINCT County FROM XTableName WHERE (Description LIKE
'%::Description::%') ORDER BY County ASC

is no longer working but if I revert to the standard query of

SELECT * FROM XTabeName WHERE (Description LIKE '%::Description::%')
ORDER BY County ASC

It works but of course I get duplicates in the drop down.

Can anyone shed any light on why this might be happening.

Thanks
M
 
Ok, You have blanks, which might be causing the failure.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================


Mettá said:
http://www.metta.org.uk/type.asp?description=Yoga

Is a temporary solution which works really badly but at least it does
something although the post code "pc1" is not correct in the main result.

Thanks
M



Jon Spivey said:
How strange - the only thing that strikes me is description being a reserved
word, you might try bracketing it
SELECT DISTINCT County
FROM XTableName
WHERE [Description] LIKE '%::Description::%'
ORDER BY County ASC

What exactly do you mean not working? Returning duplicates or no data atall?

Jon
Microsoft MVP - FP


Mettá said:
I have what seems like a real problem

I had a drop down DRW using DISTINCT to show all records containing
matching records from a separate record set. SO one table containing
various records. Say Names, Description, County, etc It has worked
fine for over two years!

The drop down did show all distinct "County" records where there was a
"Description" containing specific text.

When I modify the sql with Distinct as;

SELECT DISTINCT County FROM XTableName WHERE (Description LIKE
'%::Description::%') ORDER BY County ASC

is no longer working but if I revert to the standard query of

SELECT * FROM XTabeName WHERE (Description LIKE '%::Description::%')
ORDER BY County ASC

It works but of course I get duplicates in the drop down.

Can anyone shed any light on why this might be happening.

Thanks
M
 
Hi
If you mean blanks as county fields this is not the cause I have run a test
so that no blank County records exist. It makes no difference!

Oh why has this started to happen!!!

M


Thomas A. Rowe said:
Ok, You have blanks, which might be causing the failure.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================


http://www.metta.org.uk/type.asp?description=Yoga

Is a temporary solution which works really badly but at least it does
something although the post code "pc1" is not correct in the main result.

Thanks
M



Jon Spivey said:
How strange - the only thing that strikes me is description being a reserved
word, you might try bracketing it
SELECT DISTINCT County
FROM XTableName
WHERE [Description] LIKE '%::Description::%'
ORDER BY County ASC

What exactly do you mean not working? Returning duplicates or no data atall?

Jon
Microsoft MVP - FP


Mettá wrote:
I have what seems like a real problem

I had a drop down DRW using DISTINCT to show all records containing
matching records from a separate record set. SO one table containing
various records. Say Names, Description, County, etc It has worked
fine for over two years!

The drop down did show all distinct "County" records where there was a
"Description" containing specific text.

When I modify the sql with Distinct as;

SELECT DISTINCT County FROM XTableName WHERE (Description LIKE
'%::Description::%') ORDER BY County ASC

is no longer working but if I revert to the standard query of

SELECT * FROM XTabeName WHERE (Description LIKE '%::Description::%')
ORDER BY County ASC

It works but of course I get duplicates in the drop down.

Can anyone shed any light on why this might be happening.

Thanks
M
 
Back
Top