Home page
 
 Home 
 ASP 
 PHP 
 SQL 
 HTML 
 JavaScript 
 Search 
 Contact 
 
Search
or browse popular tags
ASP Tutorial
Subscription

Sign up for the free email newsletter for new tips, tutorials and more. Enter your email address below, and then click the button.

Privacy Policy

RSS Twitter

How to connect to MS SQL Server database

Print

In this tutorial we will show you how to connect to MS SQL Server database. Everything is commented so you won't have trouble.

Connect with a DSN

DSN stands for 'Data Source Name'. It is an easy way to assign useful and easily rememberable names to data sources which may not be limited to databases alone. If you do not know how to set up a system DSN read our tutorial How to set up a system DSN.

<%
'declare the variables
Dim Connection
Dim Recordset
Dim SQL

'declare the SQL statement that will query the database
SQL = "SELECT * FROM TABLE_NAME"

'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'open the connection to the database
Connection.Open "DSN=dsn_name;UID=user_name;PWD=password;Database=database_name"

'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL,Connection

'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records returned.")
Else
'if there are records then loop through the fields
Do While NOT Recordset.Eof   
Response.write Recordset("FIRST_FIELD_NAME")
Response.write Recordset("SECOND_FIELD_NAME")
Response.write Recordset("THIRD_FIELD_NAME")
Response.write "<br>"   
Recordset.MoveNext    
Loop
End If

'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
%>

Connect without a DSN (using a connection string)

Let’s look at a sample script to get an idea how to connect to MS SQL Server database without DSN:

<%
'declare the variables
Dim Connection
Dim ConnString
Dim Recordset
Dim SQL

'define the connection string, specify database driver
ConnString="DRIVER={SQL Server};SERVER=yourServername;UID=yourUsername;" & _
"PWD=yourPassword;DATABASE=yourDatabasename"

'declare the SQL statement that will query the database
SQL = "SELECT * FROM TABLE_NAME"

'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database

Connection.Open ConnString

'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL,Connection

'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records returned.")
Else
'if there are records then loop through the fields
Do While NOT Recordset.Eof   
Response.write Recordset("FIRST_FIELD_NAME")
Response.write Recordset("SECOND_FIELD_NAME")
Response.write Recordset("THIRD_FIELD_NAME")
Response.write "<br>"
   
Recordset.MoveNext    
Loop
End If


'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
%>

Note: Don't forget to replace the constants (TABLE_NAME, FIELD_NAME) with real names of tables and fields in your database.

Related Articles

MS SQL Server Connection Strings




Tags:

Add To: Add to dzone dzone | Digg this digg | Add to del.icio.us del.icio.us | Stumble it stumbleupon

  • Comments





Copyright © 2005-2023             www.WebCheatSheet.com All Rights Reserved.