dynamic multiple insert

Question

Hi

I have a form that allows users to insert "items" in a database. A field asks how many items they want to insert. Identical data (apart from their ID) is inserted in the database the number of times they chose. I've found different ways to insert multiple items but not when this is dynamically done. I'm using .net 3.5 and SQLServer 2005. Do I have to use a For...Next statement or is there any other way? Thanks

UPDATE: My simplified insert code is as follows:

    Dim sqlCmd1 As String

    sqlCmd1 = "INSERT INTO Table_Items (ItemType, ItemDescription, ContactName, ContactEmail) VALUES (@ItemType, @ItemDescription, @ContactName, @ContactEmail);"

    'Dim ID As Integer
    Dim connectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
    Dim conn As SqlConnection = New SqlConnection(connectionString)
    Dim Cmd1 As SqlCommand = New SqlCommand(sqlCmd1, conn)

    With Cmd1.Parameters
        .Add(New SqlParameter("@ItemType", Textbox1.Text))
        .Add(New SqlParameter("@ItemDescription", Textbox2.Text))
        .Add(New SqlParameter("@ContactName", Textbox3.Text))
        .Add(New SqlParameter("@ContactEmail", Textbox4.Text))

    End With

    Try
        conn.Open()
        ID = Cmd1.ExecuteScalar()
        Server.Transfer("confirmation.aspx")
        conn.Close()
    Catch ex As SqlException
        lblError.Text = ex.Message()
    End Try

Answer 1

Since this is identical data and considering worst case that the number user chooses to insert (how many items) will be reasonably more, to avoid unwanted payload to the database, I would write a stored procedure and just pass the total and the item data. Use XML for item data if the properties are more.

Answer 2

The simplest way to go with your style just add call to Cmd1.ExecuteScalar() in cycle

For i As Integer = 1 To Counter Cmd1.ExecuteScalar() Next

But be aware this is certainly not right way for general usage. While you keep it small, there are no so much overhead, else better go @anivas way.

Also check ASP.NET Data Access Tutorials and I would recommend to switch to LINQToSql if it's possible. It will save you a lot of time. Check Using LINQ to SQL Series, LINQ via C# and Using LINQ to SQL in Visual Basic