Connect to SQL Server Compacy (SqlCe) database with ASP. Here is how to install the
Microsoft.SQLSERVER.CE.OLEDB.4.0 Provider and use classic ASP to connect to an SqlCe (SQL Server Compact) database sdf file, as a Microsoft Access database replacement. For Windows Server IIS. 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.
But there is a new kid in town: SQL Compact Server 4.0, or SqlCe for short.
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.
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:\www\path\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>
The SQL Server Compact 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.
My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 10 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, websites & optimization.
Has this post saved you time, helped you solve a problem? Or do you think Saotn is just awesome? Then why not support us and make a small, one-time, donation?
A small donation supports us in research time, hosting costs, and growth.
Please buy me a cup of coffee ($2.5) to support these articles and posts.
Or use this link to enter your own donation amount. Thank you!
Send email with SMTP authentication and TLS using ASP
Test MySQL database connectivity with ASP.NET, PHP, ASP
Online Access-database comprimeren
Connectiestring naar een Access database met ASP, PHP
Prepared statements: SQL Injection tegengaan
Threads in C#