CODEWITHSUNDEEP
c#winformsms-accessdatagridview
Kaoru
Kaoru

Reputation: 2883

Prevent the user entering the same number twice in database c#

i already created the windows forms that the function is to add the information to the database. But, i have a problem. When i type the number like this one "SM0001" in the "Product Code" column, and hit enter, it store the data to the database and when i type the same number like i typed before, it does not prevent the user like the entered "Product Code" already exists in the database. So, this is my currently database (displayed in the datagridview in the system):

enter image description here

As you can see the row "1" and row "2" has the same "Product Code".. My question is: How do i prevent the user to entering the same number twice?

Here is the code that i am using to store to the database:

private void AddDatabase(object sender, EventArgs e)
        {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                string query = "INSERT INTO [Record] ([ProductCode], [Quantity], [Description], [SubTotal], [Total], [IssuedBy], [To], [Dates], [Times]) VALUES (@ProductCode, @Quantity, @Description, @SubTotal, @Total, @IssuedBy, @To, @Dates, @Times)";
                string _query = "INSERT INTO [TransRecord] ([ProductCode], [Quantity], [Description], [SubTotal], [Total], [IssuedBy], [To], [Dates], [Times]) VALUES (@ProductCode, @Quantity, @Description, @SubTotal, @Total, @IssuedBy, @To, @Dates, @Times)";

                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("@Quantity", System.Data.OleDb.OleDbType.Integer);
                    cmd.Parameters["@Quantity"].Value = Convert.ToInt32(this.numericUpDown1.Value);

                    cmd.Parameters.Add("@Description", System.Data.OleDb.OleDbType.VarChar);
                    cmd.Parameters["@Description"].Value = this.textBox5.Text;

                    cmd.Parameters.Add("@SubTotal", System.Data.OleDb.OleDbType.Integer);
                    cmd.Parameters["@SubTotal"].Value = Convert.ToInt32(this.numericTextBox2.Text);

                    cmd.Parameters.Add("@Total", System.Data.OleDb.OleDbType.Integer);
                    cmd.Parameters["@Total"].Value = Convert.ToInt32(this.numericTextBox3.Text);

                    cmd.Parameters.Add("@IssuedBy", System.Data.OleDb.OleDbType.VarChar);
                    cmd.Parameters["@IssuedBy"].Value = this.textBox1.Text;

                    cmd.Parameters.Add("@To", System.Data.OleDb.OleDbType.VarChar);
                    cmd.Parameters["@To"].Value = this.textBox2.Text;

                    cmd.Parameters.Add("@Dates", System.Data.OleDb.OleDbType.Date);
                    cmd.Parameters["@Dates"].Value = this.textBox3.Text;

                    cmd.Parameters.Add("@Times", System.Data.OleDb.OleDbType.DBTimeStamp);
                    cmd.Parameters["@Times"].Value = DateTime.Now.ToShortTimeString();

                    cmd.ExecuteNonQuery();

                    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("@Quantity", System.Data.OleDb.OleDbType.Integer);
                        _cmd.Parameters["@Quantity"].Value = Convert.ToInt32(this.numericUpDown1.Value);

                        _cmd.Parameters.Add("@Description", System.Data.OleDb.OleDbType.VarChar);
                        _cmd.Parameters["@Description"].Value = this.textBox5.Text;

                        _cmd.Parameters.Add("@SubTotal", System.Data.OleDb.OleDbType.Integer);
                        _cmd.Parameters["@SubTotal"].Value = Convert.ToInt32(this.numericTextBox2.Text);

                        _cmd.Parameters.Add("@Total", System.Data.OleDb.OleDbType.Integer);
                        _cmd.Parameters["@Total"].Value = Convert.ToInt32(this.numericTextBox3.Text);

                        _cmd.Parameters.Add("@IssuedBy", System.Data.OleDb.OleDbType.VarChar);
                        _cmd.Parameters["@IssuedBy"].Value = this.textBox1.Text;

                        _cmd.Parameters.Add("@To", System.Data.OleDb.OleDbType.VarChar);
                        _cmd.Parameters["@To"].Value = this.textBox2.Text;

                        _cmd.Parameters.Add("@Dates", System.Data.OleDb.OleDbType.Date);
                        _cmd.Parameters["@Dates"].Value = this.textBox3.Text;

                        _cmd.Parameters.Add("@Times", System.Data.OleDb.OleDbType.DBTimeStamp);
                        _cmd.Parameters["@Times"].Value = DateTime.Now.ToShortTimeString();

                        _cmd.ExecuteNonQuery();
                    }

                    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);

                            ClearTextBox(sender, e);
                        }
                    }

                    dataGridView1.ClearSelection();
                }

                conn.Close();
            }
        }

Upvotes: 0

Views: 1400

Answers (3)

Damith
Damith

Reputation: 63065

you can make your column as primary or unique, then when you try to insert you will get exception.

if you can't change the database you can check for the ProductCode exist before insert like below

Select count(*) from [Record] where ProductCode =@ProductCode

set the parameter and run cmd.ExcuteScaler(), based on return value you can show the message like "Product Code" already exists or insert 

conn.Open();
using (OleDbCommand cmd = new OleDbCommand("Select count(*) from  [Record] where ProductCode =@ProductCode", conn))
{
   cmd.Parameters.AddWithValue("@ProductCode",this.numericTextBox1.Text);
   object count = command.ExecuteScalar();
   int intcount;
   if(count !=null && int.TryParse(count.Tostring(), out intcount) && intcount >0)
   {
     // Product Code already exists, you can show error message to user 
     return;
   }
}

// your code ..
string query = "INSERT INTO [Record] ([ProductCode], [Quantity], [Description], [SubTotal], [Total], [IssuedBy], [To], [Dates], [Times]) VALUES (@ProductCode, @Quantity, @Description, @SubTotal, @Total, @IssuedBy, @To, @Dates, @Times)";
string _query = "INSERT INTO [TransRecord] ([ProductCode], [Quantity], [Description], [SubTotal], [Total], [IssuedBy], [To], [Dates], [Times]) VALUES (@ProductCode, @Quantity, @Description, @SubTotal, @Total, @IssuedBy, @To, @Dates, @Times)";

using (OleDbCommand cmd = new OleDbCommand(query, conn))

Upvotes: 1

Kenny
Kenny

Reputation: 1054

Use SQL Server EXISTS syntax.

IF NOT EXISTS (SELECT * FROM Recrd WHERE (PRoductCode  = @ProductCode))
BEGIN
    INSERT INTO [Record] ([ProductCode], [Quantity], [Description], [SubTotal], [Total], [IssuedBy], [To], [Dates], [Times]) VALUES (@ProductCode, @Quantity, @Description, @SubTotal, @Total, @IssuedBy, @To, @Dates, @Times)


    END
    ELSE
    BEGIN
do an update or give me some message
    END

Ok, for Access. I am not great with it but I think something like this will work:

int intNumRows; 
string query = "SELECT COUNT(*) FROM [Record ] WHERE PRoductCode = @ProductCode "; 
objCmd = new OleDbCommand(strSQL, objConn); 
intNumRows = Convert.ToInt32(objCmd.ExecuteScalar()); 
if(intNumRows > 0) 
{ 
this.lblStatus.Text = "Record already exists for this customer."; 
} 
else
{ 
 string query = "INSERT INTO [Record] ([ProductCode], [Quantity], [Description], [SubTotal], [Total], [IssuedBy], [To], [Dates], [Times]) VALUES (@ProductCode, @Quantity, @Description, @SubTotal, @Total, @IssuedBy, @To, @Dates, @Times)";
                string _query = "INSERT INTO [TransRecord] ([ProductCode], [Quantity], [Description], [SubTotal], [Total], [IssuedBy], [To], [Dates], [Times]) VALUES (@ProductCode, @Quantity, @Description, @SubTotal, @Total, @IssuedBy, @To, @Dates, @Times)";

---
---

Upvotes: 2

azpc
azpc

Reputation: 690

Depending on the underlying database create an index on the desired column with unique = true

Upvotes: 1

Related Questions