Database MDB Null value

[mipakteh]

Hi All,

what I'm Doing,

From Excel to datagridview then save to MDB file database.

1.Button1 - Create Database MDB

2.Button2 - Put from Excel to Datagridview

3.Button3 - Save Datagridview to Database MDB file

4.Button4 - Test Read /Open File Database MDB

My problem is when the cell in Datagridview contains Null or nothing.The Column "Date_Received" have only one at the first Row.Other is Nothing.



I use the Button3 as save.It save like bellow;



I need ,when enter the Button4.It show ;



Imports ADOX
Imports System.Data.OleDb

Public Class Form1

Dim ds1 As New DataSet

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
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_Daun2017.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("TN", DataTypeEnum.adDouble)
ADOXtable.Columns.Append("Date_Received", DataTypeEnum.adDate)



'append tables to database
catNewDB.Tables.Append(ADOXtable)

catNewDB = Nothing

End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim _filename As String = "C:\Users\Uset\Desktop\DATA.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 TN,Date_Received FROM [Sheet1$]"

da.SelectCommand = _command

Try
da.Fill(ds1, "Sheet1")
MessageBox.Show("The import is complete!")
Me.DataGridView1.DataSource = ds1
Me.DataGridView1.DataMember = "Sheet1"

Catch e1 As Exception
MessageBox.Show("Import Failed, correct Column name in the sheet!")
End Try

End Sub

Private Sub Button3_Click(sender As Object, vbEmpty 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_(TN,Date_Received) Values(?,?)"
Dim _parTN As New OleDbParameter("@TN", OleDbType.VarChar, 255)
Dim _parDate_Received As New OleDbParameter("@Date_Received", OleDbType.Date)



Try
Using _connection As New OleDbConnection(_conn)
_connection.Open()
Using _command As New OleDbCommand(_sql, _connection)
_command.Parameters.AddRange(New OleDbParameter() {_parTN, _parDate_Received})

For i As Integer = 0 To Me.DataGridView1.RowCount - 2
_parTN.Value = Me.DataGridView1.Item(0, i).Value
If Not DataGridView1.Rows(i).Cells("Date_Received").Value Is DBNull.Value Then
_parDate_Received.Value = Me.DataGridView1.Item(1, i).Value
End If
_command.ExecuteNonQuery()
Next


End Using
End Using
Catch ex As Exception
MsgBox(ex.ToString)
End Try

End Sub

Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
Dim con As New OleDbConnection
Dim ds As New DataSet
Dim dt As New DataTable
Dim da As New OleDbDataAdapter


con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Users\Uset\Documents\db_.mdb"
con.Open()
ds.Tables.Add(dt)
da = New OleDbDataAdapter("Select * from Result_", con)
da.Fill(dt)
DataGridView1.DataSource = dt
con.Close()
End Sub
End Class

Continue reading...