SQL "Where" Problem in ASP

[Toytown]

Hi

I just noticed this on my site the other day and its driving my crazy trying
to fix it. The ASP pages connect to a ACCESS database and attempt to get
records using sample code like the one below. Since January this has
worked without any issues (and continues to work on my test server) however
it no longer works on the live server, the only updates i have done are the
windows ones and these are the only changes that have been made to the
server.


This code works on test but not on the live server (both are using win 2000
server)

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("spirit.mdb")
Set rsDatabase = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM Characters WHERE Server = 'test'"
rsDatabase.Open strSQL, adoCon

If i change the code to the following then the code works correctly but i
need the WHERE statement to select the correct records.

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("spirit.mdb")
Set rsDatabase = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM Characters"
rsDatabase.Open strSQL, adoCon

The error message i get back using the first example is, which off course
dosent help at all
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Unknown
/testdb.asp, line 7

I have tried the following on the live server.
1. Reinstall MDAC 2.8
2. Reinstall Service Pack 4
3. Remove/Reinstall IIS
4. Reinstall Jet Service Pack 8

none of these fix it, can anybody give me any clues as to where to look? I
cant reinstall the OS!

[Aaron [SQL Server MVP]]

How about:

db = Server.MapPath("spirit.mdb")
Set adoCon = CreateObject("ADODB.Connection")
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db
strSQL = "SELECT * FROM Characters WHERE Server = 'test'"
Set rsDatabase = adoCon.Execute(strSQL)

Changes:

(a) avoiding an explicit ADODB.Recordset when unwarranted
[url]http://www.aspfaq.com/2191[/url]

(b) avoiding ODBC/DSN
[url]http://www.aspfaq.com/2126[/url]

Recommended further change: stop using SELECT *!
[url]http://www.aspfaq.com/2096[/url]

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"Toytown" <teh-billy@microsoft.com> wrote in message
news:_eAIc.26689$xi3.12320@fe18.usenetserver.com.. .

> Hi
>
> I just noticed this on my site the other day and its driving my crazy

trying

> to fix it. The ASP pages connect to a ACCESS database and attempt to get
> records using sample code like the one below. Since January this has
> worked without any issues (and continues to work on my test server)

however

> it no longer works on the live server, the only updates i have done are

the

> windows ones and these are the only changes that have been made to the
> server.
>
>
> This code works on test but not on the live server (both are using win

2000

> server)
>
> Set adoCon = Server.CreateObject("ADODB.Connection")
> adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("spirit.mdb")
> Set rsDatabase = Server.CreateObject("ADODB.Recordset")
> strSQL = "SELECT * FROM Characters WHERE Server = 'test'"
> rsDatabase.Open strSQL, adoCon
>
> If i change the code to the following then the code works correctly but i
> need the WHERE statement to select the correct records.
>
> Set adoCon = Server.CreateObject("ADODB.Connection")
> adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("spirit.mdb")
> Set rsDatabase = Server.CreateObject("ADODB.Recordset")
> strSQL = "SELECT * FROM Characters"
> rsDatabase.Open strSQL, adoCon
>
> The error message i get back using the first example is, which off course
> dosent help at all
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC Microsoft Access Driver] Unknown
> /testdb.asp, line 7
>
> I have tried the following on the live server.
> 1. Reinstall MDAC 2.8
> 2. Reinstall Service Pack 4
> 3. Remove/Reinstall IIS
> 4. Reinstall Jet Service Pack 8
>
> none of these fix it, can anybody give me any clues as to where to look?

I

> cant reinstall the OS!
>
>
>

[Bob Barrows [MVP]]

Toytown wrote:

> strSQL = "SELECT * FROM Characters WHERE Server = 'test'"
> rsDatabase.Open strSQL, adoCon

I can't find the word in any of the lists I have, but from the symptoms, I
suspect "Server" may be a reserved keyword. In addition to the changes Aaron
recommended, I suggest surrounding the word with brackets:

" ... [Server] = 'test'"

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron [SQL Server MVP]]

> I suggest surrounding the word with brackets:

Or renaming the column. ;-)

A

[Toytown]

Just to let you know, im going to make the changes identified above and see
if they fix it, but also that any of the fields (server etc) will cause the
same issue.

Also why does this still work on one system and not another?
And why would one stop working, unless one of the updates screwed it?


"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:OzmQxKEaEHA.2520@TK2MSFTNGP12.phx.gbl...

> > I suggest surrounding the word with brackets:

>
> Or renaming the column. ;-)
>
> A
>
>

[Toytown]

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:uC6ihzDaEHA.2944@TK2MSFTNGP11.phx.gbl...

> How about:
>
> db = Server.MapPath("spirit.mdb")
> Set adoCon = CreateObject("ADODB.Connection")
> adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db
> strSQL = "SELECT * FROM Characters WHERE Server = 'test'"
> Set rsDatabase = adoCon.Execute(strSQL)
>

I get exactly the same error :( using the above code, im sure the server is
screwed somehow, but is there any idea of how i can get it working
again?even if i use a different database or field name (server) i still get
the same problem. I really appreciate the help BTW.

Microsoft JET Database Engine error '80040e14'

Unknown

/testdb2.asp, line 6

[Aaron [SQL Server MVP]]

> Also why does this still work on one system and not another?

> And why would one stop working, unless one of the updates screwed it?

Probably because one of the updates "screwed it"... what updates are you
talking about, btw?

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Toytown]

The only updates to the server before it stopped working were the typical
windows ones, through windows update. The ones i remember off hand were
the JET update and the ADODB stream problem update. Like my previous
message even using your sample code didnt work......however it works on my
other system........any other clues before i give up on it. Again it works
until i enter the WHERE part of the SQL string and then it gives up :(

Thanx for all your help!!


"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:OQKQpdEaEHA.2016@TK2MSFTNGP09.phx.gbl...

> > Also why does this still work on one system and not another?
> > And why would one stop working, unless one of the updates screwed it?

>
> Probably because one of the updates "screwed it"... what updates are you
> talking about, btw?
>
> --
> [url]http://www.aspfaq.com/[/url]
> (Reverse address to reply.)
>
>