none
save to access database .MDB RRS feed

  • Question

  • Hi  All,

    Trying TO DO;

    1.Button_click1 : create Access database .mdb

    2.Button_Click2 : Export data Excel to Datagridview1

    3.Button_Click3 : Save All Datagridview1 to file DB.mdb

    Click button3 fail.

    My question is how to write code for saving in button3?.

    Thank.

    Imports ADOX
    Imports System.Data.OleDb
    Imports System.Data.SqlClient
    
    
    Public Class Form1
    
        Dim ds1 As New DataSet
        Dim ds2 As New DataSet
        Public dt_ As DataTable
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            'cleanup old databases
            Dim catNewDB As New ADOX.Catalog
            Dim ADOXtable As New ADOX.Table
    
            Dim fi As New IO.FileInfo("C:\Users\Uset\Documents\DB.mdb")
            If fi.Exists Then
                If MessageBox.Show("Delete?", "Existing File db.mdb", _
                   MessageBoxButtons.YesNo) = DialogResult.Yes Then
                    fi.Delete()
                Else
                    Exit Sub
                End If
            End If
            catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Users\Uset\Documents\DB.mdb")
    
            ADOXtable.Name = "Result"
    
            ADOXtable.Columns.Append("No_ID", DataTypeEnum.adInteger, 40)
            ADOXtable.Columns.Append("A", DataTypeEnum.adDouble)
            ADOXtable.Columns.Append("B", DataTypeEnum.adDouble, 20)
    
            'append tables to database
            catNewDB.Tables.Append(ADOXtable)
    
            catNewDB = Nothing
    
    
        End Sub
    
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            dt_ = New DataTable()
            Dim _filename As String = "C:\Users\Uset\Documents\3Lines.xlsx"
    
            Dim _conn As String
            _conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _filename & ";Extended Properties='Excel 12.0;IMEX=1;'"
    
            Dim _connection As OleDbConnection = New OleDbConnection(_conn)
            Dim da As OleDbDataAdapter = New OleDbDataAdapter()
            Dim _command As OleDbCommand = New OleDbCommand()
    
            _command.Connection = _connection
            _command.CommandText = "SELECT * FROM [3Lines$]"
    
            da.SelectCommand = _command
    
            Try
                da.Fill(ds1, "3Lines")
                MessageBox.Show("The import is complete!")
                Me.DataGridView1.DataSource = ds1
                Me.DataGridView1.DataMember = "3Lines"
    
            Catch e1 As Exception
                MessageBox.Show("Import Failed, correct Column name in the sheet!")
            End Try
    
        End Sub
    
    
        Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
            Try
                Dim scb = New OleDbCommandBuilder(adapter)
                adapter.Update(dt_)
                MessageBox.Show("OK!")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
    
        End Sub

    Friday, March 1, 2019 3:10 PM

Answers

  • Hi,
    for No_ID as Integer, A and B as Double try this, but values in the third column musst be double (not d3, d5 …):

      Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim _filename As String = "C:\Users\Uset\Documents\DB.mdb"
    
        Dim _conn As String = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={_filename}"
    
        Dim _sql = "INSERT INTO Result(No_ID ,A ,B) Values(?,?,?)"
        Dim _parID As New OleDbParameter("@ID", OleDbType.Integer)
        Dim _parA As New OleDbParameter("@A", OleDbType.Double)
        Dim _parB As New OleDbParameter("@B", OleDbType.Double)
    
        Try
          Using _connection As New OleDbConnection(_conn)
            _connection.Open()
            Using _command As New OleDbCommand(_sql, _connection)
              _command.Parameters.AddRange(New OleDbParameter() {_parID, _parA, _parB})
              For i As Integer = 0 To Me.DataGridView1.RowCount - 2
                _parID.Value = CType(Me.DataGridView1.Item(0, i).Value, Integer)
                _parA.Value = CType(Me.DataGridView1.Item(1, i).Value, Double)
                _parB.Value = CType(Me.DataGridView1.Item(2, i).Value, Double)
                _command.ExecuteNonQuery()
              Next
            End Using
          End Using
        Catch ex As Exception
          MsgBox(ex.ToString)
        End Try
      End Sub


    --
    Viele Grüsse
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks



    Sunday, March 3, 2019 9:19 AM

All replies

  • Using a datagridview for this purpose is going to result in much much slower data transfer rates as you will have to wait on UI/draw procedures. You should go from a datatable to another datatable if you're looking to update in this method.

    Also you say button 3 fail, but you dont say what the exception is.


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Friday, March 1, 2019 3:17 PM
  • Thank Gtripodi for your feedback,

    I'm really don't know but try to understand about database MDB.I hope you can give me some example to do.

    bellow I try to do but error at.

    "_command.ExecuteNonQuery()"

       Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
    
            Dim _filename As String = "C:\Users\Uset\Documents\DB.mdb"
            Dim _conn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _filename
    
    
            Dim No_ID, A, B As String
            For i As Integer = 0 To Me.DataGridView1.RowCount - 1
                No_ID = Me.DataGridView1.Item(0, i).Value
                A = Me.DataGridView1.Item(1, i).Value
                B = Me.DataGridView1.Item(2, i).Value
    
                Dim _connection As OleDbConnection = New OleDbConnection(_conn)
                Dim da As OleDbDataAdapter = New OleDbDataAdapter()
                Dim _command As OleDbCommand = New OleDbCommand()
    
                _command.Connection = _connection
                _command.CommandText = "insert into Result(No_ID ,A ,B) values('" & No_ID & A & B & "')"
    
                _connection.Open()
                _command.ExecuteNonQuery()
    
                da.SelectCommand = _command
    
                _command.Dispose()
                MsgBox("Result Added!", MsgBoxStyle.Information, "Add")
                _connection.Close()
    
            Next
    
        End Sub

    Saturday, March 2, 2019 3:08 AM
  • Hi,
    try this (in VS 2017):

      Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim _filename As String = "C:\Users\Uset\Documents\DB.mdb"
    
        Dim _conn As String = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={_filename}"
    
        Dim _sql = "INSERT INTO Result(No_ID ,A ,B) Values(?,?,?)"
        Dim _parID As New OleDbParameter("@ID", OleDbType.VarChar, 255)
        Dim _parA As New OleDbParameter("@A", OleDbType.VarChar, 255)
        Dim _parB As New OleDbParameter("@B", OleDbType.VarChar, 255)
    
        Try
          Using _connection As New OleDbConnection(_conn)
            _connection.Open()
            Using _command As New OleDbCommand(_sql, _connection)
              _command.Parameters.AddRange(New OleDbParameter() {_parID, _parA, _parB})
              For i As Integer = 0 To Me.DataGridView1.RowCount - 2
                _parID.Value = Me.DataGridView1.Item(0, i).Value
                _parA.Value = Me.DataGridView1.Item(1, i).Value
                _parB.Value = Me.DataGridView1.Item(2, i).Value
                _command.ExecuteNonQuery()
              Next
            End Using
          End Using
        Catch ex As Exception
          MsgBox(ex.ToString)
        End Try
      End Sub  


    --
    Viele Grüsse
    Peter Fleischer (ehem. MVP für Developer Technologies)
    Meine Homepage mit Tipps und Tricks

    Sunday, March 3, 2019 6:11 AM
  • Thank Peter for  feedback.

    Trying run this code but error, I don't how to do.

    Sunday, March 3, 2019 7:57 AM
  • Hi,
    if the type of No_ID is Integer then try this:

      Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim _filename As String = "C:\Users\Uset\Documents\DB.mdb"
    
        Dim _conn As String = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={_filename}"
    
        Dim _sql = "INSERT INTO Result(No_ID ,A ,B) Values(?,?,?)"
        Dim _parID As New OleDbParameter("@ID", OleDbType.Integer, 255)
        Dim _parA As New OleDbParameter("@A", OleDbType.VarChar, 255)
        Dim _parB As New OleDbParameter("@B", OleDbType.VarChar, 255)
    
        Try
          Using _connection As New OleDbConnection(_conn)
            _connection.Open()
            Using _command As New OleDbCommand(_sql, _connection)
              _command.Parameters.AddRange(New OleDbParameter() {_parID, _parA, _parB})
              For i As Integer = 0 To Me.DataGridView1.RowCount - 2
                _parID.Value = CType(Me.DataGridView1.Item(0, i).Value, Integer)
                _parA.Value = Me.DataGridView1.Item(1, i).Value
                _parB.Value = Me.DataGridView1.Item(2, i).Value
                _command.ExecuteNonQuery()
              Next
            End Using
          End Using
        Catch ex As Exception
          MsgBox(ex.ToString)
        End Try
      End Sub


    --
    Viele Grüsse
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Sunday, March 3, 2019 8:25 AM
  • Peter, THe same thing error raise.

    Sunday, March 3, 2019 8:54 AM
  • Hi,
    please, show the types of your columns in the database.

    The types of the parameters must be identical with types in the database.


    --
    Viele Grüsse
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks



    Sunday, March 3, 2019 8:57 AM
  •       'cleanup old databases
            Dim catNewDB As New ADOX.Catalog
            Dim ADOXtable As New ADOX.Table
    
            Dim fi As New IO.FileInfo("C:\Users\Uset\Documents\DB.mdb")
            If fi.Exists Then
                If MessageBox.Show("Delete?", "Existing File db.mdb",
                   MessageBoxButtons.YesNo) = DialogResult.Yes Then
                    fi.Delete()
                Else
                    Exit Sub
                End If
            End If
            catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Users\Uset\Documents\DB_B.mdb")
    
            ADOXtable.Name = "Result"
    
            ADOXtable.Columns.Append("No_ID", DataTypeEnum.adInteger, 40)
            ADOXtable.Columns.Append("A", DataTypeEnum.adDouble)
            ADOXtable.Columns.Append("B", DataTypeEnum.adDouble, 20)
    
            'append tables to database
            catNewDB.Tables.Append(ADOXtable)
    
            catNewDB = Nothing

    Sunday, March 3, 2019 9:03 AM
  • Hi,
    for No_ID as Integer, A and B as Double try this, but values in the third column musst be double (not d3, d5 …):

      Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim _filename As String = "C:\Users\Uset\Documents\DB.mdb"
    
        Dim _conn As String = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={_filename}"
    
        Dim _sql = "INSERT INTO Result(No_ID ,A ,B) Values(?,?,?)"
        Dim _parID As New OleDbParameter("@ID", OleDbType.Integer)
        Dim _parA As New OleDbParameter("@A", OleDbType.Double)
        Dim _parB As New OleDbParameter("@B", OleDbType.Double)
    
        Try
          Using _connection As New OleDbConnection(_conn)
            _connection.Open()
            Using _command As New OleDbCommand(_sql, _connection)
              _command.Parameters.AddRange(New OleDbParameter() {_parID, _parA, _parB})
              For i As Integer = 0 To Me.DataGridView1.RowCount - 2
                _parID.Value = CType(Me.DataGridView1.Item(0, i).Value, Integer)
                _parA.Value = CType(Me.DataGridView1.Item(1, i).Value, Double)
                _parB.Value = CType(Me.DataGridView1.Item(2, i).Value, Double)
                _command.ExecuteNonQuery()
              Next
            End Using
          End Using
        Catch ex As Exception
          MsgBox(ex.ToString)
        End Try
      End Sub


    --
    Viele Grüsse
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks



    Sunday, March 3, 2019 9:19 AM
  • Hello,

    When reading this understand there are many variables when working with Excel and Access for this type of operation, you should be able to use code "as is" or make changes to accommodate your specific needs. 

    I put together an example which uses a MDB I had on hand. The full project source is in the following GitHub repository. Please note this is a simple example which may or may not full fill your exact requirements as there are many moving parts from "does the WorkSheet have strict data typing or not", "are there column names or not for the WorkSheet" and so on.

    There are eight columns and 1000 records, time to insert into the database table is less than one second. Note that I work against the underlying DataTable, not from the DataGridView. Also note a BindingSource component is used, this is optional, whenever I use to write desktop apps I always used them.

    • Excel WorkSheet data is read in using the following class.
    • DataGridView data is pushed to a MS-Access database using the following class.
    • When performing the inserts I traverse DataColumn types and dynamically create parameters on the fly against string or double, more types like integer and date time types can easily be added.
    • Form code can be found here and shown below.

    Form code

    Imports System.IO
    Imports ForumQuestion.Classes
    Imports ForumQuestion.ExtensionMethods
    
    Public Class Form1
        Private bsData As New BindingSource
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim eops As New ExcelOperations
            Dim excelFileName As String =
                    Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "PeopleData.xlsx")
    
            Dim dt = eops.Read(excelFileName, "People1$")
    
            If eops.IsSuccessful Then
    
                bsData.DataSource = dt
                DataGridView1.DataSource = bsData
    
                For Each column As DataGridViewColumn In DataGridView1.Columns
                    column.HeaderText = column.HeaderText.SplitCamelCase
                Next
    
            End If
    
        End Sub
    
        Private Sub exportButton_Click(sender As Object, e As EventArgs) Handles exportButton.Click
            Dim ops As New DataOperations
            Dim dt = CType(bsData.DataSource, DataTable)
    
            ops.InsertRows(dt)
    
            If ops.IsSuccessful Then
                MessageBox.Show($"Inserted {ops.RowCount}")
            Else
                MessageBox.Show($"Failure '{ops.LastException.Message}'")
            End If
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, March 3, 2019 12:10 PM
    Moderator
  • Yesssssssssssss,you very goood.

    Sunday, March 3, 2019 12:37 PM
  • Thank you very much Karen.
    Monday, March 4, 2019 1:34 AM
  • Thank you very much Karen.
    Your very welcome :-)

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, March 4, 2019 1:43 AM
    Moderator