How to Retrieve data from MS access db to windows forms?

Question

I have made a simple login program that uses an access db that stores usernames and passwords. here is the code for the login button that opens the second form:

private void btnlogin_Click(object sender, EventArgs e)
    {
        con.Open();
        string login = "SELECT * FROM tbluser WHERE username= '" + txtusername.Text + "' and password= '" + txtpassword.Text + "'";
        cmd = new OleDbCommand(login,con);
        OleDbDataReader dr = cmd.ExecuteReader();

        if (dr.Read() == true)
        {
            new Form3().Show();
            this.Close();
        }
        else
        {
            MessageBox.Show("Invalid Username or Password", "Login failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
            txtpassword.Text = "";
            txtusername.Text = "";
        }
    }

Form3 has no code, its a blankform. How do display the name of the user that logged in on the blank form? Would i need a seperate table that has a foreign key that points to the user ID? If so how would I code this into the program?

Thanks

Your code will break if someone enters a username or password containing an apostrophe. — Dai, Feb 14 '22 at 14:30
_"access db that stores usernames and passwords"_ - stop right there. Do not go any further. DO NOT store clear-text Passwords. — Fildor, Feb 14 '22 at 14:32
Use [parametrized query](https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbcommand.parameters?view=dotnet-plat-ext-6.0&WT.mc_id=DT-MVP-5003235#examples). Also don't forget to close and [dispose the connection](https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbconnection?view=dotnet-plat-ext-6.0&WT.mc_id=DT-MVP-5003235#examples) when you no more need it, you can do it easily by a using statement. — Reza Aghaei, Feb 14 '22 at 14:35
First, do what @Fildor and Reza said, second for your Form3 .... use a constructor with a LoggedUser parameter — J.Salas, Feb 14 '22 at 14:42
@Fildor This program is just for me only, I understand you can encrypt or password protect access databases. This is the first program i have made with a DB, as im sure you can tell. — Stefan71, Feb 14 '22 at 14:45
I don't care if it's your first or 1000s ... Just take away that you never, never, never store plain text passwords. Not even "encrypted" ones. If you want to learn how to do this, there are plenty of lectures. Just don't do it even for a "fun project". — Fildor, Feb 14 '22 at 14:50
^^ "how to do this" meaning "how to handle passwords safely". If you need users in your fun "only for me project" - why do they need passwords in the first place? It's better to have them logged in if you remeber their name than implementing a password mechanism that's utterly unsafe. And you _will_ get used to that. And sooner or later, you will use password similar to your actual passwords.... you see, where I'm going, right? — Fildor, Feb 14 '22 at 15:14
@J.Salas Unfortunately adding a parameterized constructor to a `Form` subclass will break Visual Studio's WinForms designer. WinForms was designed in the very-early 2000s at a time when no-one really cared about constructors, and it was considered A-OK to have objects instantiated in an invalid state (which also explains why constructors are are still a PITA in C# today). — Dai, Feb 14 '22 at 17:02
@fildor I have now recreated the program. I scrapped the access db and instead used an SQL database instead. Is that a better way to go rather than access? — Stefan71, Feb 14 '22 at 17:45
Look the problem is not what DB you use or how "safe" it is itself. Reconstructable passwords are such a dangerous attack vector that quite literally no one in a professional setting is likely to use them any more. If you do not want to end up in the news some time in the future with a headline like "millions of user passwords stolen ..." then remember that something you don't keep cannot be stolen. — Fildor, Feb 15 '22 at 08:15

JonasH · Answer 1 · 2022-02-15T07:36:03.563

You will need an oledb provider for access. The current one would be Ace, and is provided in a redistributable or bundled with Office. Keep in mind that you need the x86 or x64 version depending on your program.

Unfortunately I do not have any usage examples at hand, so I would refer to the documentation exactly how you should use it.

That said, the only use case for Access I would recommend is to get legacy data out of it to convert to some other format. I have spent way to much time with various crashes and other issues with Access. SqLite seem to be a popular in-process alternative. While Postgres or MsSQL Express would be common classical databases. For very simple data, just using a plain file might be sufficient. This goes double if you are new to databases, you would be much better of learning how to use a real database than struggling with various Access issues. Look for articles about EntityFramework if you need some introduction.

And as mentioned in the comments, never store passwords in clear text, use proper password storage techniques. If this project is for learning you should learn the right way from the beginning. If it is just for personal usage you do not really need any passwords or other users.

The information about the Provider is not correct. `Jet.OleDb` and `ACE.OLEDB` are two different drivers, there's no 64bit version of the former. The latter can be used in place of the former in all scenarios, including old `.mdb` databases, CSV files, Excel Sheets pre or post Office 2007 etc. — Jimi, Feb 14 '22 at 16:49
_"the only use case for Access I would recommend is to get legacy data out of it to convert to some other format"_ - standing ovations, here! — Fildor, Feb 15 '22 at 08:17

How to Retrieve data from MS access db to windows forms?

1 Answers1