时间戳

首页 > 解决方案 > 如何防止在填写 DataGridView 列之前调用 DateTime 参数？

datagridview - 如何防止在填写 DataGridView 列之前调用 DateTime 参数？

问题描述

我有一个带有 4 列的 DataGridView，可以使用参数动态添加或编辑。只要首先填写 StartDate（第 4 列），一切正常。当我正常填充 DGV（从左到右）时，查询失败并给出错误“System.FormatException：'String 未被识别为有效的 DateTime。” 如何防止 DateTime 参数在 DataGridView 行完成之前触发。

我试图在 StartDate 列单元格中插入一个 DBNull，直到填满 DGV，但得到一个存储过程错误“参数太多”。我在代码底部的注释中包含了存储过程代码以供参考。在此先感谢，

导入 System.Data.SqlClient 公共类 Form1

Private connectionstring As String = "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Employment;Integrated Security=True;"

Private Sub PopulatePositionComboBox()
    Using sqlCon As SqlConnection = New SqlConnection(connectionstring)
        sqlCon.Open()
        Dim sqlDa As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Position", sqlCon)
        Dim dt As DataTable = New DataTable()
        sqlDa.Fill(dt)
        cbxPositionID.ValueMember = "PositionID"
        cbxPositionID.DisplayMember = "Position"
        Dim topItem As DataRow = dt.NewRow()
        topItem(0) = 0
        topItem(1) = "-Select-"
        dt.Rows.InsertAt(topItem, 0)
        cbxPositionID.DataSource = dt
    End Using
End Sub

Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
    PopulatePositionComboBox()
    PopulateDataGridView()
End Sub

Private Sub PopulateDataGridView()
    Using sqlCon As SqlConnection = New SqlConnection(connectionstring)
        sqlCon.Open()
        Dim sqlDa As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Employee", sqlCon)
        Dim dt As DataTable = New DataTable()
        sqlDa.Fill(dt)
        DgvEmployment.DataSource = dt
    End Using
End Sub

Private Sub DgvEmployment_CellValueChanged(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs) Handles DgvEmployment.CellValueChanged
    If DgvEmployment.CurrentRow IsNot Nothing Then
        Using sqlCon As SqlConnection = New SqlConnection(connectionstring)
            sqlCon.Open()
            Dim dgvRow As DataGridViewRow = DgvEmployment.CurrentRow
            Dim sqlCmd As SqlCommand = New SqlCommand("AddOrEdit", sqlCon)
            sqlCmd.CommandType = CommandType.StoredProcedure

            ' INSERT
            If dgvRow.Cells("txtEmployeeID").Value Is DBNull.Value Then
                sqlCmd.Parameters.AddWithValue("@EmployeeID", 0)
            Else
                ' UPDATE
                sqlCmd.Parameters.AddWithValue("@EmployeeID", Convert.ToInt32(dgvRow.Cells("txtEmployeeID").Value))
            End If

            ' PROBLEM AT HERE, FAILS Query because looking for VALID DATE after 'txtName' is entered 
            'ERROR MESSAGE = System.FormatException: 'String was not recognized as a valid DateTime.'
            sqlCmd.Parameters.AddWithValue("@Name", If(dgvRow.Cells("txtName").Value Is DBNull.Value, "", dgvRow.Cells("txtName").Value.ToString()))
            sqlCmd.Parameters.AddWithValue("@PositionID", Convert.ToInt32(If(dgvRow.Cells("cbxPositionID").Value Is DBNull.Value, "0", dgvRow.Cells("cbxPositionID").Value)))
            sqlCmd.Parameters.AddWithValue("@Office", If(dgvRow.Cells("txtOffice").Value Is DBNull.Value, "", dgvRow.Cells("txtOffice").Value.ToString()))
            sqlCmd.Parameters.AddWithValue("@StartDate", Convert.ToDateTime(If(dgvRow.Cells("txtStartDate").Value Is DBNull.Value, "0", dgvRow.Cells("txtStartDate").Value)))
            sqlCmd.ExecuteNonQuery()
            PopulateDataGridView()
        End Using
    End If
End Sub


'The code for the STORED PROCEDURE  dbo.AddOrEdit
'    CREATE PROC AddOrEdit
'@EmployeeID int,
'@Name varchar(100),
'@PositionID int,
'@Office varchar(50),
'@StartDate date
'As
'If  @EmployeeID  = 0
'INSERT INTO Employee (Name, PositionID, Office, StartDate)
'VALUES (@Name, @PositionID, @Office, @StartDate)
'Else
'UPDATE Employee
'    Set
'Name = @Name,
'PositionID = @PositionID,
'Office = @Office,
'StartDate = @StartDate
'WHERE EmployeeID = @EmployeeID

我希望 DGV 从左到右填写，最后一列是“StartDate”。EG 姓名、职位 ID、办公室、开始日期。

标签： datagridview

解决方案

如果您的StartDate列可以为空，那么我认为可以通过更改行来解决

sqlCmd.Parameters.AddWithValue("@StartDate",  Convert.ToDateTime(If(dgvRow.Cells("txtStartDate").Value Is DBNull.Value, "0", dgvRow.Cells("txtStartDate").Value)))

至

sqlCmd.Parameters.AddWithValue("@StartDate", If(dgvRow.Cells("txtStartDate").Value Is DBNull.Value, DBNull.Value, Convert.ToDateTime(dgvRow.Cells("txtStartDate").Value)))

因此，如果未填写，请保存NULL到数据库，否则将您的txtStartDate字段转换为DateTime并保存。

将“0”保存为 DateTime 将不起作用。

推荐阅读