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.

In your first try you have some problems:
1) Each time you call the getDBConn() you obtain a New Connection, but... because is exactly the same as any previous call, the connection poll will try tu use the same.
2) When you issue the 'If setUsersOnline(username) = True Then' sentence, the the data reader still open. It should be closed before issuing any update to the DB.
3) On your first try, you miss to set the Insert, Update and Delete commands of the data adapter so, when you call dadap.Update(dtable) nothing can happen.
4) On your last try, nothing happens? Do you get an error message? Anyway I think is better to instantiate a new Sql Command in your setUsersOnline sub.

Hope this helps

I have changed by code as per your guidance, but it didn't help in resolving the problem. Is there any other way to go ahead. Changes made by me are given below for recheck. Since the OleDbConnection is already open at loginvalidate() sub. I am only passing on the reference of the connection to setUsersOnline() sub. Also I am not closing the OleDbConnection in the setUsersOnline() sub, since it is taken care of at the loginvalidate() sub.

My first way of dealing with setUsersOnline() sub goes like this- -----

Private Sub setUsersOnline(ByVal username As String, ByRef dbConn1 As OleDbConnection)
   SaveSetting(My.Application.Info.ProductName, "Settings", "LastLogIn", username)
   mySqlQuery = "SELECT USERNAME FROM DBUSERS"
   dadap = New OleDbDataAdapter(mySqlQuery, dbConn)
   Dim dset as Dataset = New DataSet()
   cmdBuilder = New OleDbCommandBuilder(dadap)
   dadap.Fill(dset, "DBUSERS")
   rowPosn = dset.Tables("DBUSERS").Rows.Count - 1
   Try
      dadap.UpdateCommand = cmdBuilder.GetUpdateCommand()
      If dset.Tables("DBUSERS").Rows(rowPosn)("USERNAME") = username Then
         dset.Tables("DBUSERS").Rows(rowPosn)("STATUS") = "ACTIVE"
         dadap.Update(dset)
         dset.AcceptChanges()
         MsgBox(username & " STATUS is UPDATED")
      End If
      If dtable.Rows(rowPosn)("USERNAME") = username And dtable.Rows(rowPosn)("STATUS") = "ACTIVE" Then
         MsgBox(username & " is ACTIVE - JUST TO CONFIRM")
      Else
         MsgBox("Failed to set " + username + " as ACTIVE")
      End If
   Catch ex As Exception
      MsgBox("Failed to set " + username + " as ACTIVE")
      Trace.Write(ex.ToString)
   End Try
   dtable.Dispose()
   dadap.Dispose()
   cmdBuilder.Dispose()
End Sub

My second way of dealing with setUsersOnline() sub goes like this. ......

Private Sub setUsersOnline(ByVal username As String, ByRef dbConn1 as OleDBConnection)
   SaveSetting(My.Application.Info.ProductName, "Settings", "LastLogIn", username)
   mySqlQuery = "UPDATE DBUSERS SET STATUS = 'ACTIVE' WHERE USERNAME='" +username+ "'"
   Dim trans as new OleDBTransaction = dbConn.BeginTransaction
   cmd = New OleDBCommand(mySqlQuery, dbConn1)
   cmd.Connection = dbConn1
   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()
End Sub

In this way of writing the code. The application is stopping to execute the method at cmd.ExecuteNonQuery() part.
-----------
Actually in my second attempt as explained initially & above, I had intentionally omitted the boolean part, both in the loginvalidate() as well as setusersonline() sub, since it is not necessary for me to return a boolean. My main purpose is to set the logged in user as 'ACTIVE'. Thats all.

Could anyone kindly let me know, how do i do that.

Thanks in advance.

The application is stopping to execute the method at cmd.ExecuteNonQuery() part

Sorry, what do you mean? Is just Hunging? Or is there any message?

On your las try you can change the lines

cmd = New OleDBCommand(mySqlQuery, dbConn1)
cmd.Connection = dbConn1
cmd.Transaction = trans
   Try
      cmd.CommandText = mySqlQuery
      cmd.ExecuteNonQuery()

by

cmd = New OleDBCommand(mySqlQuery, dbConn1, trans)
Try
   cmd.ExecuteNonQuery()

OK I have changed my code accordingly, but still its not working.

Private Sub setUsersOnline(ByVal username As String, ByRef dbConn1 as OleDBConnection)
      SaveSetting(My.Application.Info.ProductName, "Settings", "LastLogIn", username)
      mySqlQuery = "UPDATE DBUSERS SET STATUS = 'ACTIVE' WHERE USERNAME='" +username+ "'"
      Dim trans as new OleDBTransaction = dbConn.BeginTransaction
      cmd = New OleDBCommand(mySqlQuery, dbConn1, trans)
      Try
          cmd.ExecuteNonQuery()
          trans.Commit()
      Catch ex As Exception
          Trace.Write(ex.ToString)
          trans.Rollback()
      End Try
      trans.Dispose()
   End Sub

I have searched for help on the internet, but could not find one. What do I do now?

Whe you say

but still its not working.

can you be so kind to specify wich are the simptoms?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.