Click here to Skip to main content
15,905,148 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have the following code behind.
C#
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
SQL
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]

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?
Posted
Updated 15-Mar-13 23:20pm
v3

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

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[^]
 
Share this answer
 
Comments
Surendra0x2 16-Mar-13 5:32am    
+5
Darpan Dahal 16-Mar-13 5:43am    
Thnks bro! it's working nice. And Thanx for suggestion.
Darpan Dahal 16-Mar-13 5:57am    
I have used the above syntax in all the pages. Do u have any idea to solve this problem without changing the coding part.
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.

C#
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...
 
Share this answer
 
Comments
Darpan Dahal 16-Mar-13 5:40am    
Thnks bro! it's working nice. And Thanx for suggestion.
OriginalGriff 16-Mar-13 5:49am    
You're welcome!
And seriously: if you have similar code anywhere else in your program, change it now. Or you will find your database empty one day...
Darpan Dahal 16-Mar-13 5:57am    
I have used the above syntax in all the pages. Do u have any idea to solve this problem without changing the coding part.
OriginalGriff 16-Mar-13 6:15am    
No. There isn't one.
The problem is that SQL Injection is horribly easy to do: All I have to do is type into your purchase code text box and when you concatenate your strings you include exactly what I typed into your SQL command. So if I add "DROP TABLE tblPurchacedInfo" then that is fed through to SQL and it will happily do just that - delete the table.

Have a quick google for "Bobby Tables" and don't assume it is just a joke!
They last UK Census was the first that could be submitted online, and within an hour of the site opening people were complaining that SQL Injection didn't work! It is a real problem, and if you expose your commands to the general public, they will give it a try. Heck, your best friend will try it just to see the look on your face! :laugh:

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900