Thursday, August 5, 2010

Openining Connection with OleDB

First create a new text file, rename the file NewConnection.udl, or whatever you wish, the important thing is to change the file extension to .udl (Universal Data Link). Windows will give you a warning message about renaming file extensions, click Yes to proceed.

Double click the newly created .udl file, this will open the Data Link Properties box shown below:

Select Microsoft OLE DB Provider for SQL Server from the list shown and click the Next>> button.

Select or type in the server name in the text box number 1. Next click the relevant radio button and select whether you wish to use Windows NT integrated security or a specific user name and password. Finally select the database on the SQL Server to which you wish to connect.

Click the Test Connection button to confirm that everything is working correctly and click OK. Now right click the .udl file and select Open with Notepad.

If you selected Windows NT Integrated security you should see something like this:

; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=YourDB;_

If you selected Use a specific user name and password, you will see something like this:

; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Password=YourPassword;Persist Security Info=True;User ID=YourUserID;_
Initial Catalog=YourDB;Data Source=YOUR_LOCAL_SERVER_NAME

You can now copy and paste the connection string wherever it's required, for example:

Set commInsert = Server.CreateObject("ADODB.Connection")
commInsert.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;_
    PersistSecurity Info=False;Initial Catalog=_
strInsertCommand = "INSERT INTO tableName(TextField, NumericField)_
    VALUES('ABCDE', 12345);"
commInsert.Execute(strInsertCommand) ' Execute the insert command
Set commInsert = Nothing

You can also use .Udl files directly in your application. This approach is good when you want to protect your database credentials
 Code for using .UDL file directly in ASP
Dim ObjConn            
Dim ConnStr
Set ObjConn = Server.CreateObject("ADODB.Connection")      
connStr = "File Name=C:\\conn.udl; "

No comments:

Post a Comment