SQL statement with datetimepicker
This will hopefully be simple. When using a date picker in a window form, I want the SQL statement to execute, for example:
string sql = "SELECT * FROM Jobs WHERE JobDate = '" + dtpJobDate.Text + "'";
Unfortunately this does not actually produce any results because the JobDate field is stored as a DateTime value. I would like to be able to search for all records that are on this date, no matter what time can be stored, any help?
New request:
SqlDataAdapter da2 = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Jobs WHERE JobDate >= @p_StartDate AND JobDate < @p_EndDate";
cmd.Parameters.Add ("@p_StartDate", SqlDbType.DateTime).Value = dtpJobDate.Value.Date;
cmd.Parameters.Add ("@p_EndDate", SqlDbType.DateTime).Value = dtpJobDate.Value.Date.AddDays(1);
cmd.Connection = conn;
da2.SelectCommand = cmd;
da2.Fill(dt);
dgvJobDiary.DataSource = dt;
Thanks so much for all the help!
source to share
Just one answer: use parameterized queries .
This is for various reasons:
- security (no risk SQL Injection
- no longer the same problems for which you open the topic
- performance.
Thus, write your statement like this:
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Jobs WHERE JobDate = @p_Date"
cmd.Parameters.Add ("@p_Date", SqlDbType.DateTime).Value = dtpJobDate.Value;
If you want to ignore time, then I find it best to do a range lookup if the time is stored in the DB. Something like this (SQL query only):
SELECT * FROM Jobs WHERE JobDate >= @p_StartDate AND JobDate < @p_EndDate
StartDate will be then dtpJobDate.Value.Date
and EndDate will bedtpJobDate.Value.Date.AddDays(1)
If the time is not stored in the DB, you can do this:
SELECT * FROM Jobs WHERE JobDate = @p_Date
where the search argument should be dtpJobDate.Value.Date
source to share
Unlike SQL injection in other answers, you can use something like this:
dtpJobDate.Value.ToString("yyyyMMdd HH:mm:ss");
But you probably won't find anything with an exact time match, so you can change your query to something like
string sql = "SELECT * FROM Jobs WHERE JobDate BETWEEN '" + dtpJobDateStart.Value.ToString("yyyyMMdd HH:mm:ss") + "' AND '" + + dtpJobDateEnd.Value.ToString("yyyyMMdd HH:mm:ss") + " + "'";
source to share
First of all - you left the door open for SQL injection in your example.
Also - to answer your question, you need to drop the time in the JobDate column to get a match. Try something like this (SQL Injection code left in the example for comparison) ...
string sql = "SELECT * FROM Jobs WHERE CAST(CONVERT(CHAR(8), JobDate, 112) AS DATETIME) = '" + dtpJobDate.Text + "'";
If you want to parameterize your query - you can do something like this ...
using (var conn = new SqlConnection(myConnectionString))
using (var cmd = new SqlCommand("SELECT * FROM Jobs WHERE JobDate = @JobDate", conn))
{
cmd.Parameters.Add(new SqlParameter("@JobDate", dtpJobDate.Value));
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// your code here to deal with the records...
}
}
}
source to share