Insert and select in one query

Question

I have a query

 insert into carsdescription
 (description,idCar) 
 value (@description,@idCar)
 where idCar=@idCar;
 select nameOfCar
 from Cars 
 where idCar=@idCar";

How to in one sqlCommand execute this??

using(SqlConnection conn=new SqlConnection(connString))
{
    using(sqlCommand cmd=new SqlCommand())
    {
        cmd.Parameteres, conn etc... //this is a peanut

        // your proposition    
    }
}

table schema:

Carsdescription :

 ID (PK, int, autoincrement)
idcar(FK, int)
Description(varchar)

Cars

Id(int, PK, autoincrement)
Name(nvarchar(255)

Answer 1

insert into carsdescription (description) value (@description) where idCar=@idCar;

INSERT ... WHERE ??

(not that I know what you're actually trying to do, but that query won't work in the first place)

Answer 2

You cannot. Not in a single statement. Why do you even want to?? What's your idea, or your requirement??

You have to use a cmd.ExecuteNonQuery call first to insert the new data, and then you need a second call using cmd.ExecuteReader or filling a DataTable using the SqlDataAdapter to retrieve the data again.

using(SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();

    string cmdInsert = "insert into carsdescription(description) value (@description)";

    using(sqlCommand cmd = new SqlCommand(cmdInsert, conn))
    {
        cmd.Parameters.AddWithValue("@description", description);
        cmd.ExecuteNonQuery();
    }

    string selectStmt = "select nameOfCar from dbo.Cars where idCar = @idCar";

    using(sqlCommand cmd2 = new SqlCommand(selectStmt, conn))
    {
        cmd2.Parameters.AddWithValue("@idCar", idCar);

        string resultValue = cmd2.ExecuteScalar().ToString();
    }

    conn.Close();
}

Answer 3

    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = your_query;
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.Add(new SqlParameter("@description", description));
        cmd.Parameters.Add(new SqlParameter("@idCar", idCar));
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            //reader here
        }
    }

Answer 4

Are you trying to copy data from one table to another?

Something like

INSERT INTO CarsDescription (description)
    SELECT nameOfCar
        FROM Cars
        WHERE idCar = @idCar

That will copy all the 'nameOfCar' values into the CarsDescription table. If you don't want duplicates change the SELECT to SELECT DISTINCT

Answer 5

Create a stored procedure that takes Description and IDCar as parameters and returns either a scalar NameOfCar or else a result set from the Cars table. You can then call that stored procedure with a single command from your C# code.

Answer 6

This is perfectly valid to execute in a single call. It might not be recommended, but it is possible.

using (SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=JunkBox;Integrated Security=SSPI;"))
            {
                SqlCommand cmd = new SqlCommand("INSERT INTO JunkSO(Id, Name) VALUES(@Id, @Description)  SELECT * FROM JunkSO", conn);
                cmd.Parameters.AddWithValue("@Id", 10);
                cmd.Parameters.AddWithValue("@Description", "TestDescription");
                conn.Open();
                using (SqlDataReader rd = cmd.ExecuteReader())
                {
                    if (rd.HasRows)
                    {
                        while (rd.Read())
                        {
                            MessageBox.Show(rd[0].ToString() + "  " + rd[1].ToString());
                        }
                    }
                }
            }