how should i close my oledbconnection ?

Question

0.00/5 (No votes)

See more:

i have an oledb connection called myConnection declared inside a module. i use it in a form .i wish to know how to close the connection. should i close it in the module or form and how? please post the code.i am a beginner in vb.net and have recently learned about modules. i am open to all kinds of suggestion.thanks.

the code for the form is:

Imports System.Data<br />
Imports System.Data.OleDb<br />
<br />
<br />
Public Class Form1<br />
<br />
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click<br />
<br />
        Dim Str As String = "SELECT * FROM Login WHERE username='" & txtUsername.Text & "' AND password='" & txtPassword.Text & "'"<br />
        Dim cmd As OleDbCommand = New OleDbCommand(Str, DBconnection)<br />
        dr = cmd.ExecuteReader<br />
        If dr.HasRows Then<br />
            MsgBox("successfull")<br />
        Else : MsgBox("incorrect username or password")<br />
        End If<br />
<br />
    End Sub<br />
   <br />
End Class

the code for the module is:

Imports System.Data<br />
Imports System.Data.OleDb<br />
Module Module1<br />
<br />
<br />
    Friend conString As String<br />
    Public dr As OleDbDataReader<br />
<br />
<br />
    Public Function DBconnection() As OleDbConnection<br />
<br />
        Dim myConnection As New OleDb.OleDbConnection<br />
        conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = D:\vb programs\working copy for windows application 4\WindowsApplication4\student.mdb"<br />
        myConnection = New OleDb.OleDbConnection(conString)<br />
        myConnection.Open()<br />
        Return myConnection<br />
<br />
    End Function<br />
<br />
End Module<br />

Posted 29-Jun-13 9:13am

hlsc1983

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

SamFad · Accepted Answer · 2013-06-29T13:08:00

Solution 1

Close it in a module. You can use this code:
First move this line of code out of the function you created

VB

Public myConnection As New OleDb.OleDbConnection
conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = D:\vb programs\working copy for windows application 4\WindowsApplication4\student.mdb"
myConnection = New OleDb.OleDbConnection(conString)

and your function will be re-coded like this:

VB

Public Function DBconnection() As OleDbConnection
myConnection.Open()
Return myConnection

End Function

VB

Public sub CloseOleDB()
 Try
   myConnection.close()
 Catch ex as Exception
   'Add you error message here
 End Try
End Sub

You can then call CloseOleDB() within the form to close your connection.

Cheers!

Posted 29-Jun-13 13:08pm

SamFad

Comments

hlsc1983 30-Jun-13 8:50am

OK i moved the lines out of the function but i get an error with this line..
myConnection = New OleDb.OleDbConnection(conString). the error is 'declaration expected'

SamFad 30-Jun-13 9:34am

Recontruct your code like this...

Imports System.Data
Imports System.Data.OleDb

conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = D:\vb programs\working copy for windows application 4\WindowsApplication4\student.mdb"

Public myConnection As OleDbConnection= New OleDbConnection(conString)

Public Sub OpenOleDB()
Try
If myConnection.State = ConnectionState.Closed Then
myConnection.Open()
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "Database Connection Error!")
End Try
End Sub

Public Sub CloseOleDB()
myConnection.Close()
End Sub

Call OpenOleDB() whenever you want to open a connection and CloseOleDB() anytime you wish to close.

This should work perfectly.

hlsc1983 30-Jun-13 10:58am

thanks...i combined the two statements into one single statement
'Dim myConnection As New OleDb.OleDbConnection' and 'myConnection = New OleDb.OleDbConnection(conString)' into

' Public myConnection As New OleDb.OleDbConnection(connString)'
i dont get any error in this case.

i do however have more doubts.
is sub a better way than function to open a connection?

NanaKwame · Accepted Answer · 2013-07-07T15:03:00

Just use the connection variable in this way and it will work.

Dim connect as New Oledb.OledbConnection

connect.close()
or
connect.Dispose()

And you should place this code at the beginning if the connection was already
opened. After you closed the connection you reopen it to perform your action
after that you close it again.

Private Sub btnSubmit_Click.............btnSubmit.Click
connect.Dispose()
Try
...........
............
connect.Open()
............
..........
Catch ex As Exception
MsgBox(ex.Message)
End Try
connect.Dispose()
End Sub

Where btnSubmit is the name of the click button.