Access SQL Server Compact 4 with ASP Classic and VbScript

Now that you can use SQL Server Compact 4 with ASP.NET, some developers wonder if you can also use it with ASP Classic. To access with ASP Classic, the SQL Server Compact 4.0 OLEDB provider must be installed on the system, so the 4.0 MSI must be installed by an administrator – no private deployment.

But YES, you can access a SQL Server Compact 4 database file from ASP Classic:

 

(The url is: http://localhost/aspclassic/default.asp)

Of course the required read/write permissions must be given the the relevant process user to the folder where the database file is located.

Here is the code to do it:

   

<%

set conn = Server.CreateObject("ADODB.Connection")

strCnxn = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;" & _
    "Data Source=C:\inetpub\wwwroot\AspClassic\App_Data\nw40.sdf;"

conn.Open strCnxn

set rs = Server.CreateObject("ADODB.recordset")
rs.Open "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES", conn

do until rs.EOF
    for each x in rs.Fields
       Response.Write(x.name)
       Response.Write(" = ")
       Response.Write(x.value & "
")
    next
    Response.Write("
")
    rs.MoveNext
loop
'
rs.close
conn.close
%>