none
Form with exact date and time RRS feed

  • Question

  • To avoid a mess on forum, I assume my own question.

    I'm based on this program and I would like to operate the exact time of issue and return. I used the example from this question
    https://social.msdn.microsoft.com/Forums/vstudio/en-US/54074ca3-1b57-465c-b2e0-65a46ba664b8/adding-second-datetime-in-database?forum=vbgeneral
    I got a little problem.... when I add new record I get date like that : 2018-01-21 00:00 wen I edit record I can set any Time.

    Sunday, January 21, 2018 4:05 PM

All replies

  • For the example, it uses MS-Access which does not have a time type so start off with.

    Now if you want to set the time

    Set the following properties of the DateTimePicker (note you can take off the seconds if so desire)

    Which gives us

    Code

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            MessageBox.Show($"Date: {DateTimePicker1.Value.ToString("MM-dd-yyyy")} " &
                $"Time: {DateTimePicker1.Value.ToString("hh:mm tt")} and {DateTimePicker1.Value}")
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, January 21, 2018 4:51 PM
    Moderator
  • Thank you for good advice :) It is possible to apply it to your project.Specifically, I mean this method:

    Imports System.Data.OleDb
    
    ' Contains BaseExceptionProperties class
    Imports KarensBaseClasses
    
    Public Class DatabaseOperations
        Inherits BaseExceptionProperties
    
        Private Builder As New OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0"
            }
        ''' <summary>
        ''' Default our connection to a database in the executable folder
        ''' </summary>
        Public Sub New()
            Builder.DataSource = IO.Path.Combine(
                AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
        End Sub
        ''' <summary>
        ''' Use a different database then the one in the executable folder.
        ''' When using this the database must have the same table and fields.
        ''' </summary>
        ''' <param name="pDataSource"></param>
        Public Sub New(ByVal pDataSource As String)
            Builder.DataSource = pDataSource
        End Sub
        ''' <summary>
        ''' Read USA customers from database into a DataTable
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks>
        ''' Database is assumed to be in the Bin\Debug folder.
        ''' </remarks>
        Public Function LoadCustomers() As DataTable
            Dim dt As New DataTable With {.TableName = "Customer"}
    
            If Not IO.File.Exists(Builder.DataSource) Then
                mHasException = True
                mLastException = New IO.FileNotFoundException(Builder.DataSource)
            End If
    
            Using cn As New OleDbConnection(Builder.ConnectionString)
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT 
                                Identifier, 
                                JoinDate,
                                Data1,
                                CompanyName, 
                                ContactName, 
                                ContactTitle,
                                Country
                            FROM Customer 
                            ORDER BY CompanyName;
                        </SQL>.Value
    
                    Try
                        cn.Open()
                        dt.Load(cmd.ExecuteReader)
    
                        dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
                        dt.Columns("Country").ColumnMapping = MappingType.Hidden
    
                    Catch ex As Exception
                        mHasException = True
                        mLastException = ex
                    End Try
    
                    Return dt
    
                End Using
            End Using
        End Function
    
        ''' <summary>
        ''' Load a list of contact titles.
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks>
        ''' In a well normalized database we would have a contact tile
        ''' table and the customer table would have a integer field that
        ''' would join to the contact title table.
        ''' </remarks>
        Public Function LoadContactTitles() As List(Of String)
    
            Dim titleList As New List(Of String)
    
            Using cn As New OleDbConnection(Builder.ConnectionString)
                Using cmd As New OleDbCommand With {.Connection = cn}
    
                    cmd.CommandText =
                        <SQL>
                            SELECT DISTINCT ContactTitle
                            FROM Customer
                        </SQL>.Value
    
                    Try
    
                        cn.Open()
    
                        Dim reader As OleDbDataReader = cmd.ExecuteReader
    
                        While reader.Read
                            titleList.Add(reader.GetString(0))
                        End While
    
                    Catch ex As Exception
                        mHasException = True
                        mLastException = ex
                    End Try
    
                End Using
            End Using
    
            Return titleList
    
        End Function
        ''' <summary>
        ''' Remove a customer by primary key
        ''' </summary>
        ''' <param name="pIdentfier"></param>
        ''' <returns></returns>
        Public Function RemoveCurrentCustomer(ByVal pIdentfier As Integer) As Boolean
            Try
                Using cn As New OleDbConnection(Builder.ConnectionString)
                    Using cmd As New OleDbCommand With {.Connection = cn}
    
                        cmd.CommandText = "DELETE FROM Customer WHERE Identifier = @Identifier"
    
                        Dim IdentifierParameter As New OleDbParameter With
                            {
                                .DbType = DbType.Int32,
                                .ParameterName = "@Identifier",
                                .Value = pIdentfier
                            }
                        cmd.Parameters.Add(IdentifierParameter)
    
                        Try
                            cn.Open()
    
                            Dim Affected = cmd.ExecuteNonQuery
                            Return Affected = 1
    
                        Catch ex As Exception
                            mHasException = True
                            mLastException = ex
                            Return False
                        End Try
                    End Using
                End Using
    
            Catch ex As Exception
                mHasException = True
                mLastException = ex
                Return IsSuccessFul
            End Try
        End Function
        ''' <summary>
        ''' Add a new customer to the database table
        ''' </summary>
        ''' <param name="pName"></param>
        ''' <param name="pContact"></param>
        ''' <param name="pContactTitle"></param>
        ''' <param name="pIdentfier"></param>
        ''' <returns></returns>
        Public Function AddNewRow(
            ByVal pName As String,
            ByVal pContact As String,
            ByVal pContactTitle As String,
            ByVal pJoinDate As Date,
            ByVal pData1 As Date,
            ByRef pIdentfier As Integer) As Boolean
    
            Dim Success As Boolean = True
            Try
                Using cn As New OleDbConnection(Builder.ConnectionString)
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText =
                            <SQL>
                                INSERT INTO Customer 
                                    (
                                        CompanyName,
                                        ContactName,
                                        ContactTitle,
                                        JoinDate,
                                        Data1
                                    ) 
                                Values
                                    (
                                        @CompanyName,
                                        @ContactName,
                                        @ContactTitle,
                                        @JoinDate,
                                        @Data1
                                    )
                            </SQL>.Value
    
                        cmd.Parameters.AddWithValue("@CompanyName", pName)
                        cmd.Parameters.AddWithValue("@ContactName", pContact)
                        cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
                        cmd.Parameters.AddWithValue("@JoinDate", pJoinDate)
                        cmd.Parameters.AddWithValue("@Data1", pData1)
    
                        cn.Open()
    
                        cmd.ExecuteNonQuery()
    
                        cmd.CommandText = "Select @@Identity"
                        pIdentfier = CInt(cmd.ExecuteScalar)
    
                    End Using
                End Using
    
            Catch ex As Exception
                mHasException = True
                mLastException = ex
                Success = False
            End Try
    
            Return Success
    
        End Function
        ''' <summary>
        ''' Update a row
        ''' </summary>
        ''' <param name="pDataRow"></param>
        ''' <returns></returns>
        ''' <remarks>
        ''' I used Parameters.Add instead of Parameters.AddWithValue
        ''' where Parameters.AddWithValue is usually best yet wanted to
        ''' show Parameters.Add as many don't realize this is a viable
        ''' way to add parameters and really shines when doing multiple
        ''' adds or updates in say a for-each or for-next.
        ''' </remarks>
        Public Function UpdateRow(ByVal pDataRow As DataRow) As Boolean
    
            Try
                Using cn As New OleDbConnection(Builder.ConnectionString)
    
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText =
                        <SQL>
                            UPDATE 
                                Customer 
                            SET 
                                CompanyName = @CompanyName, 
                                ContactName = @ContactName,
                                ContactTitle = @ContactTitle,
                                JoinDate = @JoinDate, 
                                Data1 = @Data1
                            WHERE Identifier = @Identifier
                        </SQL>.Value
    
    
                        cmd.Parameters.AddWithValue("@CompanyName", pDataRow.Field(Of String)("CompanyName"))
                        cmd.Parameters.AddWithValue("@ContactName", pDataRow.Field(Of String)("ContactName"))
                        cmd.Parameters.AddWithValue("@ContactTitle", pDataRow.Field(Of String)("ContactTitle"))
                        cmd.Parameters.AddWithValue("@JoinDate", pDataRow.Field(Of DateTime)("JoinDate").Date)
                        cmd.Parameters.AddWithValue("@Data1", pDataRow.Field(Of DateTime)("Data1").Date)
                        cmd.Parameters.AddWithValue("@Identifier", pDataRow.Field(Of Integer)("Identifier"))
    
                        Try
                            cn.Open()
    
                            Dim Affected = cmd.ExecuteNonQuery
                            Return Affected = 1
                        Catch ex As Exception
    
                            mHasException = True
                            mLastException = ex
    
                            Return IsSuccessFul
    
                        End Try
                    End Using
                End Using
    
            Catch ex As Exception
                mHasException = True
                mLastException = ex
    
                Return IsSuccessFul
    
            End Try
        End Function
    End Class
    


    Sunday, January 21, 2018 6:03 PM
  • I really want you to try first for learning. Also I don't have time to spend on this, just spent over an hour writing a code sample for a C# question and now getting back to finishing a MSDN code sample for Microsoft today.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, January 21, 2018 6:10 PM
    Moderator
  • ok, sorry for the problem
    Sunday, January 21, 2018 7:13 PM
  • Remove in the sample from Karen the word .Date after the date parameter settings.

    cmd.Parameters.AddWithValue("@JoinDate", pDataRow.Field(Of DateTime)("JoinDate").Date)
    cmd.Parameters.AddWithValue("@Data1", pDataRow.Field(Of DateTime)("Data1").Date)


    Success Cor

    Sunday, January 21, 2018 7:58 PM
  • Remove in the sample from Karen the word .Date after the date parameter settings.

    cmd.Parameters.AddWithValue("@JoinDate", pDataRow.Field(Of DateTime)("JoinDate").Date)
    cmd.Parameters.AddWithValue("@Data1", pDataRow.Field(Of DateTime)("Data1").Date)


    Success Cor

    Thank you for answering, but this is for UpdateRow function.
    Problem is to add a time to new record, now time always is 00:00

    • Edited by Betti111 Sunday, January 21, 2018 9:07 PM
    Sunday, January 21, 2018 9:07 PM
  • There are no time field types in ms-access..

    This shows no time

    Because of this 

    When passing proper values to the database table the first row shows data and time but the second does not because only a date was passed in with the default time.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, January 21, 2018 10:25 PM
    Moderator
  • Yes, I try some things and I changed:

         DataGridView1.Columns("JoinDate").DefaultCellStyle.Format = "yyyy-MM-dd HH:mm"
         DataGridView1.Columns("Data1").DefaultCellStyle.Format = "yyyy-MM-dd HH:mm"
         cmd.Parameters.AddWithValue("@JoinDate", pJoinDate.ToString("yyyy-MM-dd HH:mm"))
         cmd.Parameters.AddWithValue("@Data1", pData1.ToString("yyyy-MM-dd HH:mm"))
    and
            ByVal pJoinDate As DateTime,
            ByVal pData1 As DateTime,

    My Time looks always with 00:00 time. When i edit it I can set hours and minutes


    • Edited by Betti111 Monday, January 22, 2018 8:41 AM
    Monday, January 22, 2018 8:40 AM
  • Here is a quick fix (which I justed coded and tested and works).

    In UpdateRow Data1 (and JoinDate) need to use

    cmd.Parameters.AddWithValue("@Data1", pDataRow.Field(Of DateTime)("Data1").ToString("MM/dd/yyyy hh:mm"))

    In AddNewRow

    cmd.Parameters.AddWithValue("@Data1", pData1.ToString("MM/dd/yyyy hh:mm"))

    Next change

    Private Sub AddNewRow_Click(ByVal sender As System.Object, ByVal e As EventArgs) Handles ToolStripButton2.Click
        Dim f As New frmEditor
    
        Try
            f.cboTitle.DataSource = mTitleList
            If f.ShowDialog = DialogResult.OK Then
                Dim Identifier As Integer = 0
                If ops.AddNewRow(f.txtCompanyName.Text, f.txtContactName.Text, f.cboTitle.Text, f.DateTimePicker1.Value, f.DateTimePicker2.Value, Identifier) Then
                    bsCustomers.DataTable.Rows.Add(New Object() {Identifier, f.DateTimePicker1.Value, f.DateTimePicker2.Value, f.txtCompanyName.Text, f.txtContactName.Text, f.cboTitle.Text})
                    bsCustomers.Locate("Identifier", Identifier.ToString)
                Else
                    If Not ops.IsSuccessFul Then
                        MessageBox.Show($"Failed to add new record: {ops.LastExceptionMessage}")
                    End If
                End If
            End If
        Finally
            f.Dispose()
        End Try
    End Sub

    Then

    Private Sub EditCurrentCustomer()
    
        Dim f As New frmEditor
    
    
        Try
            ' Generally we don't bind data like this but instead pass data via
            ' a custom new constructor, place the data into a class e.g. a Customer
            ' class then populate controls from this. The class would be public
            ' so when the form is closed we would get the values. I wanted to show
            ' data binding as another option.
            '
            ' Two fields are bound while the third (the combo box) is manually set.
            '
            f.txtCompanyName.DataBindings.Add("Text", bsCustomers, "CompanyName")
            f.txtContactName.DataBindings.Add("Text", bsCustomers, "ContactName")
            f.cboTitle.DataSource = mTitleList
    
            f.DateTimePicker1.Value = CType(bsCustomers.Current, DataRowView).Row.Field(Of DateTime)("JoinDate")
            Dim testForNull = CType(bsCustomers.Current, DataRowView).Row("Data1")
    
            If Convert.IsDBNull(testForNull) Then
                f.DateTimePicker2.Value = Now
            Else
                f.DateTimePicker2.Value = CType(bsCustomers.Current, DataRowView).Row.Field(Of DateTime)("Data1")
            End If
    
            ' Display the current Contact title by locating
            ' the title in a list of titles. We could optimize
            ' this by having a ContactTitle table yet I'm simply using
            ' a pre-existing table from Microsoft.
            f.cboTitle.SelectedIndex = mTitleList.IndexOf(bsCustomers.CurrentRow.Field(Of String)("ContactTitle"))
    
            f.ActiveControl = f.cmdCancel
    
            If f.ShowDialog = DialogResult.OK Then
    
                bsCustomers.CurrentRow.SetField(Of String)("ContactTitle", f.cboTitle.Text)
                bsCustomers.CurrentRow.SetField(Of DateTime)("JoinDate", f.DateTimePicker1.Value)
                bsCustomers.CurrentRow.SetField(Of DateTime)("Data1", f.DateTimePicker2.Value)
                If ops.UpdateRow(bsCustomers.CurrentRow) Then
                    bsCustomers.DataTable.AcceptChanges()
                Else
                    If Not ops.IsSuccessFul Then
                        MessageBox.Show($"Edit failed: {ops.LastExceptionMessage}")
                    End If
                End If
            Else
                bsCustomers.CancelEdit()
            End If
        Finally
            f.Dispose()
        End Try
    
    End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, January 22, 2018 11:00 AM
    Moderator
  • Karen,

    Because of the time she gives reactions, I doubt she is using an USA date format. More likely is ISO or standard world order of notation. 


    Success Cor

    Monday, January 22, 2018 11:05 AM
  • Karen,

    Because of the time she gives reactions, I doubt she is using an USA date format. More likely is ISO or standard world order of notation. 


    Success Cor

    Cor, if that is the case and being a coder they should be able to figure this out :-)

    Dim timeSep As String = CultureInfo.CurrentCulture.DateTimeFormat.TimeSeparator
    Dim formatDate As String = $"{CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern} " &
        $"hh{timeSep}mm{timeSep}ss"


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Monday, January 22, 2018 11:49 AM
    Moderator