Friday, August 6, 2010

Opening Connection with ODBC

There are three different ways of Opening connection with ODBC. These three different ways refer to three different locations where connections string are save.
  1. Windows registry
  2. File DSN and
  3. DSN – Less Connection
A DSN is a definition that is stored on the workstation, and can be used to connect to data using an ODBC driver. Under the Windows operating system, DSN definitions are stored in the Windows Registry

Saving Connection String in Windows Registry

Below giving Screens guide you how to store Connection string in windows   registry  

Under control panel double click on Administrative tools

 

Then select ODBC data source
 
A new window will pop up as shown below. click on the Add button

Select SQL native client and then click on next

 

 In the new window click the second radio button and enter Authentication credentials and click the next button

Now select the database from the first dropdown list and click on next button

 A new widow as shown below will pop up here you can test your connection


    now you can use the DSN name in your program for opening connection with database

    <%
        Set MyConn = Server.CreateObject("ADODB.Connection")
        MyConn.Open "DSN=MyDSN "
       %>



    Opening Connection with file DSN

    Click on File DSN and then click on ADD

    Then Click on Add and select the driver as shown in  step 4 above. And click on next button . In the New widow browse and save the file wherever you want . in my case I am saving the file in C drive with the name binaytest and click on next button


    Af  Ater this all the steps are same as we done above for saving the connection in  windows registry

    4. After finish a file with the name binaytest.dsn (MyDSN.dsn) will be created in C drive.
    5. Now we can include this file in our ASP code as shown below

    <%
             conn.open "FILEDSN=c:\binaytest.dsn
     
          %
     
     DSN less Connection
     
    <%
    Dim SqlConn
    Set SqlConn = Server.CreateObject("ADODB.Connection")

    SqlConn.ConnectionString="Driver={SQL Server};" & "Server=MS SQL Server IP;" & _"Database=dbname;" & "Uid=username;" & "Pwd=password;"
    %>
    Enhanced by Zemanta

    2 comments:

    1. Hi,
      I have an asp page but I am not able to connect to database through ODBC connection, which brings some report on the website.

      ReplyDelete