SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help on SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

satyen
Starting Member

21 Posts

Posted - 02/05/2013 :  10:33:30  Show Profile  Reply with Quote
Hello, I am getting a error message -

'EMSError
Incorrect systax error near ')'
Incorrect systax error near ')'
Incorrect systax error near ')'
at Line:40, Char:32.'

-------------------------------------

for the following syntax:


Var
casecode;

begin

CaseCode := 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

India
52325 Posts

Posted - 02/05/2013 :  10:36:29  Show Profile  Reply with Quote
i think

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 ');


should be

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 ');

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

satyen
Starting Member

21 Posts

Posted - 02/05/2013 :  10:43:55  Show Profile  Reply with Quote
I am still getting the same error :( --- any other ideas?

'EMSError
Incorrect systax error near ')'
Incorrect systax error near ')'
Incorrect systax error near ')'
at Line:40, Char:32.'
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 02/05/2013 :  10:50:05  Show Profile  Reply with Quote
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)+ '');
Go to Top of Page

satyen
Starting Member

21 Posts

Posted - 02/05/2013 :  10:55:43  Show Profile  Reply with Quote
Still having no luck :(
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/05/2013 :  10:57:27  Show Profile  Reply with Quote
do a print of gridform.query and post the result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 02/05/2013 :  10:59:58  Show Profile  Reply with Quote
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.
Go to Top of Page

satyen
Starting Member

21 Posts

Posted - 02/05/2013 :  11:09:19  Show Profile  Reply with Quote
Nope I am not able to print out the string

I have changed the syntax But I am getting the following error now -

EMS Error
Incorrect sytax near the keyword 'from'. at Line: 40, Char 32

Syntax:

Var
casecode;

begin

CaseCode := 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;


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 02/05/2013 :  12:40:22  Show Profile  Reply with Quote
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:
begin

CaseCode := 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;
Go to Top of Page

satyen
Starting Member

21 Posts

Posted - 02/06/2013 :  05:33:22  Show Profile  Reply with Quote
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 quotes
does 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
Go to Top of Page

satyen
Starting Member

21 Posts

Posted - 02/06/2013 :  05:36:23  Show Profile  Reply with Quote
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 quotes

does 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000