'无法将参数值从字符串转换为日期时间。' [英] 'Failed to convert parameter value from a string to a datetime.'

问题描述

我在尝试从下拉列表中选择日期时遇到此错误，一旦选择了日期，它应该根据下拉列表值在GridView中显示数据。这是使用''Where''语句等于所选索引的值来实现的。



我知道日期显示在下拉列表中的方式存在问题，而不是它们在数据库中保存的方式。所以我试图在下拉列表中将格式从dd / mm / yyyy交换到yyyy / mm / dd，看看是否能解决问题。但我似乎无法让它工作。



请有人推荐一个解决方案吗？



下拉列表选择的索引更改C＃：

I am currently getting this error when trying to select a date from a drop down list, once the date is selected it should Display data in a GridView depending on the drop down list value. This is achieved using a ''Where'' statement equals the value of the selected index.

I understand that it is a problem with the way the dates are being displayed in the drop down list, as opposed to the way they are saved in the database. So I have tried to swap the format from dd/mm/yyyy to yyyy/mm/dd in the drop down list, to see if this fixes the problem. But I can''t seem to get it to work.

Please can someone recommend a fix to this?

Drop Down List selected index change C# :

protected void DropDownList2_SelectedIndexChanged(object sener, EventArgs e)
        {
            String query = "SELECT Stock_Take.Username, Item.ItemID, Item.ItemDesc, Stock_Take_Item.BarQuantity, Stock_Take_Item.StorageQuantity, Stock_Take.StockTakeIDNew FROM Item INNER JOIN Stock_Take_Item ON Item.ItemID = Stock_Take_Item.ItemID INNER JOIN Stock_Take ON Stock_Take_Item.StockTakeIDNew = Stock_Take.StockTakeIDNew where Stock_Take.Username = @USER AND Stock_Take.StockDate = @DATE";

            SqlConnection con = new SqlConnection(@"Data Source=(local)\;Initial Catalog=SmallBatch;Integrated Security=True;");
            con.Open();
            SqlCommand cmd = new SqlCommand(query, con);

            cmd.Parameters.Add("@USER", SqlDbType.VarChar).Value = DropDownList1.SelectedValue;
            cmd.Parameters.Add("@DATE", SqlDbType.DateTime).Value = DropDownList2.SelectedValue;
           // DateTime date = Convert.ToDateTime(DropDownList2.SelectedValue.ToString());
            SqlDataReader reader = cmd.ExecuteReader();
            GridView1.DataSource = reader;
            GridView1.DataBind();
            con.Close();
         

        }

将数据库中的日期绑定到下拉列表C＃代码：

Binding the dates from the database to the drop down list C# code:

private void BindDropDownList2(String field)
        {
            DataTable dataTable = new DataTable();
            SqlConnection con = new SqlConnection(@"Data Source=(local)\;Initial Catalog=SmallBatch;Integrated Security=True;");
            try
            {
                con.Open();
                String Query = "Select StockDate, StockTakeIDNEW from Stock_Take WHERE Username = @Value1";
                SqlCommand sqlCmd = new SqlCommand(Query, con);
                sqlCmd.Parameters.AddWithValue("@Value1", field);
               
                SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
                sqlDa.Fill(dataTable);
                if (dataTable.Rows.Count > 0)
                {
                    DropDownList2.DataSource = dataTable;
                    DropDownList2.DataTextField = "StockDate";
                    DropDownList2.DataValueField = "StockTakeIDNew";
                   // DropDownList2.DataTextFormatString = "(yyyy/MM/dd}";
                    DropDownList2.DataBind();
                }
               
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                string msg = "Fetch Error";
                msg += ex.Message;
                throw new Exception(msg);
            }
            finally
            {
                con.Close();
            }


        }

我的尝试：



*我还有另一个下拉列表，用户选择使用rname from，它反过来影响DropDownList2中显示的日期

What I have tried:

*I also have another drop down list, that a user selects a Username from, which in turn affects what dates are shown in the DropDownList2

推荐答案

您需要将值转换为DateTime值。



您可以使用DateTime.TryParse - 参考; DateTime.TryParse方法（String，DateTime）（系统） [ ^ ]或 DateTime.TryParse方法（字符串，IFormatProvider，DateTimeStyles，DateTime）（系统） [ ^ ]



或者你可以使用DateTime .TryParseExact - 参考; DateTime.TryParseExact Method（System） [ ^ ]



注意：如果你只想要日期值 - IE - 没有时间组件，SQL解析yyyymmdd格式，无论数据库的整理如何;



亲切的问候

You need to convert the value to a DateTime value.

You can use DateTime.TryParse - refer; DateTime.TryParse Method (String, DateTime) (System)[^] or DateTime.TryParse Method (String, IFormatProvider, DateTimeStyles, DateTime) (System)[^]

Alternatively you can use DateTime.TryParseExact - refer; DateTime.TryParseExact Method (System)[^]

Note: If you just want the date value - I.E. - no time component, SQL parses "yyyymmdd" format regardless of the collation of the database;

Kind Regards

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