Problem in DateTime conversion!! Please help
-
Hello, I am having problem in running query for the following code: Actually My database in in MS Access and I need to compare the date column (which is in Date format in the database) with the value of the Date textbox (which is a string). So maybe I need to convert the string to date format and then compare......should I convert to date format? My date is stored in the database as dd-mmm-yy. For example it's stored as 31-Aug-09. Please assist:
public void executePositiveListQuery_ANY(string selected_Date)
{
string sql;
if(selected_Date=="empty")
sql= "SELECT DISTINCT re_MLNO FROM ml_hiv_status WHERE (re_HIV1_Status=1 AND re_HIV2_Status=1)";
else
{
string selectedDate = (Convert.ToDateTime(selected_Date)).ToShortDateStr ing();// I am having problem here
//DateTime DD= Convert.ToDateTime(selected_Date);
sql= "SELECT re_MLNO FROM ml_hiv_status WHERE ( re_SpecimenDate='" + selectedDate + "' AND re_HIV1_Status=1 AND re_HIV2_Status=1)";
resultsLabel.Text ="You Have selected"+ selectedDate;
}try
{
OleDbConnection connection = new OleDbConnection(HIV.Database.DataConstants.CONNECT ION_STRING);
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(sql, connection);DataSet ds = new DataSet();
adapter.SelectCommand = command;int count = adapter.Fill(ds);
if (count > 0 && count <= 200)
{
DataView view = ds.Tables[0].DefaultView;
//view.Sort=e.SortExpression;
resultsDatagrid.DataSource = view;
resultsDatagrid.DataBind();
resultsDatagrid.Visible = true;
resultsLabel.Text = count.ToString() + " results found.";
resultsLabel.Visible = true;
exportLinkbutton.Visible = true;
}
else if (count> 200)
{
DataView view = ds.Tables[0].DefaultView;
//view.Sort=e.SortExpression;
resultsDatagrid.DataSource = view;
resultsDatagrid.DataBind();
resultsDatagrid.Visible = false;
resultsLabel.Text = "More than 200 results found. Please Click the Export to Excel Link to Download the Results.";
resultsLabel.Visible = true;
exportLinkbutton.Visible = true;
}
else
{
resultsLabel.Text = "No results found.";
resultsLabel.Visible = true;
exportLinkbutton.Visible = false;
resultsDatagrid.Visible = false;
}
connection.Close();
}
catch(Exception ex)
{
message.InnerHtml = "SQL: " + sql + "<p></p>" + ex.ToString(); //Now it throws exception here!!
} -
Hello, I am having problem in running query for the following code: Actually My database in in MS Access and I need to compare the date column (which is in Date format in the database) with the value of the Date textbox (which is a string). So maybe I need to convert the string to date format and then compare......should I convert to date format? My date is stored in the database as dd-mmm-yy. For example it's stored as 31-Aug-09. Please assist:
public void executePositiveListQuery_ANY(string selected_Date)
{
string sql;
if(selected_Date=="empty")
sql= "SELECT DISTINCT re_MLNO FROM ml_hiv_status WHERE (re_HIV1_Status=1 AND re_HIV2_Status=1)";
else
{
string selectedDate = (Convert.ToDateTime(selected_Date)).ToShortDateStr ing();// I am having problem here
//DateTime DD= Convert.ToDateTime(selected_Date);
sql= "SELECT re_MLNO FROM ml_hiv_status WHERE ( re_SpecimenDate='" + selectedDate + "' AND re_HIV1_Status=1 AND re_HIV2_Status=1)";
resultsLabel.Text ="You Have selected"+ selectedDate;
}try
{
OleDbConnection connection = new OleDbConnection(HIV.Database.DataConstants.CONNECT ION_STRING);
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(sql, connection);DataSet ds = new DataSet();
adapter.SelectCommand = command;int count = adapter.Fill(ds);
if (count > 0 && count <= 200)
{
DataView view = ds.Tables[0].DefaultView;
//view.Sort=e.SortExpression;
resultsDatagrid.DataSource = view;
resultsDatagrid.DataBind();
resultsDatagrid.Visible = true;
resultsLabel.Text = count.ToString() + " results found.";
resultsLabel.Visible = true;
exportLinkbutton.Visible = true;
}
else if (count> 200)
{
DataView view = ds.Tables[0].DefaultView;
//view.Sort=e.SortExpression;
resultsDatagrid.DataSource = view;
resultsDatagrid.DataBind();
resultsDatagrid.Visible = false;
resultsLabel.Text = "More than 200 results found. Please Click the Export to Excel Link to Download the Results.";
resultsLabel.Visible = true;
exportLinkbutton.Visible = true;
}
else
{
resultsLabel.Text = "No results found.";
resultsLabel.Visible = true;
exportLinkbutton.Visible = false;
resultsDatagrid.Visible = false;
}
connection.Close();
}
catch(Exception ex)
{
message.InnerHtml = "SQL: " + sql + "<p></p>" + ex.ToString(); //Now it throws exception here!!
}The best thing to do would be to parameterise the query so the conversion work is done for you. Failing that, converting the date format in your query string to either yyyy-mm-dd or dd mmm yyyy should do the trick.
-
The best thing to do would be to parameterise the query so the conversion work is done for you. Failing that, converting the date format in your query string to either yyyy-mm-dd or dd mmm yyyy should do the trick.
-
Use SQL Parameters to Overcome Ad Hoc Performance Issues[^]
Jeremy Likness Latest Article: Whats in Your Collection? Part 1 of 3: Interfaces Blog: C#er : IMage
-
Hello, I am having problem in running query for the following code: Actually My database in in MS Access and I need to compare the date column (which is in Date format in the database) with the value of the Date textbox (which is a string). So maybe I need to convert the string to date format and then compare......should I convert to date format? My date is stored in the database as dd-mmm-yy. For example it's stored as 31-Aug-09. Please assist:
public void executePositiveListQuery_ANY(string selected_Date)
{
string sql;
if(selected_Date=="empty")
sql= "SELECT DISTINCT re_MLNO FROM ml_hiv_status WHERE (re_HIV1_Status=1 AND re_HIV2_Status=1)";
else
{
string selectedDate = (Convert.ToDateTime(selected_Date)).ToShortDateStr ing();// I am having problem here
//DateTime DD= Convert.ToDateTime(selected_Date);
sql= "SELECT re_MLNO FROM ml_hiv_status WHERE ( re_SpecimenDate='" + selectedDate + "' AND re_HIV1_Status=1 AND re_HIV2_Status=1)";
resultsLabel.Text ="You Have selected"+ selectedDate;
}try
{
OleDbConnection connection = new OleDbConnection(HIV.Database.DataConstants.CONNECT ION_STRING);
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(sql, connection);DataSet ds = new DataSet();
adapter.SelectCommand = command;int count = adapter.Fill(ds);
if (count > 0 && count <= 200)
{
DataView view = ds.Tables[0].DefaultView;
//view.Sort=e.SortExpression;
resultsDatagrid.DataSource = view;
resultsDatagrid.DataBind();
resultsDatagrid.Visible = true;
resultsLabel.Text = count.ToString() + " results found.";
resultsLabel.Visible = true;
exportLinkbutton.Visible = true;
}
else if (count> 200)
{
DataView view = ds.Tables[0].DefaultView;
//view.Sort=e.SortExpression;
resultsDatagrid.DataSource = view;
resultsDatagrid.DataBind();
resultsDatagrid.Visible = false;
resultsLabel.Text = "More than 200 results found. Please Click the Export to Excel Link to Download the Results.";
resultsLabel.Visible = true;
exportLinkbutton.Visible = true;
}
else
{
resultsLabel.Text = "No results found.";
resultsLabel.Visible = true;
exportLinkbutton.Visible = false;
resultsDatagrid.Visible = false;
}
connection.Close();
}
catch(Exception ex)
{
message.InnerHtml = "SQL: " + sql + "<p></p>" + ex.ToString(); //Now it throws exception here!!
}OledbParamenter @yourtime