date time store problem in asp.net

Question

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 15-Mar-13 23:06pm

Darpan Dahal

Updated 15-Mar-13 23:20pm

Sandeep Mewara

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sandeep Mewara · Accepted Answer · 2013-03-15T23:22:00

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[^]

OriginalGriff · Accepted Answer · 2013-03-15T23:20:00

Solution 1

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...

Posted 15-Mar-13 23:20pm

OriginalGriff

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: