vb 2005, Access DB mass data insertion example code needed

[lotharious]

Im looking for some example code of which I have not found anywhere to basically copy 1 database into another.

I can read in the data into a dataset and use it just fine, however I have not been able to figure out how to copy mass data from the dataset into my new database. There are multiple tables. I can read into the dataset the exact data in order to be inserted into the new database. I just do not know the code to do so. Everything I have found is long an complicated. And when I can have upwards of 10,000 records to insert, and some of the tables have 30+ fields the long complicated thing is not going to work.

 

[PRPhx]

Hmm.. and why would you expect this to be an easy thing???? You might try looking at select into as a way of copying one table to another.

What are you copying your Access data to? If it's SQL Server, you might try googling for dts (data transformation services).

IF your copying to SQL Server, you can set it up manually, then let SQL server script your copy proccess.

 

[SiriusBlackOp]

I don't think you can insert mass records all at once. You will have to loop through the datarows in the datatable and insert them into the database one at a time.

Even MS SQL Server on a DTS Import inserts records one at a time.


Senior Software Developer

http://www.scoutsft.com

 

[lotharious]

I do realize that they will be inserted 1 at a time. I just need simple code that will do it. Any code that I have used in the past, is depreciated.

I guess, if I can find a way to do a single sql insert statement, and use that over and over without having to close the connection after every statement it would work.
The dataadapter is way too complicated the way it works for what I need, at least from all the searches I did.

And as I said Its a MS access database. Access 2000 to be precise. The data Im getting from the original database is not exactly what is going to be inserted. DTS will not work, 1) We dont have any, 2) The changes in the data is not always automated.

Old VB Code I would just use ADODB, And For loops.
This is not an option with VB 2005.

 

[PRPhx]

What you can do is use an insert and a select satement togather. That will insert all records from a one table into a second table.

I was thinking a select into would work, but it won't, as it creates an identical table.

And it really doesn't matter too much what the source is when using DTS. And calling a DTS package from VB net IS possible (google returns a number of examples).

Your biggest problem will be coverting your Access data to SQL Server, automated or not. One way or the other you will need to transform the data in your Access tables into acceptable types in SQL Server.

How many tables are you talking about? You need to do some research on what SQL Server can and can not do. 10,000 records and 30 fields really isn't a big deal unless your data types are really messed up. I find dates to be the worst. Sometimes transforming integers into varchars can be a problem also.

 

[SiriusBlackOp]

Here is some code for you. Pass it a data table and it will attempt to insert it. This is very basic, so you may want to add some dynamic or flexibility features to it.

    Sub InsertDataTable(ByVal dt As DataTable)
        Dim cnn As New SqlClient.SqlConnection("connection string here")
        Try
            Dim cmd As New SqlClient.SqlCommand()
            cmd.Connection = cnn
            cnn.Open()
            For Each dr As DataRow In dt.Rows
                cmd.CommandType = CommandType.Text

                'Choose ONLY ONE of the following 2 options
                '1: ---Text Option (simple, but not friendly with characters like ' in data
                cmd.CommandText = "INSERT INTO Table1 (Field1,Field2,Field3...) Values(" & _
                    dr("dt Field 0 Name") & "," & _
                    dr("dt Field 1 Name") & "," & _
                    dr("dt Field 2 Name") & _
                    ")"

                '2: ---Parmameterized Option (little more complex, but more stable)
                cmd.CommandText = "INSERT INTO Table1 (Field1,Field2,Field3...) Values(" & _
                    "@F1," & _
                    "@F2," & _
                    "@F3" & _
                    ")"
                cmd.Parameters.Add(New SqlClient.SqlParameter("@F1", dr("dt Field 0 Name")))
                cmd.Parameters.Add(New SqlClient.SqlParameter("@F2", dr("dt Field 1 Name")))
                cmd.Parameters.Add(New SqlClient.SqlParameter("@F3", dr("dt Field 2 Name")))

                'Then Execute the Insert
                cmd.ExecuteNonQuery()
            Next
        Catch ex As Exception
            'you can handle errors here, or not.
            Throw ex
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try
    End Sub

Senior Software Developer

http://www.scoutsft.com

 

[lotharious]

ok, heres what i got. no errors, but doesnt insert into DB either.
any ideas?

    Dim cvconn As New OleDb.OleDbConnection(My.Settings.CabVisConnection)
    Dim RoomInsert As New OleDb.OleDbCommand("insert into rooms(roomid,roomname,romdescription) values(?,?,?)", cvconn)
    Dim CabinetInsert As New OleDb.OleDbCommand()
    Dim PartInsert As New OleDb.OleDbCommand()
    Dim JobInsert As New OleDb.OleDbCommand("insert into jobs(po,client,custaddr) values(?,?,?)", cvconn)

    ' --------------------------------------------------------------------------------------------------------------------
    Private Sub ImportData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ImportData.Click
        Dim da As New OleDb.OleDbDataAdapter("select roomid,roomname,roomdescription from rooms", conn)
        Dim ds As New DataSet("job")
        Dim errorMessages As String

        conn.Open()
        da.Fill(ds, "room")
        ' cabinets
        ' parts
        ' other
        conn.Close()

        Try
            cvconn.Open()
            Dim n As Integer
            For i = 1 To 5 ' for each dr as datarow in da("room") 
                n = i + 3000
                'OutputBox.Text = My.Settings.CabVisConnection.ToString
                OutputBox.Text += "\r" + n.ToString
                JobInsert.Parameters.Add("@po", OleDb.OleDbType.Char, 10).Value = n.ToString
                JobInsert.Parameters.Add("@client", OleDb.OleDbType.Integer).Value = i
                JobInsert.Parameters.Add("@custaddr", OleDb.OleDbType.Char, 50).Value = n.ToString + " Duh Ave"
                JobInsert.ExecuteNonQuery()
            Next i

        Catch ex As OleDb.OleDbException
            errorMessages = ""
            For i = 0 To ex.Errors.Count - 1
                errorMessages += "Index #" & i.ToString() & ControlChars.Cr _
                               & "Message: " & ex.Errors(i).Message & ControlChars.Cr _
                               & "NativeError: " & ex.Errors(i).NativeError & ControlChars.Cr _
                               & "Source: " & ex.Errors(i).Source & ControlChars.Cr _
                               & "SQLState: " & ex.Errors(i).SQLState & ControlChars.Cr
            Next i
            OutputBox.Text = errorMessages
            Throw ex
        Finally
            If cvconn.State = ConnectionState.Open Then
                cvconn.Close()
            End If
        End Try

    End Sub

 

[SiriusBlackOp]

I think the problem is here: "values(?,?,?)"

Try: "values(@po,@client,@custaddr)"

Senior Software Developer

http://www.scoutsft.com

 

[lotharious]

nope 8(
thx for help though

 

[lotharious]

Bump.
Does any else have any ideas?
This should be my last major hurdle for my program.

 

[lotharious]

ok, finally found my problem. it was using 2 version of my db. One it was reading data from for my forms, the other it was writing too.

 

[AlexCuse]

I've found access rather finicky with parameters at times. Maybe it would work for you to modify the portion of the string containing the values each time (in your command's CommandText property), rather than using parameters?

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!