LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 04-21-2005, 11:37 AM   #1
trscookie
Member
 
Registered: Apr 2004
Location: oxford
Distribution: gentoo
Posts: 438

Rep: Reputation: 30
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!!
 
Old 04-21-2005, 04:14 PM   #2
hspc
LQ Newbie
 
Registered: Apr 2005
Location: Egypt
Distribution: Ubuntu
Posts: 8

Rep: Reputation: 0
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.
 
Old 04-21-2005, 04:53 PM   #3
trscookie
Member
 
Registered: Apr 2004
Location: oxford
Distribution: gentoo
Posts: 438

Original Poster
Rep: Reputation: 30
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??
 
Old 05-02-2005, 11:39 AM   #4
trscookie
Member
 
Registered: Apr 2004
Location: oxford
Distribution: gentoo
Posts: 438

Original Poster
Rep: Reputation: 30
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
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
connecting to a MySQL database hsa Linux - General 3 09-16-2005 06:34 AM
probs with connecting to mysql database using php ankscorek Linux - Software 4 08-16-2005 07:40 PM
connecting to MYSQL remote database opsraja Linux - Software 1 02-18-2005 05:52 AM
Connecting Mysql Database on remote server akamol Programming 4 10-16-2003 11:46 AM
Connecting a Postgres Database from a Java application Paulo Ges Programming 4 08-23-2003 10:08 AM


All times are GMT -5. The time now is 12:53 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration