none
C#: How to check if label value is inside an Access file table? RRS feed

  • Question

  • For example, I have a label: lblUser.Text = Environment.UserName;  (workplace computer, employees have their own unique username)

    I also have an Access file: P:\Shared\TestProject\SampleFile.accdb   (Workplace shared drive, every group employee can access the drive)  One unrelated question to the subject line: If I encrypt this access file by using a password, can the program works fine when checking label value against Access table? 

    And there is a table inside the Access file: User_access  I will add each group member's username in the table (field name: Username ).

    I have a button, and how to write the code for comment portion?

    Thanks.

    private void btnLogin_Click(object sender, EventArgs e)
            {
                bool AuthorizedUser = false;
    
                //Check if lblUser.Text is in the access table User_access field Username. If it is True, then set AuthorizedUser = true;
    
                if (AuthorizedUser == false)
                {
                    MessageBox.Show("You do not have the permission to access.", "Error Message");
                    return;
                }
    
                if (AuthorizedUser == true)
                {
                    FormInterface fInterface = new FormInterface();
                    fInterface.Show();
                    this.Close();
                }
    
    
    
    
              }

    Monday, November 18, 2019 10:21 PM

Answers

  • Hi VA_er,

    If your Access file does not have password, you don't need to add any words in 'Pwd'.

    string connString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + "Dbq=D:\\Db1.accdb;Uid=Admin;Pwd=;"

    You can also use following connection string.

    string connString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + "Dbq=D:\\Db1.accdb;";

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by VA_er Monday, November 25, 2019 2:59 PM
    Friday, November 22, 2019 1:20 AM
    Moderator

All replies

  • Hello,

    Use this as a model for checking to see if the user is in the database table.

    public bool Login(string userName)
    {
        using (var cn = new OleDbConnection {ConnectionString = "TODO"})
        {
            using (var cmd = new OleDbCommand {Connection = cn })
            {
                cmd.CommandText = "SELECT UserName FROM User_access WHERE UserName = @UserName";
                cmd.Parameters.AddWithValue("@UserName", userName);
    
                cn.Open();
    
                var reader = cmd.ExecuteScalar();
                return reader != null;
            }
        }
    }
    In regards to encrypting the database, there is an old way and new way explained here. For the connection string see the following "with database password"


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, November 18, 2019 11:49 PM
    Moderator
  • Hi VA_er, 

    Thank you for posting here.

    If you encrypt the access file by using a password, you need to add ‘Jet OLEDB:Database Password= your Password;’ in your connect string.

    As Kareninstructor suggested, you can use the method to judge the value of AuthorizedUser.

    Here’s the whole code.

           private void btnLogin_Click(object sender, EventArgs e)
           {
                bool AuthorizedUser = false;
    
                AuthorizedUser = judge(lblUser.Text);
    
                if (AuthorizedUser == false)
                {
                    MessageBox.Show("You do not have the permission to access.", "Error Message");
                    return;
                }
    
                if (AuthorizedUser == true)
                {
                    FormInterface fInterface = new FormInterface();
                    fInterface.Show();
                    this.Close();
                }
            }
    
            public bool judge(string userName)
            {
                string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= your accdb file path;Persist Security Info=False;Jet OLEDB:Database Password= your password;";
                using(OleDbConnection myConn = new OleDbConnection(connString)){
    
                    myConn.Open();
                    using (OleDbCommand cmd = new OleDbCommand { Connection = myConn })
                    {
                        cmd.CommandText = "SELECT UserName FROM  User_access WHERE UserName = @UserName";
                        cmd.Parameters.AddWithValue("@UserName", userName);
                        var reader = cmd.ExecuteScalar();
                        return reader != null;
                    }            
                }
            }
    

    Hope it can help you.

    Best Regards,

    Xingyu Zhao



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 19, 2019 6:08 AM
    Moderator
  • Hi VA_er, 

    Thank you for posting here.

    If you encrypt the access file by using a password, you need to add ‘Jet OLEDB:Database Password= your Password;’ in your connect string.

    ..............

    Best Regards,

    Xingyu Zhao



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thank you very much. What do those errors mean? What does the code "cmd.Parameters.AddWithValue("@UserName", userName)" mean? Maybe I should use ODBC connection string?



    • Edited by VA_er Thursday, November 21, 2019 2:44 AM
    Thursday, November 21, 2019 2:40 AM
  • I tried to use ODBC, found another error. I use below standard security connection string, not sure if I need to update Admin ? I just replace the Dbq file path.

    Thanks.

    Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\mydatabase.accdb;
    Uid
    =Admin;Pwd=;


    • Edited by VA_er Thursday, November 21, 2019 3:04 AM
    Thursday, November 21, 2019 2:59 AM
  • Hi VA_er, 

    Thanks for your feedback.

    For your question, you need to add your accdb file path in ‘Dbq’, and if you encrypt the access file, you also need to add the password in ‘Pwd’.

    I make a simple test on my side, and here’s the code.

                string connString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + "Dbq=D:\\Db1.accdb;Uid=Admin;Pwd=your password;";
                using (OdbcConnection myConn = new OdbcConnection(connString))
                {
                    try 
                    {
                        myConn.Open();
                        return true;
                    } 
                    catch (Exception ex)
                    {
                        return false;
                    }                  
                }
    

    Result:

    Here’s my accdb file path:

    Hope it can help you.

    Best Regards,

    Xingyu Zhao



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 21, 2019 9:55 AM
    Moderator
  • Thanks. 

    Do you also need "Pwd=your password;" even if my Access file does not have password?

    Thursday, November 21, 2019 12:56 PM
  • Hi VA_er,

    If your Access file does not have password, you don't need to add any words in 'Pwd'.

    string connString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + "Dbq=D:\\Db1.accdb;Uid=Admin;Pwd=;"

    You can also use following connection string.

    string connString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + "Dbq=D:\\Db1.accdb;";

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by VA_er Monday, November 25, 2019 2:59 PM
    Friday, November 22, 2019 1:20 AM
    Moderator