从日期时间到字符串格式的转换错误 [英] Conversion error from date time to string format

问题描述

我对这段代码有疑问，我正在尝试在预订表中插入一条记录。我尝试输入的值是（6，3，3，20/06/2018 00:00:00，400，2800.00，True，560.00）

I have a problem with this section of code, I'm trying to insert a record into my booking table. The values I'm trying to input are (6, 3, 3, 20/06/2018 00:00:00, 400, 2800.00, True, 560.00)

        public void insertBooking(int bookingID, int customerID, int entertainmentID, 
                                  DateTime bookingDate, int numberOfGuests, double price, 
                                  bool deposit, decimal depositPrice)
        {
            db.Cmd = db.Conn.CreateCommand();
            db.Cmd.CommandText = "INSERT INTO Booking (bookingID, customerID, entertainmentID, 
                                  [Booking Date], [Number Of Guests], [Price], [Deposit?], 
                                  [Deposit Price]) " + "Values ('" + bookingID + "','" + 
                                  customerID + "','" + entertainmentID + "','" + 
                                  bookingDate + "','" + numberOfGuests + "','" + price + 
                                  "','" + deposit + "','" + depositPrice + "')";
            db.Cmd.ExecuteNonQuery();
        }

我得到的错误如下，

从字符
字符串转换日期和/或时间时，转换失败。

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

我已尽力研究此问题，但我不知道该如何解决。感谢您的帮助。

I have tried to research the problem as best I can but I can't figure out how to fix this. Any help is appreciated.

推荐答案

Okey，您的代码有一些问题。我将尝试按顺序说明所有这些内容。

Okey, you have a few issues on your code. I will try to explain all of them in order.

首先，如果您将 DateTime 值与字符串一起使用级联（在 + 运算符上）， .ToString 方法自动为其调用，您可能会或可能不会正确为您的数据库列生成的文本。 不要不要为列选择错误的数据类型。您需要直接传递您的 DateTime 值。如果您不知道，您知道哪个 SqlDbType 您的值，则可以阅读也映射CLR参数数据。

First of all, if you use DateTime values with string concatenation (on + operator), .ToString method automatically called for them and you get may or may not "proper" textual generated for your database column. Do not choose wrong data types for your columns. You need to pass your DateTime value directly. If you don't know, which SqlDbType you know for your values, you can read the Mapping CLR Parameter Data also.

如评论中所建议，解决这种情况的最佳方法是使用参数化查询。另一方面，这些字符串连接对 SQL注入攻击开放。

As suggested on the comments, best way to solve this kind of situations is using parameterized queries. On the other hand, these string concatenations are open for SQL Injection attacks.

也可以使用 使用语句来自动处理连接（我们看不到，但是..）和命令，而不是自动调用 .Dispose 方法（您没有

Also use using statement to dispose your connection (which we don't see but..) and commands automatically instead of calling .Dispose method (which you didn't) manually.

例如；

public void insertBooking(int bookingID, int customerID, int entertainmentID,
                          DateTime bookingDate, int numberOfGuests, double price,
                          bool deposit, decimal depositPrice)
{
    using (var con = new SqlConnection(yourConnectionString))
    using (var cmd = con.CreateCommand())
    {
        cmd.CommandText = @"INSERT INTO Booking (bookingID, customerID, entertainmentID, 
                            [Booking Date], [Number Of Guests], [Price], [Deposit?], 
                            [Deposit Price]) Values(@bookId, @cusId, @entId, @bookdate, @guests, @price, @deposit, @depositPrice)";
        cmd.Parameters.Add("@bookId", SqlDbType.Int).Value = bookingID;
        cmd.Parameters.Add("@cusId", SqlDbType.Int).Value = customerID;
        cmd.Parameters.Add("@entId", SqlDbType.Int).Value = entertainmentID;
        cmd.Parameters.Add("@bookdate", SqlDbType.DateTime).Value = bookingDate;
        cmd.Parameters.Add("@guests", SqlDbType.Int).Value = numberOfGuests;
        cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = price;
        cmd.Parameters.Add("@deposit", SqlDbType.Bit).Value = deposit;
        cmd.Parameters.Add("@depositPrice", SqlDbType.Decimal).Value = depositPrice;

        con.Open();
        cmd.ExecuteNonQuery();
    }
}

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