Connect to A SQL Server database using OLEDB

SQL Server is the database of choice when using ASP especially if you have a popular site . In this example we will show you how to connect to our example database which has a table called example in it as well .

Code :

<%
'variables to store our ado constants
Dim adOpenForwardOnly , adLockReadOnly , adCmdTable
adOpenForwardOnly = 0
adLockReadOnly = 1
adCmdTable = 2
'variables for Connection and Recordset objects
Dim objConn , objRS
'create instances of Recordset and Connection objects
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
'connect to our database
objConn.open " Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;" & _
"Initial Catalog=example;Data Source=myserver"
'open the example table
objRS.Open "example" , objConn , adOpenForwardOnly , adLockReadOnly , adCmdTable
'display all links in table
While Not objRS.EOF
Response.Write objRS.Fields("link") & "&nbsp"
Response.Write objRS.Fields("description") & "&nbsp"
Response.Write "<p>"
objRS.MoveNext
Wend
'close everything
objRS.Close
objConn.Close
'destroy objects
Set objRS = Nothing
Set objConn = Nothing
%>

Notes :

A few things to note here we are using the sa which is the server admin but with no password this is not recommended for security reasons .

Instructions :

Start the SQL server enterprise manager
Create a database called example by right clicking on Databases in the Left hand pane of SQL server . Here is what you should see .
sqlserver1.gif
Now click on New Database and enter example in the text box and click on OK . sqlserver2.gif .
Now right click on example and select New in the first menu and then table in the second menu . sqlserver3.gif
Now the table contains two columns link and description which are both text data types . Save this table as example .sqlserver4.gif

Now we have our database lets add some data to it , we are going to import our data from a CSV file created in Notepad , you can download this file here .

Right click on the example database , go to All Tasks and then Import data and click on it . sqlserver5.gif .

Now go through all of the stages in the wizard ensuring you enter the correct information like username , password , database etc . In the data source step ensure that you select text file from the drop down and then specify the location of the file you downloaded above .

In the next step leave the options like this sqlserver6.gif . In the next step make sure the option is set for comma like this sqlserver7.gif .

Now the next step is your destination which is your server , username , password and the database you are using . After this click next on the next 2 stages and then finish and the data will be transferred from the text file into your database ready for you to use .