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!

can you please paste your table definition and the possibility is that you have `memNo` set up as your primary key of `string` datatype and then for the new student you are sending the same key. which is blank in your case which is giving you the **Primary key violation error** as there is already an entry in the table with the same value(i.e. *blank*). — vikscool, Sep 11 '18 at 04:43
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) — marc_s, Sep 11 '18 at 04:53
can you try with same sql query with your database to check is inserting or not ? — senthilkumar2185, Sep 11 '18 at 04:54
Have you tried debugging your app? https://learn.microsoft.com/en-us/visualstudio/debugger/walkthrough-debugging-a-windows-form?view=vs-2017 — Access Denied, Sep 11 '18 at 05:21
can you please put a `breakpoint` in your code before insert and check what is the value in the `cmbMemNo.Text` and match that value with the one in the `DB` for field `MemNo`. — vikscool, Sep 11 '18 at 05:38

Access Denied · Answer 1 · 2018-09-11T04:50:10.907

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

edited Sep 11 '18 at 04:50

answered Sep 11 '18 at 04:44

Access Denied

8,723
4
42
72

Actually,I'm inserting unique data to the primary key field. – Akila Ranasinghe Sep 11 '18 at 04:55
But it is not saved to the database. – Akila Ranasinghe Sep 11 '18 at 04:58
Specify columnNames explicitly: INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN); – Access Denied Sep 11 '18 at 05:02
@AkilaRanasingha It says that primary key is empty string not null. Probably your textbox is empty or incorrect one and you get empty primary key in your query. Debug your app and see what is the query to sql connection. – Access Denied Sep 11 '18 at 05:18

Isanka Thalagala · Answer 2 · 2018-09-11T05:48:33.347

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

Can I auto increment starting from my preferred number,like 10000? — Akila Ranasinghe, Sep 11 '18 at 05:35
@AkilaRanasingha yes, https://stackoverflow.com/questions/1280705/set-start-value-for-column-with-autoincrement — Access Denied, Sep 11 '18 at 05:43
@AkilaRanasingha yes.set is as [MemNo] int identity(10000,1) PRIMARY KEY NOT NULL, — Isanka Thalagala, Sep 11 '18 at 05:44
@IsankaThalagala Did you read comment by @marc_s? http://bobby-tables.com/ — Access Denied, Sep 11 '18 at 05:49
Then how can I display the first number for the textbox "Your membership no is". — Akila Ranasinghe, Sep 11 '18 at 05:49
@AkilaRanasingha to get next Id number > SELECT max(MemNo)+1 as [next Id Number] FROM StReg or you can use SELECT top 1 (MemNo+1) as [next Id Number] FROM StReg order by MemNo desc — Isanka Thalagala, Sep 11 '18 at 06:06
Can you please give me a full code from selecting the max(MemNo) to converting it from int to string and then displaying it in the textbox.I'm having trouble coding it. — Akila Ranasinghe, Sep 11 '18 at 06:32

score 0 · Answer 3 · answered Sep 11 '18 at 04:48

0

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.

answered Sep 11 '18 at 04:48

MemNo(The Primary Key) is set as not null, and also, each time I'm inserting unique numbers to it. But sill, I'm getting the error. – Akila Ranasinghe Sep 11 '18 at 04:53
Can you paste your table definition code? the problem with Membership number, but we need to see the source code first so we can know what cause the issue. – Sep 11 '18 at 05:04
Follow what @Isanka said in his comment. However, I prefer you to use UNIQEIDENTIFIER as primary key type, and set NEWID() as default value. Here we use Guid as primary key which will prevent any duplication in the future – Sep 11 '18 at 06:11

Errors in inserting data to database

3 Answers3