MS Access DB errors

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"

 

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



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:



"Provider=sqloledb;Data Source=mssql06;Initial Catalog=DB_********;User Id=*******;Password=******"</BLOCKQUOTE>
Here is the code (written with Dreamweaver):

<%
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>

 

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


I am sitll having another error that says:



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):


<%
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>

 

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.

 

Thanks! That helped.