从datetimepicker获取日期值并将日期存储到数据库中但不是时间!! [英] get date value from datetimepicker and store date into database but not with time!!

问题描述

专业人士和主人请看看我的代码，我不知道如何解决我的问题有关get（datevalue）和插入数据库，我尽力自定义代码，但插入（datevalue） ）仍然是时间'12：00：00'。请帮帮我!!!





表格加载部分

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

正常加载，在datagridview上显示！

推荐答案

您可以设置日期时间选择器本身的格式。在页面加载

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:

除非你将数据库中的日期存储为字符串 - 这是一个非常糟糕的想法，因为你不能做比较或者轻松排序 - 然后您可以使用DateTime.Date去除Time元素，并将其存储在SQL Date字段而不是DateTime中。如果没有指定时间，DateTime将始终具有时间组件，通常为午夜00:00:00。



通常，发布类似'的值2013-07-08 10:43'进入日期字段会很好 - 时间部分将被忽略。

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.

thx to OriginalGriff

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

这篇关于从datetimepicker获取日期值并将日期存储到数据库中但不是时间!!的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！