错误：无法将参数值从String转换为DateTime [英] ERROR: Failed to convert parameter value from a String to a DateTime

问题描述

大家好，我遇到了这个错误：尝试将复选框列表中的多个选定日期传递给我要在sql中使用的参数时，无法将参数值从String转换为DateTime 。



我已经尝试过其他数据类型，例如nvarchar它可以工作，我将多个选定的值传递给1个存储过程参数并使用dytnamic返回select语句用SQL填充我的gridview。



非常感谢您的帮助。谢谢:)





ASPX.CS

Hi all, I have faced this error: Failed to convert parameter value from a String to a DateTime when trying to passed multiple selected dates from checkboxlist to my parameter to be used in sql.

I have tried it with other datatypes such as nvarchar it works, which I am to passed in multiple selected values to 1 stored procedure parameter and return the select statement using dytnamic sql to populate my gridview.

Your kind help is kindly appreciated. Thanks:)


ASPX.CS

        protected void Page_Load(object sender, EventArgs e)
        {
            DATE.DataTextFormatString = "{0:dd-MMM-yyyy}";

            using (SqlConnection conn = new SqlConnection(dbConn))
            {
                try //Call stored procedure
                {

                    SqlCommand cmd = new SqlCommand(spddl, conn);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    if (!IsPostBack)
                    {
                        DATE.DataSource = ds.Tables[0];
                        DATE.DataTextField = ds.Tables[0].Columns["DATE"].ToString();
                        DATE.DataBind();

 

                    }
                    if (IsPostBack)
                    {
                        Bind();
                    }    
                    
                }

                catch (Exception i)
                {
                    bool exception = true;
                    if (exception == true)
                    {
                        //txtMessage.Text += e.Message;
                    }
                }
            }
        }

public void Bind()
       {
DateTime choosenDate = DateTime.MinValue;

               using (SqlConnection conn = new SqlConnection(dbConn))
           {
               using (SqlCommand cmd = new SqlCommand(spretrieve, conn))
               {
                     String selectedDATE = String.Empty;


                    if (DATE.SelectedValue == "All")
                    {
                        selectedDATE = "DATE";
                    }
                    else
                    {
                        foreach (ListItem item in DATE.Items)
                        {
                            if (item.Selected)
                            {
                               selectedDATE = item.Text;                                
                               //selectedDATE += "'" + item.Text + "',";
                            }
                        }

                        selectedDATE = selectedDATE.Substring(0, selectedDATE.Length - 1);
                       DateTime.TryParse(selectedDATE, out choosenDate);
                    }
 if (!choosenDate.Equals(DateTime.MinValue))
                    {
 
                   cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@param", SqlDbType.DateTime).Value = choosenDate;
 

                   conn.Open();
                   SqlDataAdapter da = new SqlDataAdapter(cmd);
                   DataSet ds= new DataSet();
                   da.Fill(ds);
                   GRIDVIEW.DataSource = ds.Tables[0];
                   GRIDVIEW.DataBind();
}
 
               }
           }

SQL

SQL

 ALTER PROCEDURE [dbo].[SP]
 
@param nvarchar(512)
 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(max)
    SET @sql = 'SELECT * FROM TABLENAME WHERE [COLUMN] IN (' + @param + ')'
    EXEC sp_executesql @sql;
END

推荐答案

在开头声明一个DateTime对象of Bind（）方法：

Declare a DateTime object at the beginning of Bind() method:

DateTime choosenDate = DateTime.Minvalue;

$ b

after

selectedDATE = selectedDATE.Substring(0, selectedDATE.Length - 1);

you state：

you state:

DateTime.TryParse(selectedDate, out choosenDate);

如果TryParse成功，choosenDate现在的日期与1月1日不同



so：

If the TryParse succeedded choosenDate has now a different date than 1 january 1

so:

if (!choosenDate.Equals(DateTime.MinValue)
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@param", SqlDbType.DateTime).Value = choosenDate;
 
 
                   conn.Open();
                   SqlDataAdapter da = new SqlDataAdapter(cmd);
                   DataSet ds= new DataSet();
                   da.Fill(ds);
                   GRIDVIEW.DataSource = ds.Tables[0];
                   GRIDVIEW.DataBind();
}

参见： https://msdn.microsoft.com/en-us/library/ch92fbc1(v = vs.110）.aspx [ ^ ]

这篇关于错误：无法将参数值从String转换为DateTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！