CODEWITHSUNDEEP
c#sql-server
Dil
Dil

Reputation: 417

get selected date from calender and save it to sql server

I want to get selected date from monthCalender1 and retrieve my data from a SQL Server table. But I get exception messages

Can't convert to datetime format

SQL Server table date format is "dd/mm/yyyy"

private void monthCalendar1_DateChanged(object sender, DateRangeEventArgs e)
{
          rtbDocument.Clear();
          SqlConnection cn = null;
          SqlCommand cmd = null;
          SqlDataReader reader = null;

          string date = monthCalendar1.SelectionRange.ToString();

          System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-CA");
          DateTime dt = DateTime.Parse(date); //uses the current Thread's culture
          CultureInfo provider = CultureInfo.InvariantCulture;
          textBox1.Text = date; 

          try
          {
            cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\User\Desktop\myproject_c#\diary\photogallery\RicherTextBox_src\RicherTextBox\diary.mdf;Integrated Security=True;User Instance=True;");
            cn.Open();
            cmd = new SqlCommand("SELECT rtf_file_content FROM rtf WHERE rtf_date=@dt AND user_rtf_id=1", cn);
            cmd.Parameters.AddWithValue("@dt", dt);
            reader = cmd.ExecuteReader();
            reader.Read();
            if (reader.HasRows)
            {
              if (!reader.IsDBNull(0))
              {
                Byte[] rtf = new Byte[Convert.ToInt32((reader.GetBytes(0, 0,
                                                       null, 0, Int32.MaxValue)))];
                long bytesReceived = reader.GetBytes(0, 0, rtf, 0, rtf.Length);
                ASCIIEncoding encoding = new ASCIIEncoding();
                rtbDocument.Rtf = encoding.GetString(rtf, 0, Convert.ToInt32(bytesReceived));

              }
            }
          }
          catch(Exception ex)
          {
            MessageBox.Show(ex.Message);
          }
          finally
          {
            if (null != reader) reader.Close();
            if (null != cn) cn.Close();
          }
        }

Upvotes: 1

Views: 155

Answers (1)

marc_s
marc_s

Reputation: 755451

Don't convert your dates to string all the time (and then two lines down parse it back to a DateTime ....) - stop doing that! Use the DateTime as is ... also: DATETIME in SQL Server has not format - it's an 8-byte binary value - it only gets a formatting when you need to output it as a string (or parse it from a string - but try to avoid that as much as possible!).

Change your code to something like this:

cmd = new SqlCommand("SELECT rtf_file_content FROM rtf WHERE rtf_date=@dt AND user_rtf_id=1", cn);
cmd.Parameters.Add("@dt", SqlDbType.DateTime).Value = monthCalendar1.SelectionStart;

and that's all you need to do - no messing with string formatted date literals or anything like that - just use the dates as they are!

I would also recommend to put your SqlConnection, SqlCommand and SqlDataReader variables into using(..) { ... } blocks to ensure immediate and proper disposal after use, and the handling of the reader is also a bit wonky.....

I would use this code as a whole:

private void monthCalendar1_DateChanged(object sender, DateRangeEventArgs e)
{
    rtbDocument.Clear();

    // set up your connection string (typically from a config) and query text
    string connString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\User\Desktop\myproject_c#\diary\photogallery\RicherTextBox_src\RicherTextBox\diary.mdf;Integrated Security=True;User Instance=True;";
    string query = "SELECT rtf_file_content FROM rtf WHERE rtf_date=@dt AND user_rtf_id=1";

    // set up connection and command - both are disposable, put them in a "using" block
    using (SqlConnection cn = new SqlConnection(connString))
    using (SqlCommand cmd = new SqlCommand(query, conn))
    {
        // set up parameter for query
        // define the parameter to be a "DateTime" parameter
        // set value directly from a "DateTime" property of your "monthCalendar1"
        cmd.Parameters.Add("@dt", SqlDbType.DateTime).Value = monthCalendar1.SelectionStart;

        // open connection, execute reader...
        conn.Open();

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            // loop over reader - reading all rows being returned by query
            while (reader.Read())
            {
                if (!reader.IsDBNull(0))
                {
                     Byte[] rtf = new Byte[Convert.ToInt32((reader.GetBytes(0, 0, null, 0, Int32.MaxValue)))];
                     long bytesReceived = reader.GetBytes(0, 0, rtf, 0, rtf.Length);
                     ASCIIEncoding encoding = new ASCIIEncoding();
                     rtbDocument.Rtf = encoding.GetString(rtf, 0, Convert.ToInt32(bytesReceived));
                }
            }
        }
    }
}

Upvotes: 2

Related Questions