Errors in inserting data to database

Question

I'm doing a project to develop a library management system to a library. In it, I'm registering students by saving their details to the database. When a record is inserted at first it is saved without the given membership number.

And then, when I start to save the next student details, I get an error message saying "Violation of PRIMARY KEY constraint."

After I click the OK button in the message and try to save the data once more, I get a message saying, "Connection was not closed".

Although I try to find unclosed connection, I can't find one in my code.Here is the code.

        try
        {
            if (rbtnMale.Checked == true)
            {
                Gender = "Male";
            }
            else if (rbtnFemale.Checked == true)
            {
                Gender = "Female";
            }

            if (cmbMemNo.Visible == true)
            {
                String insert_query = "INSERT INTO StReg VALUES('" + cmbMemNo.Text + "','" + txtFName.Text + "','" + txtName.Text + "','" + Gender + "','" + dtpDOB.Text + "','" + txtTelNo.Text + "','" + txtSchool.Text + "','" + txtAdNo.Text + "','" + txtMom.Text + "','" + txtMomOcc.Text + "','" + txtDad.Text + "','" + txtDadOcc.Text + "')";
                Con.Open();
                Cmd = new SqlCommand(insert_query, Con);
                Cmd.ExecuteNonQuery();
                Con.Close();
                MessageBox.Show("The new Student " + txtName.Text + "( S-" + cmbMemNo.Text + " ) has successfully inserted into the system!!!", "INSERTED!!!", MessageBoxButtons.OK, MessageBoxIcon.Information);
                Clear();
            }
            else if (txtMemNo.Visible == true)
            {
                String insert_query = "INSERT INTO StReg VALUES('" + cmbMemNo.Text + "','" + txtFName.Text + "','" + txtName.Text + "','" + Gender + "','" + dtpDOB.Text + "','" + txtTelNo.Text + "','" + txtSchool.Text + "','" + txtAdNo.Text + "','" + txtMom.Text + "','" + txtMomOcc.Text + "','" + txtDad.Text + "','" + txtDadOcc.Text + "')";
                Con.Open();
                Cmd = new SqlCommand(insert_query, Con);
                Cmd.ExecuteNonQuery();
                Con.Close();
                MessageBox.Show("The new Student " + txtName.Text + "( S-" + cmbMemNo.Text + " ) has successfully inserted into the system!!!", "INSERTED!!!", MessageBoxButtons.OK, MessageBoxIcon.Information);
                Clear();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error while Inserting details to the Database!!!" + Environment.NewLine + ex);
        }

This is my table design.

And here is my table definition code.

CREATE TABLE [dbo].[StReg]
(
    [MemNo]   VARCHAR(12)  NOT NULL,
    [FName]   VARCHAR(MAX) NOT NULL,
    [Name]    VARCHAR(50)  NOT NULL,
    [Gender]  VARCHAR(6)   NOT NULL,
    [DOB]     DATE         NOT NULL,
    [TelNo]   VARCHAR(10)  NULL,
    [School]  VARCHAR(50)  NOT NULL,
    [AdNo]    VARCHAR(10)  NOT NULL,
    [MomName] VARCHAR(50)  NOT NULL,
    [MomOcc]  VARCHAR(50)  NOT NULL,
    [DadName] VARCHAR(50)  NOT NULL,
    [DadOcc]  VARCHAR(50)  NOT NULL,

    PRIMARY KEY CLUSTERED ([MemNo] ASC)
);

No matter how hard I try to find any error, I still cannot figure it out. Please help!

Answer 1

I suggest you to set primary key as auto increment.And no need to insert primary key value when it is auto generating

Create you table is like this

CREATE TABLE [dbo].[StReg]
(
    [MemNo]   int identity(1,1)  PRIMARY KEY  NOT NULL,
    [FName]   VARCHAR(MAX) NOT NULL,
    [Name]    VARCHAR(50)  NOT NULL,
    [Gender]  VARCHAR(6)   NOT NULL,
    [DOB]     DATE         NOT NULL,
    [TelNo]   VARCHAR(10)  NULL,
    [School]  VARCHAR(50)  NOT NULL,
    [AdNo]    VARCHAR(10)  NOT NULL,
    [MomName] VARCHAR(50)  NOT NULL,
    [MomOcc]  VARCHAR(50)  NOT NULL,
    [DadName] VARCHAR(50)  NOT NULL,
    [DadOcc]  VARCHAR(50)  NOT NULL
 
);

identity(1,1) will make your primary key auto increment, identity([start number],[increment amount])

eg: identity(10000,5) => your primary key start by 10000 and increment by 5 everytime.

And modify your insert query like this without cmbMemNo.Text because it'll generate unique id automatically.

 String insert_query = "INSERT INTO StReg VALUES('" + txtFName.Text + "','" + txtName.Text + "','" + Gender + "','" + dtpDOB.Text + "','" + txtTelNo.Text + "','" + txtSchool.Text + "','" + txtAdNo.Text + "','" + txtMom.Text + "','" + txtMomOcc.Text + "','" + txtDad.Text + "','" + txtDadOcc.Text + "')";

About your second error,It's occurring because you first exception occur on Cmd.ExecuteNonQuery(); line and execute line step ahead to catch() then your Con.Close(); not execute and connection won't close. you have 2 option for that matter,

1st option - check connection state and open if it's closed

 String insert_query = "INSERT INTO StReg VALUES('" + cmbMemNo.Text + "','" + txtFName.Text + "','" + txtName.Text + "','" + Gender + "','" + dtpDOB.Text + "','" + txtTelNo.Text + "','" + txtSchool.Text + "','" + txtAdNo.Text + "','" + txtMom.Text + "','" + txtMomOcc.Text + "','" + txtDad.Text + "','" + txtDadOcc.Text + "')";
            if (Con.State == System.Data.ConnectionState.Closed)
            {
                Con.Open();
            }
            Cmd = new SqlCommand(insert_query, Con);
            Cmd.ExecuteNonQuery();
            Con.Close();
            MessageBox.Show("The new Student " + txtName.Text + "( S-" + cmbMemNo.Text + " ) has successfully inserted into the system!!!", "INSERTED!!!", MessageBoxButtons.OK, MessageBoxIcon.Information);
            Clear();

**2nd option - set Con.Close(); within try catch finally{} **

  try
            {
                String insert_query = "INSERT INTO StReg VALUES('" + cmbMemNo.Text + "','" + txtFName.Text + "','" + txtName.Text + "','" + Gender + "','" + dtpDOB.Text + "','" + txtTelNo.Text + "','" + txtSchool.Text + "','" + txtAdNo.Text + "','" + txtMom.Text + "','" + txtMomOcc.Text + "','" + txtDad.Text + "','" + txtDadOcc.Text + "')";
                if (Con.State == System.Data.ConnectionState.Closed)
                {
                    Con.Open();
                }
                Cmd = new SqlCommand(insert_query, Con);
                Cmd.ExecuteNonQuery();
                //Con.Close(); - move this line to finally
                MessageBox.Show("The new Student " + txtName.Text + "( S-" + cmbMemNo.Text + " ) has successfully inserted into the system!!!", "INSERTED!!!", MessageBoxButtons.OK, MessageBoxIcon.Information);
                Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error occur  :"+ ex.Message);

            }
            finally {
                Con.Close();
            }

Answer 2

Connection is not closed because you didn't close it. Exception happened before connection was closed and you have opened connection left. Make all your queries like this:

try
{
   Con.Open();
   Cmd = new SqlCommand(insert_query, Con);
   Cmd.ExecuteNonQuery();
}
finally
{
    Con.Close();
}

Exception happens because you either provided non-unique primary key or didn't provide it at all. Don't use vulnerable to SQL injections statements, use SQL parameters, for example:

SqlCommand cmd = new SqlCommand("select * from Customers where city = @City", conn);
SqlParameter param = new SqlParameter();
param.ParameterName = "@City";
param.Value = inputCity;
cmd.Parameters.Add(param);

Answer 3

Cannot insert duplicate key in obj 'dbo.StReg'. The duplicate key value is ().

For StReg table, what is the primary key? make sure you set the primary key as NOT NULL and insert unique value for each record. If you're using UNIQUEIDENTIFIER type for your primary key, you can can add NEWID() in Default Value or Binding, which will automatically assign new Guid for each new record.