none
Cannot convert date time into string RRS feed

  • Question

  •                 double ours = Convert.ToDouble(hours_text.Text);
                    DateTime dt = DateTime.Today;
                                         
                    int day = 1;

                    conect.Open();
                    SqlCommand cmd = conect.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "insert into tab1 values('" + day + "','" + dt.ToString() + "','" + dt.DayOfWeek.ToString() + "','" + ours + "')";
                    cmd.ExecuteNonQuery(); // here i get the error
                    conect.Close();

                    
                    Close();
    Wednesday, November 13, 2019 8:38 PM

Answers

  • Hello,

    Here is a model to work from, you will need to make sure things are correct e.g. is the day variable a string, add a connection string etc.

    using (var cn = new SqlConnection {ConnectionString = "TODO"})
    {
        using (var cmd = new SqlCommand {Connection = cn})
        {
    
            if (!double.TryParse(hours_text.Text, out var hours))
            {
                return;
            }
            string day = "";
            DateTime dt = DateTime.Today;
    
            cmd.CommandText = "insert into tab1 values(@p1,@p2,@p3,@p4)";
    
            cmd.Parameters.AddWithValue("@p1", day);
            cmd.Parameters.AddWithValue("@p2", dt);
            cmd.Parameters.AddWithValue("@p3", dt.DayOfWeek);
            cmd.Parameters.AddWithValue("@p4", hours);
    
            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                Console.WriteLine(exception.Message);
            }
    
        }
    }

    Note that when using INSERT INTO without specifying field names the values must be in the proper order in the table, I advise using 

    INSERT INTO table_name (column1, column2, column3, ...)
    VALUES (value1, value2, value3, ...);


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, November 13, 2019 11:33 PM
    Moderator

All replies

  •  There are many things that can go wrong with a SQL command, why do you think the issue is with the datetime => string conversion? What is the text of the error?

    Ethan


    Ethan Strauss

    Wednesday, November 13, 2019 9:35 PM
  • Hello,

    Here is a model to work from, you will need to make sure things are correct e.g. is the day variable a string, add a connection string etc.

    using (var cn = new SqlConnection {ConnectionString = "TODO"})
    {
        using (var cmd = new SqlCommand {Connection = cn})
        {
    
            if (!double.TryParse(hours_text.Text, out var hours))
            {
                return;
            }
            string day = "";
            DateTime dt = DateTime.Today;
    
            cmd.CommandText = "insert into tab1 values(@p1,@p2,@p3,@p4)";
    
            cmd.Parameters.AddWithValue("@p1", day);
            cmd.Parameters.AddWithValue("@p2", dt);
            cmd.Parameters.AddWithValue("@p3", dt.DayOfWeek);
            cmd.Parameters.AddWithValue("@p4", hours);
    
            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                Console.WriteLine(exception.Message);
            }
    
        }
    }

    Note that when using INSERT INTO without specifying field names the values must be in the proper order in the table, I advise using 

    INSERT INTO table_name (column1, column2, column3, ...)
    VALUES (value1, value2, value3, ...);


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, November 13, 2019 11:33 PM
    Moderator
  • yeah right. Because i get the error message when i debug...

    Thursday, November 14, 2019 6:39 PM
  • Thanks for your help. 

    Your code it's great and it work but it have another issue.

    when i execute first time it works fine but when i put another date i show the error message that says violation of primary key ... 
    Day in data table in sql is the primary key and i dont know if i need to put his value or not. 

    i have try the both of cases when i put that value get the error cannot doplicate the primary key

    when i take of that value i get the error of missing value.





    • Edited by Alban_Troplini Thursday, November 14, 2019 8:17 PM this work but it have another issue
    Thursday, November 14, 2019 6:56 PM
  • If it is the primary key, then the field must always be present, and must be unique.  You can't insert two records with the same primary key.

    Karen's example specified an empty string.  Your code had an integer.  What is it you want in there?


    Tim Roberts | Driver MVP Emeritus | Providenza & Boekelheide, Inc.

    Friday, November 15, 2019 1:07 AM