C# and SQL server 2008 DateTime

[brainbox]

hi

I am badly stuck with inserting and retrieving Date in SQL DB from C#. I have a C# application which needs to save date in SQL 2008 Database for various purposes.

The format in DB is DateTime for my DateColumn and i am using Datetime Picker in my c# application to get the date from user and save it in database.

I have tried all sorts of formats, however, unfortunately i cant get the program to save the date in the database correctly, it saves as some random date mainly 1900-1-1 or 1905 ... from somewhere

I have tried:

this.dtpDOB.Value.Date.ToShortDateString()
this.dtpDOB.text and splitted into tokens and then saved it
string origDate = this.dtpDOB.Value.ToShortDateString();

string[] dateTokens = origDate.Split('/');
string sqlDate = dateTokens[2] + "-" + dateTokens[0] + "-" + dateTokens[1];

and i have also tried

this.dtpDOB.Value.ToShortDateString()

Please Help As soon as possible

Thanks
Sarah

[kvprajapati]

This is 22nd post and yet your code is not surrounded with BB code tags.

Can I see your source code and also table structure?

[brainbox]

sqlstring = "INSERT INTO EmployeeDetails(EmployeeId,Title,FirstName,MiddleName,Surname,DOB,Age,Nationality,NINO,PicPath,HousePhoneNo,MobileNo,Email,Other,HouseName,Address1,Address2,City,County,PostCode,Country)" + 
                    "VALUES(" +
                    "'"+ this.txtEmployeeIdHidden.Text+"'," +
                    "'" + this.cmbEmployeeTitle.Text + "'," +
                    "'" + this.cmbEmployeeFirstName.Text + "'," +
                   "' ',' ',"+
                    this.dtpDOB.Value.Date.ToShortDateString() + 
                    "," + this.txtEmployeeAge.Text + "," +
                    "'" + this.txtEmployeeNationality.Text + "'," +
                    "'" + this.txtEmployeeNINO.Text + "'," +
                    "'" + filepath + "'," +
                    "'" + this.txtBxHousePhoneNo.Text + "'," +
                    "'" + this.txtBxMobile.Text + "'," +
                    "'" + this.txtBxEmail.Text + "'," +
                    "'" + this.txtBxOther.Text + "'," +  
                    "'" + this.txtBxHouseNum.Text + "'," +
                    "'" + this.txtBxAddress1.Text + "'," +
                    "'" + this.txtBxAddress2.Text + "'," +
                    "'" + this.txtBxCity.Text + "'," +
                    "'" + this.txtBxCounty.Text + "'," +
                    "'" + this.txtBxPostcode.Text + "'," +
                    "'" + this.txtBxCountry.Text + "'" +
                    ")";

Rest is working fine, however , DOB cannot be inserted the format for which is datetime as i mentioned earlier.

sorry about the code tags, hope this is fine now

thanks in advance

Sarah

[kvprajapati]

Date must be surrounded by single quotes.

...,'"+   this.dtpDOB.Value.Date.ToShortDateString() + "'," .....

[brainbox]

Thanks a lot , but when i tried, it came up with a message box saying
"The conversion of a varchar datatype to a datetime datatype resulted in an out-of-range value "

What should i do now?

[kvprajapati]

Change the datatype of DOB to DateTime in a table EmployeeDetails.

[brainbox]

it is DateTime already , but still doesnt work

[kvprajapati]

Your table's columns 6th has DateTime datatype (its not a smalldatetime) - So,

Try this

...,'"+   this.dtpDOB.Value + "'," .....

[brainbox]

Your table's columns 6th has DateTime datatype (its not a smalldatetime) - So,

Try this

...,'"+   this.dtpDOB.Value + "'," .....

I have tried this, but it does not work either and says error near '00' i believe it is taking time with it and when i use ToshortDateString() it comes up with the same error as before - the string conversion.

And it is datetime (small letters) i have double checked, i have tried DateTime but its not there

thanks for your help in advance

[kvprajapati]

Did you change the datetimepicker's format? If so, know us the format.

PS: To test your application remove datetimepicker and use textbox (date format must be mm-dd-yyyy).

[sknake]

Please see thread http://www.daniweb.com/forums/thread191241.html as you are having the same issue. Use parameters when you build your query. Building queries with string values is dangerous because the user could potentially escape the input and write raw TSQL, in addition it hurts performance.

Do something like:

private void simpleButton1_Click(object sender, EventArgs e)
    {
      const string query = "Insert Into aTable (aString, aDateTime) Values (@aString, @aDateTime)";
      const string connStr = @"Data Source=apex2006sql;Initial Catalog=DB;Integrated Security=True;";
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          string s1 = "abc123";
          DateTime dtNow = DateTime.Now;
          cmd.Parameters.Add(new SqlParameter(@"aString", SqlDbType.VarChar)).Value = s1;
          cmd.Parameters.Add(new SqlParameter(@"aDateTime", SqlDbType.DateTime)).Value = dtNow;
          cmd.ExecuteNonQuery();
        }
        conn.Close();
      }
    }

[brainbox]

Thanks very much, your advice led me to the solution, i have tested it with one of my tables hopefully all will work now, thanks once again, however, the @ didnt work and i had to use '?' which i belive could be the case witl Oledb
please correct me if i am wrong, the problem might have been caused by oledb before, however, your advice of using parameters and solution solved the problem as i changed sql to oledb and used parameters.

http://groups.google.com/group/DotNetDevelopment/browse_thread/thread/e9436a2d5fa10426

i followed the above given link, and managed it fine,

Thanks once again

[sknake]

Yes OleDb can used ?Parameters and I believe ODBC uses ? without name and relies on the order which they appear. I try to stay away from those drivers as much as possible so i'm not sure.

Is there a specific reason you are using OleDb for SQL2008? I would highly recommend using a native driver.

[brainbox]

For my previous project i used the connection class which used the oledb and i just used that class to just reuse the code and basically use the .udl so that it will be easier, not a parrticular reason behind using oledb instead of SQL , if i wud have know that this would have taken my time that i wud have spent this time on making a SQLConnection class. well will learn from mistakes :)

yes they are in particular order and i am not pretty sure whether this is good practice to use ?,?,? when you have so many parameters to pass, well in this short period of time i will have to do whatever i can, i am afraid, i dont knw how it cud be used professionally.

i dont knw much about drivers

thanks once again