CODEWITHSUNDEEP
c#sql
user278618
user278618

Reputation: 20222

Insert and select in one query

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)

Upvotes: 1

Views: 7901

Answers (7)

Sourabh Mishra
Sourabh Mishra

Reputation: 1

Well, I also found one way, I know that it's messy but I am also a beginner so I did as follows for myself.

SqlConnection add = new SqlConnection(ConfigurationManager.ConnectionStrings["sismanager"].ConnectionString);
        SqlCommand additem = new SqlCommand("INSERT INTO Godown(Gname, Gstock) VALUES(@Gname, @Gstock)", add);
        additem.Parameters.AddWithValue("@Gname", textBox1.Text.Trim());
        additem.Parameters.AddWithValue("@Gstock", textBox2.Text.Trim());
        try
        {               
            add.Open();
            additem.ExecuteNonQuery();              
        }
        catch
        {

        }
        finally
        {
            add.Close();

            dataGridView1.Columns.Clear();
            SqlConnection setcon = new SqlConnection(ConfigurationManager.ConnectionStrings["sismanager"].ConnectionString);
            using (SqlCommand getdata = new SqlCommand("SELECT Gid, Gname, Gstock FROM Godown ORDER BY Gname ASC", setcon))
            {
                setcon.Open();
                SqlDataAdapter da = new SqlDataAdapter(getdata);
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    comboBox1.DisplayMember = "Gname";
                    comboBox1.ValueMember = "Gid";
                    comboBox1.DataSource = dt;

                    dataGridView1.DataSource = dt;
                    dataGridView1.Columns[0].HeaderText = "Item Id";
                    dataGridView1.Columns[1].HeaderText = "Item";
                    dataGridView1.Columns[2].HeaderText = "Item Stock";

                    DataGridViewButtonColumn deletebtn = new DataGridViewButtonColumn();
                    dataGridView1.Columns.Add(deletebtn);
                    deletebtn.HeaderText = "Remove";
                    deletebtn.Text = "Remove";
                    deletebtn.Name = "Dbtn";
                    deletebtn.UseColumnTextForButtonValue = true;
                }
                else
                {
                    MessageBox.Show("No Stock Items to Show", "Stock Details", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
        }

However this answer is irrelevant to your question, but I have tried my own solution.

Upvotes: 0

marc_s
marc_s

Reputation: 754220

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();
}

Upvotes: 3

Jacob G
Jacob G

Reputation: 3665

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());
                        }
                    }
                }
            }

Upvotes: 2

Larry Lustig
Larry Lustig

Reputation: 50970

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.

Upvotes: 2

CaffGeek
CaffGeek

Reputation: 22054

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

Upvotes: 3

Andrey
Andrey

Reputation: 60055

    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
        }
    }

Upvotes: 0

Select0r
Select0r

Reputation: 12628

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)

Upvotes: 2

Related Questions