Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

insert into sql table from list

Posted on 2014-10-02
4
Medium Priority
?
129 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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Foolproof security solutions has become one of the key necessities of every e-commerce or Internet banking website. If you too own an online shopping site then its vital for you to equip your web portal with customer security features that can allow…
Thoughout my experience working on eCommerce web applications I have seen applications succumbing to increased user demand and throughput. With increased loads the response times started to spike, which leads to user frustration and lost sales. I ha…
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.

671 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