[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

vb.net insert array into MS SQL 2005 DB

Posted on 2010-08-24
4
Medium Priority
?
1,248 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 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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 real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

830 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