Results 1 to 9 of 9

Thread: DataGridView with DateTimePicker value not being saved in SQLite db.

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2020
    Posts
    7

    DataGridView with DateTimePicker value not being saved in SQLite db.

    Hi all. I have a dgv with 4 columns using 'Designer'. ID, Company, Price, Date. (Non Formatted)
    and a SQLite db with Date column property type DATE.
    This code works when typing a date in the dgv. It saves to the db and loads back to the dgv without any problems. It will not save the date to the db with a datepicker installed and will wipe any previously typed dates. I have also coded this project with Dgv1_CellValueChanged and used parameters for updating. It also works fine when dates are typed in. but it also does not save the date with a datepicker. I want to use a datepicker as I prefer not to have those ugly 'Incorrect data input' messages popping up at the user. There are no errors given when this code is run. It just does not save the date to the db. I'm guessing its a format problem and I don't know how to fix that. Any help would be appreciated. Thanks, lejoc

    Code:
    ' DateTimePicker (value) save to SQLite db
    
    Imports System.Data.SQLite
    Public Class Form1
        Private sqlCon As SQLiteConnection
        Private Const connectionString As String = "Data Source= E:\SQLiteDatabases\Customers;Version=3"
    
        Private da As SQLiteDataAdapter
        Private dt As DataTable
        Private cmdBuilder As SQLiteCommandBuilder
        Private DateTimePicker1 As DateTimePicker
    
        Private Sub sampleForm_Load(sender As Object, e As System.EventArgs) Handles Me.Load
            Try
                sqlCon = New SQLiteConnection(connectionString)
                sqlCon.Open()
                da = New SQLiteDataAdapter("SELECT * FROM Customer", sqlCon)
                cmdBuilder = New SQLiteCommandBuilder(da)
                dt = New DataTable
                da.Fill(dt)
                Me.Dgv1.DataSource = dt
    
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Sub
    
        '    SET UP DateTimePicker
        Private Sub DPTextchange(ByVal sender As Object, ByVal e As EventArgs)
            Dgv1.CurrentCell.Value = DateTimePicker1.Text.ToString()
        End Sub
    
        Private Sub DPClose(ByVal sender As Object, ByVal e As EventArgs)
            DateTimePicker1.Visible = False
        End Sub
    
        ' SET DATETIMEPICKER  
        Private Sub Dgv1_CellClick(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs) Handles Dgv1.CellClick
            If e.ColumnIndex = 3 Then
                Dim DateTimePicker1 As DateTimePicker = New DateTimePicker()
                Dgv1.Controls.Add(DateTimePicker1)
                DateTimePicker1.Format = DateTimePickerFormat.Custom
                DateTimePicker1.CustomFormat = "dd/MM/yyyy"
                Dim displaycalendar As Rectangle = Dgv1.GetCellDisplayRectangle(e.ColumnIndex, e.RowIndex, True)
                DateTimePicker1.Size = New Size(displaycalendar.Width, displaycalendar.Height)
                DateTimePicker1.Location = New Point(displaycalendar.X, displaycalendar.Y)
            End If
        End Sub
    
        Private Sub SaveButton_Click(sender As System.Object, e As System.EventArgs) Handles SaveButton.Click
            Try
                da.Update(dt)
                MsgBox("Changes Done")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End Sub
    End Class

  2. #2

    Thread Starter
    New Member
    Join Date
    Feb 2020
    Posts
    7

    Re: DataGridView with DateTimePicker value not being saved in SQLite db.

    Hi all. Since I posted this question, I have changed this code to run with a Sql Server local db. It still does not save the DatePicker value to the db. In the original question I said that the DatePicker wipes the previously typed date, this is incorrect. The typed date remains in the db, just does not get updated. I am still looking to solve this problem. I am now thinking I may have invoked the DateTimePicker incorrectly and so it does not return a value to be updated the db. I'm still searching for an answer. Please help. Thanks, lejoc.

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    35,024

    Re: DataGridView with DateTimePicker value not being saved in SQLite db.

    The Update method returns an integer. Often, you can ignore that, but it would be useful to know, in this case. What it returns is the number of records updated. In your case, if the call to da.Update returns 0, then you know that the problem is that the datatable has no changes. That's most likely the reason, in this case, but it would be worth checking to make sure. If the return is anything other than 0, the problem becomes stranger.
    My usual boring signature: Nothing

  4. #4

    Thread Starter
    New Member
    Join Date
    Feb 2020
    Posts
    7

    Re: DataGridView with DateTimePicker value not being saved in SQLite db.

    @shaggy Hiker. Thank you for your reply. I have seen this advice given to others before. I don't know how to code for a da.Update return, but I will research and give it a go and come back with the answer. Thanks, lejoc

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    35,024

    Re: DataGridView with DateTimePicker value not being saved in SQLite db.

    All you'd have to do is change:

    da.Update(dt)

    to this:

    dim someVariable = da.Update(dt)

    That's it. You can then put a breakpoint on the next line and take a look at what someVariable holds.
    My usual boring signature: Nothing

  6. #6

    Thread Starter
    New Member
    Join Date
    Feb 2020
    Posts
    7

    Re: DataGridView with DateTimePicker value not being saved in SQLite db.

    @ShaggyHiker. I ran your suggestion. It returned 0. How do I read the value and pass it to the command builder?

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,958

    Re: DataGridView with DateTimePicker value not being saved in SQLite db.

    You are going about this in very much the wrong way. Creating your own control and displaying it over the grid is very dodgy. Instead, you should be creating your own custom column type and adding that to the grid. It will then automatically create the correct control type for editing cells. Here's Microsoft's example, that just so happens to do exactly what you want:

    https://docs.microsoft.com/en-us/dot...gridview-cells

  8. #8
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    35,024

    Re: DataGridView with DateTimePicker value not being saved in SQLite db.

    Ha. I didn't even look at that. It's an old technique that was used to get around the fact that the MSFlexGrid in VB6 was read only. You had to put something in front of a cell for editing, then swap the data back into the underlying cell after editing was done.
    My usual boring signature: Nothing

  9. #9

    Thread Starter
    New Member
    Join Date
    Feb 2020
    Posts
    7

    Re: DataGridView with DateTimePicker value not being saved in SQLite db.

    Hi JM. I have ran that MS Docs code Yes it works. It's a separate column. I tried to use it but I do not have enough knowledge to create a DGV and add the column using that code. If I could do it, would the date data get saved to the database separately to a second table. Would I do a join to read all my data back to the DGV. I intend this data to be manipulated by the date field. I decided to use a DateTimePicker after reading much discussion about the problems people were having with date validation and formatting. More advice would be welcome. Thank you. lejoc.

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