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
– vikscool, Commented Sep 11, 2018 at 4:43
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables — marc_s
– marc_s, Commented Sep 11, 2018 at 4:53
can you try with same sql query with your database to check is inserting or not ? — senthilkumar2185
– senthilkumar2185, Commented Sep 11, 2018 at 4:54
Have you tried debugging your app? learn.microsoft.com/en-us/visualstudio/debugger/… — Access Denied
– Access Denied, Commented Sep 11, 2018 at 5: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
– vikscool, Commented Sep 11, 2018 at 5:38

Access Denied · Accepted Answer · 2018-09-11 04:50:10Z

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, 2018 at 4:50

answered Sep 11, 2018 at 4:44

Access Denied

9,6114 gold badges47 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Akila Ranasinghe Over a year ago

Actually,I'm inserting unique data to the primary key field.

Akila Ranasinghe Over a year ago

But it is not saved to the database.

Access Denied Over a year ago

Specify columnNames explicitly: INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN);

Access Denied Over a year ago

@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.

Isanka Thalagala · Accepted Answer · 2018-09-11 05:48:33Z

2

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

edited Sep 11, 2018 at 5:48

answered Sep 11, 2018 at 5:28

Isanka Thalagala

1,7513 gold badges20 silver badges44 bronze badges

8 Comments

Akila Ranasinghe Over a year ago

Can I auto increment starting from my preferred number,like 10000?

Access Denied Over a year ago

@AkilaRanasingha yes, stackoverflow.com/questions/1280705/…

Isanka Thalagala Over a year ago

@AkilaRanasingha yes.set is as [MemNo] int identity(10000,1) PRIMARY KEY NOT NULL,

Access Denied Over a year ago

@IsankaThalagala Did you read comment by @marc_s? bobby-tables.com

Access Denied Over a year ago

"SELECT max(MemNo) FROM StReg" and +1

|

user6573749 · Accepted Answer · 2018-09-11 04:48:02Z

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, 2018 at 4:48

user6573749

3 Comments

Akila Ranasinghe Over a year ago

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.

user6573749 Over a year ago

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.

user6573749 Over a year ago

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

Collectives™ on Stack Overflow

Errors in inserting data to database

3 Answers 3

4 Comments

8 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

8 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related