error in code vb.net import data from access to sdf

Question

enter image description hereI am using a SQL Server Compact 3.5 database file (.sdf) in vb.net

There was an error parsing the query. [Token line number,Token line offset,,Token in error,,]

Dim flag As Boolean
    Dim flag2 As Boolean
    Me.OpenFileDialog1.Filter = "mdb files (*.mdb)|"
    Me.OpenFileDialog1.Filter = "mdb files (*.mdb)|*.mdb|All files (*.*)|*.*"
    flag2 = (Me.OpenFileDialog1.ShowDialog() = DialogResult.OK)
    If flag2 Then
        flag = (Operators.CompareString(FileSystem.Dir(Me.OpenFileDialog1.FileName, FileAttribute.Normal), "", False) <> 0)
    End If
    Dim myConnectionStringMDB As String = "provider=Microsoft.Ace.OLEDB.12.0;" & "data source=" & Me.OpenFileDialog1.FileName
    Dim myConnectionStringSQL As String = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" & "Data Source=" & Application.StartupPath & "\Archive.sdf"
    Using conSQL As OleDbConnection = New OleDbConnection(), conMDB As OleDbConnection = New OleDbConnection()
        conSQL.ConnectionString = myConnectionStringSQL
        conSQL.Open()
        conMDB.ConnectionString = myConnectionStringMDB
        conMDB.Open()
        Using cmdSQL As OleDbCommand = New OleDbCommand(), cmdMDB As OleDbCommand = New OleDbCommand()
            cmdMDB.CommandType = Data.CommandType.Text
            cmdMDB.Connection = conMDB
            cmdMDB.CommandText = "SELECT * FROM [student]"
            Dim daMDB = New System.Data.OleDb.OleDbDataAdapter(cmdMDB)
            Dim dt = New Data.DataTable()
            daMDB.Fill(dt)
            For Each dr As Data.DataRow In dt.Rows
                ' change row status from "Unchanged" to "Added" so .Update below will insert them
                dr.SetAdded()
            Next
            cmdSQL.CommandType = Data.CommandType.Text
            cmdSQL.Connection = conSQL
            cmdSQL.CommandText = "SELECT * FROM [student]"
            Dim daSQL = New System.Data.OleDb.OleDbDataAdapter(cmdSQL)
            Dim cbuilderMDB = New OleDbCommandBuilder(daSQL)
            cbuilderMDB.QuotePrefix = "["
            cbuilderMDB.QuoteSuffix = "]"
            daSQL.Update(dt)
        End Using
        conSQL.Close()
        conMDB.Close()
    End Using

Mary · Accepted Answer · 2020-03-01 01:10:10Z

I got rid of the extra boolean variables flag and flag2 and just tested the values directly.

I divided the Using blocks into 2 blocks so the first group of objects can be closed and disposed before the next group starts their work.

I shortened the code by passing properties directly to the constructors of the objects where possible. DataAdapter and StringBuilder also expose a .Dispose method so I included them in the Using blocks.

daMDB.AcceptChangesDuringFill = False

This line of code allows the .DataRowState to remain as Added (normally it is changed to Unchanged by the .Fill method) so, the DataTable will be ready to add all the records to the second table without the loop.

I don't believe that student is a reserved word in either database so I removed the square brackets.

Both the .Fill and the .Update methods of the DataAdapter will .Open and .Close the connection if they find the connection closed. If they find it open they will leave it open. So, closing the connection is not necessary. Also the End Using closes and disposes all objects included in the Using portion (first line including commas) of the block.

Private Sub OPCode()
    OpenFileDialog1.Filter = "mdb files (*.mdb)|*.mdb|All files (*.*)|*.*"
    Dim MDBFile As String
    If OpenFileDialog1.ShowDialog = DialogResult.OK AndAlso Not String.IsNullOrEmpty(OpenFileDialog1.FileName) Then
        MDBFile = OpenFileDialog1.FileName
    Else
        Exit Sub
    End If
    Dim myConnectionStringMDB As String = "provider=Microsoft.Ace.OLEDB.12.0;" & "data source=" & MDBFile
    Dim myConnectionStringSQL As String = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" & "Data Source=" & Application.StartupPath & "\Archive.sdf"
    Dim dt = New Data.DataTable()
    Using conMDB As OleDbConnection = New OleDbConnection(myConnectionStringMDB),
            cmdMDB As OleDbCommand = New OleDbCommand("SELECT * FROM student", conMDB),
            daMDB As New System.Data.OleDb.OleDbDataAdapter(cmdMDB)
        daMDB.AcceptChangesDuringFill = False
        daMDB.Fill(dt)
    End Using
    Using conSQL As OleDbConnection = New OleDbConnection(myConnectionStringSQL),
                cmdSQL As OleDbCommand = New OleDbCommand("SELECT * FROM student", conSQL),
                daSql As New OleDbDataAdapter(cmdSQL),
                cbuilderMDB As New OleDbCommandBuilder(daSql)
        daSql.Update(dt)
    End Using
End Sub

Collectives™ on Stack Overflow

error in code vb.net import data from access to sdf

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related