Solved

insert into sql table from list

Posted on 2014-10-02
4
125 Views
Last Modified: 2014-10-03
I need to insert into sql table list of strings ("A", "B", "C" etc) as parameters
this is my code:

  Dim strSQLSubItem As String
Try
            Dim strConn As String = "Data Source=***;Initial Catalog=***;Persist Security Info=True;User ID=***;Password=***"
            Dim Connection As SqlConnection = New SqlConnection(strConn)

            Connection.Open()
Dim v As Integer = Integer.Parse(txtCountPerItem.Text)

            Dim ItemId As Integer = 3

strSQLSubItem = "Insert into InventoryVendorItem_SubItem(ItemId,VendorSubItemDefinition) Values(@ItemId,@VendorSubItemDefinition )"
                                    Using cmdInsertSubItem As New SqlCommand(strSQLSubItem, Connection)
                With cmdInsertSubItem

                    .CommandText = strSQLSubItem
                    .Connection = Connection
                    .CommandType = CommandType.Text

                    .Parameters.Add("@VendorSubItemDefinition", SqlDbType.VarChar)
                    .Parameters.Add("@ItemId", SqlDbType.Int)

 Dim SubItems As New List(Of String)


                    SubItems.Add("A")
                    SubItems.Add("B")
                    SubItems.Add("C")
                    SubItems.Add("D")
                    SubItems.Add("E")
                    SubItems.Add("F")
                    SubItems.Add("G")
                    SubItems.Add("H")
                    SubItems.Add("I")
                    SubItems.Add("J")

                    For i As Integer = 0 To v - 1


                                                .Parameters.Add("@VendorSubItemDefinition", SqlDbType.VarChar).Value = TryCast(SubItems.Item(i), String)
                        .Parameters.Add("@ItemId", SqlDbType.Int).Value = ItemId

                        .ExecuteNonQuery()

                    Next

                End With
            End Using
            Connection.Close()


        Catch Ex As Exception

        End Try

Open in new window


I cannot insert into sql table.

Please, help.
0
Comment
Question by:msout
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 40359112
You keep adding parameters to the Command object in the loop. Remove the .Parameters.Add() statements on lines 19 and 20. Then change the loop code (lines 36-44) to:
                    .Parameters.Add("@ItemId", SqlDbType.Int).Value = ItemId
                    .Parameters.Add("@VendorSubItemDefinition", SqlDbType.VarChar)
                    For i As Integer = 0 To v - 1
                        .Parameters("@VendorSubItemDefinition").Value = SubItems.Item(i)
                        .ExecuteNonQuery()
                    Next

Open in new window

Note that I can't make sure this works obviously without access to your database but this should be the general idea.
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 40359133
quick test with local sqlexpress database was successful:
screen capture of inserted records
0
 

Author Closing Comment

by:msout
ID: 40359635
Great!
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developer tools in browsers have been around for a while, yet they are still heavily underused by developers. Developers still fix html or CSS then refresh page to see effect, or they put alert or debugger in JavaScript and then try again and again …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.
Suggested Courses

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question