how to compare data in Form and data from Database?

Question

0.00/5 (No votes)

See more:

Dear all,

here is my code

private void btnAdd_Click(object sender, EventArgs e)
       {
           SqlCommand com = new SqlCommand();
           com.Connection = cn;
           com.CommandType = CommandType.Text;

           //com.CommandText = "insert into Persons_info(perID, latinName, gender, dob, pob, phone, passport, curAdd, status) values('" + txtID.Text + "','" + txtLatinName.Text + "','" + cbGender.Text + "'" + dTPdob.Text + txtPob.Text + "','" + txtPhone.Text + "','" + txtPassport.Text + "'" + txtCurAdd.Text + "'" + cbStatus.Text + " )";
           com.CommandText = "insert into tbl_stuInfo(perId, latinName, gender, dob, pob, phone, passport, curAdd, status, registerDate,majorName,acaYear) values (@perId, @latinName, @gender, @dob, @pob, @phone, @passport, @curAdd, @status, @registerDate, @majorName, @acaYear)";
           com.Parameters.Add(new SqlParameter("@perId", txtID.Text));
           com.Parameters.Add(new SqlParameter("@latinName", txtLatinName.Text));
           com.Parameters.Add(new SqlParameter("@gender", cbGender.Text));
           com.Parameters.Add(new SqlParameter("@dob", Convert.ToDateTime(this.dTPdob.Value).ToString("MM/dd/yyyy")));
           com.Parameters.Add(new SqlParameter("@pob", txtPob.Text));
           com.Parameters.Add(new SqlParameter("@phone", txtPhone.Text));
           com.Parameters.Add(new SqlParameter("@passport", txtPassport.Text));
           com.Parameters.Add(new SqlParameter("@curAdd", txtCurAdd.Text));
           com.Parameters.Add(new SqlParameter("@status", cbStatus.Text));
           com.Parameters.Add(new SqlParameter("@registerDate", Convert.ToDateTime(this.DTP_register.Value).ToString("MM/dd/yyyy")));
           com.Parameters.Add(new SqlParameter("@majorName", cbMajor.Text));
           com.Parameters.Add(new SqlParameter("@acaYear", txtAcayear.Text));
           com.ExecuteNonQuery();


           MessageBox.Show("Saving is done!");
       }

This code is used for inserting data to database(Ms SQL server). But I don't know how to test duplicate number.
perID is a primary key, so it cannot have the duplicate number. So we have to test this condition.

Please help me. Thanks

Posted 8-Mar-12 22:01pm

phanny 2011

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

walterhevedeich · Answer 1 · 2012-03-08T22:21:00

Is the primary key manually entered? If it is, then I don't think it's a good idea. What if there are 1 million records already on the database and every time the user keys in the primary key, there's already an existing one on the database? I would suggest that you change the properties of your primary key such that its automatically generated on the database(GUID or autonumber). In some cases where your primary key needs to comply to a specific format, you can also create some code that automatically generates it. And then on that code, you include the validation of the key by selecting if the key exist on the database or not.

[Edit]
To answer your question, there's no autonumber data type but you can make an int to auto increment whenever there's a new record inserted. Its called an Identity column[^]. You can either follow the instructions on the link, or modify your column in SQL Server Management Studio. When you modify your table and click on a column, you can see the column properties, usually below. Expand the Identity Specification item under the Table Designer group and then set (Is Identity) to yes. Given that Identity Increment and Identity Seed are set to 1, you're all set after you save your changes. However, I don't think this will work if you have existing data on your table. I think you should recreate your table, with the identity column, and then transfer the data from your old table. Make sure you have a backup before doing this.

Rahul Rajat Singh · Answer 2 · 2012-03-08T22:13:00

If you have set "perId" as primary key is database then you need not pass it from the application. You just have to set the isidentity to true and set the field as primary key from the database.

If due to some reason(i couldn't forsee why) you cannot make this primary key in database then you will perhaps want to dip into database once before inserting as

"select count(*) from tbl_stuInfo where perId = txtID.Text"

and insert only if the count is 0 else not.

Pete O'Hanlon · Answer 3 · 2012-03-08T22:09:00

I'm confused as to why you need to do this in the first place. Why does this field have to be the primary key? Typically, unique keys are automatically generated for you, either by you creating a Guid and using that as the key, or by getting the key from the database.

Consider the case that you keep with this approach. What type of user experience are you going to be giving the user if you tell them that they cannot save the data because the key isn't unique? Are they going to have to keep randomly trying combinations of letters until they hit on a unique combination?

Perhaps you could explain why you need to do this?

OriginalGriff · Answer 4 · 2012-03-08T22:07:00

If you are inserting a new record, why are you letting the user specify the ID?
If all you want is a new integer, then make it an Identity field by setting IsIdentity to true, and SQL will handle it for you. If the user really needs to know the number, then show him after you have inserted the row, as part of your existing MessageBox.
Otherwise, you have to tell the user which ones are "free" and that could easily change while they are entering data.

how to compare data in Form and data from Database?

4 solutions

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0