Member Avatar for finnj6

Hi all

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

here is an extract of my code

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

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

  • 3 Contributors
  • 3 Replies
  • 460 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by finnj6

Recommended Answers

All 3 Replies

Member Avatar for JerryShaw

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.

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();
Member Avatar for sknake

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.

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();
      }
    }
Member Avatar for finnj6

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.