c# - System.Data.SqlClient.SqlException：“将数据类型 nvarchar 转换为日期时出错。”

问题描述

我想分配培训记录时遇到此错误。我尝试了几件事，但没有进展 - 有人可以帮我解决这个问题吗？感谢您的辛勤工作，非常感谢您！

这是我的代码：

    protected void ImgBtnDate_Click(object sender, ImageClickEventArgs e)
    {
        if(CalTrain.Visible)
        {
            CalTrain.Visible = false;
        }
        else
        {
            CalTrain.Visible = true;
        }
        CalTrain.Attributes.Add("style", "position:absolute");
    }

    protected void CalTrain_SelectionChanged(object sender, EventArgs e)
    {
        txtDate.Text = CalTrain.SelectedDate.ToString("dd/MM/yyyy");
        CalTrain.Visible = false;
    }

    protected void CalTrain_DayRender(object sender, DayRenderEventArgs e)
    {
        if(e.Day.IsOtherMonth)
        {
            e.Day.IsSelectable = false;
            e.Cell.BackColor=System.Drawing.Color.Red;
        }

    }

    protected void lnkEdit_Click(object sender, EventArgs e)
    {
        int TrainID = Convert.ToInt32((sender as LinkButton).CommandArgument);
        if (sqlCon.State == ConnectionState.Closed)
            sqlCon.Open();
        SqlDataAdapter sqlDa = new SqlDataAdapter("TrainViewByID", sqlCon);
        sqlDa.SelectCommand.CommandType = CommandType.StoredProcedure;
        sqlDa.SelectCommand.Parameters.AddWithValue("@TrainID", TrainID);
        DataTable dtbl = new DataTable();
        sqlDa.Fill(dtbl);
        sqlCon.Close();
        hfTrainID.Value = TrainID.ToString();
        txtDate.Text = dtbl.Rows[0]["TrainDate"].ToString();

        DDLTrainProgram.Text = dtbl.Rows[0]["TrainProgram"].ToString();
        DDLTrainTime.Text = dtbl.Rows[0]["TrainTime"].ToString();
        DDLTrainVenue.Text = dtbl.Rows[0]["TrainVenue"].ToString();
        btnAssign.Text = "Update";
        btnDelete.Enabled = true;
    }


    protected void btnClear_Click(object sender, EventArgs e)
    {
        Clear();
    }

    public void Clear()
    {
        hfTrainID.Value = "";
        txtDate.Text = "";
        lblSuccessMessage.Text = lblErrorMessage.Text = "";
        btnAssign.Text = "Assign";
        btnDelete.Enabled = false;
    }

    protected void btnAssign_Click(object sender, EventArgs e)
    {
        if (sqlCon.State == ConnectionState.Closed)
            sqlCon.Open();
        SqlCommand sqlCmd = new SqlCommand("TrainCreateOrUpdate", sqlCon);
        sqlCmd.CommandType = CommandType.StoredProcedure;
        sqlCmd.Parameters.AddWithValue("@TrainID", (hfTrainID.Value == "" ? 0 : Convert.ToInt32(hfTrainID.Value)));
        sqlCmd.Parameters.AddWithValue("@TrainDate",DateTime.ParseExact(txtDate.Text,"dd/MM/yyyy",CultureInfo.InvariantCulture));
        sqlCmd.Parameters.AddWithValue("@TrainProgram",DDLTrainProgram.Text.Trim());
        sqlCmd.Parameters.AddWithValue("@TrainTime",DDLTrainTime.Text.Trim());
        sqlCmd.Parameters.AddWithValue("@TrainVenue",DDLTrainVenue.Text.Trim());
        sqlCmd.ExecuteNonQuery();
        sqlCon.Close();
        string TrainID = hfTrainID.Value;
        Clear();
        if (TrainID == "")
            lblSuccessMessage.Text = "Assigned Successfully";
        else
            lblSuccessMessage.Text = "Updated Successfully";
        FillGVTrain();
    }

    void FillGVTrain()
    {
        if (sqlCon.State == ConnectionState.Closed)
            sqlCon.Open();
        SqlDataAdapter sqlDa = new SqlDataAdapter("TrainViewAll", sqlCon);
        sqlDa.SelectCommand.CommandType = CommandType.StoredProcedure;
        DataTable dtbl = new DataTable();
        sqlDa.Fill(dtbl);
        sqlCon.Close();
        gvTrain.DataSource = dtbl;
        gvTrain.DataBind();
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        if (sqlCon.State == ConnectionState.Closed)
            sqlCon.Open();
        SqlCommand sqlCmd = new SqlCommand("TrainDeleteByID", sqlCon);
        sqlCmd.CommandType = CommandType.StoredProcedure;
        sqlCmd.Parameters.AddWithValue("@TrainID", Convert.ToInt32(hfTrainID.Value));
        sqlCmd.ExecuteNonQuery();
        sqlCon.Close();
        Clear();
        FillGVTrain();
        lblSuccessMessage.Text = "Deleted Successfully";
    }
}

因为这是一个插入-删除-更新功能。如果我使用上面提到的代码，它不能插入。但是，如果我使用代码

sqlCmd.Parameters.AddWithValue("@TrainDate", DateTime.ParseExact(txtDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture));

它可以成功插入，但是在编辑详细信息时，我必须重新输入日期，因为它会发生另一个错误

System.FormatException：字符串未被识别为有效的 DateTime。

顺便说一句，我在 SQL Server 中分配的数据类型是date。它无法像我想象的那样运行。作为一名学生，我是编程的新手。这些代码来自 Youtube 教程。

标签： c#sql-server

c# - System.Data.SqlClient.SqlException：“将数据类型 nvarchar 转换为日期时出错。”

问题描述

解决方案

推荐阅读