Results 1 to 4 of 4

Thread: How to insert null or empty date from datagridview column to database using vb.net?

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Question How to insert null or empty date from datagridview column to database using vb.net?

    Hello fellow coders. I'm asking for your help on how to insert or save empty date value or null value from datagridview column to access database. I research over the web but unfortunately not solved my problem. I'm trying to set dateret as string but it says: "String cannot be converted to date". If I set dateret as Date, then it says: "Not recognized as valid datetime" or "Datatype mismatched". How to address this problem? Following is my simple code. Thank you.

    Code:
    Dim connstring As String
                connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=retirement.accdb"
                Dim conn As OleDbConnection = New OleDbConnection(connstring)
                Dim ln, fn, mn, retiredate As String
                'Dim dateret as Date
                'Dim dateret as String
    
                For i As Integer = 0 To Me.DataGridView1.RowCount - 1
    
                    ln = Me.DataGridView1.Item(0, i).Value
                    fn = Me.DataGridView1.Item(1, i).Value
                    mn = Me.DataGridView1.Item(2, i).Value
                  
                  
              If Me.DataGridView1.Item(3, i).Value Is Nothing Or Me.DataGridView1.Item(3, i).Value = String.Empty Then
                    'nothing to insert or null value inserted
              else
                      dateretire = Me.DataGridView1.Item(3, i).Value
              End If
                  
                    Dim Sql = "insert into emprecords(lastname, firstname, middlename, dateretire) values('" & ln & "','" & fn & "','" & mn & "','" & dateret & "')"
    
    
                    Dim comm As New OleDb.OleDbCommand
                    comm.CommandText = Sql
                    comm.Connection = conn
                    conn.Open()
                    comm.ExecuteNonQuery()
    
                    comm.Dispose()
                    conn.Close()
    
                Next
                MsgBox("Imported Successfully! Click refresh button to reload data.", MsgBoxStyle.Information, "Import")
                Me.Close()
            End If

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: How to insert null or empty date from datagridview column to database using vb.ne

    It's so painful seeing people purposely make their lives difficult and then asking how to fix it. If you do it properly in the first place then there's nothing to fix. Create a DataTable with the appropriate schema, either by calling Fill or FillSchema on a data adapter or just building it manually. Bind that DataTable to your grid, preferably via a BindingSource. Any changes the user makes in the grid will automatically be propagated to the DataTable. You then simply call Update on your data adapter and the data is saved. The DataTable already contains the data, including NULL values where appropriate, so there's nothing to do.

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Re: How to insert null or empty date from datagridview column to database using vb.ne

    Quote Originally Posted by jmcilhinney View Post
    It's so painful seeing people purposely make their lives difficult and then asking how to fix it. If you do it properly in the first place then there's nothing to fix. Create a DataTable with the appropriate schema, either by calling Fill or FillSchema on a data adapter or just building it manually. Bind that DataTable to your grid, preferably via a BindingSource. Any changes the user makes in the grid will automatically be propagated to the DataTable. You then simply call Update on your data adapter and the data is saved. The DataTable already contains the data, including NULL values where appropriate, so there's nothing to do.
    Thank you so much for your suggestion sir but I cannot do that because the data in datagridview is inserted from MS excel.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: How to insert null or empty date from datagridview column to database using vb.ne

    That's a non sequitur. There's no reason that you cannot populate a DataTable with that data. You can even use a data adapter to retrieve the data from your Excel file straight into a DataTable.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width