How to use "SELECT" statement on DataTables [modified]

[Verghese]

I want to use the "SELECT " statement on DataTable, which fetches records between these 2 date ranges: StartDate : 01-01-2007 and, EndDate : 01-01-2008 The code is as follows:

if (newPDS.Tables[0].Columns["ADM_DATE"]== null)
{
DataColumn dCol = new DataColumn(newPDS.Tables[0].Columns.Add("ADM_DATE", typeof(DateTime), "").ToString());
}

for(Int32 i = 0; i < newPDS.Tables[0].Rows.Count; i++)
{
row = newPDS.Tables[0].Rows[i];
row["ADM_DATE"] = row["CADMMM"] + "/" + row["CADMDD"] + "/" + row["CADMHH"] + row["CADMYY"];
DateTime newDT = new DateTime();
newDT = Convert.ToDateTime(row["ADM_DATE"]).Date;
row["ADM_DATE"] = newDT.ToShortDateString();
string newQuery = "ADM_DATE between StartDate and EndDate";
newDR = myTable.Select(newQuery);
}

myReport.SetDataSource(newDR);
crystalReportViewer1.ReportSource = myReport;

Thanking you in anticipation.

modified on Tuesday, August 5, 2008 3:33 PM

[Wendelius]

Hi, Try using syntax #m/d/yy# for literal date strings. For example:

string newQuery = "ADM_DATE between #1/1/2007# and #1/1/2008#";

Mika

[Verghese]

Thanks Mika. Just now I learned that, ADO.Net expressions do not support the "BETWEEN" keyword (it's showing an error as : Expression contains unsupported operator"BETWEEN").......so I just break it up into clauses with an "AND" as

"ADM_DATE > StartDate AND ADM_DATE < EndDate"

And when I'm using the above lines, its showing up the database connection popup, where it asks for Server Name, Database Type, LoginId, Password etc. That means that its still looking for the Crystal Reports datasource as DataSet's Table. My previously working Crystal Reports datasource expression was like this :

myReport.SetDataSource(newPDS.Tables["SAMFILE_CLIENTP"]);

where : newPDS - DataSet SAMFILE_CLIENTP - DataSet Table Any idea ???

[Wendelius]

Glad it solved. It's been a while since I last used Crystal but if I remember correctly, try also to use SetDatabaseLogon method and after setting databases and datasources, use VerifyDatabase-method. This way you have full control over datasources and their logon information and you can catch any possible problems while executing VerifyDatabase. Hope this helps, Mika