none
Database MDB Null value RRS feed

  • Question

  • 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


    Saturday, March 9, 2019 5:54 AM

Answers

  • Hi,
    I change your code. The demo ist working properly.

    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class Form03
    
      Private WithEvents btn1 As New Button With {.Text = "Create Database", .Dock = DockStyle.Top}
      Private WithEvents btn2 As New Button With {.Text = "Load data", .Dock = DockStyle.Top}
      Private WithEvents btn3 As New Button With {.Text = "Insert row direct in database", .Dock = DockStyle.Top}
      Private WithEvents btn4 As New Button With {.Text = "Save data from DataGridView", .Dock = DockStyle.Top}
      Private DataGridView1 As New DataGridView With {.Dock = DockStyle.Fill}
    
      Private dbFileName As String = "db_.mdb"
      Private dbCnStringJet As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}"
      Private dbCnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=True"
    
      Private xlFileName As String = "C:\temp\DATA.xlsx"
      Private xlCnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;IMEX=1;'"
    
      Private ds1 As New DataSet
    
      Private Sub Form03_Load(sender As Object, e As EventArgs) Handles Me.Load
        Me.Controls.AddRange(New Control() {DataGridView1, btn4, btn3, btn2, btn1})
      End Sub
    
      Private Sub btn1_Click(sender As Object, e As EventArgs) Handles btn1.Click
        ' test exists and delete if necessary
        Dim fi As New FileInfo(dbfilename)
        If fi.Exists Then
          If MessageBox.Show("Delete?", $"Existing File {dbfilename}",
             MessageBoxButtons.YesNo) = DialogResult.Yes Then fi.Delete()
        End If
        ' create new databas file it not exists
        If Not File.Exists(dbfilename) Then
          Dim catNewDB As New ADOX.Catalog
          catNewDB.Create(String.Format(dbcnStringJet, dbfilename))
          catNewDB = Nothing
        End If
        ' Create Table if not exists
        Using cn As New OleDb.OleDbConnection(String.Format(dbcnString, {dbfilename}))
          cn.Open()
          Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
            cmd.CommandText = "CREATE Table [Result_]([TN] Double,[Date_Received] Date)"
            Try
              cmd.ExecuteNonQuery()
            Catch ex As Exception
            End Try
          End Using
        End Using
      End Sub
    
      Private Sub btn2_Click(sender As Object, e As EventArgs) Handles btn2.Click
        Try
          Using da As New OleDbDataAdapter("SELECT [TN], [Date_Received] FROM [Sheet1$]", String.Format(xlCnString, xlFileName))
            da.Fill(ds1, "Sheet1")
            Me.DataGridView1.DataSource = ds1
            Me.DataGridView1.DataMember = "Sheet1"
          End Using
        Catch ex As Exception
          MsgBox($"Import Failed:{vbNewLine}{ex}")
        End Try
      End Sub
    
      Private Sub btn3_Click(sender As Object, e As EventArgs) Handles btn3.Click
    
        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(String.Format(dbCnString, dbFileName))
            _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 btn4_Click(sender As Object, e As EventArgs) Handles btn4.Click
        Try
          For Each row As DataRow In ds1.Tables("Sheet1").Rows
            row.SetAdded()
          Next
          Using da As New OleDbDataAdapter("Select * from Result_", String.Format(dbCnString, dbFileName))
            Dim cb As New OleDbCommandBuilder(da)
            da.Update(ds1.Tables("Sheet1"))
          End Using
        Catch ex As Exception
          MsgBox($"Save Failed:{vbNewLine}{ex}")
        End Try
      End Sub
    
    End Class


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

    • Marked as answer by mipakteh Sunday, March 10, 2019 1:47 AM
    Saturday, March 9, 2019 9:30 AM

All replies

  • Hi,
    I change your code. The demo ist working properly.

    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class Form03
    
      Private WithEvents btn1 As New Button With {.Text = "Create Database", .Dock = DockStyle.Top}
      Private WithEvents btn2 As New Button With {.Text = "Load data", .Dock = DockStyle.Top}
      Private WithEvents btn3 As New Button With {.Text = "Insert row direct in database", .Dock = DockStyle.Top}
      Private WithEvents btn4 As New Button With {.Text = "Save data from DataGridView", .Dock = DockStyle.Top}
      Private DataGridView1 As New DataGridView With {.Dock = DockStyle.Fill}
    
      Private dbFileName As String = "db_.mdb"
      Private dbCnStringJet As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}"
      Private dbCnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=True"
    
      Private xlFileName As String = "C:\temp\DATA.xlsx"
      Private xlCnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;IMEX=1;'"
    
      Private ds1 As New DataSet
    
      Private Sub Form03_Load(sender As Object, e As EventArgs) Handles Me.Load
        Me.Controls.AddRange(New Control() {DataGridView1, btn4, btn3, btn2, btn1})
      End Sub
    
      Private Sub btn1_Click(sender As Object, e As EventArgs) Handles btn1.Click
        ' test exists and delete if necessary
        Dim fi As New FileInfo(dbfilename)
        If fi.Exists Then
          If MessageBox.Show("Delete?", $"Existing File {dbfilename}",
             MessageBoxButtons.YesNo) = DialogResult.Yes Then fi.Delete()
        End If
        ' create new databas file it not exists
        If Not File.Exists(dbfilename) Then
          Dim catNewDB As New ADOX.Catalog
          catNewDB.Create(String.Format(dbcnStringJet, dbfilename))
          catNewDB = Nothing
        End If
        ' Create Table if not exists
        Using cn As New OleDb.OleDbConnection(String.Format(dbcnString, {dbfilename}))
          cn.Open()
          Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
            cmd.CommandText = "CREATE Table [Result_]([TN] Double,[Date_Received] Date)"
            Try
              cmd.ExecuteNonQuery()
            Catch ex As Exception
            End Try
          End Using
        End Using
      End Sub
    
      Private Sub btn2_Click(sender As Object, e As EventArgs) Handles btn2.Click
        Try
          Using da As New OleDbDataAdapter("SELECT [TN], [Date_Received] FROM [Sheet1$]", String.Format(xlCnString, xlFileName))
            da.Fill(ds1, "Sheet1")
            Me.DataGridView1.DataSource = ds1
            Me.DataGridView1.DataMember = "Sheet1"
          End Using
        Catch ex As Exception
          MsgBox($"Import Failed:{vbNewLine}{ex}")
        End Try
      End Sub
    
      Private Sub btn3_Click(sender As Object, e As EventArgs) Handles btn3.Click
    
        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(String.Format(dbCnString, dbFileName))
            _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 btn4_Click(sender As Object, e As EventArgs) Handles btn4.Click
        Try
          For Each row As DataRow In ds1.Tables("Sheet1").Rows
            row.SetAdded()
          Next
          Using da As New OleDbDataAdapter("Select * from Result_", String.Format(dbCnString, dbFileName))
            Dim cb As New OleDbCommandBuilder(da)
            da.Update(ds1.Tables("Sheet1"))
          End Using
        Catch ex As Exception
          MsgBox($"Save Failed:{vbNewLine}{ex}")
        End Try
      End Sub
    
    End Class


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

    • Marked as answer by mipakteh Sunday, March 10, 2019 1:47 AM
    Saturday, March 9, 2019 9:30 AM
  • very Nice,Thank you very much Peter.
    Sunday, March 10, 2019 1:47 AM