Solved

vb.net insert array into MS SQL 2005 DB

Posted on 2010-08-24
4
1,148 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

762 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