将存储过程参数从字符串转换为日期 [英] Convert a Stored Procedure Parameter from String to Date

问题描述

private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                SubmitChangesViaStoredProcedure();

            }

                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            

        }

        private void SubmitChangesViaStoredProcedure()
        {
            da.UpdateCommand = CreateUpdateViaSPCommand();

            da.Update(dataTable);
            MessageBox.Show("Record Saved", "COB PartC Non-Horizon", MessageBoxButtons.OK);
            /*AutoClosingMessageBox.Show("Member data saved", "MSP", 2000);*/
        }

       
            
            private SqlCommand CreateUpdateViaSPCommand()
            {
                SqlCommand cmd2 = new SqlCommand("PartC_NonHorizonUpdate_Grid", sqlConnection1);
                cmd2.CommandType = CommandType.StoredProcedure;
                SqlParameterCollection pc = cmd2.Parameters;

                cmd2.Parameters.AddWithValue("@relHICN","relHICN");
                cmd2.Parameters.AddWithValue("@relRelation", "relRelation");
                cmd2.Parameters.AddWithValue("@relInsurerName", "relInsurerName");
                cmd2.Parameters.AddWithValue("@relMSPCode", "relMSPCode");

                //DateTime dtEff = DateTime.ParseExact("relMSPEffDate", "yyyy-MM-dd HH:mm tt", System.Globalization.CultureInfo.InvariantCulture);
                DateTime dtEff = Convert.ToDateTime("relMSPEffDate");
                cmd2.Parameters.AddWithValue("@relMSPEffDate", SqlDbType.Date);
                cmd2.Parameters["@relMSPEffDate"].Value = dtEff.ToShortDateString();

                //cmd2.Parameters.AddWithValue("@relMSPEffDate", "relMSPEffDate");

                DateTime dtTerm = Convert.ToDateTime("relMSPTermDate");
                cmd2.Parameters.Add("@relMSPTermDate", SqlDbType.Date);
                cmd2.Parameters["@relMSPTermDate"].Value = dtTerm.ToShortDateString();

                //cmd2.Parameters.AddWithValue("@relMSPTermDate", "relMSPTermDate");

                cmd2.Parameters.AddWithValue("@relComment", "relComment");

                DateTime dtActEff = Convert.ToDateTime("relActMSPEffDate");
                cmd2.Parameters.AddWithValue("@relActMSPEffDate", SqlDbType.Date);
                cmd2.Parameters["@relActMSPEffDate"].Value = dtActEff.ToShortDateString();

                //cmd2.Parameters.AddWithValue("@relActMSPEffDate", "relActMSPEffDate");

                DateTime dtActTerm = Convert.ToDateTime("relActMSPTermDate");
                cmd2.Parameters.Add("@relActMSPTermDate", SqlDbType.Date);
                cmd2.Parameters["@relActMSPTermDate"].Value = dtActTerm.ToShortDateString();

                //cmd2.Parameters.AddWithValue("@relActMSPTermDate", "relActMSPTermDate");

                cmd2.Parameters.AddWithValue("@relActMSPStatus", "relActMSPStatus");
                cmd2.Parameters.AddWithValue("@relTransActionCd", "relTransActionCd");

               return cmd2;


Stored Procedure:

USE [MSP]
GO
/****** Object:  StoredProcedure [dbo].[PartC_NonHorizonUpdate_Grid]    Script Date: 2/6/2018 10:02:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PartC_NonHorizonUpdate_Grid]
	@relHICN nvarchar(12),
	@relRelation nvarchar(2),
	@relInsurerName nvarchar(32),
	@relMSPCode nvarchar(1),
	@relMSPEffDate date,
	@relMSPTermDate date,
	@relComment nvarchar(max),
	@relActMSPEffDate date,
	@relActMSPTermDate date,
	@relActMSPStatus nvarchar(30),
	@relTransActionCd nvarchar(2)
AS
	UPDATE [PartC_NonHorizon_Master] 
	SET [relComment] = @relComment,
	[relActMSPEffDate] = @relActMSPEffDate, [relActMSPTermDate] = @relActMSPTermDate,
	[relActMSPStatus] = @relActMSPStatus, [relTransActionCd] = @relTransActionCd
	WHERE relHICN=@relHICN AND relRelation=@relRelation AND
		relInsurerName=@relInsurerName AND relMSPCode=@relMSPCode AND
		relMSPEffDate=@relMSPEffDate AND relMSPTermDate=@relMSPTermDate


            }

在C＃.net中我在表单中显示DataGridView.DataGridView绑定到数据表.DataTable从SQL Server表加载，值显示在DataGridView中。
那里用户可以更新DataGridView中的4个日期字段。用户更新字段后，按下保存按钮执行存储过程，该过程将4个日期字段以及其他字段作为参数传递。日期字段
在SQL Server中有一个数据类型的日期，但是当加载到数据表中时它们被加载为字符串。我尝试在调用存储之前使用DateTime.ParseExact和Convert.ToDateTime将日期字段转换为日期格式过程，但收到
错误，该字符串未被识别为有效的DateTime。如何在调用存储过程之前将日期字段转换为日期数据类型。

推荐答案

嗨gaugust，

Hi gaugust,

你可以重播使用以下代码转换转换日期时间，然后再试一次。

You can replace the convert datetime using the following code, and try it again.

var dtEff = Convert.ToDateTime("relMSPEffDate").ToString("yyyy-MM-dd");
                    cmd2.Parameters.AddWithValue("@relMSPEffDate", dtEff);

最好的问候，

Cherry

这篇关于将存储过程参数从字符串转换为日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！