Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SELECT STATEMENT DOESNT WORK WHEN IN C#, BUT WORKS IN VS2010 QUERY ANALYSER Expand / Collapse
Author
Message
Posted Tuesday, July 26, 2011 3:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 27, 2011 4:13 PM
Points: 4, Visits: 2
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?

[code="sql]
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[/code]
Post #1148727
Posted Wednesday, July 27, 2011 12:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 13,069, Visits: 11,908
WOW that is hard on the eyes.

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1149586
Posted Wednesday, July 27, 2011 1:14 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 25, 2013 7:43 AM
Points: 1,384, Visits: 644
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



Post #1149595
Posted Wednesday, July 27, 2011 2:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 13,069, Visits: 11,908
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


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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1149625
Posted Wednesday, July 27, 2011 4:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 27, 2011 4:13 PM
Points: 4, Visits: 2
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...
Post #1149714
Posted Wednesday, July 27, 2011 4:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 27, 2011 4:13 PM
Points: 4, Visits: 2
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.
Post #1149716
Posted Wednesday, July 27, 2011 4:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 27, 2011 4:13 PM
Points: 4, Visits: 2
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.
Post #1149718
Posted Thursday, July 28, 2011 2:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 209, Visits: 335
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?
Post #1150554
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse