1

I am a beginner at C# .net and my question is about creating a program in C# which shows the result from a small database for each date selected from month calendar. For example I choose 25/5/2013 and it shows in a richtextbox appointment with john smith.

I get the date with 

this.richTextBox1.Text = monthCalendar1.SelectionRange.Start.Date.ToShortDateString();

but when I try to show the row I want, the program crashes. I used this code to show it in datagrid

SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=event_agenda;Integrated Security=True");
DataTable dt = new DataTable();
SqlDataAdapter SDA = new SqlDataAdapter("Select * from event where date_event =" + monthCalendar1.SelectionRange.Start.Date.ToShortDateString(), conn);
SDA.Fill(dt);

dataGridView1.DataSource = dt;

it doesn't show anything

But with this it shows all rows in database without problem

SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=event_agenda;Integrated Security=True");
DataTable dt = new DataTable();
SqlDataAdapter SDA = new SqlDataAdapter("Select * from event", conn);
SDA.Fill(dt);

dataGridView1.DataSource = dt;

My database has a table event and two columns: date_event (varchar(9)) and notes (varchar(150))

Where is my mistake? I would appreciate if someone helps me

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
v kon
  • 21
  • 3
  • Pragram crashes or don't show anything? And if it crashes what is the error message? – Steve May 25 '13 at 09:37
  • the program crashes and it highlights SDA.Fill(dt); line and in call stack it shows this > e-desk.exe!desk.Form1.button5_Click(object sender, System.EventArgs e) Line 112 C# and this e-desk.exe!desk.Program.Main() Line 18 + 0x1d bytes C# – v kon May 25 '13 at 09:51
  • You need to pass the parameter in the correct date format. What is the date format you store in the `date_event varchar(9)` column? – Alex Filipovici May 25 '13 at 09:52
  • how can i do it? i tried i with date_event as date type in sql, but it was really different from monthcalendar selected date, example 2013/5/25 and 25/5/2013 – v kon May 25 '13 at 09:54
  • [Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) - you should always use the most appropriate data type - that's what they're there for, after all! So if you have a column storing a date - **make it a `DATE` or `DATETIME`** and don't store dates in string columns! – marc_s May 25 '13 at 10:05
  • +1 for the nicely structured question. – Alex Filipovici May 25 '13 at 10:12

2 Answers2

1

First thing. You should never represents dates with strings in database.
So I really suggest you to change that field to a DateTime column

Second, your query fails because you pass a string without using quotes around it

Let me show how to do it correctly (after changing your column to a datetime)

DateTime mDate = monthCalendar1.SelectionRange.Start.Date;
SqlDataAdapter SDA = new SqlDataAdapter("Select * from event where date_event=@dt", conn);
SDA.SelectCommand.Parameters.AddWithValue("@dt", mDate);
SDA.Fill(dt);

This code use a parameterized query. The placeholder @dt in the query text will be handled by the Framework code using the value assigned to the parameter named @dt.
This parameter is added to the parameter collection of the SelectCommand used by the SqlDataAdapter to retrieve your data.

In this way you don't have to worry how to represent (format as a string) a date and you avoid the risk of Sql Injection

If you really insist in using a string to represent a date (a very bad practice that will do nothing good to your program in the long run) you could simply add, as value, the string representation of the date extracted by your calendar.

Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286
0

I suggest you should change the table column to date. Then, in your code, you may format the date as follows:

var d = monthCalendar1.SelectionRange.Start.Date;
var formattedDate = d.ToString("yyyyMMdd");

You query would look like:

SqlDataAdapter SDA = 
    new SqlDataAdapter("Select * from event where date_event ='" + formattedDate + "'", conn);
Alex Filipovici
  • 31,789
  • 6
  • 54
  • 78
  • 1
    Why would you not use parameters like Steve suggests? That way you don't have to bother with formats of the date, just pass the date to the parameter and let that code handle it. – Patrick May 25 '13 at 11:19
  • @Patrick, I agree that Steve's answer is the way to go. My answer was posted before his and I wanted to clarify the OP where the problem lies. Actually, I upvoted Steve :) – Alex Filipovici May 25 '13 at 15:48