Member Avatar for kayjenx

I have a database I am creating with about 50 users over a network. There are about 6 groups of users and these users are spread across 10 branches. I want to block/allow users from particular forms and views. For example I want branch users forms to be filtered by their branch number but depending on the group they are in they can only see some forms. i.e. 50 users, 10 branches, each branch has about 5 ppl of where 1 is in group A and 4 are in group B. I want all users that belong to branch 1 to see the information they enter, but Group B enter data but cannot approved or delete, and group A can enter, approve and delete. So then. On my login form I have the user type his/her username but the i want LEAVE function to update 2 textbox where I have named GroupName and BranchNo. Basically to run a query and look for the username and return in one textbox that user's groupname and the other textbox the user's branch no. This information will be used to filter forms and block the user from certain controls. This is the code I have so far but still can't figure how to display the results.

Private Sub UsernameTextBox_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles UsernameTextBox.Leave
        'Make Connection to database
        Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Atlanet\loantracking\CTS_be.mdb")
        'Query information for Branch Number
        Dim BranchNumber As OleDbCommand = New OleDbCommand("SELECT Branch_Number From MSysUserList WHERE Username like '" & UsernameTextBox.Text & "'")
        BranchNo.Text = BranchNumber
        'Query information for User Group
        Dim UserGroup As OleDbCommand = New OleDbCommand("SELECT UserGroup From MSysUserList WHERE Username like '" & UsernameTextBox.Text & "'")
        UserGroupTextBox.Text = UserGroup
    End Sub

It keeps highlighting the BranchNumber and UserGroup and giving error "Value of type 'String' cannot be converted to System.Data.OledbCommand

What am I doing wrong?

  • 2 Contributors
  • 2 Replies
  • 218 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by kayjenx

Dani AI

Generated

Quick follow-up to the working fix from : opening the connection was the immediate missing piece, but there are a few reliable improvements to make this robust and safe for a multi-user app.

Use one parameterized query to fetch both columns, dispose objects with Using blocks, and guard against no-row/DBNull results. Parameterized queries (OleDb uses positional ? parameters) prevent accidental SQL issues and odd behavior from user input. Prefer = for an exact username match (use LIKE only with wildcards). Also trim the username before sending it to the database and make sure the Username column is indexed/unique so you get a single row back.

' Example pattern (replace YourConnectionString and YourUserTable)
Dim connString As String = "YourConnectionString"
Using con As New OleDbConnection(connString)
    Using cmd As New OleDbCommand("SELECT TOP 1 Branch_Number, UserGroup FROM YourUserTable WHERE Username = ?", con)
        cmd.Parameters.AddWithValue("?", UsernameTextBox.Text.Trim())
        con.Open()
        Using rdr As OleDbDataReader = cmd.ExecuteReader()
            If rdr.Read() Then
                BranchNo.Text = If(IsDBNull(rdr("Branch_Number")), String.Empty, rdr("Branch_Number").ToString())
                UserGroupTextBox.Text = If(IsDBNull(rdr("UserGroup")), String.Empty, rdr("UserGroup").ToString())
            Else
                BranchNo.Clear()
                UserGroupTextBox.Clear()
            End If
        End Using
    End Using
End Using

Extra notes and troubleshooting

  • Check for Nothing/DBNull before calling ToString to avoid runtime errors.
  • Avoid keeping permissions in editable controls; store branch/group in a read-only user object or module-level variable and use central helper methods to enable/disable UI elements.
  • For responsiveness, run DB lookups off the UI thread (BackgroundWorker/Task).
  • If using Jet (Microsoft.Jet.OLEDB.4.0) on modern Windows, either compile x86 or use the ACE provider. If the app will be used concurrently by many people, consider moving to a server DB (SQL Server) to avoid MDB file locking and concurrency problems.

These steps will make the login lookup reliable, safer, and easier to reuse across forms.

Recommended Answers

All 2 Replies

Member Avatar for Renukavani

Getting error because You are trying to assign the command into control text. Try below set of statements and Red color are modified

'Make Connection to database        
            Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Atlanet\loantracking\CTS_be.mdb")        
            'Query information for Branch Number        
            Dim BranchNumber As OleDbCommand = New OleDbCommand("SELECT Branch_Number From MSysUserList WHERE Username like '" & UsernameTextBox.Text & "'" ,con)        
            
            BranchNo.Text = BranchNumber.ExecuteScalar().ToString()        
            'Query information for User Group        
            Dim UserGroup As OleDbCommand = New OleDbCommand("SELECT UserGroup From MSysUserList WHERE Username like '" & UsernameTextBox.Text & "'" ,con)        
            UserGroupTextBox.Text = UserGroup.ExecuteScalar().ToString()
Member Avatar for kayjenx

Getting error because You are trying to assign the command into control text. Try below set of statements and Red color are modified

'Make Connection to database        
            Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Atlanet\loantracking\CTS_be.mdb")        
 con.Open()           
'Query information for Branch Number        
            Dim BranchNumber As OleDbCommand = New OleDbCommand("SELECT Branch_Number From MSysUserList WHERE Username like '" & UsernameTextBox.Text & "'" ,con)        
            
            BranchNo.Text = BranchNumber.ExecuteScalar().ToString()        
            'Query information for User Group        
            Dim UserGroup As OleDbCommand = New OleDbCommand("SELECT UserGroup From MSysUserList WHERE Username like '" & UsernameTextBox.Text & "'" ,con)        
            UserGroupTextBox.Text = UserGroup.ExecuteScalar().ToString()

This worked but only after I added code to open the connection. See in GREEN.

Thanks so much for your help.

Edited by kayjenx because: n/a
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.