Create query to SQL server C#

Question

I have problem with field car_brand_id. I need this field to create relationship, but i have error in this string. How fix this problem?

string brand_query = "SELECT car_brand_id FROM car_brand WHERE name_brand = @brand ";
cmd = new SqlCommand(brand_query, SqlConn);
cmd.Parameters.AddWithValue("@brand", brand);
int car_brand_id = cmd.ExecuteNonQuery();  // There i have an error



string model_query = "INSERT INTO car_model (name_model, car_brand_id) VALUES (@model, @car_brand_id)";
cmd = new SqlCommand(model_query, SqlConn);
cmd.Parameters.AddWithValue("@model", model);
cmd.Parameters.AddWithValue("@car_brand_id", car_brand_id);
cmd.ExecuteNonQuery();

Error message:

The INSERT statement conflicted with the FOREIGN KEY constrains. "FK_car_model_car_b30F848ED". The conflict occured in db "avtoservice:, table "dbo.car_brand", column "car_brand_id". The statument has been terminated

@SLaks The INSERT statement conflicted with the FOREIGN KEY constrains. "FK_car_model_car_b30F848ED". The conflict occured in db "avtoservice:, table "dbo.car_brand", column "car_brand_id". The statument has been terminated — Rick Has
– Rick Has, Commented Jun 1, 2015 at 21:20
Read the error message. Your database isn't letting you insert that data. — SLaks
– SLaks, Commented Jun 1, 2015 at 21:32
is your car_brand_id valid? Check your car_brand table data. — R Quijano
– R Quijano, Commented Jun 1, 2015 at 21:40

dub stylee · Accepted Answer · 2015-06-01 21:56:22Z

3

Try changing this line:

int car_brand_id = cmd.ExecuteNonQuery();  // There i have an error

To this:

int car_brand_id = (int)cmd.ExecuteScalar();

The ExecuteNonQuery() method will return the number of rows affected, not the value from your query, as it appears you are expecting.

edited Jun 1, 2015 at 21:56

answered Jun 1, 2015 at 21:41

dub stylee

3,3906 gold badges43 silver badges61 bronze badges

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

3 Comments

Rick Has Over a year ago

Implicit conversion type " object " in the "INT" is impossible. There is a clear transformation ( possibly there is no reduction )

Jared Moore Over a year ago

int car_brand_id = (int) cmd.ExecuteScalar();

dub stylee Over a year ago

Yes, you will need to cast it to int as @JaredMoore stated. I will fix the answer to reflect this.

SLaks · Accepted Answer · 2015-06-01 21:15:06Z

1

Parameters should not be in quotes.

That's a string that literally says @brand, not the value of the parameter.

answered Jun 1, 2015 at 21:15

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

Collectives™ on Stack Overflow

Create query to SQL server C#

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related