CODEWITHSUNDEEP
c#sql
Poyraz Kürkçüoğlu
Poyraz Kürkçüoğlu

Reputation: 99

Getting a query result into a Variable then passing it to a command

Here is my code. I am trying to get a result of a SQL query stored in query1 variable, and want to use that result in a SQL query stored in query variable. Messagebox is irrelevant, I just wrote it to see if I am getting the correct results from listbox1 or not.If i hardcode the query1's result into command parameters, it works perfectly.

private void btnAddCoarse_click(object sender, EventArgs e)
{
    MessageBox.Show(listBox1.SelectedItem.ToString());


    string query1 ="SELECT ogrenciNo FROM ders,ogrenci,Enrollment WHERE ogrenciId=ogrenciNo AND dersId=dersKodu AND ogrenci.email='" + mainform.Username + "' AND Enrollment.dersId='"+listBox1.SelectedValue+"'";
    string query = "INSERT INTO Enrollment(dersId,ogrenciId) VALUES (@dersId, @ogrenciId)";

    using (connection = new SqlConnection(connectionString))
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        connection.Open();

        command.Parameters.AddWithValue("@dersId", listBox1.SelectedValue);
        command.Parameters.AddWithValue("@ogrenciId",//this is where i need the query1//);
        command.ExecuteNonQuery();
        PopulateList2();
    }
}

Upvotes: 1

Views: 659

Answers (3)

hdkhardik
hdkhardik

Reputation: 662

Try doing this

private void btnAddCoarse_click(object sender, EventArgs e)
{
       //MessageBox.Show(listBox1.SelectedItem.ToString());


       string query1 ="SELECT ogrenciNo FROM ders,ogrenci,Enrollment WHERE ogrenciId=ogrenciNo AND dersId=dersKodu AND ogrenci.email='" + mainform.Username + "' AND Enrollment.dersId='"+listBox1.SelectedValue+"'";
       string query = "INSERT INTO Enrollment(dersId,ogrenciId) VALUES (@dersId, @ogrenciId)";



       using (connection = new SqlConnection(connectionString))
       {
            int result=0;
            using (SqlCommand command = new SqlCommand(query1, connection))
            {
                connection.Open();

                result=Convert.ToInt32(command.ExecuteScalar());
                connection.Close();
            }
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                connection.Open();

                command.Parameters.AddWithValue("@dersId", listBox1.SelectedValue);
                command.Parameters.AddWithValue("@ogrenciId",result);
                command.ExecuteNonQuery();
                PopulateList2();
            }
        }

}

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20794

You only need one sql command. The structure would be like this:

insert into table2
(field1, field2)
select value1, value2
etc

Upvotes: 2

Giorgi_Mdivani
Giorgi_Mdivani

Reputation: 383

 int id;   
 using (connection = new SqlConnection(connectionString))
           using (SqlCommand command = new SqlCommand(query1, connection))
           {
             connection.Open();
             id = Convert.ToInt32(command.ExecuteScalar()); //I guess it returns only 1 id?..  
           }

Upvotes: 1

Related Questions