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
 
        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:
 
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 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
 
        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
  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 215
1 George Jonsson 175
2 Kornfeld Eliyahu Peter 159
3 PIEBALDconsult 110
4 Richard MacCutchan 85
0 OriginalGriff 6,080
1 DamithSL 4,648
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,624
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 9 Jul 2013
Copyright © CodeProject, 1999-2014
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