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

问题描述

这是我的payment.aspx.cs文件

This is my payment.aspx.cs file

public partial class Categories_Payment : System.Web.UI.Page
{

    DataTable dt;

    SignIn sgn = new SignIn();
    UserADO ado = new UserADO();
    Utility utils = new Utility();
    protected void Page_Load(object sender, EventArgs e)
    {

        if (!IsPostBack)
        {

            txtcurrentdate.Text = DateTime.Now.ToShortDateString();
            txtcurrentdate.Enabled = false;

            dt = new DataTable();
            dt = (DataTable)Session["addtocart"];

            GridView1.DataSource = dt;
            GridView1.DataBind();
            Session["addtocart"] = dt;
            Txtdate.Enabled = false;

            DropDownList1.Items.Insert(0, "--Select--");
            DropDownList1.Items.Insert(1, "Master Card");
            DropDownList1.Items.Insert(2, "Credit Card");
            DropDownList1.Items.Insert(3, "Debit Card");

        }
    }
    protected void Calendar1_SelectionChanged(object sender, EventArgs e)
    {
        if (Calendar1.SelectedDate > DateTime.Now)
        {
            Txtdate.Enabled = true;
            Txtdate.Text = Calendar1.SelectedDate.ToLongDateString();
        }
        else
        {
            Txtdate.Text = "";
        }
    }
    protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
    {
        if (e.Day.Date < DateTime.Now.Date)
        {
            e.Cell.Enabled = false;
            e.Cell.BackColor = Color.White;
        }
    }
       protected void Btn_checkout_Click(object sender, EventArgs e)
    {
        dt = new DataTable();
        dt = (DataTable)Session["addtocart"];
        string str = Session["UID"].ToString();
        //string adrress = Session["shippingaddress"].ToString();
        if (Txtcardnumber.Text.Length == 15)
        {
            lblMessage.Text = "<font color="red">Please enter your 16 digit card number.</font>";
            return;
        }
        if (Txtpwd.Text.Length == 3)
        {
            lblMessage.Text = "<font color="red">Please enter 4 digit Password.</font>";
            return;
        }

        string dates;
        dates = txtcurrentdate.Text;
        string QueryOrder = "insert into order_detail values ('" + str + "','" + dates + "') select scope_identity()";

        string oid = ado.ExecuteScalerByQuery(QueryOrder);

        string dp;
        dp = Txtdate.Text;
        
        string QueryPayment = "insert into paymentdetail values('" + str + "','" + oid + "','" + DropDownList1.SelectedValue + "','" + Txtcardnumber.Text + "','" + dp + "')";
        ado.InsertUpdateByQuery(QueryPayment);
        //string QueryShipping = "insert into shippingdetail values('" + oid + "','" + adrress + "')";
        //ado.InsertUpdateByQuery(QueryShipping);
        Session["billnum"] = oid.ToString();
        Session["paymentmode"] = DropDownList1.SelectedItem.Text;
        Session["cardnumber"] = Txtcardnumber.Text;
        Session["xpirydate"] = Txtdate.Text;
        Session["currentdate"] = txtcurrentdate.Text;

        string ODetail = "";
        foreach (DataRow dr in dt.Rows)
        {
            ODetail = "insert into oderdetail values('" + oid + "','" + dr["Tilte"].ToString() + "','" + Convert.ToInt32(dr["Qty"]) + "','" + Convert.ToDouble(dr["total"]) + "')";
            ado.InsertUpdateByQuery(ODetail);
        }
             
        Response.Redirect("billgeneration.aspx");
    }
}

运行时出现此错误

This error occurs when i run

Server Error in '/ebook1' Application.

Conversion failed when converting date and/or time from character string.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string.

Source Error:


Line 148:        _command.CommandType = CommandType.Text;
Line 149:        _command.CommandText = strQry;
Line 150:        int i = _command.ExecuteNonQuery();
Line 151:        CloseConnection();
Line 152:        return i;


Source File: g:\ebook1\App_Code\DL\UserADO.cs    Line: 150

推荐答案

您没有向我们显示生成错误的行，您不会指出它与哪个SQL语句相关。这没有用。



但很明显问题可能是什么：你将用户输入文本直接发送到SQL并期待它将它作为日期字段处理。

您的方法存在许多问题：

1）不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。

2）即使用户输入正确，他也会以对他有意义的格式输入日期（也可能是他正在处理的PC） - 这样做并不意味着它是一种合理的甚至是可读的SQL格式，它可能位于不同的PC上，可以配置为不同的语言环境。



所以：你需要做两件事。

首先，你需要将用户输入从字符串检查并转换为DateTime - 使用DateTime.TryParse并报告任何给用户的问题而不是继续。如果您无法理解用户输入，SQL服务器就没有任何机会。在顶部做一堆检查，报告具体问题，甚至在用户修复问题之前甚至不尝试完成剩下的方法。



其次，用参数化查询替换所有字符串连接，并将处理后的值传递给SQL字段的正确数据类型。因此，如果字段是DateTime，则将DateTime作为参数传递。如果是整数，则传递和整数。



您还需要更改用户界面 - 例如，文本框不是让用户输入日期的友好方式。 DateTimePicker要好得多，并且直接提供一个永远无效的DateTime值。

You don''t show us the line that generates the error, you don''t indicate which SQL statement it relates to. This doesn''t help.

But it''s pretty clear what the problem is likely to be: you are sending user input text directly to SQL and expecting it to process it as a date field.
There are a number of things wrong with your approach:
1) Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
2) Even if the user types it correctly, he will type the date in a format which makes sense to him (and possibly to the PC he is working on) - that does not mean that it is in a "sensible" or even "readable" format for SQL, which is likely to be on a different PC which may be configured for a different locale.

So: you need to do two things.
Firstly, you need to do a check-and-convert of the user input from a string to a DateTime - use DateTime.TryParse and report any problems to the user instead of continuing. If you can''t understand the user input, SQL server has no chance whatsoever. Do a bunch of checks at the top, report specific problems and do not even try to do the rest of the method until teh user has fixed the problems.

Secondly, replace all you string concatenations with parametrized queries, and pass the values though processed into the correct datatypes for the SQL fields. So if a field is a DateTime, pass a DateTime as a parameter. If it is an integer, pass and integer.

It would also be worth your looking at changing your user interface - textboxes are not a "friendly" way to get a user to input a date for example. A DateTimePicker is a lot better, as well as providing a DateTime value directly which can never be invalid.

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