How I check if value is already exist in database when I insert values using C#

Question

1.00/5 (1 vote)

See more:

this is my code in project :-

C#

s = "insert into Cars (Car_id , Car_No, Car_No_2, Car_shs, Car_Motor)
s = s + "values (@Car_id , @Car_No, @Car_No_2, @Car_shs, @Car_Motor
sCommand = new SqlCommand(s, con);
con.Open();
sCommand.Parameters.AddWithValue("@Car_id", txt_Car_id.Text);
sCommand.Parameters.AddWithValue("@Car_No", txt_Car_No.Text);
sCommand.Parameters.AddWithValue("@Car_No_2", txt_Car_No_2.Text);
sCommand.Parameters.AddWithValue("@Car_shs", txt_Car_shs.Text);
sCommand.Parameters.AddWithValue("@Car_Motor", txt_Mator_No.Text);
sCommand.ExecuteNonQuery();

What I have tried:

before value saved in database check if Car_id is exist in database

Posted 20-Mar-16 23:12pm

Member 12244977

Updated 16-Jul-19 1:35am

Add a Solution

3 solutions

Solution 4

I assumed that you are using a MSSQL database, Use SQL MERGE statement on database side.

Posted 13-Jul-19 1:59am

SatamDipesh

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)

Andy Lanng · Accepted Answer · 2016-03-20T23:25:00

There are two ways.

One: create a select or select count(*) with the same parameters and check if there are any results > 0 returned. Don't perform the second insert unless the select has no results

Two: (the correct way) You can use Select even if you only have variables to insert. You can use this syntax to add a Where clause

Add a clause on the insert to avoid duplicates:

C#

//use @ for a literal string.  That way you just just multiline it
s = @"
insert into Cars (Car_id , Car_No, Car_No_2, Car_shs, Car_Motor)
Select @Car_id , @Car_No, @Car_No_2, @Car_shs, @Car_Motor
Where not exists (
    select * 
    from Cars 
    where 
        Car_id = @Car_id
    and Car_No = @Car_No,
    and Car_No_2 = @Car_No_2
    and Car_shs = @Car_shs
    and Car_Motor = @Car_Motor)
"
sCommand = new SqlCommand(s, con);
con.Open();
sCommand.Parameters.AddWithValue("@Car_id", txt_Car_id.Text);
sCommand.Parameters.AddWithValue("@Car_No", txt_Car_No.Text);
sCommand.Parameters.AddWithValue("@Car_No_2", txt_Car_No_2.Text);
sCommand.Parameters.AddWithValue("@Car_shs", txt_Car_shs.Text);
sCommand.Parameters.AddWithValue("@Car_Motor", txt_Mator_No.Text);
sCommand.ExecuteNonQuery();

This will not insert if it exists.

You can check if it was inserted by checking the return value of sCommand.ExecuteNonQuery(). It returns a count of the rows affected:

C#

var recordsAffected = sCommand.ExecuteNonQuery();
return recordsAffected == 1;

Hope that helps ^_^
Andy

Manas_Kumar · Accepted Answer · 2016-03-20T23:36:00

Try with below code:

C#

// Checking car exist in Database
string s = @"SELECT COUNT(*) FROM Cars WHERE Car_id = @Car_id))";
sCommand = new SqlCommand(s, con);
sCommand.Parameters.AddWithValue("@Car_id", txt_Car_id.Text);
con.Open();
int records = (int)sCommand.ExecuteScalar();

if(records == 0)
{
	sCommand.Parameters.Clear();
	s = @"insert into Cars (Car_id , Car_No, Car_No_2, Car_shs, Car_Motor)
		values (@Car_id , @Car_No, @Car_No_2, @Car_shs, @Car_Motor)";
	sCommand = new SqlCommand(s, con);

	sCommand.Parameters.AddWithValue("@Car_id", txt_Car_id.Text);
	sCommand.Parameters.AddWithValue("@Car_No", txt_Car_No.Text);
	sCommand.Parameters.AddWithValue("@Car_No_2", txt_Car_No_2.Text);
	sCommand.Parameters.AddWithValue("@Car_shs", txt_Car_shs.Text);
	sCommand.Parameters.AddWithValue("@Car_Motor", txt_Mator_No.Text);
	sCommand.ExecuteNonQuery();
}
else
{
	Response.Write("Records Exists");
}

How I check if value is already exist in database when I insert values using C#

3 solutions

Solution 1

Solution 2

Solution 4

Add your solution here

Preview 0