get date value from datetimepicker and store date into database but not with time!!

Question

0.00/5 (No votes)

See more:

pros and master out there please have a look on my code, i do not how to solve my problem regarding to get (datevalue) and insert into database, i had try my best to custom code them out but the inserted (datevalue) still with time '12:00:00'. please help me!!!

Form load part

Private Sub Admin_and_GA_Matter_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        DateTimePicker1.Format = DateTimePickerFormat.Custom
        DateTimePicker1.CustomFormat = "dd MMM yyyy"
         DateTimePicker2.Format = DateTimePickerFormat.Custom
        DateTimePicker2.CustomFormat = "dd MMM yyyy"

    End Sub

insert part

Private Sub insertinto()

       Dim insert1 As String = "INSERT INTO AGITbl (CreateBy, StartDate, CloseDate, StartTime, CloseTime, Contentx, Remark, Status, ShowOnB) VALUES (@CreateBy, @StartDate, @CloseDate, @StartTime, @CloseTime, @Contentx, @Remark, @Status, @ShowOnB)"
       Dim cmd As New SqlCommand(insert1, connection)
       cmd.Parameters.AddWithValue("@CreateBy", Label10.Text.Trim)

       cmd.Parameters.AddWithValue("@StartDate", DateTimePicker1.Value.ToShortDateString)
       cmd.Parameters.AddWithValue("@CloseDate", DateTimePicker2.Value.ToShortDateString)

       cmd.Parameters.AddWithValue("@StartTime", DateTimePicker3.Value.ToShortTimeString)
       cmd.Parameters.AddWithValue("@CloseTime", DateTimePicker4.Value.ToShortTimeString)
       cmd.Parameters.AddWithValue("@Contentx", RichTextBox1.Text.Trim)
       cmd.Parameters.AddWithValue("@Remark", RichTextBox2.Text.Trim)
       cmd.Parameters.AddWithValue("@Status", Label7.Text.Trim)
       cmd.Parameters.AddWithValue("@ShowOnB", CInt(Label9.Text))

       If RichTextBox1.Text = "" And RichTextBox2.Text = "" And Label7.Text = "" And Label9.Text = 2 Then
           MsgBox("blank content not allow to send")
       ElseIf RichTextBox1.Text IsNot Nothing And RichTextBox2.Text IsNot Nothing And Label7.Text IsNot Nothing And Label9.Text <> 2 Then
           If connection.State = ConnectionState.Closed Then
               connection.Open()
           End If
           cmd.ExecuteNonQuery()
           MsgBox("Information sent")
           connection.Close()
           RichTextBox1.Text = ""
           RichTextBox2.Text = ""
           Label7.Text = ""
           Label9.Text = ""
           Me.RadioButton1.Checked = False
           Me.RadioButton2.Checked = False
           Me.RadioButton3.Checked = False
           Me.RadioButton4.Checked = False
           Me.RadioButton5.Checked = False
       End If

additional load out codes:

VB

Public Sub loadalll()
        Dim sqlq As String = "SELECT * FROM AGITbl"
        Dim sqlcmd As New SqlCommand
        Dim sqladpt As New SqlDataAdapter
        Dim tbl As New DataTable

        With sqlcmd
            .CommandText = sqlq
            .Connection = connection
        End With

        With sqladpt
            .SelectCommand = sqlcmd
            .Fill(tbl)
        End With

        DataGridView1.Rows.Clear()
        For i = 0 To tbl.Rows.Count - 1
            With DataGridView1
                .Rows.Add(tbl.Rows(i)("AGINO"), tbl.Rows(i)("CreateBy"), tbl.Rows(i)("StartDate"), tbl.Rows(i)("CloseDate"), tbl.Rows(i)("StartTime"), tbl.Rows(i)("CloseTime"), tbl.Rows(i)("Contentx"), tbl.Rows(i)("Remark"), tbl.Rows(i)("Status"), tbl.Rows(i)("ShowOnB"))
            End With
        Next
        connection.Close()
    End Sub

A normal load out, show on datagridview!!!

Posted 7-Jul-13 22:52pm

donaldliaw87

Updated 8-Jul-13 14:58pm

v2

Add a Solution

3 solutions

Solution 1

You can set format of your datetime picker itself. On page load

C#

DateTimePicker1.Format = DateTimePickerFormat.Custom
DateTimePicker1.CustomFormat = "MM/dd/yyyy"//set format according to your needs

So change the below lines in your code:

C#

cmd.Parameters.AddWithValue("@StartDate", DateTimePicker1.Value.ToShortDateString)
cmd.Parameters.AddWithValue("@CloseDate", DateTimePicker2.Value.ToShortDateString)

[Additional]:

Custom Date and Time Format Strings[^]

Regards..:laugh:

Posted 7-Jul-13 23:28pm

Thanks7872

Updated 7-Jul-13 23:29pm

v2

Solution 3

thx to OriginalGriff

VB

Public Sub loadalll()
        Dim sqlq As String = "SELECT * FROM AGITbl"
        Dim sqlcmd As New SqlCommand
        Dim sqladpt As New SqlDataAdapter
        Dim tbl As New DataTable
 
        With sqlcmd
            .CommandText = sqlq
            .Connection = connection
        End With
 
        With sqladpt
            .SelectCommand = sqlcmd
            .Fill(tbl)
        End With
 
        DataGridView1.Rows.Clear()
        For i = 0 To tbl.Rows.Count - 1
            With DataGridView1
                .Rows.Add(tbl.Rows(i)("AGINO"), tbl.Rows(i)("CreateBy"), tbl.Rows(i)("StartDate").ToShortDateString, tbl.Rows(i)("CloseDate").ToShortDateString, tbl.Rows(i)("StartTime"), tbl.Rows(i)("CloseTime"), tbl.Rows(i)("Contentx"), tbl.Rows(i)("Remark"), tbl.Rows(i)("Status"), tbl.Rows(i)("ShowOnB"))
            End With
        Next
        connection.Close()
    End Sub

Posted 9-Jul-13 17:04pm

donaldliaw87

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-07-07T23:44:00

Solution 2

Unless you are storing dates in your database as strings - which is a very poor idea to start with as you can't do comparisons or sorting easily - then you can strip off the Time element with DateTime.Date, and store it in an SQL Date field instead of a DateTime. A DateTime will always have a time component, which will often be 00:00:00 for midnight if a time is not specified.

Normally, posting a value like '2013-07-08 10:43' into a Date field will be fine - the time portion will be ignored.

Posted 7-Jul-13 23:44pm

OriginalGriff

Comments

donaldliaw87 8-Jul-13 6:36am

HI originalfriff, my datatype in db is date i'm just get date from datetimepicker and store datevalue into date field. what if i don't want the 00:00:00, is it possible can be done?

cmd.Parameters.AddWithValue("@StartDate", DateTimePicker1.Value.ToShortDateString)

but why still time still exist~!?

OriginalGriff 8-Jul-13 7:18am

If your DB field is Date not DateTime (as you say) then it never stores the time portion, and you don't want to pass a string at all:
cmd.Parameters.AddWithValue("@StartDate", DateTimePicker1.Value)
will do it without any conversions (try to avoid converting datatypes - it can lead to errors such as the month and day being swapped).

If you mean when you present the data back to the user you get a time portion, then show us the code you use for that!

donaldliaw87 8-Jul-13 20:46pm

ok i try your advice!!

donaldliaw87 8-Jul-13 20:51pm

i change to datetimepicker1.value that you had advised but still the same, can you help me again have a look on my codes presented to user ?

OriginalGriff 9-Jul-13 3:16am

When you read the data out of the DB, it is converted to a DateTime - because .NET does not have a Date equivalent. At that point, the 00:00:00 time is added back, because a DateTime always has a time portion (it is a number of milliseconds since an arbitrary point in the past).

Format your DateTime when you add it to the DataGridView (ToShortDateString will work fine):
.Rows.Add(..., tbl.Rows(i)("StartDate").ToShortDateString, ...)

donaldliaw87 9-Jul-13 19:39pm

ok zzzz why i did not think of put that converter when load out==", thx very much OriginalGriff i will try!!!

OriginalGriff 10-Jul-13 3:11am

You're welcome!
(It's a lot easier to see things in other peoples code than your own - you tend to see what you intended to write rather than what you did write. Strange, but true)