save to access database .MDB

[mipakteh]

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

Continue reading...