asp.net中的日期时间存储问题 [英] date time store problem in asp.net

问题描述

我有以下代码。

I have the following code behind.

string qry = "insert into tblPurchasedInfo(PurchaseCode,Purchasedate,EntryDate,BillNo,VendorCode) values('" + txtPurchaseCode.Text + "','" + txtPurchaseDate.Text + "','" + DateTime.Today + "','" + txtBillNo.Text + "','" + cmbVenCode.SelectedValue + "')";
 
SqlCommand  cmd = new SqlCommand(qry, con);
 int rws = cmd.ExecuteNonQuery();

和数据库设计

and database design

CREATE TABLE [dbo].[tblPurchasedInfo](
    [PurchaseCode] [varchar](50) NOT NULL,
    [PurchaseDate] [smalldatetime] NOT NULL,
    [EntryDate] [smalldatetime] NOT NULL,
    [BillNo] [varchar](50) NOT NULL,
    [VendorCode] [varchar](50) NOT NULL,
    [Purchaseno] [bigint] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

错误信息是

（将varchar数据类型转换为smalldatetime数据类型导致超出范围的值。）



你可以吗？告诉我如何处理和解决问题？

And the error message is
(The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.)

Can u tell me how to handle and solve the problem?

推荐答案

string qry = "insert into tblPurchasedInfo(PurchaseCode,Purchasedate,EntryDate,BillNo,VendorCode) values('" + txtPurchaseCode.Text + "','" + txtPurchaseDate.Text + "','" + DateTime.Today + "','" + txtBillNo.Text + "','" + cmbVenCode.SelectedValue + "')";

您将字符串数据传递给在DB中标记为datetime数据类型的字段。



此外，你实现它的方式，它对SQL注入是开放的。您应该使用参数化查询来避免安全问题以及轻松处理数据类型数据。

在这里查看参数化查询及其用法：

MSDN：配置参数和参数数据类型（ADO.NET） [ ^ ]

MSDN：DataAdapter参数（ADO。 NET） [ ^ ]

MSDN：SqlCommand.Parameters Property [< a href =http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameterstarget =_ blanktitle =New Window> ^ ]





请阅读以下有关保护SQL注入的信息： SQL注入缓解：使用参数化查询 [ ^ ]

You are passing string data to the fields that are marked as datetime datatype in DB.

Further, the way you have implemented it, it''s open for SQL Injection. You should use paramterized query to avoid the security issue as well as handle the datatype data supply easily.
Look here for parameterized query and it''s usage:
MSDN: Configuring Parameters and Parameter Data Types (ADO.NET)[^]
MSDN: DataAdapter Parameters (ADO.NET)[^]
MSDN: SqlCommand.Parameters Property [^]


Read about protecting from SQL Injection here: SQL Injection Mitigation: Using Parameterized Queries[^]

不要这样做！

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

Don''t do it like that!
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.

string qry = "INSERT INTO tblPurchasedInfo(PurchaseCode,Purchasedate,EntryDate,BillNo,VendorCode) values(@PC, @PD, @NOW, @BN, @VC)";
SqlCommand cmd = new SqlCommand(qry, con);
cmd.Parameters.AddWithValue("@PC", txtPurchaseCode.Text);
cmd.Parameters.AddWithValue("@PD", txtPurchaseDate.Text);
cmd.Parameters.AddWithValue("@NOW", DateTime.Today);
cmd.Parameters.AddWithValue("@BN", txtBillNo.Text);
cmd.Parameters.AddWithValue("@VC", cmbVenCode.SelectedValue);
SqlCommand cmd = new SqlCommand(qry, con);

你应该发现你的问题已经消失了，我不能从世界的另一端删除您的数据库......

You should find your problem has disappeared, and I can''t delete your database from the other side of the world...

这篇关于asp.net中的日期时间存储问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！