从字符串转换日期和/或时间时转换失败。 [英] Conversion failed when converting date and/or time from character string.

问题描述

转换后收到错误

getting error after converting

private void LoadData()
    {
        con.Open();
      
        string s = "SELECT user_name FROM login" + " WHERE user_name='" + Label1.Text + "'";
        SqlCommand cmd = new SqlCommand(s, con);
        SqlDataReader Dr = cmd.ExecuteReader();
        if (Dr.Read())
        {
            con.Close();
            DateTime datetime = Convert.ToDateTime(Date.Text);
            string query = @"SELECT id,Build_name,Dept,Floor_no,Call_recv FROM  Tech_data where Call_assign='" + Label1.Text + "'  and date_time='" + datetime  + "'";

            SqlDataAdapter da = new SqlDataAdapter(query, con);
            DataTable table = new DataTable();
            da.Fill(table);

            GridView1.DataSource = table;
            GridView1.DataBind();
        }

    }

推荐答案

这有很多原因，但最多常见的是，输入的日期与当前计算机所需的Locale格式不同 - 或者当然是用户输入的垃圾！ ：笑：



我要做的第一件事就是不使用日期条目的文本框：如果我可以，我会使用DateTimePicker，因为这不允许用户输入无效日期，也不需要转换，因为它直接提供DateTime值。



如果这不可能，我会使用DateTime。在我打开任何连接或执行任何其他操作之前，TryParse或DateTime.TryParseExact而不是Convert.ToDateTime消除用户错误。在开始处理之前验证您的数据并报告错误！

There are a number of reasons for this, but the most common is that the date entered is not in the same format as the current Locale for the computer expects - or the user typed rubbish, of course! :laugh:

This first thing I would do is not use a text box for a Date entry: I would use a DateTimePicker instead if I could as this does not allow the user to enter an invalid date, and it also needs no conversion as it provides a DateTime value directly.

If this wasn't possible, I would use DateTime.TryParse or DateTime.TryParseExact instead of Convert.ToDateTime to eliminate user errors before I opened any connections or did anything else. Verify your data and report errors before you start on processing!

使用参数 [ ^ ]集合，用于将用户输入参数分配到SQL命令中。

您可以尝试：

Use Parameters[^] collection for assigning user input parameters into SQL commands.
You can try this:

string s = "SELECT user_name FROM login WHERE user_name = @u"; // there are no apostrophes around %u!
SqlCommand cmd = new SqlCommand(s, con);
cmd.Parameters.AddWithValue("@u", Label1.Text);

对日期时间做同样的事情 - 只需用@d参数创建另一个命令并将其放入SqlDataAdapter而不是普通的sql查询。



为什么你在 Dr.Read（）之后关闭你的连接（ con ）？

Do the same with date time - just create another command with @d parameter and put it into your SqlDataAdapter instead of plain sql query.

Why are you closing your connection (con) right after Dr.Read() ?

tried this worked fine . thnk you all


DateTime datetime = Convert.ToDateTime(Date.Text);
            SqlCommand cmnd = new SqlCommand("SELECT * FROM  Tech_data where Call_assign= @Call_assign and dateadd(dd, datediff(dd,0, [date_time]), 0) = @date_time ");
            cmnd.Connection = con;
            cmnd.Parameters.Add("@Call_assign", SqlDbType.VarChar).Value = Label1.Text;
            cmnd.Parameters.Add("@date_time", SqlDbType.DateTime).Value = datetime.Date;
            SqlDataAdapter da = new SqlDataAdapter(cmnd);
            DataSet ds = new DataSet();
            da.Fill(ds);

这篇关于从字符串转换日期和/或时间时转换失败。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！