SETHA SITE INFORMATION

  • Increase font size
  • Default font size
  • Decrease font size

How to Connect from Visual Basic .Net to Microsoft SQL Server?

E-mail Print PDF

This article shows you the way to connect from Visual Basic .Net code to Microsoft SQL Server Database. There two ways in this article, first using OLEDB namespace (old style) and second using SQLClient namespace (new style in ADO.Net).

Suppose that Microsoft SQL Server database named: TEST is running on server named: Setha-PC. For SQL Server authentication mode, User ID and password are applied.

I. Using OLEDB namespace

  • Provider - the type of database source (eg. SQLOLEDB.1 for MS-SQL Server)
  • Data Source - name of server where the MS-SQL Server database is located
  • Initial Catalog - the database name
For SQL Server Authentication Mode
Dim cn As New OleDb.OleDbConnection

cn.ConnectionString = "Provider=SQLOLEDB.1; " & _
                      " Data Source='Setha-PC'; " & _
                      " Initial Catalog='TEST'; " & _
                      " User ID=sa; Password='123'"
cn.Open()

MsgBox("Connection establish!")

cn.Close()
For Windows Authentication Mode
Dim cn As New OleDb.OleDbConnection

cn.ConnectionString = "Provider=SQLOLEDB.1; " & _
                      " Data Source='Setha-PC'; " & _
                      " Initial Catalog='TEST'; " & _
                      " Integrated Security=SSPI;" & _
                      " Persist Security Info=False;"
cn.Open()

MsgBox("Connection establish!")

cn.Close()

II. Using SQLClient namespace

  • Data Source - name of server where the MS-SQL Server database is located
  • Database - the database name
For SQL Server Authentication Mode
Dim cn As New SqlClient.SqlConnection

cn.ConnectionString = "Data Source='Setha-PC'; " & _
                      "Database='TEST'; " & _
                      "User ID=sa; Password='123'"
cn.Open()

MsgBox("Connection establish!")

cn.Close()
For Windows Authentication Mode
Dim cn As New SqlClient.SqlConnection

cn.ConnectionString = "Data Source='Setha-PC'; " & _
                      "Database='TEST'; " & _
                      " Integrated Security=SSPI;" & _
                      " Persist Security Info=False;"
cn.Open()

MsgBox("Connection establish!")

cn.Close()