Solved

vb.net insert array into MS SQL 2005 DB

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

707 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

15 Experts available now in Live!

Get 1:1 Help Now