-
Jun 12th, 2021, 09:36 AM
#1
Thread Starter
Member
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
-
Jun 12th, 2021, 09:44 AM
#2
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.
-
Jun 12th, 2021, 10:16 AM
#3
Thread Starter
Member
Re: How to insert null or empty date from datagridview column to database using vb.ne
Originally Posted by jmcilhinney
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.
-
Jun 12th, 2021, 10:21 AM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|