Making Database Connection

Submitted by admin on Sunday, April 26, 2009 - 16:18.
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
Tags
vb.net
database
connection

Comments

Submitted by Anonymous (not verified) on Sat, 06/06/2009 - 16:24

Imports System.Data.SqlClient Imports System.Data.OleDb para saan tong code.........

Submitted by admin on Sun, 06/07/2009 - 11:44

This will import OleDB and SQL Server data provider namespaces.

Submitted by Anonymous (not verified) on Fri, 06/19/2009 - 09:19

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.

Submitted by Anonymous (not verified) on Tue, 07/07/2009 - 18:53

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???..

Submitted by Anonymous (not verified) on Tue, 07/07/2009 - 18:57

GoodDay!!! ...admin... can we have your contact?.. pls.... are familiar on Wireless Monitoring???.. pls........ response k nmn po agad!!! salamat po....

Submitted by faz200 on Wed, 07/29/2009 - 12:11

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

Submitted by admin on Wed, 07/29/2009 - 17:35

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

Submitted by Anonymous (not verified) on Fri, 08/07/2009 - 18:37

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

Submitted by Anonymous (not verified) on Fri, 09/18/2009 - 12:31

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

Submitted by admin on Fri, 09/18/2009 - 17:28

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.

Submitted by raja ghosh (not verified) on Sun, 05/06/2012 - 20:05

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..

Submitted by Anonymous (not verified) on Wed, 09/23/2009 - 15:18

Thank you so much

Submitted by Anonymous (not verified) on Fri, 10/23/2009 - 01:13

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

Submitted by admin on Fri, 10/23/2009 - 09:16

I have made a program on this already. Though I'm using DataGridView. Please browse the Database Programming Made Easy.

Submitted by Anonymous (not verified) on Tue, 11/03/2009 - 17:27

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]

Submitted by admin on Thu, 11/05/2009 - 11:51

Please check the location of the northwind.mdb file.

Submitted by Anonymous (not verified) on Wed, 01/27/2010 - 08:53

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?

Submitted by Anonymous (not verified) on Sun, 01/31/2010 - 10:37

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

Submitted by Anonymous (not verified) on Tue, 03/02/2010 - 12:18

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

Submitted by Anonymous (not verified) on Fri, 03/12/2010 - 14:18

how can i connect c# to sql database

Submitted by Anonymous (not verified) on Thu, 03/25/2010 - 11:22

Any chance you have this code in VB 2005?

Submitted by Anonymous (not verified) on Tue, 04/06/2010 - 19:00

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

Submitted by Anonymous (not verified) on Thu, 05/20/2010 - 18:55

Thanks

Submitted by Anonymous (not verified) on Tue, 06/01/2010 - 15:27

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

Submitted by Anonymous (not verified) on Fri, 08/06/2010 - 21:33

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.

Submitted by Anonymous (not verified) on Tue, 10/26/2010 - 14:37

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

Submitted by Anonymous (not verified) on Mon, 02/14/2011 - 10:10

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

Submitted by Anonymous (not verified) on Mon, 02/21/2011 - 14:26

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

Submitted by Anonymous (not verified) on Wed, 04/13/2011 - 15:09

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

Submitted by Anonymous (not verified) on Fri, 09/23/2011 - 00:24

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.

Submitted by Anonymous (not verified) on Thu, 11/10/2011 - 19:48

.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 ..

Submitted by Anonymous (not verified) on Fri, 11/25/2011 - 04:27

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

Submitted by Anonymous (not verified) on Fri, 11/25/2011 - 04:38

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

Submitted by lKidl on Mon, 01/23/2012 - 12:25

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

Submitted by Ippo Makonouchi (not verified) on Wed, 02/08/2012 - 21:24

thanks man! it helps a lot.. :)

Submitted by Anonymous (not verified) on Wed, 10/17/2012 - 15:57

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

Submitted by Mehr (not verified) on Wed, 10/24/2012 - 22:23

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

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

Submitted by Mai Nabil (not verified) on Sun, 06/16/2013 - 06:32

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

Submitted by bibi (not verified) on Mon, 11/17/2014 - 14:01

Hi sir. question lang po. any code to connect the SQL server management studio 2008 to Visual basic 2010. thanks sa reply .

Submitted by reymark talabi (not verified) on Tue, 01/27/2015 - 17:06

who want to help me doing a simple library system with a ms access of data base??

Add new comment

About text formats