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

Hi
1-try to put the error-causing line in a Try / Catch block and check the exception's Message property
2-seems you don't open the connection at all before openning the datareader.

the first connection (the function 'Function connected') connects and i can retrieve details from it but when i try the next function ('Private Sub lnk_editdetails_LinkClicked') it fails on the line txt_reg_password.Text = objReader.GetString(4), but it has already returned the password in the previous function so im not closing it in between. i just cant figure it out!!! doesnt a try catch function just stop the programing from abruptly ending i tryed it but it doesnt return that anything is wrong, the error that i get is at the top of the last thread, have you got any other ideas, someone mentioned that i have to make doubly sure that i close the connection other wise this would happen. but im not sure how to closes it and msdn is about as useful as a chocolate tea-pot.
how would you go about closing that connection??

does anyone know how to find out what ROW a username reside on from a database? help is much apriciated, thanks.

this is what i have got so far:

Function DelUserRow()
Dim strConn As String = _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=../database/database.mdb;Persist Security Info=False"
Dim objConn As New System.Data.OleDb.OleDbConnection(strConn)
Dim strSQL As String = "SELECT * FROM CustomerDetails"
Dim objData As New OleDb.OleDbDataAdapter(strSQL, strConn)
Dim objDataSet As New DataSet
Dim objTable As New DataTable
Dim intCurr As Integer
Dim curRow As Long
Dim i As Integer


Try
objConn.Open()
Catch ex As Exception
MsgBox("Error trying to open database!")
End Try

Dim updateCommand As New OleDb.OleDbCommandBuilder(objData)

objData.Update(objDataSet, "AllCustomers")



For i% = 0 To (objTable.Rows.Count - 1)
curRow = objTable.Rows(intCurr).Item("UserID")

Next
Dim objRow As DataRow = objTable.Select("UserID=" & curRow)(0)

objRow.Delete()

objData.Update(objDataSet, "AllCustomers")

MsgBox("Data erased")
End Function