while updatetion datetime conversion problem

[virusisfound]

hi,

i have create a program in C# windows application with backend SQl Server 2005.

I have a done coding for save, delete,update. save and delete is working properly.
But update in that i have an error message taht datetime is not in proper format.what should i do to convert datetime in to string..

I provide u all code for update the database.

 SqlConnection cn = new SqlConnection();
            cn.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\hos.mdf;Integrated Security=True;User Instance=True";
            cn.Open();
            SqlCommand cmd = new SqlCommand("update outpatient set nm = @nm, addr =@addr,phno = @phno, mobno = @mobno,problem =@problem,docid =@docid,docnm =@docnm,spea = @spea,app = @app, age =@age,[B]dt = @dt[/B],ti = @ti where opid=" + textBox1.Text.ToString() + "", cn);
            //cmd.Parameters.AddWithValue("opid", textBox1.Text);
            cmd.Parameters.AddWithValue("nm", textBox2.Text);
            cmd.Parameters.AddWithValue("addr", textBox3.Text);
            cmd.Parameters.AddWithValue("phno", textBox4.Text);
            cmd.Parameters.AddWithValue("mobno", textBox5.Text);
            cmd.Parameters.AddWithValue("problem", textBox6.Text);
            cmd.Parameters.AddWithValue("docid", textBox9.Text);
            cmd.Parameters.AddWithValue("docnm", textBox7.Text);
            cmd.Parameters.AddWithValue("spea", textBox8.Text);
            cmd.Parameters.AddWithValue("app", comboBox1.Text);
            cmd.Parameters.AddWithValue("age", textBox10.Text);
         [B]cmd.Parameters.AddWithValue("dt", textBox11.Text);[/B]
            cmd.Parameters.AddWithValue("ti", comboBox2.Text);

            int n = cmd.ExecuteNonQuery();
            if (n > 0)
            {
                MessageBox.Show("Record Updated");
            }


            cn.Close();

If any one can find the error please let me know...

[Rogachev]

Hello virusisfound,
I guess you have to define SQL parameter type before setting value. Please note, value should be "DateTime" type as well. For example:

cmd.Parameters.Add("@dt", SqlDbType.DateTime);
cmd.Parameters["@dt"].Value = value;

[Lusiphur]

As Rogachev says you are not setting the proper value when passing your date information to the DB.

Your code of:

cmd.Parameters.AddWithValue("dt", textBox11.Text);

is passing a text value as if it were a dateTime value. As long as the textbox is correctly formatted (ie: all the dateTime components in the correct order) you should be able to correct this by changing it to this:

cmd.Parameters.AddWithValue("dt", Convert.ToDateTime(textBox11.Text));

Alternately, you can also follow Rogachev's solution as it only adds 1 line of code to your solution and would be entered as:

cmd.Parameters.Add("@dt", SqlDbType.DateTime);
cmd.Parameters["@dt"].Value = Convert.ToDateTime(textbox11.Text);

Hope that helps :) Please remember to mark as solved once your issue is resolved.

[tanor]

cmd.Parameters["@dt"].Value = Convert.ToDateTime(textbox11.Text);

OK.

[nick.crane]

Consider using a DateTimePicker instead of a TextBox for the the date/time value.
This will remove the posibility of someone entering the date in an invalid format and the Value property is already a DateTime so no convertion is needed.

Also, make sure you are validating the format of all your text fields as even the numeric values will fail if alph characters are entered in the text boxes.

[virusisfound]

hi,

I when i try to run the code its give me an error message on the opid that opid is invalid In the above code its treat like a comment but its by mistake. why this error is occured

[Lusiphur]

Well, if "cmd.Parameters.AddWithValue("opid", textBox1.Text);" is uncommented then from what I'm seeing it serves no purpose as you use

SqlCommand cmd = new SqlCommand("update outpatient set nm = @nm, addr =@addr,phno = @phno, mobno = @mobno,problem =@problem,docid =@docid,docnm =@docnm,spea = @spea,app = @app, age =@age,dt = @dt,ti = @ti where opid=" + textBox1.Text.ToString() + "", cn);

However right there you have a problem because what it should be is

SqlCommand cmd = new SqlCommand("update outpatient set nm = @nm, addr =@addr,phno = @phno, mobno = @mobno,problem =@problem,docid =@docid,docnm =@docnm,spea = @spea,app = @app, age =@age,dt = @dt,ti = @ti where opid='" + textBox1.Text.ToString() + "'", cn);

Realistically you could get away with eliminating the entire line

cmd.Parameters.AddWithValue("opid", textBox1.Text);

as it is not used in your UPDATE string.

The lack of single quotes around your inserted textbox value is where you are receiving a SQL error for an invalid input because it's trying to say "WHERE opid=value" and it should be saying "WHERE opid='value'".

Hope that helps :)

[virusisfound]

Hi,

Its working
thanks a lot....

Virusisfound

[nick.crane]

It might be better to do "... where opid=@opid" and have the opid parameter. This will prevent the possibility of code injection and ensure the value of opid is of the correct type.