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
'Set up connection string
Dim cnString As String
txtStatus.Text = "Connecting to database using MS Access" & vbCrLf & vbCrLf
cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\Northwind.mdb"
'Create connection
Dim conn As OleDbConnection = New OleDbConnection(cnString)
Try
' Open connection
conn.Open()
txtStatus.Text = txtStatus.Text & "Connection opened" & vbCrLf
Catch ex As SqlException
txtStatus.Text = "Error: " & ex.ToString & vbCrLf
Finally
' Close connection
conn.Close()
txtStatus.Text = txtStatus.Text & "Connection closed."
End Try
2. Using SqlConnection – Microsoft SQL Server
'Set up connection string
Dim cnString As String
txtStatus.Text = "Connecting to database using SQL Server" & vbCrLf & vbCrLf
cnString = "Data Source=localhost;Integrated Security=True"
'Create connection
Dim conn As SqlConnection = New SqlConnection(cnString)
Try
' Open connection
conn.Open()
txtStatus.Text = txtStatus.Text & "Connection opened" & vbCrLf
Catch ex As SqlException
txtStatus.Text = txtStatus.Text & "Error: " & ex.ToString & vbCrLf
Finally
' Close connection
conn.Close()
txtStatus.Text = txtStatus.Text & "Connection closed."
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:
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:
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
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.
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???..
GoodDay!!!
...admin...
can we have your contact?..
pls....
are familiar on Wireless Monitoring???..
pls........
response k nmn po agad!!!
salamat po....
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
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..
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]
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
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.
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]
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
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