Connection to a Microsoft SQL Server database with Asp

Who I am
Martí Micolau
@martimicolau
Author and references

SQL Server is a powerful relational DBMS from Microsoft that works in a similar way to the already known Access, while offering far superior services, power and stability. In this article we will see how to interface an Asp script to a SQL Server database.

Let's start by saying that SQL Server does not allow you to create database files like Access MDBs but works through a database server on which you can create your own databases intended as database structures, more or less like MySQL works.



Connection to a Microsoft SQL Server database with Asp

The first step is to create a database on SQL Server. Access the DBMS interface through the menu

Start / Programmi / Microsoft SQL Server / Enterprise Manager

At this point we create the database db_prova in which we create the table tab_utenti. Create the fields id (Type: number - Identity: Yes), name (Type: char) and surname (Type: char). Now open the table and enter test data, following the path indicated in the figure:

Now create the cn_sqlserver.asp file on your Web server and insert the following code accompanied by appropriate comments:


<% // Stringa di connessione a SQL Server
var stringa = “Driver={SQL Server};Server=PROVA;Database=db_prova;Uid=sa;Pwd=sa;”;
// Query to be launched
var SQL = “SELECT * FROM tab_utenti”;
// I open the connection and the recordset
var cn = new ActiveXObject(“ADODB.Connection”);
var rs = new ActiveXObject(“ADODB.Recordset”);
cn.Open(stringa);
rs.Open(SQL, cn, 1)
// I check if there is any data in the table
if (rs.EOF) { Response.Write(“Nessun dato presente”); }
// If there are any, I print them on screen
else
{ with (Response)
{ while (rs.EOF == false)
{ Write(“ID: ” + rs(“id”) + “”);
Write(“Nome: ” + rs(“nome”) + “”);
Write(“Cognome: ” + rs(“cognome”) + “”); rs.MoveNext();
}
}
} rs.Close();
cn.Close ();
%>



Nothing complicated or particularly different from a connection and a query on an Access database: in fact the only thing that changes, at the Asp code level and for a basic example like this, is the database connection string it presents the following form:


Driver={SQL Server};Server=PROVA;Database=db_prova;Uid=sa;Pwd=sa;

Where Server & Hosting indicates the name of the server on which the database is located, where the server version of SQL Server is therefore installed; Database indicates the name of the database to connect to; Uid e pwd indicate the SQL Server login credentials.


add a comment of Connection to a Microsoft SQL Server database with Asp
Comment sent successfully! We will review it in the next few hours.