Solved

vb.net insert array into MS SQL 2005 DB

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

896 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now