While Inserting data into two tables =>ERROR: "Invalid column name Id"

Question

I am getting this error

Invalid column name Id.

What is wrong?

private void btnInsert_Click(object sender, EventArgs e)
        {          
            connection.Open();              
            try
            {
                string query2 = "INSERT INTO Suppliers (SupName) OUTPUT 
                                 Inserted.Id VALUES (@SupName)";
                SqlCommand cmd2 = new SqlCommand(query2, connection);
                cmd2.Parameters.AddWithValue("@SupName", txtCname.Text);

                int supID = (int)cmd2.ExecuteScalar();
                //cmd2.Parameters.Clear();

                string query3 = "INSERT INTO SupplierContacts (SupConAddress, 
                                 SupConCity, AffiliationId, SupplierId) 
                                 VALUES (@SupConAddress, @SupConCity, 
                                 @AffiliationId, @SupplierId)";
                SqlCommand cmd3 = new SqlCommand(query3, connection);
                cmd3.Parameters.AddWithValue("@SupConAddress", txtCaddress.Text);
                cmd3.Parameters.AddWithValue("@SupConCity", txtCcity.Text);
                cmd3.Parameters.AddWithValue("@AffiliationId", aff.AffilitationId);
                cmd3.Parameters.AddWithValue("@SupplierId", supID);
                // cmd3.Parameters.Clear();
                MessageBox.Show("Data Inserted");
            }
            catch(SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }

            connection.Close();

        }

Perhaps you're running an old SQL Server version that doesn't understand `Inserted.Id` - see [this question](http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id)? — Kenney, Feb 04 '16 at 02:20
@GordonLinoff yes you are right, so where do you see Supplier.Id here? Sorry i am slightly confused.. — new_coder, Feb 04 '16 at 02:41
@GordonLinoff But that's the syntax .. right? I am totally new in sql and didn't know much. How should I fix it? — new_coder, Feb 04 '16 at 02:48

score 0 · Answer 1 · answered Feb 04 '16 at 03:04

You are trying to use the OUTPUT clause. However, the correct syntax requires a table name. In T-SQL, this might be written as:

DECLARE @SupplierIds TABLE (SupplierId int);

INSERT INTO Suppliers (SupName)
    OUTPUT Inserted.SupplierId INTO @SupplierIds
    VALUES (@SupName);

This is saying to put the new value of SupplierId into the table SupplierIds. You can then retrieve the value by referring to the table:

SELECT *
FROM @SupplierIds;

However, you cannot reference the table across multiple queries.

My suggestion is that you run the whole script using multiple statements. Or (what I would probably do) write a stored procedure with all the logic for inserting into both tables.

Okies, I will give it a try.. Thanks!! – new_coder Feb 04 '16 at 03:11 — new_coder, Feb 04 '16 at 03:11

While Inserting data into two tables =>ERROR: "Invalid column name Id"

1 Answers1