By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,360 Members | 3,011 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,360 IT Pros & Developers. It's quick & easy.

Saved Query Works in Access but not when called by ASP

P: n/a
Guys

I have built a database with saved queries that runs fine in Access
but when I call it from the web using ASP, an exception occurs. I
have tried multiple ways of testing the databases with the following
results. www.brinkster.com has a tool on their free asp hosting where
you can dump an access .mdb database on there and then run SQL queries
against it. Using Access databases it is possible to just call the
query by using its name. ie. instead of "Select * From etc" You just
type into the SQL query box: queryname. My query does not run here,
it says Error: Exception occurred. Keep in mind that if I execute the
same query in Access, it runs fine and gives the expected results.

The same exception error occurs when I run the query using an ASP
page.
One other thing to note is that I have successfully called the query
and run it from ASP scripts and the SQL tester but then as soon as I
added more than two lines of data, different data, it started throwing
exceptions.
However, this is another issue that I am pulling my teeth out trying
to resolve.

What I am wondering is, does Access have some special error handling
abilities? Why would the query run in Access and not when it is
called externally?

Any help would be appreciated.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Mattyboy,
Access runs stuff fine because it is running the Jet database engine
natively. Depending on the middleware contributing to delivering the result
for ASP your results will vary if the jdbc or odbc or ado drivers are not
able to run Jet natively.
"Mattyboy" <ma********@yahoo.com> wrote in message
news:fb**************************@posting.google.c om...
Guys

I have built a database with saved queries that runs fine in Access
but when I call it from the web using ASP, an exception occurs. I
have tried multiple ways of testing the databases with the following
results. www.brinkster.com has a tool on their free asp hosting where
you can dump an access .mdb database on there and then run SQL queries
against it. Using Access databases it is possible to just call the
query by using its name. ie. instead of "Select * From etc" You just
type into the SQL query box: queryname. My query does not run here,
it says Error: Exception occurred. Keep in mind that if I execute the
same query in Access, it runs fine and gives the expected results.

The same exception error occurs when I run the query using an ASP
page.
One other thing to note is that I have successfully called the query
and run it from ASP scripts and the SQL tester but then as soon as I
added more than two lines of data, different data, it started throwing
exceptions.
However, this is another issue that I am pulling my teeth out trying
to resolve.

What I am wondering is, does Access have some special error handling
abilities? Why would the query run in Access and not when it is
called externally?

Any help would be appreciated.

Nov 12 '05 #2

P: n/a
"Mattyboy" <ma********@yahoo.com> wrote in message
news:fb**************************@posting.google.c om...
Guys

I have built a database with saved queries that runs fine in Access
but when I call it from the web using ASP, an exception occurs. I
have tried multiple ways of testing the databases with the following
results. www.brinkster.com has a tool on their free asp hosting where
you can dump an access .mdb database on there and then run SQL queries
against it. Using Access databases it is possible to just call the
query by using its name. ie. instead of "Select * From etc" You just
type into the SQL query box: queryname. My query does not run here,
it says Error: Exception occurred. Keep in mind that if I execute the
same query in Access, it runs fine and gives the expected results.

The same exception error occurs when I run the query using an ASP
page.
One other thing to note is that I have successfully called the query
and run it from ASP scripts and the SQL tester but then as soon as I
added more than two lines of data, different data, it started throwing
exceptions.
However, this is another issue that I am pulling my teeth out trying
to resolve.

What I am wondering is, does Access have some special error handling
abilities? Why would the query run in Access and not when it is
called externally?

Any help would be appreciated.

This is not really a question for this newsgroup since Access is not
involved at all. Just because it uses an mdb file, this does not need MS
Access at all - you are probably using ADO to connect. I sometimes get the
feeling that ASP people come here to ask because this is a fairly helpful
newsgroup.

It is also hard to pinpoint the error. Can you get a connection, but not
execute the query? Do you have any error-checking? What about running a
test page like the following - which you could call TestMe.asp - just change
the value of g_strDatabase.

If you cannot get a connection, then inestigate permissions. If you can get
a connection, then there may be small syntax differences in the query.
Using Access you would write Like "Smith*" but ASP you would write Like
"Smith%"

<%

Const g_strDatabase = "Contacts.mdb"

Call Main
Sub Main()

Dim cnn
Dim strError

Set cnn = DbConnection(strError)
WriteLine "<HTML>"
WriteLine "<HEAD>"
WriteLine "<TITLE>Connection Test Page</TITLE>"
WriteLine "</HEAD>"
WriteLine "<BODY>"
WriteLine "Testing connection...<BR>"
If Len(strError) > 0 Then
WriteLine "Error:<BR>" & strError
Else
WriteLine "Success:<BR>" & cnn.ConnectionString
cnn.Close
Set cnn = Nothing
End If
WriteLine "</BODY>"
WriteLine "</HTML>"

End Sub
Sub WriteLine(strLine)
Response.Write strLine & vbCrLf
End Sub

Function DbConnection(strError)

On Error Resume Next

Dim cnn
Dim strCnn

strError = "Unknown Error"

strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath(g_strDatabase)

Set cnn = Server.CreateObject("ADODB.Connection")

cnn.ConnectionString = strCnn

cnn.Open

If Err.Number <> 0 Then
Set cnn = Nothing
If Len(Err.Description) > 0 Then
strError = Err.Description
End If
Else
strError = ""
End if

Set DbConnection = cnn

End function

%>
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.