[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Visual Studio Datatable wizard generated query error

Posted on 2007-10-10
8
Medium Priority
?
1,104 Views
Last Modified: 2008-01-09
I am using Visual Studio Professional to create reports pulling data from an Oracle Database
I Added a datatable in my dataset via right click. I configured the Fill command using the Query Builder. In the query builder I

added all of the relevant tables. The system generated the following Query. If I execute the query in the query builder it works

perfectly, but when I try to save I get the following error.

Wizard Results
The wizard detected the following problems when configuring the TableAdapter: "DataTable3":
Generated SELECT statement.
Syntax Error: Expecting '.', identifier or quoted identifier.

Here is the query.... Yes I know it is huge, but... It is what is needed.

SELECT  ITEM.ITEM_NO, ITEM_DESC.LANG_NO, ITEM_DESC.DESCR,

ZZVITTO_PRODUCTION_LINE.ORDER_COMPLETE, ZZVITTO_PRODUCTION_LINE.SALESORDER_NO,
               ZZVITTO_PRODUCTION_LINE.ORDER_NO, ZZVITTO_PRODUCTION_LINE.PRODUCTION_LINE,

SOORDER_HD.SEQ_NO, SOORDER_HD.BILL_NAME,

PCORDER_HD.QTY_REQ, PCORDER_HD.QTY_PRODUCED, ZZVITTO_WZ_CAFE.ITEM_CAFE,
ITEM_DESC_1.DESCR AS COFEEDESC
FROM     SOORDER_HD INNER JOIN
               PCORDER_HD ON SOORDER_HD.SEQ_NO = PCORDER_HD.SRC_SEQ_NO INNER JOIN
               ITEM INNER JOIN
               ITEM_DESC ON ITEM.ITEM_NO = ITEM_DESC.REF_NO INNER JOIN
               ZZVITTO_PRODUCTION_LINE ON ITEM.ITEM_NO = ZZVITTO_PRODUCTION_LINE.ITEM_NO ON

PCORDER_HD.ORDER_NO = ZZVITTO_PRODUCTION_LINE.ORDER_NO INNER JOIN
               ZZVITTO_WZ_CAFE ON ITEM.ITEM_NO = ZZVITTO_WZ_CAFE.ITEM_NO INNER JOIN
               ITEM ITEM_1 ON ZZVITTO_WZ_CAFE.ITEM_CAFE = ITEM_1.ITEM_NO INNER JOIN
               ITEM_DESC ITEM_DESC_1 ON ZZVITTO_WZ_CAFE.ITEM_CAFE = ITEM_DESC_1.REF_NO
WHERE  (ITEM_DESC.LANG_NO = 'F') AND (ITEM_DESC_1.LANG_NO = 'F')

If anybody has sharp enough eyes to spot the glitch or is aware of a bug in Visual studio.. (and of course how to get around

it) I would be eternally gratefull for your help.

Ray
0
Comment
Question by:RayAtVittoria
  • 4
  • 2
7 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20049329
change this line to

[ITEM_DESC_1].DESCR AS COFEEDESC
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20049336
anywhere else you have the name of something that also has an int in it, put in brackets like I showed you.
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20049348
here is your FROM statement, hopefully I got them all

FROM     SOORDER_HD INNER JOIN
               PCORDER_HD ON SOORDER_HD.SEQ_NO = PCORDER_HD.SRC_SEQ_NO INNER JOIN
               ITEM INNER JOIN
               ITEM_DESC ON ITEM.ITEM_NO = ITEM_DESC.REF_NO INNER JOIN
               ZZVITTO_PRODUCTION_LINE ON ITEM.ITEM_NO = ZZVITTO_PRODUCTION_LINE.ITEM_NO ON

PCORDER_HD.ORDER_NO = ZZVITTO_PRODUCTION_LINE.ORDER_NO INNER JOIN
               ZZVITTO_WZ_CAFE ON ITEM.ITEM_NO = ZZVITTO_WZ_CAFE.ITEM_NO INNER JOIN
               [ITEM ITEM_1] ON ZZVITTO_WZ_CAFE.ITEM_CAFE = [ITEM_1].ITEM_NO INNER JOIN
               ITEM_DESC [ITEM_DESC_1] ON ZZVITTO_WZ_CAFE.ITEM_CAFE = [ITEM_DESC_1].REF_NO
WHERE  (ITEM_DESC.LANG_NO = 'F') AND ([ITEM_DESC_1].LANG_NO = 'F')
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:RayAtVittoria
ID: 20049505
I tried your solution and I get the following error instead.

Error in SELECT clause: expression near '['.
Error in join expression.
Unable to parse query text.

What now?
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20049593
I went ahead and formatted it for you, I think I found more issues, anyway the problem is when you associated a number as part of a column name, you need to put in the brackets.

SELECT
      ITEM.ITEM_NO,
      ITEM_DESC.LANG_NO,
      ITEM_DESC.DESCR,

      ZZVITTO_PRODUCTION_LINE.ORDER_COMPLETE,
      ZZVITTO_PRODUCTION_LINE.SALESORDER_NO,
    ZZVITTO_PRODUCTION_LINE.ORDER_NO,
      ZZVITTO_PRODUCTION_LINE.PRODUCTION_LINE,

      SOORDER_HD.SEQ_NO,
      SOORDER_HD.BILL_NAME,

      PCORDER_HD.QTY_REQ,
      PCORDER_HD.QTY_PRODUCED,
      ZZVITTO_WZ_CAFE.ITEM_CAFE,
      [ITEM_DESC_1].DESCR AS COFEEDESC
FROM    
      SOORDER_HD INNER JOIN PCORDER_HD ON SOORDER_HD.SEQ_NO = PCORDER_HD.SRC_SEQ_NO
--this is a funny looking join of ITEM then ITEM_DESC, you probably want to look at this too
      INNER JOIN ITEM INNER JOIN ITEM_DESC ON ITEM.ITEM_NO = ITEM_DESC.REF_NO
--this looks weird too
      INNER JOIN ZZVITTO_PRODUCTION_LINE ON ITEM.ITEM_NO = ZZVITTO_PRODUCTION_LINE.ITEM_NO ON PCORDER_HD.ORDER_NO = ZZVITTO_PRODUCTION_LINE.ORDER_NO
      
      INNER JOIN ZZVITTO_WZ_CAFE ON ITEM.ITEM_NO = ZZVITTO_WZ_CAFE.ITEM_NO
      INNER JOIN ITEM [ITEM_1] ON ZZVITTO_WZ_CAFE.ITEM_CAFE = [ITEM_1].ITEM_NO
      INNER JOIN ITEM_DESC [ITEM_DESC_1] ON ZZVITTO_WZ_CAFE.ITEM_CAFE = [ITEM_DESC_1].REF_NO
WHERE
       (ITEM_DESC.LANG_NO = 'F') AND ([ITEM_DESC_1].LANG_NO = 'F')
0
 
LVL 2

Accepted Solution

by:
RayAtVittoria earned 0 total points
ID: 20051094
I figured it out... via http://www.dreamincode.net/forums/showtopic35033.htm

There is actually nothing wrong with the Query. It is a known bug in Visual Studio. I have requested the hotfix from Microsoft.
KB919095

Thanks Anyway.
0
 
LVL 1

Expert Comment

by:modus_operandi
ID: 20077326
Closed, 500 points refunded.
modus_operandi
Community Support Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question