You are Here: FAQ Web Space & Access MSSQL Article #3

How do I make a connection to a MSSQL Database using ASP?

Microsoft Packages Only!
This includes:

	1&1 MS Home	1&1 Dual Standard Windows
	1&1 MS Business	1&1 Dual Unlimited Windows
	1&1 MS Business Pro	1&1 Dual Business Windows
	1&1 MS Professional

In this guide, we already have a MSSQL database created named db123456789. Within this Access database there is one table named tbl_Sales. This table holds the data for recent sales by employees. Look below at the data in the database.

tbl_Sales
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

In this example, we will create an ASP script in VBScript to connect to the MSSQL database, print the price for each sale on the page as well as the total for all sales.

<%
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
%>

It is very important to close the connection once you are done querying the database. Only then will the memory resources be released. It is always good to have very minimum connections open.

Avoiding Errors
Never under any circumstances use the following method to determine the number of lines received:

Incorrect

Do While Not RS.EOF
i = i + 1
RS.MoveNext
Loop
RS.MoveFirst

The system will have to go through the entire record and all its columns without using any of the data in there. This takes time, the page takes longer to load, and the database connection is open for longer (as already mentioned, there is a limit to the number of connections that can be open at the same time).

To find the number of row in a table, use the following code:

Correct

Set RsRowList = Server.CreateObject("ADODB.recordset")
RsRowList.Open "SELECT COUNT(*) FROM tbl_Sales", OBJdbConn
Response.Write RsRowList(0)

Disclaimer: 1&1 provides the scripts and related information on this page as a courtesy, subject to 1&1's General Terms and Conditions of Service (the "GT&C"). As set forth in more detail in the GT&C, the scripts and information are provided "as-is", without any warranty, and 1&1 is not liable for any damages resulting from your use of the scripts or information.

Print Article

Feedback

Overview	Search
Sitemap	Glossary