vb.net re-connecting to database error
hi i know this isn't a linux related question but im having trouble re-connecting to an m$ access database, i wouldn't choose to do this so please no hate mail!!! its for a piece of c/w so could you please help me?? cheers
the error message i get is:
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
i think its something to do with not closing the connection correctly but im not sure how to go about closing the connection, and then re-opening it, please please help!!!! this is the code:
Public Const strConnection As String = _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=../database/database.mdb;Persist Security Info=False"
' Constructor of the OleDbConnection object
Private objConn As New System.Data.OleDb.OleDbConnection(strConnection)
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim strSQL, strEnteredUsername, strEnteredPassword, _
strPassword, strForename, strSurname, strTitle As String
If txt_sign_username.Text = "" Then
MsgBox("username cannot be blank!", vbCritical)
txt_sign_username.Select()
Exit Sub
End If
If txt_sign_password.Text = "" Then
MsgBox("password cannot be blank!", vbCritical)
txt_sign_password.Select()
Exit Sub
End If
'set username and password to variables
strEnteredUsername = txt_sign_username.Text
strEnteredPassword = txt_sign_password.Text
Dim objComm As New System.Data.OleDb.OleDbCommand(strSQL, objConn)
Dim objReader As System.Data.OleDb.OleDbDataReader
'retrive username from database
strSQL = "SELECT * FROM CustomerDetails WHERE Username ='" & strEnteredUsername & "'"
'set database reader (for some reason this code doesnt work when placed at
'the top of the function maybe something to do with the strSQL get???
objReader = objComm.ExecuteReader
If objConn.State = ConnectionState.Open Then
If objReader.Read() Then
' Valid username
strPassword = objReader.GetString(4)
Else
' Invalid username
MsgBox("user not found", MsgBoxStyle.Exclamation)
objReader.Close()
objReader = Nothing
objComm.Dispose()
Exit Sub
End If
End If
strTitle = objReader.GetString(5)
strForename = objReader.GetString(6)
strSurname = objReader.GetString(7)
If strPassword = strEnteredPassword Then
'correctly logged on!
lnk_signin.Text = "Sign Off"
signedOn = True
grp_signin.Visible = False
globalUser = txt_sign_username.Text
txt_sign_username.Text = ""
txt_sign_password.Text = ""
lnk_editdetails.Text = "Signed On as: " & strTitle & " " & strSurname & ", " & strForename & _
vbCrLf & "click here to edit details."
lnk_editdetails.Visible = True
Else
'Icorrect password
MsgBox("Inorrect password!", vbCritical)
txt_sign_password.Text = ""
txt_sign_password.Select()
End If
objReader.Close()
objReader = Nothing
objComm.Dispose()
End Sub
Private Sub lnk_editdetails_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles lnk_editdetails.LinkClicked
Dim testings As String
Dim strSQL As String
'strPassword, strTitle, strForename, strSurname, _
'strAddress, strAddress1, strAddress2, strAddress3, strPost, _
'strContactNumber, email As String
'retreve information from sql database
strSQL = "SELECT * FROM CustomerDetails WHERE Username ='" & globalUser & "'"
'set object reader to read from database
Dim objComm As New System.Data.OleDb.OleDbCommand(strSQL, objConn)
Dim objReader As System.Data.OleDb.OleDbDataReader
' objConn.Open(strConnection)
objReader = objComm.ExecuteReader
If objConn.State = ConnectionState.Open Then
'fill in registration fields for editing
'retrive username and password
txt_reg_username.Text = globalUser
txt_reg_password.Text = objReader.GetString(4)
'txt_reg_confirm.Text = txt_reg_password.Text
'cbo_reg_title.Text = objReader.GetString(5)
'retrive forename and surname
'txt_reg_county.Text = objReader.GetString(11)
'txt_reg_post1.Text = Mid$(objReader.GetString(8), 0, 3) 'split postcode
'txt_reg_post2.Text = Mid$(objReader.GetString(8), 4, 6) 'split postcode
Else
MsgBox("error connecting to database")
objReader.Close()
objReader = Nothing
objComm.Dispose()
Exit Sub
End If
objReader.Close()
objReader = Nothing
objComm.Dispose()
End Sub
thanks again very much!!
|