.ASP page querying Access database - COUNT field error 1

[cdck]

I have an .asp page on our local intranet which is meant to query an Access database to pull the information to display. The database is located in a folder in the same directory as the .asp page.

Below is the code from the .asp page:

<% OPTION EXPLICIT %>
<%
dim strConnect, pageTitle, pageType, listSQL, vNum, vName, vType, vSurveyed, vSurvRet, Con, rstemp

listSQL="SELECT A.[Vendor #], A.[Vendor Name], A.[Vendor Type], A.[Survey Faxed], A.[Survey Returned] FROM [Vendor Addresses], A

WHERE A.[Approved Vendor] = 'yes' AND A.[Active?] = 'yes' ORDER BY A.[Vendor Name]"

%>

and later:

<%

Set Con=Server.CreateObject("ADODB.Connection")
strConnect="DSN=Vendor"
Con.Open strConnect

set rstemp= Con.Execute(listSQL)

DO UNTIL rstemp1.eof

vNum = rstemp(0)
vName = rstemp(1)
vType = rstemp(2)
vSurveyed = rstemp(3)
vSurvRet = rstemp(4)


Response.write""

rstemp.movenext
LOOP

rstemp.Close
set rstemp = Nothing
Con.Close
set Con = Nothing

%>
(NOTE: nothing is included in Response.write yet, because I'm just trying to see if I can get it to connect and pull the data without error.)

When I view the page, this is the error that it throws:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect

/avl.asp, line 37


Line 37 is the line that says "set rstemp= Con.Execute(listSQL)"

There is no COUNT field or operation included in listSQL.

Can anyone help me figure out what this error is and how to fix it?

Cheryl dc Kern

 

[Tarwn]

A couple confusions that you might be able to clear up/
1) Have you tried running this SQL statement in Access to make sure it runs?
1a) Specifically this portion: FROM [Vendor Addresses], A


I'll have more after Isee the results of that,
-T

 

[cdck]

Tarwn:

I tried running the query in Access, and it couldn't deal with the ', A', so I replaced all the A's with the full table name, so now the query looks like this:

listSQL="SELECT [Vendor Addresses].[Vendor #], [Vendor Addresses].[Vendor Name], [Vendor Addresses].[Vendor Type], [Vendor Addresses].[Survey Faxed], [Vendor Addresses].[Survey Returned] FROM [Vendor Addresses] WHERE [Vendor Addresses].[Approved Vendor] = 'yes' AND [Vendor Addresses].[Active?] = 'yes' ORDER BY [Vendor Addresses].[Vendor Name]"

But it still throws the same error.

Cheryl dc Kern

 

[DotNetGnat]

Try this in access and let us know if you encounter any errors:

SELECT A.[Vendor Name], A.[Vendor Type], A.[Survey Faxed], A.[Survey Returned] FROM [Vendor Addresses] A WHERE A.[Approved Vendor] = 'yes' ORDER BY A.[Vendor Name]

I am suspecting that it has to do with the field names that i removed from the query

-DNG

 

[steven290]

how about you getting rid of the spaces in the field names the just call it like this

SELECT Vendor_Name, Vendor_Type, Survey_Faxed, Survey_Returned FROM Vendor_Addresses WHERE Approved_Vendor = 'yes' ORDER BY Vendor_Name

i know you can put brackets why make more complicated

 

[cdck]

Thank you, that removal did help! The query works fine now, I guess I'm going to have to go back and rename the fields in the tables - unless there's a way that I can set up a query in Access that my SQL can then pull from. Is there a way to do this? (I mean, use SQL to pull from an Access query, rather than a table.)

Cheryl dc Kern

 

[cdck]

Oh, the tables are already used for other purposes and referred to by various forms/reports within the database itself, so I need to avoid re-naming as much as possible. But thanks for the suggestion, steven290.

Cheryl dc Kern

 

[DotNetGnat]

the symbol # is considered as date limiter in the access and i am not sure whether we can use it in the field name...so i think its better to change your field names..

-DNG

 

[DotNetGnat]

Try this then..not sure though...

SELECT A.[[Vendor #]], A.[Vendor Name], A.[Vendor Type], A.[Survey Faxed], A.[Survey Returned] FROM [Vendor Addresses] A WHERE A.[Approved Vendor] = 'yes' AND A.[[Active?]] = 'yes' ORDER BY A.[Vendor Name]

-DNG

 

[cdck]

DotNetGnat:

That returns a syntax error.

Is there a way to direct the page to pull the information from a query instead of the table? If so, I can set up a query that pulls the fields and gives them new names in its results...

Cheryl dc Kern

 

[Sheco]

You can get to the query by using the Access application object, similar to the way you can use the Word and Excel objects.

I think it is a bad idea to do it on a server though because it is easy to end up with phantom instances of these app running in the background but with no interface.

 

[cdck]

Ok, I'm just going to have to bite the bullet and fix the field names. It needed to be done sooner or later, anyway. Thank you all for your help!

Cheryl dc Kern

 

[DotNetGnat]

did you try...

SELECT A.* FROM [Vendor Addresses] A WHERE A.[Approved Vendor] = 'yes' AND A.[[Active?]] = 'yes' ORDER BY A.[Vendor Name]


-DNG

 

[DotNetGnat]

oops...i meant

SELECT A.* FROM [Vendor Addresses] A WHERE A.[Approved Vendor] = 'yes' AND A.[Active?] = 'yes' ORDER BY A.[Vendor Name]

-DNG

 

[cdck]

I renamed all the fields in the tables, replacing spaces with underscores and deleting question marks and number signs, and updated the query to read:

listSQL="SELECT [Vendor Addresses].[Vendor_Num], [Vendor Addresses].[Vendor_Type], [Vendor Addresses].[Survey_Faxed], [Vendor Addresses].[Survey_Returned] FROM [Vendor Addresses] WHERE [Vendor Addresses].[Approved_Vendor] = yes AND [Vendor
Addresses].[Active] = yes ORDER BY [Vendor Addresses].[Vendor_Name]"

Now the error message reads:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 7.

Is there some problem with using the underscore character in a SQL statement from an .asp page? The database has no problem with this statement.

Thanks,

Cheryl dc Kern

 

[DotNetGnat]

try with single quotes around 'yes'...

-DNG

 

[DotNetGnat]

I meant something like this...

SELECT [Vendor Addresses].[Vendor_Num], [Vendor Addresses].[Vendor_Type], [Vendor Addresses].[Survey_Faxed], [Vendor Addresses].[Survey_Returned] FROM [Vendor Addresses] WHERE [Vendor Addresses].[Approved_Vendor] = 'yes' AND [Vendor
Addresses].[Active] = 'yes' ORDER BY [Vendor Addresses].[Vendor_Name]"

Also write out your query...

Response.Write listSQL

-DNG

 

[cdck]

single quotes around the yes doesn't work because it returns a data type mismatch.

The response.write wrote:

SELECT [Vendor Addresses].[Vendor_Num], [Vendor Addresses].[Vendor_Type], [Vendor Addresses].[Survey_Faxed], [Vendor Addresses].[Survey_Returned] FROM [Vendor Addresses] WHERE [Vendor Addresses].[Approved_Vendor] = yes AND [Vendor Addresses].[Active] = yes ORDER BY [Vendor Addresses].[Vendor_Name]



Cheryl dc Kern

 

[DotNetGnat]

may be its asking for the semicolon in the end of the query string...

besides that the query looks fine to me...

-DNG

 

[cdck]

The semicolon didn't make a difference...

Cheryl dc Kern