Devart Forums

I have an ORAQuery that I'd like to reuse and submit different params each but keep the same SELECT field list. When I clear the SQL and build a new query it works until I try to put values to the new params as it doesn't recognize. Doing a ORAQuery.Refresh doesn't work. How do I reinitialize the params list? Here's what I'm currently doing:

DM.StudentsQry.Close;
DM.StudentsQry.SQL.Clear;
DM.StudentsQry.SQL.Add('select s.ID, s.LASTFIRST, s.First_Name, s.Last_Name, s.Student_Number,' +
's.DOB, s.GRADE_LEVEL, s.MAILING_STREET, s.MAILING_CITY, s.MAILING_STATE, s.MAILING_ZIP, s.HOME_PHONE' +
'from students s' +
'join cc c on s.schoolid = c.schoolid and s.id = c.studentid and c.termid >= 2500' +
'join teachers pt on c.teacherid = pt.id and c.schoolid = pt.schoolid' +
'where s.enroll_status = 0 and s.schoolid = :SCHOOL' +
'and c.section_number = :SECTION' +
'and c.teacherid = :TEACHERID' +
'AND C.COURSE_NUMBER = :COURSENUMBER' +
'order by s.lastfirst');
DM.StudentsQry.Params.AddParam('SECTION');
DM.StudentsQry.ParamByName('SCHOOL').Value := SchoolNum;
DM.StudentsQry.ParamByName('SECTION').Value := DM.GetPSSections.FieldbyName('SECTION_NUMBER').AsString;
DM.StudentsQry.ParamByName('TEACHERID').Value := DM.GetPSTeachers.FieldbyName('ID').AsString;
DM.StudentsQry.ParamByName('COURSENUMBER').Value := DM.GetPSSections.FieldbyName('COURSE_NUMBER').AsString;
DM.StudentsQry.Open;

When updating SQL query text in the OraQuery component, the list of parameters in the query is generated automatically. So you should delete the following line from the provided code sample: DM.StudentsQry.Params.AddParam('SECTION');

Removing that line had no effect. Still get an error of "Parameter 'SCHOOL' not found" when I hit the first line after updating the SQL.

Your query text misses spaces in the end of strings. Because of that the name of the :SCHOOL parameter in your case is like the following: :SCHOOLand. Add missing spaces in the end of strings of the generated query