3

I am writing a program for a university class, and i got stuck. Im having 2 tables, and i want to insert data into them with one text.

Cars

  • id INT NOT NULL PRIMARY KEY, IDENTITY(1,1)
  • userId INT NOT NULL FOREIGN KEY Users(id)

Car_Details

  • id INT NOT NULL PRIMARY KEY, FOREIGN KEY Cars(id)
  • name varchar(250) NOT NULL
  • body_type VARCHAR(250) NOT NULL,
  • fuel_type VARCHAR(250) NOT NULL,
  • motor_power VARCHAR(250) NOT NULL,
  • manufacture_year INT NOT NULL

It is saying that I am trying to insert NULL value for the id record in the Car_Details table. Down below i wrote my method, how did i try to do it.

Tried doing it with transaction, but that did not work also.

public int Insert(Record record)
{
        SqlCommand command = new SqlCommand();
        command.CommandType = System.Data.CommandType.Text;
        //command.CommandText = @"INSERT INTO Cars(userId)
        //                        VALUES ((SELECT id FROM Users WHERE id = Users.id));
        //                        INSERT INTO Car_Details(id, name, body_type, fuel_type, motor_power, manufacture_year)
        //                        VALUES ((SELECT TOP 1 id FROM Cars),@name, @body_type, @fuel_type, @motor_power, @manufacture_year);";

        command.CommandText = "SET XACT_ABORT ON BEGIN TRANSACTION DECLARE @CarID int 
  INSERT INTO Cars(userID) VALUES((SELECT id FROM Users WHERE id = Users.id))" +
            "SELECT @CarID = scope_identity()" +
            "INSERT INTO Car_Details VALUES(@CarID, @name, @body_type" + 
            "@fuel_type, @motor_power, @manufacture_year)" +
            "COMMIT";
                    //SqlParameter p_id = new SqlParameter();
                    //p_id.ParameterName = "@id";
                    //p_id.SqlDbType = System.Data.SqlDbType.Int;
                    //p_id.Direction = System.Data.ParameterDirection.Input;
                    //p_id.Value = (record as CarRecord).Id;
                    //command.Parameters.Add(p_id);

        SqlParameter p_name = new SqlParameter();
        p_name.ParameterName = "@name";
        p_name.SqlDbType = System.Data.SqlDbType.Char;
        p_name.Direction = System.Data.ParameterDirection.Input;
        p_name.Value = (record as CarRecord).Name;
        command.Parameters.Add(p_name);

        SqlParameter p_body_type = new SqlParameter();
        p_body_type.ParameterName = "@body_type";
        p_body_type.SqlDbType = System.Data.SqlDbType.VarChar;
        p_body_type.Direction = System.Data.ParameterDirection.Input;
        p_body_type.Value = (record as CarRecord).Body_type;
        command.Parameters.Add(p_body_type);

        SqlParameter p_fuel_type = new SqlParameter();
        p_fuel_type.ParameterName = "@fuel_type";
        p_fuel_type.SqlDbType = System.Data.SqlDbType.VarChar;
        p_fuel_type.Direction = System.Data.ParameterDirection.Input;
        p_fuel_type.Value = (record as CarRecord).Fuel_type;
        command.Parameters.Add(p_fuel_type);

        SqlParameter p_motor_power = new SqlParameter();
        p_motor_power.ParameterName = "@motor_power";
        p_motor_power.SqlDbType = System.Data.SqlDbType.Int;
        p_motor_power.Direction = System.Data.ParameterDirection.Input;
        p_motor_power.Value = (record as CarRecord).Motor_power;
        command.Parameters.Add(p_motor_power);

        SqlParameter p_manufactrue_year = new SqlParameter();
        p_manufactrue_year.ParameterName = "@manufacture_year";
        p_manufactrue_year.SqlDbType = System.Data.SqlDbType.Int;
        p_manufactrue_year.Direction = System.Data.ParameterDirection.Input;
        p_manufactrue_year.Value = (record as CarRecord).Manufacture_year;
        command.Parameters.Add(p_manufactrue_year);


        command.Connection = getConnection();

        int affectedRows = command.ExecuteNonQuery();
        command.Connection.Close();

        return affectedRows;
}
dezox
  • 146
  • 9
  • You probably need to take a really good read of what `scope_identity()` is for, and maybe test your SQL statements directly in a database before embedding them into code. – slugster May 19 '19 at 12:59
  • ADO.Net is expecting `@CarID` to be a parameter in your command, not a TSQL `DECLARE`d variable. Try [using the OUTPUT technique](https://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value) or make two queries in a `TransactionScope` – Crowcoder May 19 '19 at 13:09
  • Should the 2 ID columns have the `IDENTITY` property? I can't see that you're inserting a value into them, and they don't have a default value; meaning you're supplying `NULL`. – Thom A May 19 '19 at 13:13
  • The Cars table "id" column has an identity(1,1) option, but the Car_details table's id column is not, because it is a foregn key, referencing to the Cars table's id column. I wanted to separate the Cars and the details of the cars into 2 tables. – dezox May 19 '19 at 13:29
  • Im doing WinForm as client, and i have textboxes, and i would like to insert into both tables. Car id is automatically filled because of identity, and i want THIS id to go into car_details id. – dezox May 19 '19 at 13:31

1 Answers1

1

There is missing IDENTITY (1,1) clause on primary key fields. In your current scenario you need to use IDENTITY definition or specify your primary key value in code and then pass it to SQL command.

Here you can find more info: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017

There is also OUTPUT INSERTED.[PK column name] clause: https://www.sqlservercentral.com/articles/the-output-clause-for-insert-and-delete-statements

With OUTPUT clause you need to use reader to get reply from it.

Probably you need to use this approach if you are working in pending (uncommited) transaction.

VitezslavSimon
  • 342
  • 2
  • 7