<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Compact Edition
»
SELECT STATEMENT DOESNT WORK WHEN IN C#, BUT...
SELECT STATEMENT DOESNT WORK WHEN IN C#, BUT WORKS IN VS2010 QUERY ANALYSER
Rate Topic
Display Mode
Topic Options
Author
Message
camachorm
camachorm
Posted Tuesday, July 26, 2011 3:41 PM
Forum 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
Sean Lange
Sean Lange
Posted Wednesday, July 27, 2011 12:57 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 8,597,
Visits: 8,237
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
Post #1149586
danschl
danschl
Posted Wednesday, July 27, 2011 1:14 PM
Ten Centuries
Group: General Forum Members
Last Login: Monday, February 11, 2013 8:37 AM
Points: 1,382,
Visits: 641
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
Sean Lange
Sean Lange
Posted Wednesday, July 27, 2011 2:00 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 8,597,
Visits: 8,237
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
Post #1149625
camachorm
camachorm
Posted Wednesday, July 27, 2011 4:15 PM
Forum 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
camachorm
camachorm
Posted Wednesday, July 27, 2011 4:16 PM
Forum 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
camachorm
camachorm
Posted Wednesday, July 27, 2011 4:17 PM
Forum 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
Neal Sivley
Neal Sivley
Posted Thursday, July 28, 2011 2:36 PM
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 1:05 PM
Points: 209,
Visits: 309
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 »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
