Loading

Making Database Connection

Submitted by: 
Language: 
Visitors have accessed this post 72172 times.


This tutorial is part of Database Programming Made Easy Series.

[inline:Making Database Connection.jpg=Making Database Connection]

I am writing this tutorial in the hope that you will understand the different kinds of database connection using Visual Basic .NET. This tutorial is for beginner only. If you are an advance programmer I advice you suggest correction for the betterment of this tutorial.

Before you begin accessing a data, first you need to establish a session with your database. This can be done using an object called connection.

Whether you are accessing an MS Access database or MS SQL Server Database you still need to open a connection before you can read and write back to the database.

1. Using OleDbConnection – Microsoft Access Database

  1. 'Set up connection string
  2. Dim cnString As String
  3.  
  4. txtStatus.Text = "Connecting to database using MS Access" & vbCrLf & vbCrLf
  5.  
  6. cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\Northwind.mdb"
  7.  
  8. 'Create connection
  9. Dim conn As OleDbConnection = New OleDbConnection(cnString)
  10.  
  11. Try
  12. ' Open connection
  13. conn.Open()
  14. txtStatus.Text = txtStatus.Text & "Connection opened" & vbCrLf
  15. Catch ex As SqlException
  16. txtStatus.Text = "Error: " & ex.ToString & vbCrLf
  17. Finally
  18. ' Close connection
  19. conn.Close()
  20. txtStatus.Text = txtStatus.Text & "Connection closed."
  21. End Try

2. Using SqlConnection – Microsoft SQL Server

  1. 'Set up connection string
  2. Dim cnString As String
  3.  
  4. txtStatus.Text = "Connecting to database using SQL Server" & vbCrLf & vbCrLf
  5.  
  6. cnString = "Data Source=localhost;Integrated Security=True"
  7.  
  8. 'Create connection
  9. Dim conn As SqlConnection = New SqlConnection(cnString)
  10.  
  11. Try
  12. ' Open connection
  13. conn.Open()
  14. txtStatus.Text = txtStatus.Text & "Connection opened" & vbCrLf
  15. Catch ex As SqlException
  16. txtStatus.Text = txtStatus.Text & "Error: " & ex.ToString & vbCrLf
  17. Finally
  18. ' Close connection
  19. conn.Close()
  20. txtStatus.Text = txtStatus.Text & "Connection closed."
  21. End Try

As you have notice. There is just one line of code to change if you are planning to use either Microsoft Access or Microsoft SQL Server.

For Microsoft Access the connection string is:

  1. cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\Northwind.mdb"

And in Microsoft SQL Server the connection string is:

  1. cnString = "Data Source=localhost;Integrated Security=True"

In this tutorial I prepare a project file for you to practice the difference between a Microsoft Access and Microsoft SQL Server. Download the zip file below.

Next: How to Retrieve Data




Comments

Imports System.Data.SqlClient
Imports System.Data.OleDb

para saan tong code.........

admin's picture

This will import OleDB and SQL Server data provider namespaces.

Connecting to database using SQL Server

Error: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Making_Database_Connection.frmMain.btnSQLServer_Click(Object sender, EventArgs e) in C:\Documents and Settings\BTB\Desktop\temp\Making Database Connection\frmMain.vb:line 48
Connection closed.

admin's picture

What's your connection string?

Hi! admin...

are u in Philippines now?
can we have your contact pls???..

we need your help on our thesis..?
pls.....

Are you familiar on Wireless Monitoring!!!???

need your reply???..

admin's picture

Use the contact menu above.

GoodDay!!!
...admin...
can we have your contact?..
pls....

are familiar on Wireless Monitoring???..

pls........

response k nmn po agad!!!

salamat po....

I have downloaded POS system coding.can i use it for my personal perpose.

Tks.

admin's picture

As you wish. Sell it if you like. If you can make money out of it then good.

Hi i m swapneel.
my problem is....
i m using autocomplete extender in my site but its not working efficently so i want to use a javascript can u help me on this

cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\Northwind.mdb"

hav a question about this the datasource=..\data - do i need to place the complete path where the datase is located such a s C:\my document\.. i receive errors

admin's picture

If the database is relative to your application folder then you do not need to put the full path.

But if it is outside your application folder then you need to specify the full path.

no buddy u dnt have 2 give the complete path address for connectivity ....
e.g -- "northwind.mdb"
this is enough u hav 2 provide the full path address on your Data Source block in ODBC driver..

Thank you so much

Bro....actually i want to find a record den diaply it on the datagrid and then display it on the form......wat wud the code in VB.NET

admin's picture

I have made a program on this already. Though I'm using DataGridView.

Please browse the Database Programming Made Easy.

Hi admin! I have tried that codes to create connection to MS Access but it gives a compile time error that unrecognized escape sequence at the connection string cnString

cnString="Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\Northwind.mdb";

am using visual c# 2008. Please can you help me out what is wrong with that error..

[email protected]

admin's picture

Please check the location of the northwind.mdb file.

I have MSAccess 2000... I can't open the database to manipulate the fields (need to add some more to the search). Is there any way you can help?

help.. how to connect login form using ms access 2007.
the username and password are inside the database access 2007.

sir is there any way to convert a database created in ms access to sql server2005?

yes if your SQL server has import or export funtion.

how can i connect c# to sql database

Any chance you have this code in VB 2005?

What do I need to modify in the connection string to connect to the sql database located in a server,the client computer in whch the application will be installed is in the same network (LAN) with the server

Thanks

Is this source code can be used for all connection establishment between VB and MS Access?

hai sir may i have your attention please ? im sherwin asking for your help. may i know how to make libraey system using ms access? thank you !!

Plz...help me...
hi god day i am a beginner in creating programs can i ask your help about changing may user name and password using VB 6.0 her is may codes

Module
ublic Function isValidPassword(passwd As String, usrd As String) As Boolean
Dim rstAccount As ADODB.Recordset
Dim strSQL As String
Dim isLoggedIn As Boolean

strSQL = "SELECT [ID],[password],[User] FROM [accounts] WHERE [password]='" & passwd & "' AND [User]='" & usrd & "'"
Set rstAccount = cnnInventory1.Execute(strSQL)

If rstAccount.EOF Then
isLoggedIn = False
Else
If StrComp(passwd, rstAccount!Password, vbBinaryCompare) = 0 And StrComp(usrd, rstAccount!User, vbBinaryCompare) = 0 Then
isLoggedIn = True
Else
isLoggedIn = False
End If
End If

If isLoggedIn Then
isValidPassword = True
Else
isValidPassword = False
End If

rstAccount.Close
Set rstAccount = Nothing
End Function

My Login Codes is Ok but in the Mudel is not ok thier is an error pls..help me to solve my problem

Dim counter As Integer
Private Sub Command1_Click()
Dim rstAccount As ADODB.Recordset
Dim strSQL As String
Dim isLoggedIn As Boolean

strSQL = "SELECT [ID],[User],[Password] FROM [accounts] WHERE [Password]='" & txtPassword.Text & "'AND [User]='" & txtUser.Text & "'"
Set rstAccount = cnnInventory1.Execute(strSQL)

If txtUser.Text = "" Then
Call MsgBox("Please enter your userName and Password", vbCritical, "Blank User")
Exit Sub
Else
If txtPassword.Text = "" Then
Call MsgBox("Please enter your userName and Password", vbInformation, "Blank Password")
Exit Sub
Else
If rstAccount.EOF Then
isLoggedIn = False
Else
If StrComp(txtPassword.Text, rstAccount!Password, vbBinaryCompare) = 0 And StrComp(txtUser.Text, rstAccount!User, vbBinaryCompare) = 0 Then
isLoggedIn = True
Else
isLoggedIn = False
End If
End If
End If
End If

If isLoggedIn Then
cnnInventory1.Close
Unload Me
Call MsgBox("Welcome to my program.", vbInformation, "Authorization Successful")
' frmMain.mnuUtility.Enabled = True
'frmMain.mnuNewProd.Enabled = True

' frmSalesRecord.Command1.Enabled = True
frmMain.Show vbModal

Else
'Call MsgBox("Authorization failed.", vbExclamation, "Authorization failed.")
Call MsgBox("Error.", vbCritical, "Error.")
counter = counter + 1
txtUser.Text = ""
txtPassword.Text = ""
txtUser.SetFocus
End If

If counter > 2 Then
Unload Me
End If

End Sub

Private Sub Command2_Click()
End
End Sub

Private Sub Form_Load()
Open_Conn
End Sub

Private Sub Image1_Click(Index As Integer)
Unload Me
End Sub

Private Sub txtPassword_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 13 Then
Call Command1_Click
End If
End Sub

Private Sub txtUser_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 13 Then
Call Command1_Click
End If
End Sub

With the error "operation is not allowed when the object is closed"...this means that you are trying to retrieve data from the database while it is closed..this happens when two or more users access the database. If you are using Rst.Close and the end of every procedure on another procedure reopen the database b4 using it eg. Set Rst=New ADODB.Recordset then continue with your coding.

help me plz!!!
I would like find who help me in making this kind of problem that i encounter.

hi
i want to create a library management software using visual studio 2008 and the database sql server 2005. Can u help me plz.......

can you help me how to make a enrollment system ???/plz i expect for your respond...Thank you and God bless us.....

sir how about if my database is password protected? what are the possible line of code.. thanks and regards...

hi......sir i need a election voting system program in c# and ms acess connecivity.please provide me its source code ....My emai id id [email protected]

Hi Sir , how ho change the location of any DB.And I save 10 nos of Name and marks,I want to know which place i m in like example.
A=765
B=657
C=453
D=859
--------------
-------------
Z=234
Then according mark's which D Stand.
Thanks

Sir,

Good morning/afternoon to you.
I've just downloaded your source code and run in my computer but I got errors. What might be the reason? I had Visual Basic 2008 Express Edition and Microsoft Server 2008 installed. Please help me I want to learn more in DataBase programming.

Connecting to database using SQL Server

Error: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Making_Database_Connection.frmMain.btnSQLServer_Click(Object sender, EventArgs e) in C:\Users\Welcome\Desktop\CODECODERS SAMPLE\ALL DATABASE\making_database_connection\Making Database Connection\frmMain.vb:line 47
Connection closed.

Thanks in advance.
GOD BLESS.

.good day sir ,

CAn I ask for some help ?? . can I ask fOr codes frOm vb 6.0 connected to MySQL database in designing and deveLoping a Loan and informatiOn system ?? . I wouLd be very gLad if you can hand me your heLp . thank you very much sir ..

dear sir

I am using visual basic 2010 express ver on windows 7, 64 bit operating system

the problem is i download your source code when i run on vb this error appier
The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

what i do help me sir

thanking you

dear admin

i am download ur source code and run on my system but i get error msg
like The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

iam using vb2010 os windows 7 64 bit

what i do help me pls sir

thanking you

hello..sir can u pls help me to give me source code of sudents computer access system i really appreciate if u grant my request..u can email me [email protected]

lKidl's picture

how can I connect MS access 2007 to MS visual studio 2010 using module form load..please . I have many forms .. it is right to use global right? but I forgot about those things T_T .. im badly needed it for my thesis .. thanks . Im looking forward to it . GB

thanks man! it helps a lot.. :)

pls send code that's connect vb.net with sql server 2008

intindihin nyo namn ung code hindi puro copy paste lng may error tlga yan............

Hello. You can design a website code for food let me?

admin's picture

Hi,

Do you want a website? Just send us message in the contact us page.

I have a problem , My Project by Visual basic .Net with Sql server DataBase , My problem is the connection this is my code :
Dim ad As New OleDb.OleDbDataAdapter
Dim ds As New DataSet

If CN.State = ConnectionState.Closed Then
Dim conn As New SqlConnection
conn.ConnectionString = conn.ConnectionString = "Provider= System.Data.SqlClient , Data Source=.;Initial Catalog=Pharmacy2;Persist Security Info=True;User ID=sa;Password=sa123"

conn.Open()

MsgBox(conn.State)
End If

CMD.Connection = CN
CMD.CommandType = CommandType.Text

OnH = 0

and this is the error Message :
Format of the initialization string does not conform to specification starting at index 0.

I don't know what is the problem
I hope to find the right connection
Thanks

Add new comment

(If you're a human, don't change the following field)
Your first name.
(If you're a human, don't change the following field)
Your first name.
(If you're a human, don't change the following field)
Your first name.

Filtered HTML

  • You may insert videos with [video:URL]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.