Free Information Technology Magazines and eBooks

Sunday, June 07, 2009

VB.NET: How to connect to SQL Server

This topic is a part of VB.NET SQL Database Basics Tutorial series. On this article, you will learn how to connect to any version of SQL server using VB.NET. For programmers, database programming is a must if your working on data oriented companies. Before you can insert, update or query records from database, you must first know how to connect to it and this is my objective for this article. At the end of this article, you should be able to know how easy to connect to SQL server.


Note: I assumed that you already installed your SQL Server and Visual Studio .NET.

Here is the step-by-step procedure to connect to SQL server:
1. Create your VB.NET project.
2. Include the following namespaces.

Imports System.Data
Imports System.Data.SqlClient

The System.Data namespace provides access to classes that represent the ADO.NET architecture while the System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server.
3. Declare and instantiate your SQLConnection object as shown below

Dim con As New SqlConnection

SQLConnection class represents an open connection to a SQL Server database.
4. Pass the SQL connection string to ConnectionString property of your SqlConnection object.

con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"

The connectionstring value usually contains the following :
Data Source - physical server hostname
Initial Catalog - your database name
User ID - SQL username use to connect to the server
Password - SQL username's password

On this sample, I am using an SQL Server 2005. For the connectionstring for other SQL version, you can get it from here http://www.connectionstrings.com/.

5. Last step is to invoke the Open method of the connection object

con.Open()


The complete sample sourcecode:

Imports System.Data.SqlClient
Imports System.Data

Private Sub ConnectToSQL()
Dim con As New SqlConnection
Dim cmd As New SqlCommand

con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"
con.Open()
End Sub


To capture if the connection was successful or not, just tweak the above code:


Imports System.Data.SqlClient
Imports System.Data

Private Sub ConnectToSQL()
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Try
con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"
con.Open()
Catch ex As Exception
MessageBox.Show("Error while connecting to SQL Server." & ex.Message) Finally
con.Close() 'Whether there is error or not. Close the connection.
End Try
End Sub





12 comments:

Anonymous said...

So, you built your connection string ... would you like to do something with it?

Fryan Valdez said...

this is a part of a tutorial series

http://www.fryan0911.com/2009/05/vbnet-tutorial-sql-database-basics.html

Anonymous said...

Hi,
Works perfect for our configuration:
Visual Basic 2010 express and
SQL SERVER 2008.
Very useful tutorial, saved us a lot
of time. Thank you very much for this work.

Gunther / LDL - France

elschone24 said...

Hi, Where will I put the first code:

"2. Include the following namespaces.

Imports System.Data
Imports System.Data.SqlClient"

where will I put this code in my vb.net project?

Fryan Valdez said...

@elschone24 - place it at the top most of your form's code.

::Sebaik-baik kehidupan:: said...

"Imports System.Data.SqlClient
Imports System.Data

Private Sub ConnectToSQL()
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Try
con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"
con.Open()
Catch ex As Exception
MessageBox.Show("Error while connecting to SQL Server." & ex.Message) Finally
con.Close() 'Whether there is error or not. Close the connection.
End Try
End Sub "

hye, where should i paste the code?

Tanmay Chakrabarty said...

It helped me, Though I always stay in confusion about actually what my customer will need while they will run my program which have a MSSQL database included? Will they be in need to install the version of MSSQL based on which I created the database?

Whatever, I worked Access database and right from today I am getting into MSSQL. Thats why I was searching and found it. Thanks.

Anonymous said...

Thanks bud.

Anonymous said...

I'm sorry but why there is an error for "Private Sub ConnectToSQL()". Is there something missing? Btw, I'm new to vb.net.

Anonymous said...

Hi..are those codes to be pasted in a module?next, can you please post an example of codes for information fetching(Queries) from sql server?TY

marc jason said...

hello there! ahm how to know the connectionstring? im using sql server 2008.. i read the link in your blog about connection strings... but im still confuse in this code,

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

what does it mean? can you please explain it to me? thank you...

Anonymous said...

I see in the complete sample source code you declared cmd as new SqlCommand but I do not see where it was used. Is it necessary to declare cmd to connect to the database?