CODEWITHSUNDEEP
c#asp.netsqlcode-behind
user1649498
user1649498

Reputation: 121

Save list to database

This is what I have so far:

Veza.Open();
SqlCommand zadnjaN = new SqlCommand("SELECT TOP 1 id_n FROM Narudzba ORDER BY id_n DESC", Veza);
var id_zn = zadnjaN.ExecuteScalar(); //get 1 value for id_zn (last one entered)
List<int> proizvodi = new List<int>();
proizvodi = (List<int>)Session["kosarica"];
SqlCommand kupnja1 = new SqlCommand("INSERT INTO NarudzbaItemi ([narudzbaID], [proizvodID]) VALUES (@id_zn, @pro)", Veza);
for (int i = 0; i < proizvodi.Count; i++)
{
  kupnja1.Parameters.AddWithValue("pro", proizvodi[i]); //also tried @pro
  kupnja1.Parameters.AddWithValue("id_zn", id_zn); //@id_zn
  kupnja1.ExecuteNonQuery();
}
Veza.Close();

I get a message saying that variable name @pro has allready been declared. The point is, I need to insert a list of int items into column proizvodID, and however many times I insert a value in that column I need to insert an unchanging value that many times in column narudzbaID, which I get from a different table as the last value added. All 3 columns are int, and Session is List int. Using asp.net, c#, sql server 2008.

Upvotes: 1

Views: 6617

Answers (3)

Adil Mammadov
Adil Mammadov

Reputation: 8686

Here how you can do this. But John Saunders's method is better I think

    for (int i = 0; i < proizvodi.Count; i++)
    {
        //Add this line to clear parameters
        kupnja1.Parameters.Clear();
        kupnja1.Parameters.AddWithValue("pro", proizvodi[i]); //also tried @pro
        kupnja1.Parameters.AddWithValue("id_zn", id_zn); //@id_zn
        kupnja1.ExecuteNonQuery();
    }

Upvotes: 1

mattmanser
mattmanser

Reputation: 5806

It's basically saying you can't keep reusing the same command, this should work, moving the declaration into the loop:

    Veza.Open();
    SqlCommand zadnjaN = new SqlCommand("SELECT TOP 1 id_n FROM Narudzba ORDER BY id_n DESC", Veza);
    var id_zn = zadnjaN.ExecuteScalar(); //get 1 value for id_zn (last one entered)
    List<int> proizvodi = new List<int>();
    proizvodi = (List<int>)Session["kosarica"];

    for (int i = 0; i < proizvodi.Count; i++)
    {
        SqlCommand kupnja1 = new SqlCommand("INSERT INTO NarudzbaItemi ([narudzbaID], [proizvodID]) VALUES (@id_zn, @pro)", Veza);
        kupnja1.Parameters.AddWithValue("pro", proizvodi[i]); //also tried @pro
        kupnja1.Parameters.AddWithValue("id_zn", id_zn); //@id_zn
        kupnja1.ExecuteNonQuery();
    }
    Veza.Close();

Upvotes: 0

John Saunders
John Saunders

Reputation: 161791

You keep adding parameters in the loop. On the second iteration, @pro is already defined.

Try this:

Veza.Open();
object id_zn; //get 1 value for id_zn (last one entered)
using (SqlCommand zadnjaN = new SqlCommand("SELECT TOP 1 id_n FROM Narudzba ORDER BY id_n DESC", Veza))
{
    id_zn = zadnjaN.ExecuteScalar();
}
List<int> proizvodi = (List<int>)Session["kosarica"];
using (SqlCommand kupnja1 = new SqlCommand("INSERT INTO NarudzbaItemi ([narudzbaID], [proizvodID]) VALUES (@id_zn, @pro)", Veza))
{
    kupnja1.Parameters.Add("pro"); 
    kupnja1.Parameters.Add("id_zn");
    for (int i = 0; i < proizvodi.Count; i++)
    {
        kupnja1.Parameters["pro"].Value = proizvodi[i]; //also tried @pro
        kupnja1.Parameters["id_zn"].Value = id_zn; //@id_zn
        kupnja1.ExecuteNonQuery();
    }
}
Veza.Close();

Upvotes: 3

Related Questions