Saotn.org

ASP: Connect to SqlCe (SQL Server Compact) database file

ASP: Connect to SqlCe (SQL Server Compact) database file

How to install the Microsoft.SQLSERVER.CE.OLEDB.4.0 Provider and use classic ASP to connect to an SqlCe (SQL Server Compact) database file. As a Microsoft Access database replacement. For IIS 7+ (7.0, 7.5, 8.0). SQL Server Compact (SqlCe), the embedded database engine, is the default database for Visual Studio 2010 SP1 and WebMatrix 3. This means, you can better make use of SqlCe than Access nowadays.

Replace an MS Access database with SqlCe

Not too long ago I wrote two articles about MS Access as a database back-end (Dutch). Access is still widely used. An Access database needs a lot of maintenance and needs to be compressed often.

But there is a new kid in town: SQL Compact Server 4.0, or SqlCe for short.

SqlCe database of choice with WebMatrix 3

SQL Server Compact, the embedded database engine, is the default database for Visual Studio 2010 SP1 and WebMatrix 3. This means, you can better make use of SqlCe than Access nowadays. Practically every hostingprovider should support the use of SqlCe databases, We at Vevida do.

Microsoft SQL Server Compact 4.0 is a free, embedded database that software developers can use for building ASP.NET websites and Windows desktop applications. SQL Server Compact 4.0 has a small footprint and supports private deployment of its binaries within the application folder, easy application development in Visual Studio and WebMatrix, and seamless migration of schema and data to SQL Server.

Download and install SQL Server Compact 4.0

If you want to be able to connect to an SqlCe 4.0 file from a classic ASP script, you need to download and install the appropriate version of the drivers.

Example ASP-script how to connect with an SqlCe database file

Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0
This piece of ASP-code connects to an SqlCe database, performs a query and list all fields. You need to set the correct path to your database file.

<html> 
<head> 
    <title>Test SQL Compact 4 and ASP Classic + ADO</title> 
</head> 
<body>

<%
set conn = Server.CreateObject("ADODB.Connection")
strCnxn = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;" & _ 
    "Data Source=D:\wwwpath\to\database\SqlCe_file.sdf;"

conn.Open strCnxn
set rs = Server.CreateObject("ADODB.recordset") 
rs.Open "SELECT * FROM table", conn
do until rs.EOF 
    for each x in rs.Fields 
       Response.Write(x.name) 
       Response.Write(" = ") 
       Response.Write(x.value & "<br />") 
    next 
    Response.Write("<br />") 
    rs.MoveNext 
loop 

rs.close
conn.close
Set conn = Nothing
%>
</body> 
</html>

Conclusion

The SqlCe database engine has great performance and is a really great replacement for your old Microsoft Access databases. If you still use MS Access, convert and migrate Access to SqlCe. You won’t regret it.

If you feel that this post has helped solve your problem, saved time or you simply like Saotn.org, please consider making a donation. Thanks! :)

 

 

 

 


Sysadmins of the North
About The Author
My name is Jan Reilink. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing his daily thing at Vevida Services in the Netherlands. With over 10 years of experience, my specialties include Windows Server (2003, 2008 and 2012), Windows 7, IIS (6.0, 7.5 and 8.0), Linux (CentOS, Debian), PHP, websites, optimization and security. More about me @ www.reilink.nl.

 

There are no comments yet, but you can be the first

About Sysadmins of the North

Hi and welcome to Sysadmins of the North!
Sysadmins of the North is just another technical blog. Just like so many others out there. Most posts are written in English, some in Dutch. On Saotn.org you can find all kinds of computer, server, web, sysadmin, database and security related stuff.

About me: My name is Jan Reilink. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing his daily thing at Vevida Services in the Netherlands. Living in the north of the Netherlands, so hence the name Sysadmins of the North :-)
Drop me a comment somewhere or send an email to say hi, or discuss about security, website or WordPress, performance, Windows or IIS topics.

Support Saotn.org

If you feel that Sysadmins of the North has helped solve your problem, saved you time, or you just simply like Saotn.org, then please consider making a donation with Paypal. Thanks! :)

Search & find

Custom Search
IT Books & WP Themes

Windows PowerShell Cookbook: The Complete Guide to Scripting Microsoft\'s Command Shell
DNS and BIND - 5th Edition
DNSSEC Mastery: Securing the Domain Name System with BIND
Windows Server 2012 Unleashed
Enfold - Responsive Multi-Purpose WordPress Theme
Striking MultiFlex & Ecommerce Responsive WordPress Theme

 

The Sysadmins of the North network

Just for the fun of it, Sysadmins of the North is hosted on mulitple servers:

  1. one (shared) Windows Server 2012, IIS 8.0 webserver running PHP 5.5
  2. one (shared) MySQL database server, running MariaDB 5.5
  3. one Varnish Cache HTTP reverse proxy with Nginx Droplet, for offloaded static content, running Debian 7.0 @ DigitalOcean
Easy share

Share this post easy on:
RSS feed
If you like Saotn.org:
donate to Sysadmins of the North
Twitter Feed

What's happening, right now, around Saotn.org?


Bad Authentication data
Copyright © 2007-2014 Saotn.org . Design by OrangeIdea