SELECT STATEMENT DOESNT WORK WHEN IN C#, BUT WORKS IN VS2010 QUERY ANALYSER

  • The following select statment crashes with an 'Invalid Column Name' Error when executed by a C# Datareader. Claiming that PlusAreaOperationIdPlusAreaOperation doesnt exist. If I run the exact same query on the VS 2010 Query analyser I get the expected results.

    Any thoughts?

    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

  • WOW that is hard on the eyes. :crazy:

    You really should not be running dynamic sql like that directly from your managed code. I would suggest turning that beast into a view or stored proc first. The problem is almost certainly somewhere in your code. It would be fairly easy to miss a character in code when referencing your column names because they are all so loooooooong.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • around my office there is a saying

    Just because you can put it into one query doesnt me you should

    I would break that up:w00t:

  • danschl (7/27/2011)


    around my office there is a saying

    Just because you can put it into one query doesnt me you should

    I would break that up:w00t:

    Or at the very least add brevity. 😉

    Aliases could be used and the column names could surely be shortened without losing any clarity. I would bet the overall character count could be reduced by 2/3 which would increase the readability by somewhere around 1.473894e23141%.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • LOL, SQL CE Doesnt support views or SP... hence the dynamic query is required. I recently found the problem. In CE aliases arent supported in the WHERE clause... Go figure...

  • Its called speed optimization..... When u split this into multiple queries which the application requires to run separately I get a delay in execution of over 45seconds, that is unacceptable in my book.

  • For brevity I rather use color-coded syntax editors, helps reading better what im looking at. Descriptive names makes me understand to what they refer.

  • I would check two things.

    1. Verify the connection string. Make sure the database your using in VS2010 is the sames one your application is pointed to. Very often this is easily glanced at but not REALLY checked.

    2. Make sure the dll references you have in your project are the same version as your database. You don't want to have v3.0 dll's trying to access a 3.5 database.

    Can the C# application run other queries?

    What if you run the query from SSMS and not VS2010?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply