Datatype problem in my Windows Application

Question

0.00/5 (No votes)

See more:

Dear All
I developed and created setup.exe a Windows Based Application. After installing the software in client machine I open the Order Form and input some data to it and clicked submit button to insert data into database. It throws an exception as like:-

Error:- System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime datatype resulted in an out-of-range datetime value.
The statement has been terminated.

<img src="C/Users/Mashkoor/Desktop/untitled.jpg" />;
While I used datetime datatype for 'Date of Order' column in SQL Server 2005 database. In windows application I used a datetimepicker control for 'Date of Order'column. The date format in the Client Machine is like 11 September 2013

[edit]
Sir, this is the code snippet which I wrote to insert data:

C#

try
{
    string s = "insert into Sales_Details values('" + comboBox1.Text + "','" + comboBox2.Text + "','" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','"+dateTimePicker1.Value+"','"+dateTimePicker2.Value+"','" + textBox5.Text + "','" + textBox6.Text + "','" + textBox7.Text + "','" + textBox8.Text + "')";
    SqlConnection con = new SqlConnection(Class1.cs);
    con.Open();
    SqlCommand cmd = new SqlCommand(s, con);
    cmd.ExecuteNonQuery();
    MessageBox.Show("Information Saved Successfully");
    textBox3.Text = "";
    comboBox2.Text = "";
    textBox1.Text = "";
    textBox4.Text = "";
    textBox2.Text = "";
    dateTimePicker1.Text = "";
    dateTimePicker2.Text = "";
    textBox5.Text = "";
    textBox6.Text = "";
    textBox7.Text = "";
    textBox8.Text = "";
    con.Close();
}
catch (Exception ex)
{
    MessageBox.Show("Error:"+ex);
}

Posted 10-Sep-13 23:01pm

Md M Ahmad

Updated 11-Sep-13 0:34am

Richard MacCutchan

v3

Add a Solution

Comments

Richard MacCutchan 11-Sep-13 5:15am

What is the actual SQL statement, and what are the values of the parameters that it is using?

Md M Ahmad 11-Sep-13 6:26am

Comment moved to OP question.

Richard MacCutchan 11-Sep-13 6:37am

Yet another example of bad coding practices. You are not validating any of your inputs. You are not checking the return value from the SQL command - you just assume it succeeds. And your code is wide open to SQL injection attacks.
Rewrite it with proper checks and use parameterized SQL queries.

Md M Ahmad 11-Sep-13 6:50am

Sir I am very new to C#, and very curious to know things well, I don't know how to protect codes from SQL injection attacks. Please help me to write good codes. As I given some code snippet please correct it by using parametrized queries. Thanks

Richard MacCutchan 11-Sep-13 12:04pm

The subject is covered in http://csharp-station.com/Tutorial/AdoDotNet/Lesson06. I would suggest you work through all the lessons in this excellent tutorial.

3 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)

OriginalGriff · Answer 1 · 2013-09-10T23:22:00

So, if you are using a DateTimePicker, why are you converting it to a string to pass it to SQL?

Simple: because your code is wide open to SQL injection attack because you are concatenating strings to form an SQL command instead of passing them as parametrized queries and sending the values in a sensible, unconverted form.
So when you write

C#

string cmd = "INSERT INTO MyTable (dateColumn) VALUES ('" + myDateTimePicker.Value + "')";

The string presented to SQL depends on the system date and time format settings in the PC running the C# code: it could be

SQL

INSERT INTO MyTable (dateColumn) VALUES ('11 September 2013')

Or

SQL

INSERT INTO MyTable (dateColumn) VALUES ('11 09 2013')

Or

SQL

INSERT INTO MyTable (dateColumn) VALUES ('2013/9/11')

Since SQL normally expects:

SQL

INSERT INTO MyTable (dateColumn) VALUES ('2013-09-11')

there is a good chance it will complain on some PCs and not on others.
Use a parametrized query, and pass DateTime values as DateTimes, integers as integers, etc.

vinodhphoenix · Answer 2 · 2013-09-10T23:32:00

Solution 2

Use DateTime type to get the date from client machine.

Posted 10-Sep-13 23:32pm

vinodhphoenix

Comments

Md M Ahmad 11-Sep-13 6:27am

Sir, already I have used that kind of datatype.

murkalkiran · Answer 3 · 2013-09-11T01:12:00

Solution 3

change the date format in client machine

go to control panel in the double clink on regional and language option select the time format to English(united kingdom)

i think this will slove u r issue

Posted 11-Sep-13 1:12am

murkalkiran

Comments

lukeer 11-Sep-13 8:17am

You forgot to place the joke icon.

OriginalGriff 11-Sep-13 8:33am

I don't think he's joking... :sigh:

Datatype problem in my Windows Application

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us