CODEWITHSUNDEEP
sqlasp.net
Emanuel Pirovano
Emanuel Pirovano

Reputation: 236

Iterate a query with an external parameter

I am develop a e commerce site.

Here the code for pass parameter from shopping cart (data table) to Order database.

        Dim con As SqlConnection
    con = New SqlConnection("Data Source=NBK012\ARPASQL;Initial Catalog=aCommerceDB;Integrated Security=True")
    con.Open()
    Dim cmd As New SqlCommand("INSERT INTO [aOrdini] (ID,NomeProdotto,PrezzoProdotto,Quantità,NumeroOrdine) values(@ID,@NomeProdotto,@PrezzoProdotto,@Quantità,@NumeroOrdine)", con)
    cmd.Parameters.AddWithValue("@ID", GridView1.Rows(0).Cells(1).Text)
    cmd.Parameters.AddWithValue("@NomeProdotto", GridView1.Rows(0).Cells(2).Text)
    cmd.Parameters.AddWithValue("@PrezzoProdotto", GridView1.Rows(0).Cells(3).Text)
    cmd.Parameters.AddWithValue("@Quantità", GridView1.Rows(0).Cells(4).Text)
    cmd.Parameters.AddWithValue("@NumeroOrdine", TextBox9.Text)
    cmd.ExecuteNonQuery()
    con.Close()

And this work fine but only with one item in the shopping cart. First question, how can i iterate that code ? so as pass all the shopping cart to order db. The second thing is that the order number arrive from another database that i use as counter. So i don't need to iterate the order number because all item in shopping cart has the same shopping number

Thanks for the help guys.

Upvotes: 0

Views: 90

Answers (1)

Josh Darnell
Josh Darnell

Reputation: 11433

If you want to insert all the rows in your GridView to the database, you would put your insert code inside a For loop:

Dim con As SqlConnection
con = New SqlConnection("Data Source=NBK012\ARPASQL;Initial Catalog=aCommerceDB;Integrated Security=True")
con.Open()
Dim cmd As New SqlCommand("INSERT INTO [aOrdini] (ID,NomeProdotto,PrezzoProdotto,Quantità,NumeroOrdine) values(@ID,@NomeProdotto,@PrezzoProdotto,@Quantità,@NumeroOrdine)", con)

For i As Integer = 0 To GridView1.Rows.Count - 1

    cmd.Parameters.AddWithValue("@ID", GridView1.Rows(i).Cells(1).Text)
    cmd.Parameters.AddWithValue("@NomeProdotto", GridView1.Rows(i).Cells(2).Text)
    cmd.Parameters.AddWithValue("@PrezzoProdotto", GridView1.Rows(i).Cells(3).Text)
    cmd.Parameters.AddWithValue("@Quantità", GridView1.Rows(i).Cells(4).Text)
    cmd.Parameters.AddWithValue("@NumeroOrdine", TextBox9.Text)
    cmd.ExecuteNonQuery()

    cmd.Parameters.Clear()
Next

con.Close()

A few things to point out about this code:

  • See that I'm using ".Rows(i)" inside the loop, instead of ".Rows(1)" in order to get the Cell values from each row as the loop progresses
  • Notice that the parameters collection is cleared at the end of each loop iteration. This is to prevent duplicate parameters from being added, which is not allowed
  • Note that connection is only opened once, and then closed once, rather than opening and closing inside the loop. This is a more efficient, but it does leave the connection open for a long time if you have many, many rows

Upvotes: 3

Related Questions