SQL Server CE查询仅在C#DataReader中引发错误 [英] Sql server CE query only throws error in C# DataReader

问题描述

最近，我不得不创建一个偶尔脱机的应用程序，并决定使用VS2010中提供的同步功能.因此，我对SQL CE进行了一些研究，并决定测试其中提供的功能.

由于框架的缘故，由于我使用对象数据源，所以我的大多数数据库对象已经从SQL SERVER 2008 R2 DB中映射出来.

我最初在处理View时遇到问题，因为CE不支持它们，所以我决定测试一些从我们内部使用的View中提取的SELECT语句，并直接通过VS 2010的查询分析器运行它们，我很乐意验证他们工作正常.

因此，我开始了探索性工作，将框架修改为存储定义视图的SELECT STATEMENT并仅在连接到CE数据库时才使用它，而且我还决定首先在较复杂的视图之一中尝试使用它.我对查询分析器进行了测试.令我惊讶的是，在我的框架中运行它会引发错误无效的列名".对于列``PlusAreaOperationIdPlusAreaOperation''.

任何人都对如何解决这种情况有任何想法?我愿意创建一个具有所需结构的示例在线数据库，供任何人将错误复制到其他位置.只需邮寄我询问详细信息即可.

再次:在VS2010中使用查询分析器时，此查询运行良好，但在执行数据读取器时失败.

  SELECT  PlusAreaOperation.IdPlusAreaOperation  AS  PlusAreaOperationIdPlusAreaOperation，PlusAreaOperation.IdOperation  AS  PlusAreaOperationIdOperation，PlusAreaOperation.IdFrequency  AS  PlusAreaOperationIdFrequency，PlusAreaOperation.IdProduct  AS  PlusAreaOperationIdProduct，PlusAreaOperation.IdPlusWorkSiteArea  AS  PlusAreaOperationIdPlusWorkSite，PlusAreaOperation.IdEquipment  AS  PlusAreaOperationIdEEquipss，PlusAreaOperineation. "code-keyword"> AS  PlusAreaOperationIdMachineType，PlusAreaOperation.IdUsageIndex  AS  PlusAreaOperationIdUsageIndex，PlusAreaOperation.Dilution  AS  PlusAreaOperationDilution，PlusAreaOperation.CleaningFactor  AS  PlusAreaOperationIdRecipientType，PlusAreaOperation.OpPerDay  AS  PlusRecipientTypeDescription，PlusRecipientType.Size  AS  PlusRecipientTypeSize，PlusRecipientTypeTranslation.IdRecipientTypeTranslation  AS  PlusRecipientTypeTranslationDescription，CleaningOperations.Ref  AS  CleaningOperationTranslationsName，CleaningOperationTranslations.Description  AS  CleaningOperationTranslationsDes cription，CleaningOperationTranslations.TechnicalFile  AS  CleaningOperationTranslationsTechnicalFile，Frequencies.Ref  AS  FrequenciesRef，Frequencies.IntervalTimeSpam  AS  FrequenciesIntervalType，Frequencies.CycleInterventions  AS  FrequencyTranslationsName，FrequencyTranslations.Description  AS  FrequencyTranslationsDescription，Products.Ref  AS  ProductsIdSupplier，Products.IdProductFamily  AS  ProductsCode，Products.IdUsageCalculator  AS  ProductsIdUsageCalculator，Products.IdTypeOfUsage  AS  ProductsIdTypeOfUsage，Products.Concentration  AS  Products浓度，Products.Active  AS  ProductsActive，Products.Price  AS  ProductsPrice，ProductTranslations.Name  AS  ProductTranslationsName，ProductTranslations.Title  AS  ProductTranslationsTitle，ProductTranslations.Description  AS  ProductTranslationsDescription，ProductTranslations.Dilution  AS  ProductTranslationsDilution，ProductTranslations.Aspect  AS  ProductTranslationsAspect，ProductTranslations.Smell  AS  ProductTranslationsSmell，ProductTranslations.SecurityFilename  AS  ProductT ranslationsSecurityFilename，ProductTranslations.TechnicalFilename  AS  ProductTranslationsTechnicalFilename，ProductTranslations.PH  AS  ProductTranslationsPH，ProductTranslations.ImageBinary  AS  ProductTranslationsImageBinary，ProductTranslations.SecurityFile  AS  ProductTranslationsTecnicalFile，UsageIndexes.IdUsageCalculator  AS  UsageIndexesIdUsageCalculator，UsageIndexes.Ref  AS  UsageIndexesRef，UsageIndexTranslations.Name  AS  EquipmentsIDEquipmentType，Equipments.Ref  AS  EquipmentsCleaningFactor，EquipmentTranslations.Name  AS  EquipmentTranslationsDescription， EquipmentTranslations.HourPrice  AS  EquipmentTranslationsHourPrice，EquipmentTranslations.ImageBinary  AS  EquipmentTranslationsImageBinary，Equipments.IdEquipment  AS  EquipmentTranslationsIdEquipment，EquipmentTranslations.IdLanguage  AS  ProductTranslationsIdProduct，Products.IdProduct  AS  ProductsIdProduct，ProductTranslations.IdLanguage  AS  FrequencyTranslationsIdFrequency，FrequencyTranslations.IdLanguage  AS  FrequencyTranslationsIdLanguage，Frequencies.IdFrequency  AS  CleaningOperationTranslationsIdCleaningOperation，CleaningOperationTranslations.IdLanguage  AS  UsageIndexesIdUsageIndex，CleaningOperations.IdCleaningOperation  AS  CleaningOperationsIdCleaningOperation，UsageIndexTranslations.IdUsageIndex  AS  UsageIndexTranslationsIdUsageIndex，UsageIndexTranslations.IdLanguage  AS  PlusRecipientTypeIdRecipientType，PlusRecipientTypeTranslation.IdRecipientType  AS  PlusRecipientTypeTranslationIdRecipientType，PlusRecipientTypeTranslation.IdLanguage  AS  PlusRecipientTypeTranslationIdLanguage，MachineTypes.IdMachineType  AS  MachineTypesIdMachineType，MachineTypes.Ref  AS  MachineTypesRef，MachineTypes. CleaningFactor  AS  MachineTypesCleaningFactor，MachineTypeTranslations.IdMachineType  AS  MachineTypeTranslationsIdMachineType，MachineTypeTranslations.IdLanguage  AS  MachineTypeTranslationsIdLanguage，MachineTypeTranslations.Name  AS  MachineTypeTranslationsName，MachineTypeTranslations.HourPrice  AS  MachineTypeTranslationsHourPrice，Ma chineTypeTranslations.ImageBinary  AS  MachineTypeTranslationsImageBinary，PlusWorkSiteArea.IdPlusWorkSiteArea  AS  PlusWorkSiteAreaIdPlusWorksiteArea，PlusWorkSiteArea.IdPlusWorkSite  AS  PlusWorkSiteAreaName，PlusWorkSiteArea.Area  AS  PlusWorkSiteAreaIdSurfaceType，SurfaceTypes.IdSurfaceType  AS  SurfaceTypesIdSurfaceType，SurfaceTypes.Ref  AS  SurfaceTypeTranslationsIdSurfaceType，SurfaceTypeTranslations.IdLanguage  AS  SurfaceTypeTranslationsName，SurfaceTypeTranslations.Description  AS  SurfaceTypeTranslationsDescription，SurfaceTypeTranslations.ImageBinary  AS  SurfaceTypeTranslationsImageBinary  FROM 产品 INNER   JOIN  ProductTranslations 打开 Products.IdProduct = ProductTranslations.IdProduct 右 >外部  JOIN 设备 INNER   JOIN  EquipmentTranslations 打开 Equipments.IdEquipment = EquipmentTranslations.IdEquipment 右 外部 加入 SurfaceTypeTranslations  JOIN  SurfaceTypes  INNER   JOIN  PlusWorkSiteArea 打开 SurfaceTypes.IdSurfaceType = PlusWorkSiteArea.IdSurfaceType  INNER   JOIN  PlusAreaOperation  ON  PlusWorkSiteArea.IdPlusWorkSiteArea = PlusAreaOperation .IdPlusWorkSiteArea 打开 Equipments.IdEquipment = PlusAreaOperation.IdEquipment  LEFT   OUTER   JOIN  MachineTypeTranslations  INNER   JOIN  MachineTypes 打开 MachineTypeTranslations.IdMachineType = MachineTypes.IdMachine键入打开 PlusAreaOperation.IdMachineType = MachineTypes.IdMachineType 左  OUTER   JOIN 频率 INNER   JOIN  FrequencyTranslations 打开 Frequencies.IdFrequency = FrequencyTranslations.IdFrequency  ON  PlusAreaOperation.IdFrequency = Frequencies.IdFrequency 外部  INNER   JOIN  CleaningOperations > ON  CleaningOperationTranslations.IdCleaningOperation = CleaningOperations.IdCleaningOperation 左  OUTER   JOIN  PlusRecipientTypeTranslation  INNER   JOIN  PlusRecipientType  ON  PlusRecipientTypeTranslation.IdRecipientType = PlusRecipientType.IdRecipientType 打开 PlusAreaOperation.IdRecipientType = PlusRecipientType.IdRecipientType 左侧 外部  JOIN  UsageIndexes  INNER   JOIN  UsageIndexTranslations  ON  UsageIndexes.IdUsageIndex = UsageIndexTranslations.IdUsageIndex  ON  PlusAreaOperation.IdUsageIndex = UsageIndexes.IdUsageIndex  //  VwPlusAreaOperation.ListOperations(
            //  PlusProject.GetProject(87).GetPlusWorkSiteIdPlusProject()[0] .GetPlusWorkSiteAreaIdPlusWorkSite()[0]); 跨度>
            IDbConnection conn = 空;
            IDbCommand cmd = 空;
            IDataReader dr =  null ;
            IDataParameter param = 空;
            字符串 sqlText =
                " ;
            sqlText + = " ;
            尝试
            {
                conn = 新 SqlCeConnection( @" )；
                conn.Open();

                cmd = conn.CreateCommand();
                cmd.CommandText = sqlText;
                cmd.CommandType = CommandType.Text;

                param = cmd.CreateParameter();
                param.DbType = DbType. Int32 ;
                param.Direction = ParameterDirection.Input;
                param.ParameterName = " ;
                param.Value =  205 ;

                cmd.Parameters.Add(param);

                dr = cmd.ExecuteReader();

                 while (dr.Read())
                {
                    Console.WriteLine("  + dr ["  PlusAreaOperationIdPlusAreaOperation"])；
                }
            }
            捕获(异常e)
            {
                Console.WriteLine(e.Message + e.StackTrace);
            }
            最终
            {
                如果(博士！= 空)
                    dr.Close();
                如果(cmd！= 空)
                    cmd.Dispose();
                如果(conn！= 空)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }

            Console.Read(); 

删除了SHOUTING-OriginalGriff [/Edit]

解决方案

我相信您不能在WHERE子句中使用列别名.将其替换为实际的列名.

 位置 PlusAreaOperation.IdPlusWorkSiteArea =  @ PlusAreaOperationIdPlusWorkSite  

I recently had to create a occasionally offline application and decided to use the synchronization feature provided in VS2010. I hence studied a bit about SQL CE and decided to test out the features provided within.

Since I use an object datasource due to a framework most of my DB objects had already been mapped out from SQL SERVER 2008 R2 DB.

I first encountered a problem when dealing with Views, since CE doesnt support them, so I decided to test out a few SELECT statements I took from our internally used views and run them directly through the query analyser of VS 2010 and I was happy to verify they worked properly.

So I began the exaustive work of adapting the Framework to store the SELECT STATEMENT that defined the view and use it only when connected to a CE database, ofc I also decided to try it in 1 of the more complex views first. The same one I had tested against the query analyser. And to my surprise running it in my framework throws an error ''Invalid Column Name''. For Column ''PlusAreaOperationIdPlusAreaOperation''.

Anyone has any ideas on how to address this situation? I''m willing to create a sample online DB with the required structure for anyone to replicate the error elsewhere. Just mail me asking for the details.

Again: This query runs fine when using query analyser within VS2010 but it fails when executing a datareader.

SELECT PlusAreaOperation.IdPlusAreaOperation AS PlusAreaOperationIdPlusAreaOperation, PlusAreaOperation.IdOperation AS PlusAreaOperationIdOperation, PlusAreaOperation.IdFrequency AS PlusAreaOperationIdFrequency, PlusAreaOperation.IdProduct AS PlusAreaOperationIdProduct, PlusAreaOperation.IdPlusWorkSiteArea AS PlusAreaOperationIdPlusWorkSite, PlusAreaOperation.IdEquipment AS PlusAreaOperationIdEquipment, PlusAreaOperation.IdMachineType AS PlusAreaOperationIdMachineType, PlusAreaOperation.IdUsageIndex AS PlusAreaOperationIdUsageIndex, PlusAreaOperation.Dilution AS PlusAreaOperationDilution, PlusAreaOperation.CleaningFactor AS PlusAreaOperationCleaningFactor, PlusAreaOperation.IdRecipientType AS PlusAreaOperationIdRecipientType, PlusAreaOperation.OpPerDay AS PlusAreaOperationOpPerDay, PlusRecipientType.Description AS PlusRecipientTypeDescription, PlusRecipientType.Size AS PlusRecipientTypeSize, PlusRecipientTypeTranslation.IdRecipientTypeTranslation AS PlusRecipientTypeTranslationIdRecipientTypeTranslation, PlusRecipientTypeTranslation.Description AS PlusRecipientTypeTranslationDescription, CleaningOperations.Ref AS CleaningOperationsRef, CleaningOperationTranslations.Name AS CleaningOperationTranslationsName, CleaningOperationTranslations.Description AS CleaningOperationTranslationsDescription, CleaningOperationTranslations.TechnicalFile AS CleaningOperationTranslationsTechnicalFile, Frequencies.Ref AS FrequenciesRef, Frequencies.IntervalTimeSpam AS FrequenciesIntervalTimeSpan, Frequencies.IntervalType AS FrequenciesIntervalType, Frequencies.CycleInterventions AS FrequenciesCycleInterventions, FrequencyTranslations.Name AS FrequencyTranslationsName, FrequencyTranslations.Description AS FrequencyTranslationsDescription, Products.Ref AS ProductsRef, Products.IdSupplier AS ProductsIdSupplier, Products.IdProductFamily AS ProductsIdProductFamily, Products.Code AS ProductsCode, Products.IdUsageCalculator AS ProductsIdUsageCalculator, Products.IdTypeOfUsage AS ProductsIdTypeOfUsage, Products.Concentration AS ProductsConcentration, Products.Active AS ProductsActive, Products.Price AS ProductsPrice, ProductTranslations.Name AS ProductTranslationsName, ProductTranslations.Title AS ProductTranslationsTitle, ProductTranslations.Description AS ProductTranslationsDescription, ProductTranslations.Dilution AS ProductTranslationsDilution, ProductTranslations.Aspect AS ProductTranslationsAspect, ProductTranslations.Smell AS ProductTranslationsSmell, ProductTranslations.SecurityFilename AS ProductTranslationsSecurityFilename, ProductTranslations.TechnicalFilename AS ProductTranslationsTechnicalFilename, ProductTranslations.PH AS ProductTranslationsPH, ProductTranslations.ImageBinary AS ProductTranslationsImageBinary, ProductTranslations.SecurityFile AS ProductTranslationsSecurityFile, ProductTranslations.TechnicalFile AS ProductTranslationsTecnicalFile, UsageIndexes.IdUsageCalculator AS UsageIndexesIdUsageCalculator, UsageIndexes.Ref AS UsageIndexesRef, UsageIndexTranslations.Name AS UsageIndexTranslationsName, Equipments.IdEquipmentType AS EquipmentsIDEquipmentType, Equipments.Ref AS EquipmentsRef, Equipments.CleaningFactor AS EquipmentsCleaningFactor, EquipmentTranslations.Name AS EquipmentTranslationsName, EquipmentTranslations.Description AS EquipmentTranslationsDescription, EquipmentTranslations.HourPrice AS EquipmentTranslationsHourPrice, EquipmentTranslations.ImageBinary AS EquipmentTranslationsImageBinary, Equipments.IdEquipment AS EquipmentsIdEquipment, EquipmentTranslations.IdEquipment AS EquipmentTranslationsIdEquipment, EquipmentTranslations.IdLanguage AS EquipmentTranslationsIdLanguage, ProductTranslations.IdProduct AS ProductTranslationsIdProduct, Products.IdProduct AS ProductsIdProduct, ProductTranslations.IdLanguage AS ProductTranslationsIdLanguage, FrequencyTranslations.IdFrequency AS FrequencyTranslationsIdFrequency, FrequencyTranslations.IdLanguage AS FrequencyTranslationsIdLanguage, Frequencies.IdFrequency AS FrequenciesIdFrequency, CleaningOperationTranslations.IdCleaningOperation AS CleaningOperationTranslationsIdCleaningOperation, CleaningOperationTranslations.IdLanguage AS CleaningOperationTranslationsIdLanguage, UsageIndexes.IdUsageIndex AS UsageIndexesIdUsageIndex, CleaningOperations.IdCleaningOperation AS CleaningOperationsIdCleaningOperation, UsageIndexTranslations.IdUsageIndex AS UsageIndexTranslationsIdUsageIndex, UsageIndexTranslations.IdLanguage AS UsageIndexTranslationsIdLanguage, PlusRecipientType.IdRecipientType AS PlusRecipientTypeIdRecipientType, PlusRecipientTypeTranslation.IdRecipientType AS PlusRecipientTypeTranslationIdRecipientType, PlusRecipientTypeTranslation.IdLanguage AS PlusRecipientTypeTranslationIdLanguage, MachineTypes.IdMachineType AS MachineTypesIdMachineType, MachineTypes.Ref AS MachineTypesRef, MachineTypes.CleaningFactor AS MachineTypesCleaningFactor, MachineTypeTranslations.IdMachineType AS MachineTypeTranslationsIdMachineType, MachineTypeTranslations.IdLanguage AS MachineTypeTranslationsIdLanguage, MachineTypeTranslations.Name AS MachineTypeTranslationsName, MachineTypeTranslations.HourPrice AS MachineTypeTranslationsHourPrice, MachineTypeTranslations.ImageBinary AS MachineTypeTranslationsImageBinary, PlusWorkSiteArea.IdPlusWorkSiteArea AS PlusWorkSiteAreaIdPlusWorksiteArea, PlusWorkSiteArea.IdPlusWorkSite AS PlusWorkSiteAreaIdPlusWorkSite, PlusWorkSiteArea.Name AS PlusWorkSiteAreaName, PlusWorkSiteArea.Area AS PlusWorkSiteAreaArea, PlusWorkSiteArea.IdSurfaceType AS PlusWorkSiteAreaIdSurfaceType, SurfaceTypes.IdSurfaceType AS SurfaceTypesIdSurfaceType, SurfaceTypes.Ref AS SurfaceTypesRef, SurfaceTypeTranslations.IdSurfaceType AS SurfaceTypeTranslationsIdSurfaceType, SurfaceTypeTranslations.IdLanguage AS SurfaceTypeTranslationsIdLanguage, SurfaceTypeTranslations.Name AS SurfaceTypeTranslationsName, SurfaceTypeTranslations.Description AS SurfaceTypeTranslationsDescription, SurfaceTypeTranslations.ImageBinary AS SurfaceTypeTranslationsImageBinary FROM Products INNER JOIN ProductTranslations ON Products.IdProduct = ProductTranslations.IdProduct RIGHT OUTER JOIN Equipments INNER JOIN EquipmentTranslations ON Equipments.IdEquipment = EquipmentTranslations.IdEquipment RIGHT OUTER JOIN SurfaceTypeTranslations INNER JOIN SurfaceTypes ON SurfaceTypeTranslations.IdSurfaceType = SurfaceTypes.IdSurfaceType INNER JOIN PlusWorkSiteArea ON SurfaceTypes.IdSurfaceType = PlusWorkSiteArea.IdSurfaceType INNER JOIN PlusAreaOperation ON PlusWorkSiteArea.IdPlusWorkSiteArea = PlusAreaOperation.IdPlusWorkSiteArea ON Equipments.IdEquipment = PlusAreaOperation.IdEquipment LEFT OUTER JOIN MachineTypeTranslations INNER JOIN MachineTypes ON MachineTypeTranslations.IdMachineType = MachineTypes.IdMachineType ON PlusAreaOperation.IdMachineType = MachineTypes.IdMachineType LEFT OUTER JOIN Frequencies INNER JOIN FrequencyTranslations ON Frequencies.IdFrequency = FrequencyTranslations.IdFrequency ON PlusAreaOperation.IdFrequency = Frequencies.IdFrequency LEFT OUTER JOIN CleaningOperationTranslations INNER JOIN CleaningOperations ON CleaningOperationTranslations.IdCleaningOperation = CleaningOperations.IdCleaningOperation ON PlusAreaOperation.IdOperation = CleaningOperations.IdCleaningOperation LEFT OUTER JOIN PlusRecipientTypeTranslation INNER JOIN PlusRecipientType ON PlusRecipientTypeTranslation.IdRecipientType = PlusRecipientType.IdRecipientType ON PlusAreaOperation.IdRecipientType = PlusRecipientType.IdRecipientType LEFT OUTER JOIN UsageIndexes INNER JOIN UsageIndexTranslations ON UsageIndexes.IdUsageIndex = UsageIndexTranslations.IdUsageIndex ON PlusAreaOperation.IdUsageIndex = UsageIndexes.IdUsageIndex ON Products.IdProduct = PlusAreaOperation.IdProduct

//VwPlusAreaOperation.ListOperations(
            //    PlusProject.GetProject(87).GetPlusWorkSiteIdPlusProject()[0].GetPlusWorkSiteAreaIdPlusWorkSite()[0]);
            IDbConnection conn = null;
            IDbCommand cmd = null;
            IDataReader dr = null;
            IDataParameter param = null;
            string sqlText =
                "SELECT PlusAreaOperation.IdPlusAreaOperation AS PlusAreaOperationIdPlusAreaOperation, PlusAreaOperation.IdOperation AS PlusAreaOperationIdOperation, PlusAreaOperation.IdFrequency AS PlusAreaOperationIdFrequency, PlusAreaOperation.IdProduct AS PlusAreaOperationIdProduct, PlusAreaOperation.IdPlusWorkSiteArea AS PlusAreaOperationIdPlusWorkSite, PlusAreaOperation.IdEquipment AS PlusAreaOperationIdEquipment, PlusAreaOperation.IdMachineType AS PlusAreaOperationIdMachineType, PlusAreaOperation.IdUsageIndex AS PlusAreaOperationIdUsageIndex, PlusAreaOperation.Dilution AS PlusAreaOperationDilution, PlusAreaOperation.CleaningFactor AS PlusAreaOperationCleaningFactor, PlusAreaOperation.IdRecipientType AS PlusAreaOperationIdRecipientType, PlusAreaOperation.OpPerDay AS PlusAreaOperationOpPerDay, PlusRecipientType.Description AS PlusRecipientTypeDescription, PlusRecipientType.Size AS PlusRecipientTypeSize, PlusRecipientTypeTranslation.IdRecipientTypeTranslation AS PlusRecipientTypeTranslationIdRecipientTypeTranslation, PlusRecipientTypeTranslation.Description AS PlusRecipientTypeTranslationDescription, CleaningOperations.Ref AS CleaningOperationsRef, CleaningOperationTranslations.Name AS CleaningOperationTranslationsName, CleaningOperationTranslations.Description AS CleaningOperationTranslationsDescription, CleaningOperationTranslations.TechnicalFile AS CleaningOperationTranslationsTechnicalFile, Frequencies.Ref AS FrequenciesRef, Frequencies.IntervalTimeSpam AS FrequenciesIntervalTimeSpan, Frequencies.IntervalType AS FrequenciesIntervalType, Frequencies.CycleInterventions AS FrequenciesCycleInterventions, FrequencyTranslations.Name AS FrequencyTranslationsName, FrequencyTranslations.Description AS FrequencyTranslationsDescription, Products.Ref AS ProductsRef, Products.IdSupplier AS ProductsIdSupplier, Products.IdProductFamily AS ProductsIdProductFamily, Products.Code AS ProductsCode, Products.IdUsageCalculator AS ProductsIdUsageCalculator, Products.IdTypeOfUsage AS ProductsIdTypeOfUsage, Products.Concentration AS ProductsConcentration, Products.Active AS ProductsActive, Products.Price AS ProductsPrice, ProductTranslations.Name AS ProductTranslationsName, ProductTranslations.Title AS ProductTranslationsTitle, ProductTranslations.Description AS ProductTranslationsDescription, ProductTranslations.Dilution AS ProductTranslationsDilution, ProductTranslations.Aspect AS ProductTranslationsAspect, ProductTranslations.Smell AS ProductTranslationsSmell, ProductTranslations.SecurityFilename AS ProductTranslationsSecurityFilename, ProductTranslations.TechnicalFilename AS ProductTranslationsTechnicalFilename, ProductTranslations.PH AS ProductTranslationsPH, ProductTranslations.ImageBinary AS ProductTranslationsImageBinary, ProductTranslations.SecurityFile AS ProductTranslationsSecurityFile, ProductTranslations.TechnicalFile AS ProductTranslationsTecnicalFile, UsageIndexes.IdUsageCalculator AS UsageIndexesIdUsageCalculator, UsageIndexes.Ref AS UsageIndexesRef, UsageIndexTranslations.Name AS UsageIndexTranslationsName, Equipments.IdEquipmentType AS EquipmentsIDEquipmentType, Equipments.Ref AS EquipmentsRef, Equipments.CleaningFactor AS EquipmentsCleaningFactor, EquipmentTranslations.Name AS EquipmentTranslationsName, EquipmentTranslations.Description AS EquipmentTranslationsDescription, EquipmentTranslations.HourPrice AS EquipmentTranslationsHourPrice, EquipmentTranslations.ImageBinary AS EquipmentTranslationsImageBinary, Equipments.IdEquipment AS EquipmentsIdEquipment, EquipmentTranslations.IdEquipment AS EquipmentTranslationsIdEquipment, EquipmentTranslations.IdLanguage AS EquipmentTranslationsIdLanguage, ProductTranslations.IdProduct AS ProductTranslationsIdProduct, Products.IdProduct AS ProductsIdProduct, ProductTranslations.IdLanguage AS ProductTranslationsIdLanguage, FrequencyTranslations.IdFrequency AS FrequencyTranslationsIdFrequency, FrequencyTranslations.IdLanguage AS FrequencyTranslationsIdLanguage, Frequencies.IdFrequency AS FrequenciesIdFrequency, CleaningOperationTranslations.IdCleaningOperation AS CleaningOperationTranslationsIdCleaningOperation, CleaningOperationTranslations.IdLanguage AS CleaningOperationTranslationsIdLanguage, UsageIndexes.IdUsageIndex AS UsageIndexesIdUsageIndex, CleaningOperations.IdCleaningOperation AS CleaningOperationsIdCleaningOperation, UsageIndexTranslations.IdUsageIndex AS UsageIndexTranslationsIdUsageIndex, UsageIndexTranslations.IdLanguage AS UsageIndexTranslationsIdLanguage, PlusRecipientType.IdRecipientType AS PlusRecipientTypeIdRecipientType, PlusRecipientTypeTranslation.IdRecipientType AS PlusRecipientTypeTranslationIdRecipientType, PlusRecipientTypeTranslation.IdLanguage AS PlusRecipientTypeTranslationIdLanguage, MachineTypes.IdMachineType AS MachineTypesIdMachineType, MachineTypes.Ref AS MachineTypesRef, MachineTypes.CleaningFactor AS MachineTypesCleaningFactor, MachineTypeTranslations.IdMachineType AS MachineTypeTranslationsIdMachineType, MachineTypeTranslations.IdLanguage AS MachineTypeTranslationsIdLanguage, MachineTypeTranslations.Name AS MachineTypeTranslationsName, MachineTypeTranslations.HourPrice AS MachineTypeTranslationsHourPrice, MachineTypeTranslations.ImageBinary AS MachineTypeTranslationsImageBinary, PlusWorkSiteArea.IdPlusWorkSiteArea AS PlusWorkSiteAreaIdPlusWorksiteArea, PlusWorkSiteArea.IdPlusWorkSite AS PlusWorkSiteAreaIdPlusWorkSite, PlusWorkSiteArea.Name AS PlusWorkSiteAreaName, PlusWorkSiteArea.Area AS PlusWorkSiteAreaArea, PlusWorkSiteArea.IdSurfaceType AS PlusWorkSiteAreaIdSurfaceType, SurfaceTypes.IdSurfaceType AS SurfaceTypesIdSurfaceType, SurfaceTypes.Ref AS SurfaceTypesRef, SurfaceTypeTranslations.IdSurfaceType AS SurfaceTypeTranslationsIdSurfaceType, SurfaceTypeTranslations.IdLanguage AS SurfaceTypeTranslationsIdLanguage, SurfaceTypeTranslations.Name AS SurfaceTypeTranslationsName, SurfaceTypeTranslations.Description AS SurfaceTypeTranslationsDescription, SurfaceTypeTranslations.ImageBinary AS SurfaceTypeTranslationsImageBinary FROM Products INNER JOIN ProductTranslations ON Products.IdProduct = ProductTranslations.IdProduct RIGHT OUTER JOIN Equipments INNER JOIN EquipmentTranslations ON Equipments.IdEquipment = EquipmentTranslations.IdEquipment RIGHT OUTER JOIN SurfaceTypeTranslations INNER JOIN SurfaceTypes ON SurfaceTypeTranslations.IdSurfaceType = SurfaceTypes.IdSurfaceType INNER JOIN PlusWorkSiteArea ON SurfaceTypes.IdSurfaceType = PlusWorkSiteArea.IdSurfaceType INNER JOIN PlusAreaOperation ON PlusWorkSiteArea.IdPlusWorkSiteArea = PlusAreaOperation.IdPlusWorkSiteArea ON Equipments.IdEquipment = PlusAreaOperation.IdEquipment LEFT OUTER JOIN MachineTypeTranslations INNER JOIN MachineTypes ON MachineTypeTranslations.IdMachineType = MachineTypes.IdMachineType ON PlusAreaOperation.IdMachineType = MachineTypes.IdMachineType LEFT OUTER JOIN Frequencies INNER JOIN FrequencyTranslations ON Frequencies.IdFrequency = FrequencyTranslations.IdFrequency ON PlusAreaOperation.IdFrequency = Frequencies.IdFrequency LEFT OUTER JOIN CleaningOperationTranslations INNER JOIN CleaningOperations ON CleaningOperationTranslations.IdCleaningOperation = CleaningOperations.IdCleaningOperation ON PlusAreaOperation.IdOperation = CleaningOperations.IdCleaningOperation LEFT OUTER JOIN PlusRecipientTypeTranslation INNER JOIN PlusRecipientType ON PlusRecipientTypeTranslation.IdRecipientType = PlusRecipientType.IdRecipientType ON PlusAreaOperation.IdRecipientType = PlusRecipientType.IdRecipientType LEFT OUTER JOIN UsageIndexes INNER JOIN UsageIndexTranslations ON UsageIndexes.IdUsageIndex = UsageIndexTranslations.IdUsageIndex ON PlusAreaOperation.IdUsageIndex = UsageIndexes.IdUsageIndex ON Products.IdProduct = PlusAreaOperation.IdProduct";
            sqlText += " WHERE PlusAreaOperationIdPlusWorkSite = @PlusAreaOperationIdPlusWorkSite";
            try
            {
                conn = new SqlCeConnection(@"Data Source=|DataDirectory|\InfoPlus2.sdf;Max Database Size=2047");
                conn.Open();

                cmd = conn.CreateCommand();
                cmd.CommandText = sqlText;
                cmd.CommandType = CommandType.Text;

                param = cmd.CreateParameter();
                param.DbType = DbType.Int32;
                param.Direction = ParameterDirection.Input;
                param.ParameterName = "PlusAreaOperationIdPlusWorkSite";
                param.Value = 205;

                cmd.Parameters.Add(param);

                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    Console.WriteLine("PlusAreaOperationIdPlusAreaOperation: " + dr["PlusAreaOperationIdPlusAreaOperation"]);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message + e.StackTrace);
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                if (cmd != null)
                    cmd.Dispose();
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }

            Console.Read();

[Edit]SHOUTING removed - OriginalGriff[/Edit]

解决方案

I believe you cannot use column alias in the WHERE clause. Replace it with the actual column name.

WHERE PlusAreaOperation.IdPlusWorkSiteArea = @PlusAreaOperationIdPlusWorkSite

这篇关于SQL Server CE查询仅在C#DataReader中引发错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！