Winforms using SQL Server 2008

Question

con = new SqlConnection(cs);
con.Open();
DateTime current = DateTime.Now;
current = Convert.ToDateTime(DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss"));
SqlCommand cmd = new SqlCommand(@"INSERT INTO CustomerDetails
(CustomerId, Date, Name, Gender, Address, Phone, Email, MobileNo,Notes)
 VALUES  ('" + txtCustomerID.Text + "','" + current + "','" + 
 txtCustomerName.Text + "','"+Gender+"','"+txtAddress.Text+"','" + txtPhone.Text 
 + "','" + txtEmail.Text+"','" + txtMobileNo.Text + "','" +txtNotes.Text + "')", 
      con);

cmd.ExecuteNonQuery();
con.Close();

I am using this code in database my date has datetime datatype but when I am saving the data through form it is showing an error:

The conversion of a varchar datatype to a datetime data type resulted in an out-of-range value.

What is problem? Why is this error showing up?

Check your Date field if it really is a Date/Time data type or varchar as suggested by the error. — Edper
– Edper, Commented Mar 12, 2014 at 3:30

Szymon · Accepted Answer · 2014-03-12 03:43:25Z

1

You should be using parameters instead of concatenating strings. Not only it will remove the problem with date formats but will also protect you from SQL injection.

SqlCommand cmd = new SqlCommand(@"INSERT INTO CustomerDetails
    (CustomerId, Date, Name, Gender, Address, Phone, Email, MobileNo,Notes)
     VALUES  (@CustomerId, @Date, @Email, @Mobile, @Notes)", con);
cmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 10).Value = txtCustomerID.Text;
cmd.Parameters.AddWithValue("@Date", SqlDbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@Email", SqlDbType.VarChar, 10).Value = txtEmail.Text;
cmd.Parameters.Add("@Mobile", SqlDbType.VarChar, 10).Value = txtMobileNo.Text;
cmd.Parameters.Add("@Notes", SqlDbType.VarChar, 10).Value = txtNotes.Text;

answered Mar 12, 2014 at 3:43

Szymon

43k16 gold badges99 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sudhakar Tillapudi · Accepted Answer · 2014-03-12 03:41:41Z

0

use getdate() or SYSDATETIME() function of sql if you want to store current date.

Try This:

SqlCommand cmd = new SqlCommand(@"INSERT INTO CustomerDetails
(CustomerId, Date, Name, Gender, Address, Phone, Email, MobileNo,Notes)
 VALUES  ('" + txtCustomerID.Text + "',+ getdate() + ,'" + 
 txtCustomerName.Text + "','"+Gender+"','"+txtAddress.Text+"','" + txtPhone.Text 
 + "','" + txtEmail.Text+"','" + txtMobileNo.Text + "','" +txtNotes.Text + "')", 
      con);

Suggestion : your query is open to sql injection attacks so i'd suggest you to use Paramterised Queries to avoid them.

answered Mar 12, 2014 at 3:41

Sudhakar Tillapudi

26.3k5 gold badges43 silver badges70 bronze badges

Comments

attila · Accepted Answer · 2014-03-12 03:50:47Z

0

When concatenating your sql string you need to append the date string, not a datetime variable. So unless you have some other reason for the current variable being a date, I would make it a string.

string current = DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss"));

answered Mar 12, 2014 at 3:50

attila

2,2291 gold badge13 silver badges16 bronze badges

Collectives™ on Stack Overflow

Winforms using SQL Server 2008

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related