Why does a DSNless connection prevent error trapping?

[nosferer]

Hi

The following code executes quite fine with a DSN connection, but when I migrate it to a DSNless connection it falls over!

I have NO idea why it is doing this, can ANYONE HELP?

Dim Conn, dbPath, DB_USERNAME, DB_PASSWORD
  dbPath = "C:\Inetpub\[URL unfurl="true"]wwwroot\website\Supplier_catalogue\data\db1.mdb"[/URL]
Session("Connection") = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath

DB_USERNAME = "Admin"
DB_PASSWORD = ""
set RSRadiant = Server.CreateObject("ADODB.Recordset")
RSRadiant.ActiveConnection = Session("Connection")
RSRadiant.Source = "SELECT *  FROM tblCountry  WHERE Country like '" + Replace(RSRadiant__varCust, "'", "''") + "%'  ORDER BY ID ASC"
RSRadiant.CursorType = 0
RSRadiant.CursorLocation = 2
RSRadiant.LockType = 3
RSRadiant.Open
RSRadiant_numRows = 0
%>
<% If RSRadiant.EOF AND RSRadiant.BOF Then %>
<table width=&quot;100%&quot; border=&quot;0&quot; cellspacing=&quot;0&quot; cellpadding=&quot;0&quot;>
  <tr bgcolor=&quot;#FFCC00&quot;> 
    <td> 
      <div align=&quot;center&quot;><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;>NO 
        RESULTS FOUND. PLEASE SEARCH AGAIN</font></div>
    </td>
  </tr>
</table>
<% End If ' end RSRadiant.EOF And RSRadiant %>

Any help would be appreciated!!

 

[krisbrixon]

Try This:

<%
Dim objConn, objRS, strConnect, strSQL

strConnect = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\

http://wwwroot\website\Supplier_catalogue\data\db1.mdb;User

ID=Admin;Password=&quot;

Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot
objConn.Open strConnect

strSQL = &quot;SELECT * FROM tblCountry WHERE Country like '&quot; & Replace(RSRadiant__varCust, &quot;'&quot;, &quot;''&quot & &quot;%' ORDER BY ID ASC&quot;

Set objRS = objConn.Execute(strSQL)

If objRS.EOF AND objRS.BOF Then
Response.Write(&quot;Dude, I suck.<BR><BR>&quot; & strSQL & &quot;<BR><BR>&quot; & strConnect & &quot;<BR><BR>&quot
Else
While Not objRS.EOF
Response.Write(objRS(0) & &quot;<BR>&quot
'or replace 0 with the name of a field (with &quot;&quot.
Wend
End If

objRs.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

%>


Kris

 

[nosferer]

Nope, still falls over and I get the error...

Error Type:
ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/website/Supplier_catalogue/CountryResult.asp, line 67


Arrrgh, the pain, I guess I'm going to, against my better judgement, create a DSN!

 

[Geee]

The reason that DSN is more forgiving to these king of problems is that the extra layer between your ASP and DB actually parses the SQL and makes slight amendments to syntax if required. I would suggest seeing what output your SQL string generates with a well placed Response.Write SQL command. I would also suggest explicitly declaring and closing your connections in that page rather than using a session connection. Access tends to be very unreliable unless you use code whereby the connection and recordset are opened as late as possible, and closed and set to nothing as soon as they are finished being used.

Hope this is of some use. -GTM Consult, Home of USITE-
-=

http://www.gtmconsult.co.uk=-