-1

I have an OleDBCommand which queries an Oracle table to retrieve courses and in the where clause of the command, I have specified a list as one of the conditions. This list is derived from an entirely different source and looks like this (I have included on the part of the code that's relevant);

string coursereferencesList = "(" + "\'" + string.Join("\', \'", courseRefsFromOtherSource.ToArray()) + "\'" + ")";

cmd.CommandText="SELECT course.Reference as courseref, course.name as coursename, department.Name as department from COURSE_TABLE course JOIN DEPARTMENT_TABLE department ON course.ID=department.COURSE " +
"WHERE course.TYPE ='Module' and course.REFERENCE in " + coursereferencesList;

DataSet course_ds= new DataSet();
DataTable course_dt= new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
adapter = new OleDbDataAdapter
{
SelectCommand = cmd
};
adapter.Fill(course_dt);
course_ds.Tables.Add(course_dt);

The issue I have is that the adapter does not fill the datatable - it only fills in when I specify student references in the query or when the coursereferencesList is significantly smaller. It has been 'stuck' on the adapter.fill part of the code for about 5 minutes now. There are about 40,000 courses in the coursereferencesList list. I would be grateful for any pointers.

UPDATE: I put and try and catch around the adpater.fill and get this error;

The query processor ran out of stack space during query optimization. Please simplify the query.Microsoft SQL Server Native Client 11.0

Many thanks in advance.

GBM
  • 99
  • 6
  • you are missing a space before Where or after department.COURS – nbk Aug 12 '23 at 11:10
  • Two points: You don't need the \' to quote, just ' should be fine. Also you need a space before the WHERE statement to separate it from the join statement – Steve Aug 12 '23 at 11:11
  • Did you see any error? Do you have a try catch that capture the error (if any) – Steve Aug 12 '23 at 11:12
  • and you are missing as Double quote afuter course.REFERENCE in – nbk Aug 12 '23 at 11:17
  • @nbk thank you for the reponse - I have inserted the double qoutes and space but still no good. I also surrounded the adapter.fill with a triy and catch block and get this error; The query processor ran out of stack space during query optimization. Please simplify the query.Microsoft SQL Server Native Client 11.0 – GBM Aug 12 '23 at 11:29
  • @Steve Thank you for taking the time to response - I have put a try/catch block in the adapter.fill part, and get this error; The query processor ran out of stack space during query optimization. Please simplify the query.Microsoft SQL Server Native Client 11.0 – GBM Aug 12 '23 at 11:29
  • See the Remarks in https://learn.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver16 – Hans Kesting Aug 12 '23 at 11:39
  • How many items are in studentRefsFromSource? If it's too large, it could cause this: https://stackoverflow.com/a/1869810/1467987 – Jace Aug 12 '23 at 11:42
  • Try removing the WHERE and see if you get results. There may not be and data with your filters. Then try each filter one at a time to verify the filters are working properly. – jdweng Aug 12 '23 at 14:33
  • @Jace thank you for the response - there are approximately 45,000 items in the source list.... – GBM Aug 13 '23 at 08:32
  • @jdweng There is definitely data in the source list - the where clause seems to work when I significantly reduce the size of the source list i.e. I tried it with 10 courses and the adapter.fill worked just fine, so I'm thinking that Jace could be right that if the source list is too large, it could cause this error. – GBM Aug 13 '23 at 08:35
  • Without the where is always equal or larger then without the where. It is not the size. The issue is probably with the course.REFERENCE – jdweng Aug 13 '23 at 08:47

0 Answers0