Click here to Skip to main content
15,899,754 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi every one, I have datatable named as shifttable. I want to find out Rows by passing datetime parameters but it generate the same error as before that is conversion failed . what I need is how to pass parameter for the datatable. may I gate help please.
here is my code:
DateTime daterange= DateTime.now
DataRow[] foundrows = shifttable.Select("EMPLOYEEID ='" + empid.ToString() + "' and STARTDATE<='" + daterange + "' and ENDDATE>='" + daterange + "'");

in this case ,the value of daterange is 2017-11-23 8:06:34 nm. that is why my code generate error. so I should parametrize my code. any help please

What I have tried:

DateTime daterange= DateTime.now
DataRow[] foundrows = shifttable.Select("EMPLOYEEID ='" + empid.ToString() + "' and STARTDATE<='" + daterange + "' and ENDDATE>='" + daterange + "'");
Posted
Updated 23-Nov-17 22:03pm
Comments
an0ther1 23-Nov-17 17:27pm    
What is the DataType of your StartDate & EndDate columns?
If they are DateTime columns & in the same format your select should work.
If they are not DateTime columns or in a different format you will need to convert the value you are passing in
Use your debugger & look at the DataType of your columns & check the values.

Kind Regards
Tom G/mariam 23-Nov-17 23:08pm    
StartDate & EndDate columns are date time formarmat.
when I trace the value of daterange is 2017-11-23 8:06:34 nm wich is not valid date format. that is why my code generate error. so I should parametrize my code. any help please.
an0ther1 26-Nov-17 15:41pm    
As per below comment - you need to convert your DateTime value to a string in the correct format.
Refer; https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings
Converting the values to parameters will not help - DataTable.Select does not support parameters - refer; https://msdn.microsoft.com/EN-US/library/t5ce3dyt(v=VS.110,d=hv.2).aspx
Use your debugger & the Immediate window to test converting values to the various formats
F-ES Sitecore 24-Nov-17 4:15am    
use

daterange.ToString("yyyy-MM-dd")

to get it into the right format, add the time too if that is important.

1 solution

this is my .cs code

protected void Button_Date(object sender, EventArgs e)
{
if (Page.IsValid)
{
try
{

string dateFrom = DateFrom.Text; // textbox values
string dateto = DateTo.Text;
DateTime FromDt = DateTime.Parse(dateFrom); // parsing in date time
DateTime ToDt = DateTime.Parse(dateto);
using (OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["Connection_string"].ConnectionString))
{
con.Open();
OracleCommand cmd = new OracleCommand("procfordate", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("mycur",
OracleDbType.RefCursor)).Direction = ParameterDirection.Output;
cmd.Parameters.Add(new OracleParameter("dd1", DateFrom)); // sending in ddatabase
cmd.Parameters.Add(new OracleParameter("dd2", DateTo));
OracleDataAdapter Adp = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
Adp.Fill(ds);
con.Close();
return ds;
}
....
}}}

the database query will help u which m sharing below.in the procedure i had given input as the dd1 dd2 as from and to date


create or replace PROCEDURE PROCFORDATE(
MYCUR OUT SYS_REFCURSOR,
DD1 DATE,
DD2 DATE)
IS
BEGIN
OPEN MYCUR FOR
SELECT RECEIVEDDATE, O_FILENAME,
FROM Table_name WHERE RECEIVEDDATE>=DD1 and RECEIVEDDATE <=DD2
or use
-- BETWEEN DD1 and DD2
ORDER BY 1 DESC;
END;
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900