Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
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
 
<pre>
    DateTimePicker1.Format = DateTimePickerFormat.Custom
    DateTimePicker1.CustomFormat = "dd MMM yyyy"
     DateTimePicker2.Format = DateTimePickerFormat.Custom
    DateTimePicker2.CustomFormat = "dd MMM yyyy"
 
End Sub</pre>

insert part
 
 Private Sub insertinto()
 
<pre>
    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)
 
    <b>cmd.Parameters.AddWithValue("@StartDate", DateTimePicker1.Value.ToShortDateString)
    cmd.Parameters.AddWithValue("@CloseDate", DateTimePicker2.Value.ToShortDateString)</b>
 
    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 &lt;&gt; 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</pre>

additional load out codes:
 
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
 
<pre>
    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</pre>

A normal load out, show on datagridview!!!
Posted 7-Jul-13 23:52pm
Edited 8-Jul-13 15:58pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You can set format of your datetime picker itself. On page load
DateTimePicker1.Format = DateTimePickerFormat.Custom
DateTimePicker1.CustomFormat = "MM/dd/yyyy"//set format according to your needs
So change the below lines in your code:
cmd.Parameters.AddWithValue("@StartDate", DateTimePicker1.Value.ToShortDateString)
cmd.Parameters.AddWithValue("@CloseDate", DateTimePicker2.Value.ToShortDateString)
[Additional]:

Custom Date and Time Format Strings[^]

Regards..Laugh | :laugh:
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
donaldliaw87 at 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 at 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 at 8-Jul-13 20:46pm
   
ok i try your advice!!
donaldliaw87 at 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 at 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 at 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 at 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)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

thx to OriginalGriff
 
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
 
<pre>
    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</pre>
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 490
1 Sergey Alexandrovich Kryukov 405
2 Maciej Los 285
3 ProgramFOX 265
4 Peter Leow 210
0 OriginalGriff 490
1 Sergey Alexandrovich Kryukov 395
2 Maciej Los 285
3 ProgramFOX 265
4 CHill60 200


Advertise | Privacy | Mobile
Web04 | 2.8.150331.1 | Last Updated 9 Jul 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100