inserting datetimepicker value to SQL Server 2008

Question

I'm trying to insert a value from a datetimepicker value to a SQL Server table.

My table looks like this

Profile (Id, Name,..., DateofBirth(date)...)

I have tried this to convert datetime picker value to

string dt = dateTimePicker.Value.ToString("yyyy-mm-dd hh:MM:ss");

Insert into profile (id, DateofBirth)  
values(id,  CONVERT(datetime, CONVERT( varchar(11), dt, 101));

also use this

var date = new DateTime(dateTimePickerText);

also use this

DateTime date = DateBox.Value.Date;
string sDate = date.ToString("dd-MM-yy", System.Globalization.CultureInfo.InvariantCulture);
DateTime dateInsert = Convert.ToDateTime(sDate);

but can't able to insert the date into the database. 2nd how can I retrieve back the date from database?

1) what is the error message? 2) execute a sql SELECT. That's probably not the kind of answer you are looking for, but I can't imagine what are you looking for. Can you be more specific about your problem "how can I retrieve" ? Are you using a SQLCommand object, or Linq, or dataset? Details, please. — Elroy Flynn, Jan 18 '12 at 03:50
Date is saved to the database using cmd.Parameters.Add("@DateofBirth",SqlDbType.DateTime).Value=dateTimePicker but now i want to retreive it back and dolike this if(dateStored=datetime.now()) how can i do it? — buddy, Jan 18 '12 at 05:29

score 10 · Accepted Answer · answered Jan 18 '12 at 03:48

10

You must have to use SqlParameter.

 sql="Insert into profile (id, DateofBirth) values (@id,@DateofBirth)";
    using(SqlCommand cmd=new SqlCommand(sql,conn))
     {
        cmd.Parameters.Add("@id",SqlDbType.Int).Value=10;
        cmd.Parameters.Add("@DateofBirth",SqlDbType.DateTime).Value=dateTimePicker.Value;

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
     }

answered Jan 18 '12 at 03:48

KV Prajapati

93,659
19
148
186

Its work fine but during insertion of this commnd **cmd.Parameters.Add("@_name", SqlDbType.VarChar).Value = NameBox.Text;** it's prompts invalid object... how to insert it – buddy Jan 18 '12 at 05:43
Specify the size : cmd.Parameters.Add("@_name", SqlDbType.VarChar,40).Value = NameBox.Text; – KV Prajapati Jan 18 '12 at 05:48
I got one more error now **cmd.Parameters.Add("@AccountId",SqlDbType.Int).Value = AccountInformation.AccountId;** where AccountInformation.AcountId contain integer value. but the error is **Conversion failed when converting the varchar value '@AccountId' to data type int.** – buddy Jan 18 '12 at 06:24
@buddy - Double check the field type of AccountID in database. As per error description - the field AccountID and AccountInformation.AcountId must be int type. – KV Prajapati Jan 18 '12 at 08:50

score 2 · Answer 2 · edited May 23 '17 at 12:17

2

Personally I'd get into the habit of using parameters for all of your SQL queries. That way you avoid SQL injection attack vector and you can also specify the parameter type as datetime. See this answer for example.

edited May 23 '17 at 12:17

Community

1
1

answered Jan 18 '12 at 03:50

si618

16,580
12
67
84

inserting datetimepicker value to SQL Server 2008

2 Answers2

Linked