Error opening Access DB

steputt · July 22nd, 2012, 09:58 PM

I'm trying to get away from FrontPage, rewrite my site in "pure" ASP. I'm editing locally with WebMatix, and my code is borrowed from online references and from existing FP pages. I successfully open my Access DB and display data in one page, where I have hyperlinks (<a href='oneband.asp?PERFORMER=parameter'>) in one column to pass a parameter to another page. "oneband.asp" opens but shows "Microsoft OLE DB Provider for ODBC Drivers error '80040e10' [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1." for line "rs.Open SQL, Connection". The "Respone.Write"s before the error show me that the parameter is accepted and the SQL seems correct. I have tried several changes to the code (and received other error messages). Can someone please tell me what I'm doing wrong?

Code:

<%
dim passed
passed = Request.QueryString("PERFORMER")
Response.Write (passed & "<br />")
Dim ConnectionString
ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
   "DBQ=F:/mvlm/database/gigs.mdb;DefaultDir=;UID=;PWD=;"
Dim Connection
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.ConnectionTimeout = 30
Connection.CommandTimeout = 80
Connection.Open ConnectionString
' Create a RecordSet Object
Dim rs
set rs = Server.CreateObject("ADODB.RecordSet")
Dim SQL
SQL =  "SELECT * FROM musicians WHERE PERFORMER = """ & passed & """"
Response.Write (SQL & "<br />")
' Retrieve the records
rs.Open SQL, connection
Response.Write (rs(PERFORMER) & "<br />")
Response.Write (rs(LOCATION) & "<br />")
Response.Write (rs(GENRE) & "<br />")
Response.Write (rs(MEMBERS) & "<br />")
rs.Close
set rs = nothing
Connection.Close
Set Connection = nothing
%>

P.S. A "musicians.asp" page lists all bands (db fields). Visitors click on a band name and "oneband.asp" opens and is supposed to display more info from the db field. Both pages open the same database table with identical code (different query and display, of course), and "musicians.asp" works fine.

mat41 · July 22nd, 2012, 10:13 PM

dont use access anymore and havnt for a very long time however I remember this one well. I think you have a typo in a field name. Are you sure musicians is the field name??

steputt · July 23rd, 2012, 03:06 AM

Do you mean in the SQL? "gig.mdb" is the db, "musicians" is the table I'm working with. Is the "FROM" part supposed to be the table? Anyway it works in the previous page.

I've tried something else - changed the SQL, replacing the "passed" variable with an actual field value from the "PERFORMER" column. It still doesn't work, I get the same error. I hope this isn't a WebMatrix/IIS problem or an Access bug because I'll be up against a brick wall.

steputt · July 23rd, 2012, 12:24 PM

Sorry - I realized I said "fields" when I meant "records". My db is "gigs.mdb", and the table I'm working with is "musicians". Each band is a record in the table. "PERFORMERS", "LOCATION", etc. are columns in the table.

mat41 · July 23rd, 2012, 05:57 PM

I should have said field or table....Do this for me:

> Response.write the SQL statement to the browser, then copy n past it here. i want to see the value in 'passed' and they output of the surrounding quotes.

> what data type is the performer field you are comparing against?

steputt · July 24th, 2012, 12:48 AM

Thanks, Mat. Here's the page output. The "2 For the Road" is the "passed" variable. The field "PERFORMER" is a text data type.

2 For the Road
SELECT * FROM musicians WHERE PERFORMER = "2 For the Road"

Microsoft OLE DB Provider for ODBC Drivers

error '80040e10'

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

/oneband.asp, line 37

steputt · July 25th, 2012, 12:52 AM

Got it! Here's the new line that works:

SQL = "SELECT * FROM musicians WHERE PERFORMER = '" & passed & "'"

I have an older Wrox book "Beginning ASP Databases" that I haven't had the time to sit down and study. I used the index and found where the author mentions the reason for the error code I was getting, and in another chapter mentions that Access uses single quotes for text. I thought I already tried this before but I must have had a typo. Thanks for trying to help, I really appreciate it.

mat41 · July 25th, 2012, 01:19 AM

nice, glad you got it sorted. Sorry I thought I replied to you......

BTW you always use '" & varName & "' for strings....no matter what DB (all the ones I have used anyhow....)