Author |
Topic |
satyen
Starting Member
21 Posts |
Posted - 2013-02-05 : 10:33:30
|
Hello, I am getting a error message - 'EMSErrorIncorrect systax error near ')'Incorrect systax error near ')'Incorrect systax error near ')'at Line:40, Char:32.'-------------------------------------for the following syntax:Var casecode;beginCaseCode := CaseData.CaseCode;GridForm.Query.SQL.Clear; GridForm.Query.SQL.Add('select c.casecode,'); GridForm.Query.SQL.Add('c.name AS ''Case Name'','); GridForm.Query.SQL.Add('co.name AS ''Creditor'','); GridForm.Query.SQL.Add('co.cref AS ''Reference'','); GridForm.Query.SQL.Add('(select LoginName from ips_casestaff where Role = 30 and nomineetype = 1 and casecode =' +Quotedstr(CaseData.CaseCode)+ ') AS ''Case Administrator'','); GridForm.Query.SQL.Add('(select ct.nominee from ips_casestaff ct where ct.Role = 10 and ct.nomineetype = 1 and c.casecode = ' +QuotedStr(CaseData.CaseCode)+ ') AS ''Trustees Name'','); GridForm.Query.SQL.Add('mee.meetingpurpose AS ''Meeting Purpose'','); GridForm.Query.SQL.Add('c.appdate AS ''Date of appointment'','); GridForm.Query.SQL.Add('(select top(1)no.shortnote from ips_notes no where no.ntype = ''COUNCIL PROGRESS'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ') order by moddatetime) AS ''Progress Report'','); GridForm.Query.SQL.Add('(select top (1)no.shortnote from ips_notes no where no.ntype = ''BKYPROP'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ') order by moddatetime) AS ''Assets & Value'', '); GridForm.Query.SQL.Add('(select top(1)no.shortnote from ips_notes no where no.ntype = ''COUNCIL DISTRIBUTION'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ') order by moddatetime) AS ''Estimated Time of Dividend'','); GridForm.Query.SQL.Add('from ips_case c left join ips_contact co ON c.casecode = co.casecode '); //and inner join ips_casestaff ct ON ct.casecode = co.casecode and left join ips_meeting me ON mee.casecode = c.casecode'); GridForm.Query.SQL.Add('inner join ips_casestaff ct ON ct.casecode = co.casecode'); GridForm.Query.SQL.Add('left join ips_meeting me ON mee.casecode = c.casecode'); GridForm.Query.SQL.Add('where c.casecode =' +Quotedstr(CaseData.CaseCode)+ ''); GridForm.Query.SQL.Add('where co.name LIKE ''%Council%'', '); GridForm.Query.SQL.Add('where ct.Role = 30 '); GridForm.Query.SQL.Add('where ct.nomineetype = 1 '); GridForm.Query.Active := TRUE; GridForm.TableSource.DataSet := GridForm.Query; GridForm.cxGrid1DBTableView1.DataController.DataSource := GridForm.TableSource; GridForm.cxgrid1DBTableView1.DataController.CreateAllItems;end;I would appreciate if anyonne can help me. Much appreciated.Thank you, |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-05 : 10:36:29
|
i thinkGridForm.Query.SQL.Add('where c.casecode =' +Quotedstr(CaseData.CaseCode)+ ''); GridForm.Query.SQL.Add('where co.name LIKE ''%Council%'', ');GridForm.Query.SQL.Add('where ct.Role = 30 '); GridForm.Query.SQL.Add('where ct.nomineetype = 1 '); should beGridForm.Query.SQL.Add('where c.casecode =''' +Quotedstr(CaseData.CaseCode)+ ''''); GridForm.Query.SQL.Add('and co.name LIKE ''%Council%''');GridForm.Query.SQL.Add('and ct.Role = 30 '); GridForm.Query.SQL.Add('and ct.nomineetype = 1 ');------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
satyen
Starting Member
21 Posts |
Posted - 2013-02-05 : 10:43:55
|
I am still getting the same error :( --- any other ideas?'EMSErrorIncorrect systax error near ')'Incorrect systax error near ')'Incorrect systax error near ')'at Line:40, Char:32.' |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-05 : 10:50:05
|
If you are able to add a print or debug statement immediately after you finish constructing the SQL query, do that, print out the statement and post it or look at it. Also, include a few spaces - for example, in the following two statements, put a space before the WHERE on the second line; otherwise, the query looks like "....c.casecodewhere c.casecode".GridForm.Query.SQL.Add('left join ips_meeting me ON mee.casecode = c.casecode');GridForm.Query.SQL.Add('where c.casecode =' +Quotedstr(CaseData.CaseCode)+ ''); |
|
|
satyen
Starting Member
21 Posts |
Posted - 2013-02-05 : 10:55:43
|
Still having no luck :( |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-05 : 10:57:27
|
do a print of gridform.query and post the result------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-05 : 10:59:58
|
Are you able to print out the value in GridForm.Query.SQL just before the statement "GridForm.Query.Active := TRUE;" ? That is the easiest way to figure out the changes you need to make. Post that string to the forum and someone would be able to tell you the exact changes you need to make. Without that, it is a painful and slow process. |
|
|
satyen
Starting Member
21 Posts |
Posted - 2013-02-05 : 11:09:19
|
Nope I am not able to print out the stringI have changed the syntax But I am getting the following error now - EMS Error Incorrect sytax near the keyword 'from'. at Line: 40, Char 32Syntax:Var casecode;beginCaseCode := CaseData.CaseCode;GridForm.Query.SQL.Clear; GridForm.Query.SQL.Add('select c.casecode,'); GridForm.Query.SQL.Add('c.name AS ''Case Name'','); GridForm.Query.SQL.Add('co.name AS ''Creditor'','); GridForm.Query.SQL.Add('co.cref AS ''Reference'','); GridForm.Query.SQL.Add('(select LoginName from ips_casestaff where Role = 30 and nomineetype = 1 and casecode =' +Quotedstr(CaseData.CaseCode)+ ') AS ''Case Administrator'','); GridForm.Query.SQL.Add('(select ct.nominee from ips_casestaff ct where ct.Role = 10 and ct.nomineetype = 1 and c.casecode = ' +QuotedStr(CaseData.CaseCode)+ ') AS ''Trustees Name'','); GridForm.Query.SQL.Add('mee.meetingpurpose AS ''Meeting Purpose'','); GridForm.Query.SQL.Add('c.appdate AS ''Date of appointment'','); GridForm.Query.SQL.Add('(select top(1)no.shortnote from ips_notes no where no.ntype = ''COUNCIL PROGRESS'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ' order by moddatetime) AS ''Progress Report'','); GridForm.Query.SQL.Add('(select top (1)no.shortnote from ips_notes no where no.ntype = ''BKYPROP'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ' order by moddatetime) AS ''Assets & Value'', '); GridForm.Query.SQL.Add('(select top(1)no.shortnote from ips_notes no where no.ntype = ''COUNCIL DISTRIBUTION'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ' order by moddatetime) AS ''Estimated Time of Dividend'','); GridForm.Query.SQL.Add('from ips_case c left join ips_contact co ON c.casecode = co.casecode '); //and inner join ips_casestaff ct ON ct.casecode = co.casecode and left join ips_meeting me ON mee.casecode = c.casecode'); GridForm.Query.SQL.Add('inner join ips_casestaff ct ON ct.casecode = co.casecode'); GridForm.Query.SQL.Add('left join ips_meeting me ON mee.casecode = c.casecode'); GridForm.Query.SQL.Add('where c.casecode =' +Quotedstr(CaseData.CaseCode)+ '' ); GridForm.Query.SQL.Add('and co.name LIKE ''%Council%'', ' ); GridForm.Query.SQL.Add('and ct.Role = 30 ' ); GridForm.Query.SQL.Add('and ct.nomineetype = 1 ' ); GridForm.Query.Active := TRUE; GridForm.TableSource.DataSet := GridForm.Query; GridForm.cxGrid1DBTableView1.DataController.DataSource := GridForm.TableSource; GridForm.cxgrid1DBTableView1.DataController.CreateAllItems;end; |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-05 : 12:40:22
|
I thought I will go through the code and make the corrections, but there are a number of things that I don't know about your environment such as,are string literals created using single quotes, does Quotedstr function return the result with single quotes etc. Syntax errors aside, it is also hard to see the logic. In any case, here is my attempt - it would most likely give you syntax errors when sent to the server.What is the environment you are using to develop the code? Surprising that there are IDE's that do not let you print out the value of strings when developing:beginCaseCode := CaseData.CaseCode;GridForm.Query.SQL.Clear;GridForm.Query.SQL.Add('select c.casecode,');GridForm.Query.SQL.Add('c.name AS "Case Name",');GridForm.Query.SQL.Add('co.name AS "Creditor",');GridForm.Query.SQL.Add('co.cref AS "Reference",');GridForm.Query.SQL.Add('(select LoginName from ips_casestaff where Role = 30 and nomineetype = 1 and casecode =' +Quotedstr(CaseData.CaseCode)+ ') AS "Case Administrator",');GridForm.Query.SQL.Add('(select ct.nominee from ips_casestaff ct where ct.Role = 10 and ct.nomineetype = 1 and c.casecode = ' +QuotedStr(CaseData.CaseCode)+ ') AS "Trustees Name",');GridForm.Query.SQL.Add('mee.meetingpurpose AS "Meeting Purpose",');GridForm.Query.SQL.Add('c.appdate AS "Date of appointment",');GridForm.Query.SQL.Add('(select top(1)no.shortnote from ips_notes no where no.ntype = ''COUNCIL PROGRESS'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ' order by moddatetime) AS "Progress Report",');GridForm.Query.SQL.Add('(select top (1)no.shortnote from ips_notes no where no.ntype = ''BKYPROP'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ' order by moddatetime) AS "Assets & Value", ');GridForm.Query.SQL.Add('(select top(1)no.shortnote from ips_notes no where no.ntype = ''COUNCIL DISTRIBUTION'' and c.casecode = ' + QuotedStr(CaseData.CaseCode)+ ' order by moddatetime) AS "Estimated Time of Dividend" ');GridForm.Query.SQL.Add('from ips_case c left join ips_contact co ON c.casecode = co.casecode //and inner join ips_casestaff ct ON ct.casecode = co.casecode and left join ips_meeting me ON mee.casecode = c.casecode');GridForm.Query.SQL.Add('inner join ips_casestaff ct ON ct.casecode = co.casecode ');GridForm.Query.SQL.Add('left join ips_meeting me ON mee.casecode = c.casecode ');GridForm.Query.SQL.Add('where c.casecode =' +Quotedstr(CaseData.CaseCode)+ ' ' );GridForm.Query.SQL.Add('and co.name LIKE ''%Council%'' ' );GridForm.Query.SQL.Add('and ct.Role = 30 ' );GridForm.Query.SQL.Add('and ct.nomineetype = 1 ' ); GridForm.Query.Active := TRUE;GridForm.TableSource.DataSet := GridForm.Query;GridForm.cxGrid1DBTableView1.DataController.DataSource := GridForm.TableSource;GridForm.cxgrid1DBTableView1.DataController.CreateAllItems;end; |
|
|
satyen
Starting Member
21 Posts |
Posted - 2013-02-06 : 05:33:22
|
I thought I will go through the code and make the corrections, but there are a number of things that I don't know about your environment such as,are string literals created using single quotes = No with double quotesdoes Quotedstr function return the result with single quotes etc = This script is run under a Database which contains a collections of records each having unique codes = casecode. So e.g. lets say I go into the record > casecode (PK) - 1234 - I run the script. It will read and understand the casecode and populate the fields from the record data in a set requirement.Syntax errors aside, it is also hard to see the logic.In any case, here is my attempt - it would most likely give you syntax errors when sent to the server.What is the environment you are using to develop the code? Surprising that there are IDE's that do not let you print out the value of strings when developing: Notepad. There are no IDE's |
|
|
satyen
Starting Member
21 Posts |
Posted - 2013-02-06 : 05:36:23
|
Answers I thought I will go through the code and make the corrections, but there are a number of things that I don't know about your environment such as,are string literals created using single quotes = No, with double quotesdoes Quotedstr function return the result with single quotes etc = This script is run under a Database which contains a collections of records each having unique codes = casecode. So e.g. lets say I go into the record > casecode (PK) - 1234 - I run the script. It will read and understand the casecode and populate the fields from the record data in a set requirement.Syntax errors aside, it is also hard to see the logic.In any case, here is my attempt - it would most likely give you syntax errors when sent to the server.What is the environment you are using to develop the code? Surprising that there are IDE's that do not let you print out the value of strings when developing: Notepad. There are no IDE's |
|
|
|