1and1 Help Centre Categories

print article

Connect to a MSSQL Database Using ASP

Learn how to create an ASP script in VBScript to connect to the MSSQL database.

In this example we will show you how to connect to the MSSQL database and print sale details stored in the database. To clearly illustrate the procedure, we are using a MSSQL database named db123456789. Within this Access database, there is one table named tbl_Sales.

This table shows the recent sales made by employees:
Employee Product Price SaleNumber
John Shoes £39.99 28637283
Sue Dress shirt £41.99 33894673
John Pants £34.99 34783263
John Socks £4.99 38473936
Sue Gloves £7.99 38637286
Bob Jacket £74.99 39074730

You can create the following script to query the database, but keep in mind to close the connection once you are done. This will ensure that the memory resources will be released. As best practice, only a minimum connections should be open.

#// <%
total = 0
sConnStr = "Provider=sqloledb;Data Source=winsqlus01.1and1.com;Initial Catalog=db123456789;User Id=dbo123456789;Password=password;"
Set OBJdbConn = Server.CreateObject("ADODB.Connection")
OBJdbConn.Open sConnStr
Set RsPriceList = Server.CreateObject("ADODB.recordset")
RsPriceList.Open "SELECT * FROM tbl_Sales", OBJdbConn
Do While NOT RsPriceList.EOF
total = total + RsPriceList("Price")
Response.Write("<p>$" & RsPriceList("Price") & "</p>")
RsPriceList.MoveNext
Loop
Response.Write("The total is: $" & total)
RsPriceList.Close
Set RsPriceList = Nothing
OBJdbConn.Close
Set OBJdbConn = Nothing
%> #//

For additional information, you may want to reference: