CODEWITHSUNDEEP
c#.netsqlstring
AddyProg
AddyProg

Reputation: 3050

Converting dateTImePicker value into string

I am using dateTimePicker to collect date from user in a windows form to insert in SQL Server Database but when i debug it it says "connot convert dateTime into string" here is the code

string Agent = FieldAgentCombo.Text;
            string Query = "INSERT INTO Comittment(Date,Field_Staff_Date,Detail,Priority,company_name,Name) values('" + Client + "','" + Agent + "','" + Date + "','" + FieldStaffDate + "','" + Detail + "','" + Priority + "')";

            SqlCommand cmd = new SqlCommand(Query, conn);

            int status = cmd.ExecuteNonQuery();
            if (status > 0)
                MessageBox.Show("record inserted");

Upvotes: 0

Views: 6346

Answers (2)

Darin Dimitrov
Darin Dimitrov

Reputation: 1039498

Your code is vulnerable to SQL injection. I would recommend you using parametrized queries. Also in your SQL query you seem to have mixed the parameters. Make sure they are matching. For example:

// load the values that you want to insert into standard .NET types
DateTime date = ...
DateTime fieldStaffDate = ...
string detail = ...
string priority = ...
string companyName = ...
string name = ...

// now connect to the database to execute the SQL query
using (var conn = new SqlConnection(ConnectionString))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = 
    @"INSERT INTO Comittment(
          Date, 
          Field_Staff_Date, 
          Detail, 
          Priority, 
          company_name, 
          Name) 
      VALUES (
          @Date, 
          @Field_Staff_Date, 
          @Detail, 
          @Priority, 
          @company_name, 
          @name)";

    cmd.Parameters.AddWithValue("@Date", date);
    cmd.Parameters.AddWithValue("@Field_Staff_Date", fieldStaffDate);
    cmd.Parameters.AddWithValue("@Detail", detail);
    cmd.Parameters.AddWithValue("@Priority", priority);
    cmd.Parameters.AddWithValue("@company_name", companyName);
    cmd.Parameters.AddWithValue("@name", name);

    cmd.ExecuteNonQuery();
}

This way the query is no longer vulnerable to SQL injection and in addition to that ADO.NET will take care of properly formatting the .NET types into the corresponding SQL types so that you don't need to be doing any string parsing and date manipulations.

Upvotes: 3

Andrew
Andrew

Reputation: 7798

If you want to insert current date to sql, instead of doing conversions, just add now() directly to query

Other than that, check this link: http://www.csharp-examples.net/string-format-datetime/ 

// create date time 2008-03-09 16:05:07.123
DateTime dt = new DateTime(2008, 3, 9, 16, 5, 7, 123);

String.Format("{0:y yy yyy yyyy}", dt);  // "8 08 008 2008"   year
String.Format("{0:M MM MMM MMMM}", dt);  // "3 03 Mar March"  month
String.Format("{0:d dd ddd dddd}", dt);  // "9 09 Sun Sunday" day
String.Format("{0:h hh H HH}",     dt);  // "4 04 16 16"      hour 12/24
String.Format("{0:m mm}",          dt);  // "5 05"            minute
String.Format("{0:s ss}",          dt);  // "7 07"            second
String.Format("{0:f ff fff ffff}", dt);  // "1 12 123 1230"   sec.fraction
String.Format("{0:F FF FFF FFFF}", dt);  // "1 12 123 123"    without zeroes
String.Format("{0:t tt}",          dt);  // "P PM"            A.M. or P.M.
String.Format("{0:z zz zzz}",      dt);  // "-6 -06 -06:00"   time zone

Upvotes: 0

Related Questions