MS Access 2000 Connection Problem

ibby · Aug 21, 2000

Having a problem connecting to an Access Database, getting the following error in a login script. However I don't think the login script is at fault, i think it is my connection to the database. I have three methods of connecting of which one works and two do not.

With the one that works my whole site works OK, but with the other two, on the first database access (a simple select all from a single table) I get the error you see below.

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

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

login.asp, line 17

Line 17 is

set rs = myconnection.execute (SqlString)

Microsoft site says that the error results from a column name not being present in the table, but given that it works with one of the connect methods i don't think this can be the case.

Connection script is as follows

<%
dim myConnection, strConnect
dim strProvider
dim strDataSource
dim strPasswordParam

' This one works.
'strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
'strDataSource = "Data Source=C:\My Documents\corporate.mdb;"
'strPasswordParam = ""

' This bit does not (local version).
'strProvider = "DRIVER={Microsoft Access Driver (*.mdb)};"
'strDataSource = "DBQ=C:\My Documents\corporate.mdb;"
'strPasswordParam = ""

' Nor does this.
strProvider = "DSN=webdata;"
strDataSource = ""
strPasswordParam = ""

' Build overall string and connect
strConnect = strProvider & strDataSource & strPasswordParam
set myConnection = Server.CreateObject ("ADODB.Connection&quot

myConnection.open strConnect
%>

Note that one of the three block in the middle is used, the other two are commented out to try out each method. I think it might be to do with my setup, but I am pulling my hair out on this one.

OS is Win 98, Access 2000 and PWS running the ASP.

Thanks

Ibby

NickBulka · Aug 21, 2000

In my experience, this error has always resulted from a field name that I misspelled, or one that I removed from the database but forgot to update the query. (in other words, the help is correct)

Please post the code that builds the SqlString

ibby · Aug 21, 2000

Nick

Thanks for the reply. Code segment is as follows. I am pretty sure (always willing to be proved wrong!) that the SQL is OK for two reasons - one it works withthe connect method that does work and I have copied it as text and pasted it into Access in the query design view and it is OK. I also checked the names closely.

dim rs
dim SqlString, SqlString1, strUsername, strPassword, Attempt, strBranch

strUsername = request.form ("username&quot

strPassword = request.form ("password&quot

if (session("SiteAttempt&quot

<4)then
if strUsername<>"" then
SqlString = "SELECT password FROM siteuser WHERE (username=""" & strUsername & ""&quot

;"
'response.write "<HTML><HEAD></HEAD><PAGE>"
'response.write SqlString
'response.write "</PAGE></HTML>"
set rs = myconnection.execute (SqlString)

I am really at a loss on this one, but since I am fairly new to ASP I am willing to learn!!! Everything has gone really well up until now, the WROX books have been excellent teaching me so far, but they don't have much on this particular topic.

HLotis · Aug 22, 2000

Hi,
I got this message once when the query was asking for a user prompted response for a value rathen than a fixed or computed value.
Hal

Guest_imported · Aug 22, 2000

Try this:
SqlString = "SELECT password FROM siteuser WHERE username = [red]'[/red]" & strUsername & "[red]'[/red];"

Webrookie · Aug 22, 2000

Yup....guestg has it

SqlString = "select password from siteuser where username='"& strUsername & "';"

ibby · Aug 22, 2000

Yep, guestg has the answer, I have just changed the few places where I had the double double quotes and, hey, it works irrespective of the way you connect.

I am assuming there are subtle differences between the drivers, hence one worked and two did not.

Question of style/correctness - I am assuming that single quotes is the more correct SQL?

Anyway, thanks for all the help guys and girls from a very happy man in England. One of the better message boards I have come across, good response!

Many thanks again.

Webrookie · Aug 22, 2000

I've always been told that you use single quotes for strings
i.e:

SqlString = "SELECT password FROM siteuser WHERE username = '" & strUsername & "';"

where as with numbers you don't need them:

SqlString="select password from siteuser where id=" & intID & ";"

Am I right? will my second statement work? I always get it mixed up.

ibby · Aug 23, 2000

Yes, most of my SQl looks like the second string, it was only the string variables that I had to change.

One side effect of changing the connection method was the on the names of outputs from a query. If I had outputs from two tables, e.g.

SELECT a.id, b.id .....

then I used to be able to refer to A.ID and B.ID in the resulting recordset, but now I have to use the AS clause, e.g.

SELECT a.id AS aid, b.id AS bid ....

and then use aid, bid in the resulting recordset. There are obviuosly one or two differences in the drivers....... You live and learn.

Anyone recommend a good book on "standard" SQL?

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

MS Access 2000 Connection Problem

ibby

Programmer

NickBulka

Programmer

ibby

Programmer

HLotis

Programmer

Guest_imported

New member

Webrookie

Programmer

ibby

Programmer

Webrookie

Programmer

ibby

Programmer

Similar threads

Part and Inventory Search

Sponsor