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

Your getting that erro because one of the fields you are referring to is misspelled somewhere and Access thinks it is a parameter/variable. Double-check your field names against the database or just run the wqhole thing in Access (it will popup and tell you which one it thinks is a parameter).
-T

 

[cdck]

I found it!

The database exists in two locations, a current live location and a location set up for the intranet. After changing a table name in the SQL to match a new table I'd created in the database, it informed me that the table didn't exist. After much thought, I realised that I'd (*blush*) been changing the current live database, and not the intranet database.

I apologise for the mix-up.

Thanks to everyone who helped me figure out the original problem.

I also found out that the SQL automatically checks for both tables and queries that match the source name you give it, without having to do anything special. I've now set up a query that pulls each of the fields and gives those fields different names in the output, so that my end SQL query looks like:

listSQL="SELECT VList.VNum, VList.VName, VList.VType, VList.VSurveyed, VList.VReturned FROM VList WHERE VList.Approved = yes AND VList.Active = yes ORDER BY VList.VName;"


and works beautifully.

Thanks again,

Cheryl dc Kern