Solved

insert into sql table from list

Posted on 2014-10-02
4
122 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

If I have to fix slow responding website my first thoughts are server side optimizations: the database may not be optimized or caching is not enabled, or things like that. We often overlook another major part of our web application: the client. We o…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
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.

756 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