Thanks Bob.

didnt u see tht icon next to ur name..........seems u r in deep s*** man.........!!! u r crude uncouth ill-bred person lacking culture or refinement and we prefer calling thm as BARBARIANS.

i dont know wht the heck u r blabbering..........take a day off from u work and relax.........u will feel better........!!!

led mike wrote:

So for the last time (today), f*** off a**hole.

why making it the last time, say it over and over..........coz thts wht ur parents taught u..........say it very proudly.........!!!

very bad Led......i'm surprised who ur employer is...........

Please stop it.

modified on Tuesday, December 9, 2008 3:36 PM

as mentioned, the database is on AS/400 Mainframe server. without odbc how can i chk the query. can u guide me on how to proceed with this?

look dear, if u can put in some valuable comments, pls go ahead, otherwise dont try to rule anybody, u get tht.

without the last piece of code [SAMFILE.MOVESP.MCODE = A] also, this query works but it gives an incorrect result. and the only code which can fetch the true values wud be if we include the MCODE code.

cool down bro........!!! reg. the erro msg : whn i add this piece of code in the end of the SQL string (SAMFILE.MOVESP.MCODE = ""A""), the error message is : ERROR [42S22] [IBM][iSeries ACCESS ODBC Driver][DB2 UDB]SQL0206 - Column A not in specified tables. and whn I change it to : SAMFILE.MOVESP.MCODE = 'A', it takes a long time to process the webpage and after a prolonged period of time, shows the "Page cannot be displayed" error message.

I'm working on an ASP.Net application accessing couple of database tables from AS400 mainframe server database. In the code there's an SQL Query, which seems very complicated to me, the person who did the actual coding is no more with our firm. Right now the query is producing some kinda result which is wrong. So can anyone please help me out in finding what's wrong with the SQL query? The SQL query is attached below:

Me.OdbcSelectCommand2.CommandText = "SELECT SAMFILE.STAYP.SCLINT, SAMFILE.STAYP.SADMHH, SAMFILE.STAYP.SADMYY, SAMFILE.STAYP.SADMMM, SAMFILE.STAYP.SBLDG, SAMFILE.STAYP.SADMDD, SAMFILE.STAYP.STRMCD, SAMFILE.STAYP.STRMHH, SAMFILE.STAYP.STRMYY, SAMFILE.STAYP.STRMMM, SAMFILE.STAYP.STRMDD, SAMFILE.CLIENTP.CNAME, SAMFILE.CLIENTP.CRACE1, SAMFILE.CLIENTP.CRACE2, SAMFILE.CLIENTP.CRELIG, SAMFILE.CLIENTP.CSEX, SAMFILE.CLIENTP.CBTHHH, SAMFILE.CLIENTP.CBTHYY, SAMFILE.CLIENTP.CBTHMM, SAMFILE.CLIENTP.CBTHDD, SAMFILE.REFSRP.REFSDS, SAMFILE.STAYP.SMDPM, SAMFILE.STAYP.SREFRL, SAMFILE.STAYP.SADRG1, SAMFILE.STAYP.SADRG2, SAMFILE.RACETP.RACEDS, SAMFILE.RELIGP.RELGDS, SAMFILE.STAYP.SADMAG FROM SAMFILE.REFSRP, SAMFILE.RELIGP, SAMFILE.RACETP, { oj SAMFILE.STAYP LEFT OUTER "JOIN SAMFILE.CLIENTP ON SAMFILE.STAYP.SCLINT = SAMFILE.CLIENTP.""CLNT#""} WHERE SAMFILE.REFSRP.REFSCD = SAMFILE.STAYP.SREFRL AND SAMFILE.RELIGP.RELGCD = SAMFILE.CLIENTP.CRELIG AND SAMFILE.RACETP.RACECD = SAMFILE.CLIENTP.CRACE1" // After some loop conditions, the OdbcSelectCommand2 is used in 4 concatenations like this. OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " AND (({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT(digits(SADMHH), digits(SADMYY)) }, '-') }, digits(SADMMM)) }, '-') }, digits(SADMDD)) } <= { d '" & End_date_conv & "' })"OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " AND ({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT(digits(STRMHH), digits(STRMYY)) }, '-') }, digits(STRMMM)) }, '-') }, digits(STRMDD)) } >= { d '" & Start_date_conv & "' })"OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " OR ({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT(digits(SADMHH), digits(SADMYY)) }, '-') }, digits(SADMMM)) }, '-') }, digits(SADMDD)) } <= { d '" & End_date_conv & "' })"OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " AND (SAMFILE.STAYP.STRMCD = 0))" // To get the desired result, one more table is suppossed to be u

I'm working on an ASP.Net application accessing couple of database tables from AS400 mainframe server database. In the code there's an SQL Query, which seems very complicated to me, the person who did the actual coding is no more with our firm. Right now the query is producing some kinda result which is wrong. So can anyone please help me out in finding what's wrong with the SQL query? The SQL query is attached below:

Me.OdbcSelectCommand2.CommandText =

"SELECT

SAMFILE.STAYP.SCLINT,
SAMFILE.STAYP.SADMHH,
SAMFILE.STAYP.SADMYY,
SAMFILE.STAYP.SADMMM,
SAMFILE.STAYP.SBLDG,
SAMFILE.STAYP.SADMDD,
SAMFILE.STAYP.STRMCD,
SAMFILE.STAYP.STRMHH,
SAMFILE.STAYP.STRMYY,
SAMFILE.STAYP.STRMMM,
SAMFILE.STAYP.STRMDD,
SAMFILE.CLIENTP.CNAME,
SAMFILE.CLIENTP.CRACE1,
SAMFILE.CLIENTP.CRACE2,
SAMFILE.CLIENTP.CRELIG,
SAMFILE.CLIENTP.CSEX,
SAMFILE.CLIENTP.CBTHHH,
SAMFILE.CLIENTP.CBTHYY,
SAMFILE.CLIENTP.CBTHMM,
SAMFILE.CLIENTP.CBTHDD,
SAMFILE.REFSRP.REFSDS,
SAMFILE.STAYP.SMDPM,
SAMFILE.STAYP.SREFRL,
SAMFILE.STAYP.SADRG1,
SAMFILE.STAYP.SADRG2,
SAMFILE.RACETP.RACEDS,
SAMFILE.RELIGP.RELGDS,
SAMFILE.STAYP.SADMAG

FROM

SAMFILE.REFSRP,
SAMFILE.RELIGP,
SAMFILE.RACETP,
{
oj SAMFILE.STAYP
LEFT OUTER "JOIN SAMFILE.CLIENTP
ON SAMFILE.STAYP.SCLINT = SAMFILE.CLIENTP.""CLNT#""
}

WHERE

SAMFILE.REFSRP.REFSCD = SAMFILE.STAYP.SREFRL
AND
SAMFILE.RELIGP.RELGCD = SAMFILE.CLIENTP.CRELIG
AND
SAMFILE.RACETP.RACECD = SAMFILE.CLIENTP.CRACE1"

// After some loop conditions, the OdbcSelectCommand2 is used in 4 concatenations like this.

OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " AND (({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT(digits(SADMHH), digits(SADMYY)) }, '-') }, digits(SADMMM)) }, '-') }, digits(SADMDD)) } <= { d '" & End_date_conv & "' })"
OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " AND ({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT(digits(STRMHH), digits(STRMYY)) }, '-') }, digits(STRMMM)) }, '-') }, digits(STRMDD)) } >= { d '" & Start_date_conv & "' })"
OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " OR ({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT(digits(SADMHH), digits(SADMYY)) }, '-') }, digits(SADMMM)) }, '-') }, digits(SADMDD)) } <= { d '" & End_date_conv & "' })"
OdbcSelectCommand2.CommandText = OdbcSelectCommand2.Comm

u mean to say, no one ever came across this hurdle........... on my way to track it down, let's see if someone can point me to the right direction..............

i dont know why everyone stopped responding in this community........... pls see the link attached : http://www.codeproject.com/script/Forums/View.aspx?fid=12076&select=2718020&fr=51&df=90&mpp=25&noise=3&sort=Position&view=Quick#xx0xx if anyone gotta clue abt it, pls respond...........TIA

I have an ASP.NET applications (using VB.Net code) having 2 webforms and it loads couple of Crystal Reports each time depending upon selections. my execution path is as follows : http://localhost/webApps/PIN/WebForm2.aspx (it loads this page correctly) "WebForm2.aspx" is having some selection criterias for the report to be loaded. But when this page loads I get an CrystalReportViewer message on top left corner of the page which is as follows: CrystalReportViewer - CrystalReportViewer1 Use ReportSource or DataBindings property to specify a report source. Also, in "WebForm1.aspx", I've got the following - OdbcSelectCommand, OdbcDataAdapter, OdbcConnection. And when I click on the "Command Text" property of "OdbcSelectCommand" which is infact an SQL query, I get this error messsage too : " [S1000] [IBM] [Client Access Express ODBC Driver (32-bit)] Syntax error in Connection String" This is the connection string that I'm using :

Me.OdbcConnection1.ConnectionString = "DSN=as400;UID=pcain;PWD=marugo"

Please see the code below to see how Crystal Report is loaded :

Protected WithEvents active_referral1 As Crystal_dataset.active_referral
Protected WithEvents As400ds1 As crystal_dataset.as400ds

Me.OdbcSelectCommand1.Connection = Me.OdbcConnection1
Me.OdbcConnection1.ConnectionString = "DSN=as400;UID=pcain;PWD=marugo"

OdbcDataAdapter1.Fill(As400ds1)
If Report_Type = 3 Then
active_referral1.SetDataSource(As400ds1)
active_referral1.DataDefinition.FormulaFields("unboundstring3").Text = "'" &; Fac_Str &; "'"
active_referral1.DataDefinition.FormulaFields("unboundstring4").Text = "'" &; Race_Str &; "'"
active_referral1.DataDefinition.FormulaFields("unboundstring5").Text = "'" &; Rel_Str &; "'"
active_referral1.DataDefinition.FormulaFields("unboundstring6").Text = "'" &; Sex_Str &; "'"
active_referral1.DataDefinition.FormulaFields("unboundstring7").Text = "'" &; Ref_Str &; "'"
active_referral1.DataDefinition.FormulaFields("unboundstring8").Text = "'" &; Sub_Str &; "'"
active_referral1.DataDefinition.FormulaFields("unboundstring9").Text = "'" &; Sub2_Str &; "'"

If View_List = 1 Then
CrystalReportViewer1.ReportSource = active_referral1
ElseIf View_List = 2 Then
MyExportOptions.ExportFormatType = CrystalDecisions.[Shared].ExportFormatType.PortableDocFormat
MyExportRequestContext.ExportInfo = MyExportOptions
MyStream = active_referral1.FormatEngine.Expo

Thanks Alan, but that doesn't gives me solution to my problem. If the date is invalid, i want the row entry to be message like this : "Invalid Date" what shud be done in order to achieve this?

it's showing the compile error : 'System.DateTime' does not contain a definition for 'TryParse' do i need to add any other namespaces or references.

I'm facing some issues with the date thing: While debugging, I can see that the value of stringDate is coming as "3/60/1989" (MM/DD/YYYY), which is practically not possible, but these value is coming from the database as it is wrongly entered there. Now when this stringDate is parsed into DateTime format, it gives me a different date (i.e. the value of dtDate is shown as "6/30/1989") and throws an exception "Invalid DateTime Entry" at this line:

dtDate = DateTime.Parse(stringDate);

if (dtDate > startDate && dtDate < endDate)
{
row["NEW_DATE_FIELD"] = stringDate;
myTable.ImportRow(row);
}

So, now I want that my code to do the following: To enter a "INVALID DATE" message to the row["NEW_DATE_FIELD"] instead of the wrong date as such Note : In VB.NET we have a built-in method named **IsDate()**, which receives a string as parameter and return True if the string is a date or false. Is there any short-cut method in C# equivalent to IsDate method ? Any help would be appreciated.

I totally agree with what you guys are saying, but please let me know whether I can use the SELECT statement on DataSet the same way as we use on Database tables coz I don't have the datefield in database table, its only the newly created DataSet which is holding the DateField. Let's say, the code goes like this :

DataColumn dCol = new DataColumn(newPDS.Tables[0].Columns.Add("ADM_DATE", typeof(String)).ToString());
for(Int32 i = 0; i < newPDS.Tables[0].Rows.Count; i++)
{
DataRow row = newPDS.Tables[0].Rows[i];

row\["ADM\_DATE"\] = row\["CADMMM"\].ToString() + "/" + row\["CADMDD"\].ToString() + "/" + row\["CADMHH"\].ToString() + row\["CADMYY"\].ToString();

}

Now its the newPDS DataSet, which is having the DateField. So, now please tell me a way to apply the date range thing within this piece of code. (Date range from Form is entered in 2 Textboxes as START_DATE and END_DATE in the format of DD/MM/YYYY. THANKS

Luc, that's the way they do it in mainframe server. But the thing is that I'm looking for something which could actually help me in doing the date range comparison at the code level. Normally we do it at the database level but since my database doesn't have a date field, I've to do it at the code level. I'm saying so, coz my code concatenates the 4 different fields into one field and display it as date field on crystal report. So if there are chances of any comparison for date range then it has to be done at code level only coz that's the place where its actually shown up as date field. Did i confused you...........???? To be more clear, I want the date range comparison to be done exactly after the code where its concatenating the fields.............and the code for the same is as given below:

for(Int32 i = 0; i < newPDS.Tables[0].Rows.Count; i++)
{
DataRow row = newPDS.Tables[0].Rows[i];

row\["ADM\_DATE"\] = row\["CADMMM"\].ToString() + "/" + row\["CADMDD"\].ToString() + "/" + row\["CADMHH"\].ToString() + row\["CADMYY"\].ToString();            

}

Luc, there is nothing much I can do on the database coz it resides on the AS400 IBM iSeries server and that's the way how dates are stored in mainframe systems. Then regarding the "looking like date field", its infact the the date itself which is stored in a string field and if I'm not wrong that can be converted into integer or date field..........that's wht i think...........am i right............??? and moreover, i need help on the logical part of how to proceed on it.........