Solved

vb.net insert array into MS SQL 2005 DB

Posted on 2010-08-24
4
1,116 Views
Last Modified: 2012-08-13
Need some help here I am trying to do a multiple insert into a Ms SQL 2005 DB...with an array...

I would prefer to use a stored procedure somehow...

Private Sub next_function(ByVal ListOfvaluesForDB As List(Of String))
Dim i as integer
i = 1
 ' insert into db these values from an array...
  for each items in ListOfvaluesForDB
       Dim connectionString As String = ConfigurationManager.ConnectionStrings("Connection").ConnectionString
        Using connection As New SqlConnection(connectionString)
            connection.Open()
           
            Dim commandText As String = "INSERT INTO tblImgData(ID, Name, value) VALUES (@ID, @Name, @Value)"
@ID = i
@Name = "server1"
@Value = ListOfvaluesForDB(items)

            Using command As New SqlCommand(commandText, connection)
                command.Parameters.AddWithValue("@ID", id)
                command.Parameters.AddWithValue("@Name", name)
                command.Parameters.AddWithValue("@Value",value)

                Return command.ExecuteNonQuery()
            End Using
        End Using
 i = i + 1
 Next
end Sub
0
Comment
Question by:GlobaLevel
  • 2
4 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 33514063
What in particular are you having trouble with? If you simply want to use a stored procedure instead then you only need to change the text of the Command object to the name of the stored procedure and set the CommandType property to CommandType.StoredProcedure
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33517961
One option is looping and second option is using the .NET connected architecture, use dataset and SQLdataadapter and use dataset's update or insert methods, all values/records in dataset will directly update/insert into SQL.
0
 
LVL 8

Accepted Solution

by:
Mohit Vijay earned 500 total points
ID: 33517966
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33523043
using the below code, how would I pass a parameter array declared as an object or string into the dataset?

Public Function CreateCommandAndUpdate( ByVal ListOfvaluesForDB As List(Of String),_
    ByVal connectionString As String, _
    ByVal queryString As String) As DataSet

    Dim dataSet As DataSet = New DataSet
ListOfvaluesForDB = dataSet     <<<<<<<<<<<CORRECT???


    Using connection As New OleDbConnection(connectionString)
        connection.Open()
        Dim adapter As New OleDbDataAdapter()

        adapter.SelectCommand = New OleDbCommand( _
            queryString, connection)

        Dim builder As OleDbCommandBuilder = _
            New OleDbCommandBuilder(adapter)

        adapter.Fill(dataSet)

        ' Code to modify the data in the DataSet here.

        ' Without the OleDbCommandBuilder this line would fail.
        builder.GetUpdateCommand()
        adapter.Update(dataSet)
    End Using
    Return dataSet
End Function

0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

825 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