Ad:

C# Discussion Thread
Marked Solved
Views: 3128

May 8th, 2009

Problem with SqlDateTime

Expand Post »

Hi all

Im trying to insert an SQLDateTime into an SQL database column which takes DateTime

here is an extract of my code

C# Syntax (Toggle Plain Text)
sTime = new SqlDateTime(DateTime.Now);
                command.CommandText = "INSERT INTO Sensors VALUES ('" + sensors[i].getName() + "','" + sensors[i].getType() + "'," + sensors[i].getVal() + ", " + sTime.Value + " )";
                command.ExecuteNonQuery();
sTime = new SqlDateTime(DateTime.Now);
                command.CommandText = "INSERT INTO Sensors VALUES ('" + sensors[i].getName() + "','" + sensors[i].getType() + "'," + sensors[i].getVal() + ", " + sTime.Value + " )";
                command.ExecuteNonQuery();

The error I'm getting is "Incorrect syntax near '10'. "
10 is the hour it is at this moment it seems to process the date ok but gets stuck after the hour the format of sTime after

C# Syntax (Toggle Plain Text)
sTime = new SqlDateTime(DateTime.Now);
sTime = new SqlDateTime(DateTime.Now);

29/01/2002 10:28:49

Im really stumped by this and databases arn't my strong point, after this line of code is solved thats the end of my final year project in college. So help would be greatly appreciated.

Thanks
Jeff

finnj6

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since May 2009

Permalink

May 8th, 2009

Re: Problem with SqlDateTime

Make sure your DateTime value is enclosed in single quotes.

You might find it easier (to read and build) if you assemble the string using the string.Format() method.

C# Syntax (Toggle Plain Text)
SqlConnection conn = new SqlConnection();
SqlDateTime sTime = new SqlDateTime(DateTime.Now);
SqlCommand command = new SqlCommand(
string.Format("INSERT INTO Sensors VALUES ('{0}','{1}',{2},'{3}' )"
                    , sensors[i].getName()
                    , sensors[i].getType()
                    , sensors[i].getVal()
                    , sTime.Value
                    )
                , conn
                );
command.ExecuteNonQuery();
SqlConnection conn = new SqlConnection();
SqlDateTime sTime = new SqlDateTime(DateTime.Now);
SqlCommand command = new SqlCommand(
string.Format("INSERT INTO Sensors VALUES ('{0}','{1}',{2},'{3}' )"
                    , sensors[i].getName()
                    , sensors[i].getType()
                    , sensors[i].getVal()
                    , sTime.Value
                    )
                , conn
                );
command.ExecuteNonQuery();

Last edited by JerryShaw; May 8th, 2009 at 7:34 pm.

JerryShaw

Reputation Points: 69

Solved Threads: 75

Posting Pro in Training

Offline

465 posts
since Nov 2006

Permalink

May 8th, 2009

Re: Problem with SqlDateTime

Don't build queries dynamically like that, is it dangerous and hurts performance. You do not need to instantiate a new SqlDateTime(otherDateTime), just set the parameter to the CLR DateTime and let the data drivers map it for you.

C# Syntax (Toggle Plain Text)
    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();
      }
    }
    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();
      }
    }

Last edited by sknake; May 8th, 2009 at 10:35 pm. Reason: typo2

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

May 11th, 2009

Re: Problem with SqlDateTime

Thanks for the help I got into my lab last night and got it going. I'm so happy to get it working. I ended up taking sknake's advice and built the command using parameters.

Hopefully I'll get a decent mark now

Jeff

finnj6

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since May 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in C# Forum Timeline: How do I get depressed key even if my application is not in Focus?

Next Thread in C# Forum Timeline: Call Class Library in windows form on click of button

DaniWeb Message

Cancel Changes

User Name
Password