Hello folks, I am new to VB 2008 and right now I have been struggling a lot for updating access 2003 database from a VB 2008 desktop application. My problem is that I am unable to update the 'Status of the USER' to 'ACTIVE'. The problem is in the "setUsersOnline() method".
In the login screen, after the user provides username and password and presses the LOGIN button, my application is required to execute the following code (this is reqd to validate login and to set the user status 'ACTIVE'): -
Imports System.Data.OleDb
Public Class dbnModule
Dim loginResult, write As Boolean
Public dbusers, usersOnline As ArrayList
Dim i As Integer
Dim mySqlQuery As String
Dim cmd, cmd2 As OleDbCommand
Dim trans As OleDb.OleDbTransaction
Dim myDataRdr As OleDbDataReader
Dim dset As DataSet
Dim dtable As DataTable
Dim dadap As OleDbDataAdapter
Dim dbConn1, dbConn As OleDbConnection
Dim param, param1 As OleDbParameter
Private cmdBuilder As OleDbCommandBuilder
Dim rowPosn As Integer
Dim drow As DataRow
Private Function getDBConn()
dbConn1 = New OleDbConnection(My.Settings.dbUsersConnectionString)
Return dbConn1
End Function
Public Sub dbDisconnect()
dbConn = getDBConn()
If dbConn.State = System.Data.ConnectionState.Open Or dbConn.State = ConnectionState.Broken Then
dbConn = Nothing
dbConn.Close()
dbConn.Dispose()
End If
End Sub
Public Function loginValidate(ByVal username As String, ByVal password As String)
dbConn = getDBConn()
Try
mySqlQuery = "SELECT * FROM DBUSERS WHERE USERNAME = '" & username & "' AND PASSWORD = '" & password & "'"
cmd = New OleDbCommand(mySqlQuery, dbConn)
If dbConn.State = System.Data.ConnectionState.Open Or dbConn.State = ConnectionState.Broken Then
dbConn.Close()
End If
dbConn.Open()
myDataRdr = cmd.ExecuteReader
If (myDataRdr.Read() = True) Then
If setUsersOnline(username) = True Then
MsgBox("User set ACTIVE")
Else
MsgBox("Sorry failed to set user active")
End If
loginResult = True
Else
loginResult = False
End If
dbDisconnect()
Catch ex As Exception
MsgBox(ex.ToString)
dbDisconnect()
End Try
Return loginResult
End Function
Private Function setUsersOnline(ByVal username As String)
Dim state As Boolean
SaveSetting(My.Application.Info.ProductName, "Settings", "LastLogIn", username)
dbConn = getDBConn()
mySqlQuery = "SELECT USERNAME FROM DBUSERS"
dbConn.Open()
dadap = New OleDbDataAdapter(mySqlQuery, dbConn)
dtable = New DataTable()
cmdBuilder = New OleDbCommandBuilder(dadap)
dadap.Fill(dtable)
rowPosn = dtable.Rows.Count - 1
Try
If dtable.Rows(rowPosn)("USERNAME").ToString = username Then
dtable.Rows(rowPosn)("STATUS") = "ACTIVE"
dtable.AcceptChanges()
dadap.Update(dtable)
MsgBox(username & " STATUS is UPDATED")
End If
If dtable.Rows(rowPosn)("USERNAME") = username And dtable.Rows(rowPosn)("STATUS") = "ACTIVE" Then
MsgBox(username & " is ACTIVE 2")
Else
MsgBox(username & " is NOT ACTIVE 3")
End If
state = True
Catch ex As Exception
Trace.Write(ex.ToString)
state = False
End Try
dbDisconnect()
dtable.Dispose()
dadap.Dispose()
Return state
End Function
End Class
I have also tried the setUsersOnline() method with following code
Private Sub setUsersOnline(ByVal username As String)
SaveSetting(My.Application.Info.ProductName, "Settings", "LastLogIn", username)
dbConn = getDBConn()
mySqlQuery = "UPDATE DBUSERS SET STATUS = 'ACTIVE' WHERE USERNAME='" +username+ "'"
dbConn.Open()
Dim trans as new OleDBTransaction = dbConn.BeginTransaction
cmd.Connection = dbConn
cmd.Transaction = trans
Try
cmd.CommandText = mySqlQuery
cmd.ExecuteNonQuery()
trans.Commit()
Catch ex As Exception
Trace.Write(ex.ToString)
trans.Rollback()
dbDisconnect()
End Try
trans.Dispose()
dbDisconnect()
End Sub
Solving this has become a very essential thing for me.
Therefore, Any help in solving the problem is highly appreciated.
Thanks in advance.