通过 vb.net 将日期插入到 SQL Server 数据库中

Insert date into SQL Server database through vb.net

提问by Abram

Dim SALESINSERT As New SqlCommand("INSERT INTO Tbl_Sales (Sale_id, Transaction_No, Customer_id, Item_id, Amount, Date) VALUES(" _
                                 & SalesIdMax + 1 & "," & Transaction_label.Text & "," & 1 & "," & Label4.Text & "," & TextBox1.Text & _
                                 "," & DateTimePicker1.Value.Date & ")", sqlcon)

sqlcon.Open()
SALESINSERT.ExecuteNonQuery()
sqlcon.Close()

SALESINSERT = Nothing        

I have this code. Everything works just fine, but the problem is with the date. For some reason it inserts the same date every time: "1/1/1900".

我有这个代码。一切正常，但问题在于日期。出于某种原因，它每次都插入相同的日期：“1/1/1900”。

When I debugged the code to see the SQL command text it was fine and the date was fine and I executed it in SQL query and it was perfectly fine.

当我调试代码以查看 SQL 命令文本时，它很好，日期也很好，我在 SQL 查询中执行了它，它完全没问题。

But in VB it doesn't.

但在 VB 中则不然。

I do not know why it is not working.

我不知道为什么它不起作用。

Please can I have suggestions to fix it.

请问我可以有修复它的建议。

回答by Abdul Rasheed

Use the single quotes for the date value ",'" & DateTimePicker1.Value.Date & "')"

对日期值使用单引号 ",'" & DateTimePicker1.Value.Date & "')"

Or

或者

 ",#" & DateTimePicker1.Value.Date & "#)"

回答by Andrew Morton

If you always use parameterized queries then you will avoid problems with representing dates as strings.

如果您总是使用参数化查询，那么您将避免将日期表示为字符串的问题。

You can use SQL parameters (I had to guess at the database column data types) for your query like this:

您可以使用 SQL 参数（我不得不猜测数据库列数据类型）进行查询，如下所示：

Dim salesinsert As New SqlCommand("INSERT INTO Tbl_Sales ([Sale_id], [Transaction_No], [Customer_id], [Item_id], [Amount], [Date])" &
                                  " VALUES(@SaleId, @TransactionNo, @CustomerId, @ItemId, @Amount, @Date)", sqlcon)

salesinsert.Parameters.Add(New SqlParameter With {.ParameterName = "@SaleId", .SqlDbType = SqlDbType.Int, .Value = SalesIdMax + 1})
salesinsert.Parameters.Add(New SqlParameter With {.ParameterName = "@TransactionNo", .SqlDbType = SqlDbType.NVarChar, .Size = 20, .Value = Transaction_label.Text})
salesinsert.Parameters.Add(New SqlParameter With {.ParameterName = "@CustomerId", .SqlDbType = SqlDbType.Int, .Value = 1})
salesinsert.Parameters.Add(New SqlParameter With {.ParameterName = "@ItemId", .SqlDbType = SqlDbType.NVarChar, .Size = 20, .Value = Label4.Text})
salesinsert.Parameters.Add(New SqlParameter With {.ParameterName = "@Amount", .SqlDbType = SqlDbType.Decimal, .Value = CDec(TextBox1.Text)})
salesinsert.Parameters.Add(New SqlParameter With {.ParameterName = "@Date", .SqlDbType = SqlDbType.DateTime, .Value = DateTimePicker1.Value})

sqlcon.Open()
salesinsert.ExecuteNonQuery()
sqlcon.Close()

salesinsert.Dispose()

• I escaped the column names with square brackets - this avoids problems with using SQL reserved keywordsas column names. It is easier to always escape the column names.
• You should not set SALESINSERT = Nothing- instead, use salesinsert.Dispose()as this cleans up unmanaged resources properly.
• You need to change each .SqlDbType(and .Sizefor strings) to match the datatypes of the database columns. The Decimal values oughtto have the .Scaleand .Precisiondefined too.
• The controls could do with descriptive names - TextBox1does not suggest that it will have an amount in it.
• The values should be validated before running the query, e.g. can the amount text be converted to a Decimal and is it a sensible value.

• 我用方括号对列名进行了转义 - 这避免了使用SQL 保留关键字作为列名的问题。总是对列名进行转义会更容易。
• 您不应该设置SALESINSERT = Nothing- 而是使用，salesinsert.Dispose()因为这可以正确清理非托管资源。
• 您需要更改每个.SqlDbType（和.Size字符串）以匹配数据库列的数据类型。Decimal 值也应该有.Scale和.Precision定义。
• 控件可以使用描述性名称 -TextBox1并不暗示它会有数量。
• 在运行查询之前应该验证这些值，例如，金额文本是否可以转换为十进制，它是否是一个合理的值。

回答by sujith karivelil

The problem is with the format of the given date. you can escape from this problem by formatting the dateTime input using .ToString(). ie.,

问题在于给定日期的格式。您可以通过使用 格式化日期时间输入来避免此问题.ToString()。IE。，

DateTimePicker1.Value.Date.ToString("yyyy-MM-dd HH:mm:ss")

Then comes the real issue of injection; to avoid that you have to use parameterised queriesinstead for the text only queries.

然后是注入的真正问题；为避免您必须使用参数化查询来代替纯文本查询。