Obtain ID of corresponding record after a successfully adding the record

Question

1.00/5 (1 vote)

See more:

Am working with Access as Database in C# (Visual Studio 15). I want to save form entries (add record) into the Access Database and would want the corresponding ID of the record to show in MsgBox upon a successful saving. ‎

What I have tried:

private void Form21_Load(object sender, EventArgs e)
        {
            try
            {
                connection.Open();
                checkConnection.Text = "Server Connection, Successful";
                connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error, Server not connected " + ex);
            }
        }
        

        private void Button6_Click(object sender, EventArgs e)
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = "insert into Students_File ([YourNames],[Nationality],[StateOfOrigin],[PlaceOfBirth],[DoB],[HomeAddress],[LastSchools1],[LastClass1],[LastSchools2],[LastClass2],[LastSchools3],[LastClass3],[AdmClass],[CurrentClass],[Guidian],[GuardianContact],[UserName],[PassWord],[Gender],[RegistrationDate]) values('" + YourNames.Text + "','" + Nationality.Text + "','" + StateOfOrigin.Text + "','" + PlaceOfBirth.Text + "','" + DoB.Text + "','" + HomeAddress.Text + "','" + LastSchools1.Text + "','" + LastClass1.Text + "','" + LastSchools2.Text + "','" + LastClass2.Text + "','" + LastSchools3.Text + "','" + LastClass3.Text + "','" + AdmClass.Text + "','" + CurrentClass.Text + "','" + Guidian.Text + "','" + GuardianContact.Text + "','" + UserName.Text + "','" + PassWord.Text + "','" + Gender.Text + "','" + RegistrationDate.Text + "')";
            command.ExecuteNonQuery();
            //MessageBox.Show("Congrats! Your registration, is successful. You may now click close button, then proceed to login");

            command.CommandText = "Select * from Students_File where UserName='" + UserName.Text + "' and PassWord='" + PassWord.Text + "'";
            OleDbDataReader reader = command.ExecuteReader();
            int count = 0;
            while (reader.Read())
            {
                count = count + 1;
            }
            if (count == 1)
            {
                MessageBox.Show("Congrats! Your registration, is successful. You may now click close button, then proceed to login");
                this.Close();
            }
            else if (count > 1)
            {
                MessageBox.Show("Sorry, the chosen username or password is currently existing or picked by another user. Consequently, your registration was not successful. Do please, decide another but a unique one. Thank you");
            }
            connection.Close();
        }

Posted 23-Feb-21 2:18am

Member 15047809

Updated 23-Feb-21 2:28am

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2021-02-23T02:28:00

First off, don't do it like that. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Secondly, Never store passwords in clear text - it is a major security risk. There is some information on how to do it here: Password Storage: How to do it.[^]

And remember: if you have any European Union users then GDPR applies and that means you need to handle passwords as sensitive data and store them in a safe and secure manner. Text is neither of those and the fines can be .... um ... outstanding. In December 2018 a German company received a relatively low fine of €20,000 for just that.

Thirdly, checking after you insert a row if it's valid or not is a poor idea - it fills your DB up with "bad data" and you may not have any idea which is the "correct row". Check if a username is in use before you try to add a new user, and never assume that passwords make a user unique: passwords have to be changeable, if only because the security on some sites and apps is so poor that they will leak out ...

Fourthly, do yourself a favour, and stop using Visual Studio default names for everything - you may remember that "TextBox8" is the mobile number today, but when you have to modify it in three weeks time, will you then? Use descriptive names - "tbMobileNo" for example - and your code becomes easier to read, more self documenting, easier to maintain - and surprisingly quicker to code because Intellisense can get to to "tbMobile" in three keystrokes, where "TextBox8" takes thinking about and 8 keystrokes... "Buutton6" is bad enough, but "Form21"? That implies your app has a least 20 other anonymous forms!

Finally, see here: SELECT @@IDENTITY with MS-Access [^]

Obtain ID of corresponding record after a successfully adding the record

1 solution

Solution 1

Add your solution here

Preview 0

Obtain ID of corresponding record after a successfully adding the record

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us