none
C# Microsoft Access - Error in FROM Clause RRS feed

  • Question

  • Hi all,

     

    I have been trying to get data from my Access database from a query but keep getting the error "Error in FROM Clause"

     

    I can access data in some queries and tables, but not the query I want. The query gets data from two different tables linked by an id (number data type) and some of the fields retrieve data from code written in modules.

     

    I can run the query fine from within Access, but just not from c#. (Windows Application)

     

    I have the following code:

     

    //Create a Command object for the Sql string passed in

    OleDbCommand myAccessCommand = new OleDbCommand();

    myAccessCommand.CommandType = CommandType.Text;

    myAccessCommand.CommandText = strSql;

    myAccessCommand.Connection = myAccessConnection;

     

    //Create a Data Adapter to get the actual data

    OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);

     

    //Open the Access connection

    myAccessConnection.Open();

     

    //Fill the Data Set with the Delna Data

    myDataAdapter.Fill(myDataSet);

     

    It throws the error when I try to Fill the DataSet.

     

    Any help on this would be MUCH appreciated as I have spent hours searching and reading up on this but to no avail.

     

    My SQL statement (stored in strSql) is definately valid - I have even just tried "Select * From MyQuery" = where MyQuery is the name of the query I want to get the data from. It contains approximately 25,000 records and about 12 columns.

     

    Thanks in advance.

     

    Grant.

    Friday, July 6, 2007 1:04 PM

All replies

  • can you give is the full strSQL value please? It would help alot for us to help you to the best way possible.

     

    just going on your last sentence/variable called "myQuery" - remember the syntax for selecting data FROM a table is as follows:

     

    SELECT * FROM [TableName]

     

    This however is not valid:

     

    SELECT * FROM [field1] = someValue

    Friday, July 6, 2007 1:50 PM

  • Could you post the SQL from MyQuery? You should be able to execute a SELECT on a QueryDef but there may be something in the QueryDef that is causing the problem.
    Friday, July 6, 2007 2:32 PM
  • Hi,

     

    Sorry for my not being more clear - my strSql string = "Select * From BCOM_SAB;"

     

    There is no = at the end of it. I have included the underlying SQL that makes up the queries...

     

    So I have a query called: AssessmentBand, and another query (that I am trying to retrieve data from) called BCOM_SAB that references one table and the query AssessmentBand.

     

    Below are the two queries (SQL)

     

    BCOM_SAB Query:

    SELECT student.student_id, student.course_id, AssessmentBand.when_assessed, student.first_language_id, AssessmentBand.vocab_score, AssessmentBand.speedreading_score, AssessmentBand.screening_band_alt, AssessmentBand.listening_band, AssessmentBand.reading_band, AssessmentBand.writing_band_final, AssessmentBand.average_band
    FROM student INNER JOIN AssessmentBand ON student.student_id = AssessmentBand.student_id;

    AssessmentBand Query:

    SELECT assessment.*, Iif(IsNull(vocab_score) Or IsNull(speedreading_score) or IsNull(vocab_version) or IsNull(speedreading_version),Null,(SELECT TOP 1 band_number FROM band WHERE band_name='screening' AND version=(vocab_version*1000+speedreading_version) and (vocab_score+speedreading_score) <= band_max_score ORDER BY band_max_score )) AS screening_band, Iif(IsNull(vocab_score) Or IsNull(speedreading_score) or IsNull(vocab_version) or IsNull(speedreading_version),Null,(SELECT TOP 1 band_number FROM band_alt WHERE band_name='screening' AND version=(vocab_version*1000+speedreading_version) and (vocab_score+speedreading_score) <= band_max_score ORDER BY band_max_score )) AS screening_band_alt, IIf(IsNull([short_writing5_score]),Null,IIf([short_writing5_score]<4,[short_writing5_score]+3,[short_writing5_score]+4)) AS sw5_final_score, AnalyticScore([short_writing9_fluency],[short_writing9_content],[short_writing9_form],[short_writing9_avg_score]) AS sw9_final_score, AnalyticScore([short_writing9_fluency2],[short_writing9_content2],[short_writing9_form2],Null) AS sw9_final_score2, IIf(CalculateDifference(sw9_final_score,sw9_final_score2)>1,'Warning',Null) AS sw9_rater_warning, AnalyticScore([long_writing_fluency],[long_writing_content],[long_writing_form],[long_writing_avg_score]) AS lw_final_score, AnalyticScore([long_writing_fluency2],[long_writing_content2],[long_writing_form2],Null) AS lw_final_score2, IIf(CalculateDifference(lw_final_score,lw_final_score2)>1,'Warning',Null) AS lw_rater_warning, IIf(CalculateDifference(CalculateAverage(sw9_final_score,sw9_final_score2,Null,0),CalculateAverage(lw_final_score,lw_final_score2,Null,0))>1,'Warning',Null) AS writing_warning, IIf(IsNull([listening_score]),Null,(SELECT TOP 1 band_number FROM band WHERE band_name='listening' AND version=listening_version and listening_score <= band_max_score ORDER BY band_max_score )) AS listening_band, IIf(IsNull([reading_score]),Null,(SELECT TOP 1 band_number FROM band WHERE band_name='reading' AND version=reading_version and reading_score <= band_max_score ORDER BY band_max_score )) AS reading_band, IIf(IsNull([lw_final_score]),IIf(IsNull([sw9_final_score]),IIf(IsNull([sw5_final_score]),Null,'shortwriting'),'shortwriting'),'longwriting') AS writing_type, IIf(IsNull([lw_final_score]),IIf(IsNull([sw9_final_score]),[sw5_final_score],[sw9_final_score]),[lw_final_score]) AS writing_band, IIf(IsNull([lw_final_score2]) and IsNull(lw_final_score),[sw9_final_score2],[lw_final_score2]) AS writing_band2, CalculateAverage([writing_band],[writing_band2],Null,1) AS writing_band_avg, CalculateAverage([writing_band],[writing_band2],Null,0) AS writing_band_final, IIf(IsNull([long_writing_fluency]),CalculateAverage([short_writing9_fluency],[short_writing9_fluency2],Null,0),CalculateAverage([long_writing_fluency],[long_writing_fluency2],Null,0)) AS writing_fluency_final, IIf(IsNull([long_writing_content]),CalculateAverage([short_writing9_content],[short_writing9_content2],Null,0),CalculateAverage([long_writing_content],[long_writing_content2],Null,0)) AS writing_content_final, IIf(IsNull([long_writing_form]),CalculateAverage([short_writing9_form],[short_writing9_form2],Null,0),CalculateAverage([long_writing_form],[long_writing_form2],Null,0)) AS writing_form_final, IIf(CalculateDifference(writing_band_final,CalculateAverage(writing_fluency_final,writing_content_final,writing_form_final,0))>0,'Warning',Null) AS writing_band_final_warning, CalculateAverage([listening_band],[reading_band],[writing_band_avg],1) AS average_band, CalculateMin([listening_band],[reading_band],[writing_band_final]) AS min_band, CalculateMax([listening_band],[reading_band],[writing_band_avg]) AS max_band
    FROM assessment;

     

    As you can see - some of the fields are retrieving data from functions writting in VBA.

     

    Kind Regards,

     

    Grant.

    Friday, July 6, 2007 10:38 PM
  • Hi,

     

    I found the initial problem that caused the error: Error in FROM Clause... it was because one of the tables that were referenced (band) is a key word so I just put [] around it and that error was fixed... but now I have another error - Undefined function 'AnalyticScore' in expression

     

    I read somewhere that user defined functions aren't supported from Access into .Net but surely there must be a was to get this data from Access to .Net?

     

    Any help would be great.

     

    Kind Regards,

     

    Grant.

    Saturday, July 7, 2007 12:38 PM