Geek asks and answers

An unhandled exception of type "System.Data.OleDb.OleDbException" occurred in System.Data.dll C #

i already created window forms that the function should add to the database. But I have a problem. When I enter a number like this “SM0001” in the “Product Code” column and press “Enter”, it will save the data in the database, and when I type the same number as before, it does not bother the user with the entered “Product Code” "already exists in the database. So this is my current database (displayed in a datagridview on the system):

enter image description here

As you can see, line "1" and line "2" have the same "product code". My question is: how can I prevent a user from entering the same number twice?

i have already changed the Primary key in the database to "Product Code", but here is the error I get:

Error:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

Error included:

cmd.ExecuteNonQuery();

for this function:

private void AddDatabase(object sender, EventArgs e)
        {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                string query = "INSERT INTO [Table] ([ProductCode], [Description], [Price]) VALUES (@ProductCode, @Description, @Price)";
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand(query, conn))
                {
                    cmd.Parameters.Add("@ProductCode", System.Data.OleDb.OleDbType.VarChar);
                    cmd.Parameters["@ProductCode"].Value = this.numericTextBox1.Text;
                    cmd.Parameters.Add("@Description", System.Data.OleDb.OleDbType.VarChar);
                    cmd.Parameters["@Description"].Value = this.textBox3.Text;
                    cmd.Parameters.Add("@Price", System.Data.OleDb.OleDbType.Integer);
                    cmd.Parameters["@Price"].Value = this.textBox4.Text;
                    cmd.ExecuteNonQuery(); // The error is here
                    if (_choice.comboBox1.Text == "English")
                    {
                        System.Media.SoundPlayer _sound = new System.Media.SoundPlayer(@"C:\Windows\Media\Windows Exclamation.wav");
                        _sound.Play();
                        DialogResult _dialogResult = MessageBox.Show("Added Successfully!", "Success", MessageBoxButtons.OK);
                        if (_dialogResult == DialogResult.OK)
                        {
                            ViewDatabase(sender, e);
                            ClearTextBoxes(sender, e);
                        }
                    }
                }
                conn.Close();
            }
        }

I would like, when the user enters the same "Product Code", a window will appear with a message stating that the entered "Product Code" is not allowed, because it exists in the database and does not give an error (program termination).

How to fix it?

thank

Your answer will be very grateful!

+3
source share
2 answers

The error you get is quite normal. You cannot insert duplicates in the primary key (and also cannot contain the "NULL" column). More information about primary keys:

W3schools - primary key

database.about.com

AddDatabase , . .

  • SELECT TOP 1 ProductCode FROM Table WHERE ProductCode = 'this.numericTextBox1.Text'"

    , ,

  • datagridview

    datagridview , , / . ProductCode. . ( , )

    ,

  • Datagridview

    datagridview , , , .

    -

     foreach (DataGridViewRow row in dgvDataGridView.Rows)
      {
      if(row.Cells[0].Value.toString().equals(this.numericTextBox1.Text))

           { 

           // Productcode is already present
           // Throw message/exception Or whatever
           break; 
           }
      }

1, datagridview/datasource / Table

+2

INSERT , ProductCode (, , ).

- (/ , )

 var command = new OleDbCommand("SELECT COUNT(*) FROM results WHERE id = ?", _connection);
 command.Parameters.Add("id", OleDbType.Date).Value = id;
 if ((int)command.ExecuteScalar() == 0)
 {
     // not exists
 }

: AddDatabase , , AddDatabase .

2 , Exists(id) Add(...), , :

if(Database.Exists(id))
{
    // show errror
    return;
}
DataBase.Add(...);
+1

More articles:


All Articles