Copy Access table data to Sql server table programmatically

Question

User-1028909717 posted

        Dim ds As New AccessDataSource

          ds.DataFile = "c:\InputTest.mdb"
          ds.DataSourceMode = SqlDataSourceMode.DataSet
          ds.SelectCommand = "SELECT [countrycode], [monthnumber];"

        Dim dv1 As DataView = TryCast(ds.[Select](DataSourceSelectArguments.Empty), DataView)

        ' Just to check

       BatchGridView.DataSource = dv1
       BatchGridView.DataBind()

'

Dim conn As New System.Data.SqlClient.SqlConnection(MyCurrcnn)

Dim MySql As String = "SELECT ([countrycode], [monthnumber]) FROM [AAABatchesAAA];"

Dim cmd As New System.Data.SqlClient.SqlCommand(MySql, conn)

.... but then I go blank.....

Answer 1

User-211766943 posted

What exception are you getting? 

Answer 2

User-1028909717 posted

No exception ... I am stuck... don't know how to procede

I think I can see (in general terms) where I could copy it record by record by reading through dv1, changing MySql to an insert statement

and writing the records to the second record set (conn) ..... but then I seem to remember I saw somewhere where I

can copy the WHOLE dataview/dataset across in one go. That is where I run out of talent....

Answer 3

User1716267170 posted

Is the file path correct? Did you try this one?

ds.DataFile = "c:\\InputTest.mdb"

Answer 4

User1867929564 posted

 You want to Select any record from mdb and insert the same record to sql server.
Am I right ?Is there anything else/condition ?

I hope your code till BatchGridView.DataBind() is right .

what you can do is,
Select record from mdb file.

put it in datatable.

Insert the record to Sql Server db table.

Why are you again selecting record from sql server ?

Dim MySql As String = "SELECT ([countrycode], [monthnumber]) FROM [AAABatchesAAA];"

Answer 5

User-1028909717 posted

Thanks to all for your suggestions ....

I had in the meantime developed my own  solution (continuing from dv1) creating insert packets .....

Here is the relevant coding if it helps someone else.....

        Dim SqlIns = "INSERT INTO AAABatchesAAA(" _
                   & "countrycode, monthnumber)" _
                   & " VALUES ("

        Dim SqlRun As String = ""
        Dim sw As System.IO.StringWriter
        Dim output As String
        Dim table As DataTable = dv1.Table
        ' Loop through each row in the view.
        For Each rowView As DataRowView In dv1
            sw = New System.IO.StringWriter
            sw.Write(SqlIns)
            ' Loop through each column.
            For Each col As DataColumn In table.Columns
                ' Output the value of each column's data.
                Select Case col.DataType.ToString
                    Case "System.String"
                        sw.Write("'" & rowView(col.ColumnName).ToString() & "', ")
                    Case "System.DateTime"
                        sw.Write("'" & rowView(col.ColumnName).ToString() & "', ")
                    Case "System.Boolean"
                        If rowView(col.ColumnName).ToString() = "False" Then
                            sw.Write("0, ")
                        Else
                            sw.Write("1, ")
                        End If
                    Case Else
                        sw.Write(rowView(col.ColumnName).ToString() & ", ")
                End Select
            Next
            output = sw.ToString
            ' Trim off the trailing ", ", so the output looks correct.
            If output.Length > 2 Then
                output = output.Substring(0, output.Length - 2)
            End If
            output = output + ");"
          
            SqlRun = SqlRun & output
        Next

..... and then I just run the Sqlrun packets

I still think there is a "whole recordset" solution (i.e. all in one go) but record-by-record works fine for this solution.

I probably haven't included a conclusive list of col.DataTypes ... it serves my purposes.