MS Access DB errors

[slappy]

I have been getting errors when viewing pages that use an MS Access DB. This comes and goes.

Errors:



Microsoft VBScript runtime error '800a01fb'


An exception occurred: 'Open'


/locations/locations.asp, line 20


-----------------------------


ADODB.Recordset error '800a0e7a'


Provider cannot be found. It may not be properly installed.


/locations/locations.asp, line 15





I have tried these connection strings:


"Driver={Microsoft Access Driver (*.mdb)};DBQ=" & dbfilePath
"Provider=MICROSOFT.JET.OLEDB.4.0; DATA SOURCE=" & dbfilePath
"DSN=quicksellit_corporate"

[bruce]

switch your code to use OleDB DSNLess connection. There's a bug w/ ODBC under load.

Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[slappy]

I upgraded to SQL Server, and now I'm getting this error, among others.
[b]


Microsoft OLE DB Provider for SQL Server error '80040e14'


Invalid column name 'True'.


/locations/locations.asp, line 20 </BLOCKQUOTE>


The page and the others worked fine with Access. I don't have a column named 'True', it is a value I am searching for. Does SQL Server require different syntax?





Here is my connection string:
[b]


"Provider=sqloledb;Data Source=mssql06;Initial Catalog=DB_********;User Id=*******;Password=******"</BLOCKQUOTE>
Here is the code (written with Dreamweaver):
[b]
<%
Dim users__MMColParam
users__MMColParam = True
If (Request("MM_EmptyValue") <> "") Then
users__MMColParam = Request("MM_EmptyValue")
End If
%>
<%
Dim users
Dim users_numRows
Set users = Server.CreateObject("ADODB.Recordset")
users.ActiveConnection = MM_corporate_STRING
users.Source = "SELECT * FROM users WHERE locationlist = " + Replace(users__MMColParam, "'", "''") + " ORDER BY store ASC"
users.CursorType = 0
users.CursorLocation = 2
users.LockType = 1
users.Open()
users_numRows = 0
%></BLOCKQUOTE>

[slappy]

I corrected the first problem by doing the select for 1 instead of True.


I am sitll having another error that says:
[b]


Microsoft OLE DB Provider for SQL Server error '80040e14'


Line 1: Incorrect syntax near '7'.


/intranet/auction_manager/auction/auction_waiting.asp, line 34 </BLOCKQUOTE>

Here is the code (written by Dreamweaver):

[b]
<%
Dim items__status
items__status = "Auction"
If (Request("MM_EmptyValue") <> "") Then
items__status = Request("MM_EmptyValue")
End If
%>
<%
Dim items__storenum
items__storenum = "0"
If (Session("storenumber") <> "") Then
items__storenum = Session("storenumber")
End If
%>
<%
Dim items__time
items__time = "0"
If (Now <> "") Then
items__time = Now
End If
%>
<%
Dim items__paymentstatus
items__paymentstatus = "Consolidated"
If (Request("MM_EmptyValue") <> "") Then
items__paymentstatus = Request("MM_EmptyValue")
End If
%>
<%
Dim items
Dim items_numRows
Set items = Server.CreateObject("ADODB.Recordset")
items.ActiveConnection = MM_corporate_STRING
items.Source = "SELECT * FROM items WHERE status = '" + Replace(items__status, "'", "''") + "' AND storenum = " + Replace(items__storenum, "'", "''") + " AND endtime < #" + Replace(items__time, "'", "''") + "# AND paymentstatus <> '" + Replace(items__paymentstatus, "'", "''") + "' ORDER BY endtime ASC"
items.CursorType = 0
items.CursorLocation = 2
items.LockType = 1
items.Open()
items_numRows = 0
%>
</BLOCKQUOTE>

[bruce]

There's an error w/ your SQL statement, its really hard to see from the code, i suggest you dump out the statement using a response.write

Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[Scott]

Since the SQL error is 'Incorrect syntax near '7'', does either Replace(items__storenum...)=7 or does Replace(Items__paymentstatus...) = 7? Knowing that would help pinpoint nthe error.

I suspect that the culprit is probably in thiis segment ' AND endtime < #' + Replace(items__time, ''', '''') + '# . The use of '#' to explicitly convert a string tos a date looks like a carryover from MS Access that will not work in SQL Server.

If you use Bruce's suggustetion and dump out items.source using response.write, the syntax error will probably become apparent.

[slappy]

Thanks! That helped.