none
how to enter a date into sql table

    Question

  • Hi, can anybody help me with the code?

     

    da.InsertCommand =

    new SqlCommand("INSERT INTO tblNUMBERS VALUES(@FirstNum, @SecondNum, @ThirdNum, @FourthNum, @FifthNum, @Date, @Comment)", cs);

    da.InsertCommand.Parameters.Add("@FirstNum", SqlDbType.Int).Value = int

    .Parse(txtFirstNum.Text);

    da.InsertCommand.Parameters.Add(

    "@SecondNum", SqlDbType.Int).Value = int

    .Parse(txtSecondNum.Text);

    da.InsertCommand.Parameters.Add(

    "@ThirdNum", SqlDbType.Int).Value = int

    .Parse(txtThirdNum.Text);

    da.InsertCommand.Parameters.Add(

    "@FourthNum", SqlDbType.Int).Value = int

    .Parse(txtFourthNum.Text);

    da.InsertCommand.Parameters.Add(

    "@FifthNum", SqlDbType.Int).Value = int

    .Parse(txtFifthNum.Text);

     

    da.InsertCommand.Parameters.Add(

    "@Date", SqlDbType.Date).Value = DateTime

    .Parse(mskdDate.Text);

    da.InsertCommand.Parameters.Add(

    "@Comment", SqlDbType

    .VarChar).Value = txtComment.Text;

    cs.Open();

    da.InsertCommand.ExecuteNonQuery();

    cs.Close();

    I'm getting error with date field. The error is:

    

    An error occurred while executing batch. Error message is: Invalid attempt to GetBytes on column 'Date'. The GetBytes function can only be used on columns of type Text, NText, or Image.

    Any ideas. Thank you

     

    Friday, March 25, 2011 4:36 PM

Answers

  • Try to convert it as:

    Convert.ToDateTime(mskdDate.Text);
    

    But I woold strongly suggest yout to swap the masked textBox with  the dateTimePicker. The problem with masked textBoxes appears when the user does not insert the correct (or full) date). This cannot happen with dateTimePicker selection.

    Think about it. It will salve your problem in a blink of your eye. 

     

    For getting the value of dateTimePicker you simple do:

     

    dateTimePicker1.Value; (whole date with time); //or:
    dateTimePicker1.Value.Date; (only date with time set to zero);
    
    
    

     

    Mitja

    • Marked as answer by Aspen VJ Sunday, April 03, 2011 2:45 PM
    Saturday, March 26, 2011 12:20 PM
  • You ar complicating things too much. Do it this way:

     

      private void Insertion(int num1, DateTime myDate) //parameters passed to this method to insert!
      {
       using (SqlConnection sqlConn = new SqlConnection("ConnectionString"))
       {
        string query = @"INSERT INTO tblNUMBERS VALUES(@FirstNum, @Date)";
        SqlCommand cmd = new SqlCommand(query, sqlConn);
        cmd.Parameters.Add("@FirstNum", SqlDbType.Int).Value = num1;
        cmd.Parameters.Add("@Date", SqlDbType.Date).Value = myDate;
        sqlConn.Open();
        try
        {
         cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
         MessageBox.Show("Error: " + ex.Message);
        }
       }
      }
    

     

     

    My example only uses only 2 values (2 columns in DB). You do it like I did the same way for all the values. It will work.

    Hope it helps,

    Mitja


    • Proposed as answer by Hasibul Haque Saturday, March 26, 2011 8:45 AM
    • Edited by Mitja Bonca Saturday, March 26, 2011 8:53 AM Repaired as Haisbul has seen a mistake
    • Marked as answer by Aspen VJ Sunday, April 03, 2011 2:45 PM
    Friday, March 25, 2011 4:43 PM

All replies

  • You ar complicating things too much. Do it this way:

     

      private void Insertion(int num1, DateTime myDate) //parameters passed to this method to insert!
      {
       using (SqlConnection sqlConn = new SqlConnection("ConnectionString"))
       {
        string query = @"INSERT INTO tblNUMBERS VALUES(@FirstNum, @Date)";
        SqlCommand cmd = new SqlCommand(query, sqlConn);
        cmd.Parameters.Add("@FirstNum", SqlDbType.Int).Value = num1;
        cmd.Parameters.Add("@Date", SqlDbType.Date).Value = myDate;
        sqlConn.Open();
        try
        {
         cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
         MessageBox.Show("Error: " + ex.Message);
        }
       }
      }
    

     

     

    My example only uses only 2 values (2 columns in DB). You do it like I did the same way for all the values. It will work.

    Hope it helps,

    Mitja


    • Proposed as answer by Hasibul Haque Saturday, March 26, 2011 8:45 AM
    • Edited by Mitja Bonca Saturday, March 26, 2011 8:53 AM Repaired as Haisbul has seen a mistake
    • Marked as answer by Aspen VJ Sunday, April 03, 2011 2:45 PM
    Friday, March 25, 2011 4:43 PM
  • Hi Mitja Bonca,

    I think there will be end bracket at the end of following line 

    string query = @"INSERT INTO tblNUMBERS VALUES(@FirstNum, @Date";

    something like

    string query = @"INSERT INTO tblNUMBERS VALUES(@FirstNum, @Date)";



    Hasibul Haque, MCPD http://blog.e-rains.com
    Saturday, March 26, 2011 8:45 AM
  • Yes, sorry, it was a typo. This is what happens if you write a code by your heart. Hopefully there is not much of such mistakes. 

    Thx for seening the problem Hasibul. Really appreciate it.

    Mitja

    Saturday, March 26, 2011 8:53 AM
  • Hi,

    thanks for replying. What I'm trying to do here is to read the value from the masked box and insert it into a data table.  The code itself probably does it but i'm getting error at the end. I think there is a problem with format of this data. It is being parsed as datetime and my sql date table can't cope with it. Here's the code:

    da.InsertCommand =

    new SqlCommand("INSERT INTO tblNUMBERS VALUES(@Date, @Comment)", cs);                            da.InsertCommand.Parameters.Add("@Date", SqlDbType.Date).Value = DateTime.Parse(mskdDate.Text);           

    da.InsertCommand.Parameters.Add("@Comment", SqlDbType

    .VarChar).Value = txtComment.Text;

    cs.Open();                                                                                                           da.InsertCommand.ExecuteNonQuery();                                                                                               cs.Close();

    In my Sql server management studio, the Query "SELECT Comment FROM

    tblNUMBERS" is giving me the values from table. No problems there. The problem is this: "SELECT Date FROM

    tblNUMBERS". It is giving me: "

    An error occurred while executing batch. Error message is: Invalid attempt to GetBytes on column 'Date'. The GetBytes function can only be used on columns of type Text, NText, or Image."

    My Column Properties - Date, in management studio:                                                                                   Name - Date, Data Type - date, System Type - date, Primary Key - False, Allow nulls - True, Is Computed - False, Identity - False, Identity seed - 0, Identity increment - 0, Lenght - 3, Numeric Precision - 10, Numeric Scale - 0, Not for replication - False, Full text - false, ANSI Padding Status - False

    So, what do you think?

     

    Saturday, March 26, 2011 11:38 AM
  •  

    Hi,

    thanks for replying. What I'm trying to do here is to read the value from the masked box and insert it into a data table.  The code itself probably does it but i'm getting error at the end. I think there is a problem with format of this data. It is being parsed as datetime and my sql date table can't cope with it. Here's the code:

    da.InsertCommand =new SqlCommand("INSERT INTO tblNUMBERS VALUES(@Date, @Comment)", cs);                           

    da.InsertCommand.Parameters.Add("@Date", SqlDbType.Date).Value = DateTime.Parse(mskdDate.Text);             

     

     

    da.InsertCommand.Parameters.Add("@Comment", SqlDbType

     

     

    .VarChar).Value = txtComment.Text;

     

    cs.Open();

    da.InsertCommand.ExecuteNonQuery();

    cs.Close();

     

    My Column Properties - Date, in management studio:

    Name - Date, Data Type - date, System Type - date, Primary Key - False, Allow nulls - True, Is Computed - False, Identity - False, Identity seed - 0, Identity increment - 0, Lenght - 3, Numeric Precision - 10, Numeric Scale - 0, Not for replication - False, Full text - false, ANSI Padding Status - False

    So, what do you think?

     

     

     

     

    In my Sql server management studio, the Query "SELECT Comment FROM tblNUMBERS" is giving me the values from table. No problems there. The problem is this: "SELECT Date FROM tblNUMBERS". It is giving me: " An error occurred while executing batch. Error message is: Invalid attempt to GetBytes on column 'Date'. The GetBytes function can only be used on columns of type Text, NText, or Image."

     

     

     

    Saturday, March 26, 2011 11:43 AM
  • Try to convert it as:

    Convert.ToDateTime(mskdDate.Text);
    

    But I woold strongly suggest yout to swap the masked textBox with  the dateTimePicker. The problem with masked textBoxes appears when the user does not insert the correct (or full) date). This cannot happen with dateTimePicker selection.

    Think about it. It will salve your problem in a blink of your eye. 

     

    For getting the value of dateTimePicker you simple do:

     

    dateTimePicker1.Value; (whole date with time); //or:
    dateTimePicker1.Value.Date; (only date with time set to zero);
    
    
    

     

    Mitja

    • Marked as answer by Aspen VJ Sunday, April 03, 2011 2:45 PM
    Saturday, March 26, 2011 12:20 PM
  • thank you, i'll try. Thanks for now.

    JL

    Saturday, March 26, 2011 12:48 PM
  • Hi JL99,

    Does it work for you successfully? I will mark it as answer first, as I think Matja's reply is useful to you. If you still have problem, please unmark it and share your problem in detail here. Thanks for understanding and supporting.


    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Saturday, April 02, 2011 4:10 PM