Duplicate Value In Database With Same User Id

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

Hi All,

I have created a table with attributes like s_no , user_name , Phone_number , user_ID ........ and insertion divided into many parts so first query i used is insert and then update where user_ID matches.
and i use query to insert like

SQL

(insert into table (user_name , Phone_number , user_ID where user_ID =textbox1.text)

and for the rest of the attributes update where user_ID = @user_ID
something like that but the problem is if user again use the same user_ID to edit something in database there is again a new row created with the same user_ID and for this scenario user_ID must be unique and if any user again use the same user_ID then the new data will be update in the same user_ID instead of new row created. I know there must be query like

if this then

insert

else

update

but the thing is i don't have any idea about it to use insert and update at the same place.

i want to use procedure for solving this problem.

Any help will be appropriated.
Thanks

Posted 9-Mar-12 23:54pm

tnitin55

Updated 12-Mar-12 1:56am

Herman<T>.Instance

v2

Add a Solution

7 solutions

Solution 1

are you forgetting something like "Primary key"...????

Posted 10-Mar-12 0:08am

Dinesh92

Solution 4

by setting with primary key it will through error like can not allow duplicate value but your concern is if User_id is exsits in database you want to update the row rather then inserting a new row

so follow my above solution that will suitable for your requirements

if you agree with my answer so please mark as accept solution

Posted 10-Mar-12 0:20am

khan mohd faizan

Solution 6

I would like to give you an idea of solving the problem in ASP.Net4 and SQL Server 2008 with entity framework concept.
Table name: User, Entity model name: "UserModel", & Entity Name: UserEntity. Code for the "aspx.cs" page:

C#

using System;
..........
..........
using UserModel;
protected void btnInsertUpdate_Click(object sender, EventArgs e)
{
  using(UserEntity myEntity=new UserEntity())
  {
   User NUser=new User();
   int UID=Covert.ToInt32(txtUID.Text); //Assuming user_ID as int.
   NUser=(from u in myEntity.Users where u.user_ID==UID selet u).SingleOrDefault();

   if(NUser==null)                  //New User_ID
    {
      User NewUser=new User();
      NewUser.User_ID=UID;
      NewUser.user_name=txtuser_name.text;
      NUser.Phone_number=convert.ToInt32(txtPhone_number.text);   
      ..........................;
      ..........................;
      ..........................;
      myEntity.AddToUsers(NewUser);
      myEntity.SaveChanges();
    }
    else   //if user_ID already exists
    {
      NUser.user_name=txtuser_name.text;
      NUser.Phone_number=convert.ToInt32(txtPhone_number.text); 
      ...............;
      ................;
      ................;
      myEntity.SaveChanges();
    }
  }
}

Hope, it will sort out your problem.

Posted 10-Mar-12 19:33pm

Brajabas

Updated 10-Mar-12 21:36pm

v2

Comments

ProEnggSoft 11-Mar-12 3:36am

Edit: pre tag for C# code added - PES

Solution 8

Hi All,

And Thanks for you help.

Posted 12-Mar-12 1:49am

tnitin55

Comments

AmitGajjar 13-Mar-12 1:47am

do not reply as solution. add it as comment.

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)

khan mohd faizan · Accepted Answer · 2012-03-10T00:15:00

Solution 3

it is very simple to do that......

write a procedure and check the condition like that

IF Not exists(select User_id, from UserMaster where User_id='pass the user id parameter)
BEGIN
write here insert statement
END
ELSE
BEGIN
write here update statement if User_id exsists in database
END

Posted 10-Mar-12 0:15am

khan mohd faizan

Updated 10-Mar-12 0:16am

v2

Ger Hayden · Accepted Answer · 2012-03-10T00:13:00

Solution 2

"Primary Key" will throw an error if you try to insert the same value twice. Or you could to a "select" using the User_ID and if the program finds a value, then update, else insert if you prefer to control the behaviour in the code.

Posted 10-Mar-12 0:13am

Ger Hayden

Aniket Yadav · Accepted Answer · 2012-03-10T19:57:00

Solution 7

Try Solution 2 by Ger Hayden And Solution 3 By Khan Mohd Faizan.

Its the simple and easy method to update existing record or else insert a new record.

Try it. It will solve your problem. And dont forget to mention thanks to these developers.

Enjoy Coding

Posted 10-Mar-12 19:57pm

Aniket Yadav