Solved

problems with data adapter configuration wizard in C#

Posted on 2003-11-01
14
350 Views
Last Modified: 2010-04-16
I am trying to create a data adaper using the query builder in C# (I'm new to the language I should add) and am having problems. Here is what the query looks like after I run the query builder:

SELECT
    MediaLocation.*,
    Media.*,
    MediaEvent.*,
    MediaTopic.*,
    MediaType.*
FROM
    Media INNER
JOIN
    MediaEvent ON
        Media.MediaEventID = MediaEvent.MediaEventID INNER JOIN MediaLocation ON Media.MediaLocationID = MediaLocation.MediaLocationID INNER JOIN MediaTopic ON Media.MediaTopicID = MediaTopic.MediaTopicID INNER JOIN MediaType ON Media.MediaTypeID = MediaType.MediaTypeID

Here is the error message:

Generated SELECT statement.

Syntax error (missing operator) in query expression 'Media.MediaEventID = MediaEvent.MediaEventID INNER JOIN MediaLocation ON Media.MediaLocationID = MediaLocation.MediaLocationID INNER JOIN MediaTopic ON Media.MediaTopicID = MediaTopic.MediaTopicID INNER JOIN MediaType ON Media.MediaTypeID = MediaType.Medi'.

what am I doing wrong? I know it is probably something simple since I'm just a beginner in C#. I thought maybe someone could help. thanks

guid
0
Comment
Question by:guidway
14 Comments
 
LVL 4

Expert Comment

by:wile_e_coyote
ID: 9663697
Just a guess, but it looks as if your query is being truncated - the error message doesn't display the entire query.  
Try using table aliases to reduce the length of the query, for example

SELECT   ml.*,  m.*, me.*, mt.*, my.*
FROM  Media m INNER JOIN MediaEvent me ON  m.MediaEventID = me.MediaEventID
INNER JOIN MediaLocation ml ON m.MediaLocationID = ml.MediaLocationID
INNER JOIN MediaTopic mt ON m.MediaTopicID = mt.MediaTopicID
INNER JOIN MediaType my ON m.MediaTypeID = my.MediaTypeID

0
 
LVL 12

Author Comment

by:guidway
ID: 9664872
hi wile_e_coyote,

I tried that and also received the same thing. I'm thinking after reading a little more on the inner join statement that it may need to be nested. I wish I knew SQL a little more though to know if that was the problem or not. :o( I'll post a pointer link to this question from Access just in case someone else sees something outright obvious. thanks for your help.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9664920
which database system are you trying to run this against?
0
Independent Software Vendors: 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 12

Author Comment

by:guidway
ID: 9664981
MS Access 2000. What's strange is that if I try to create my query in Access first and move it over to the query interface it looks like this:

SELECT mt.*, mty.*, ml.*, m.*, me.*
FROM MediaType AS mty INNER JOIN (MediaTopic AS mt INNER JOIN (MediaLocation AS ml INNER JOIN (Media AS m INNER JOIN MediaEvent AS me ON m.MediaEventID=me.MediaEventID) ON ml.MediaLocationID=m.MediaLocationID) ON mt.MediaTopicID=m.MediaTopicID) ON mty.MediaTypeID=m.MediaTypeID

which works in the interface and creates the adapter but when I try to use it to extract the data it says there are syntax errors. I can't see why it would be so hard to set up an adapter and dataset. There must be an easier way and I'm making things to complicated. All I want to do is read my tables and store them in a dataset so I can change/add records,etc... thanks for the help

guid
0
 
LVL 12

Author Comment

by:guidway
ID: 9664986
I did remove the semicolon from the end of the statement because it appears the query builder/data adapter does not like the semicolon. I know Access usually requires one.
0
 
LVL 12

Author Comment

by:guidway
ID: 9665066
what's strange is that I just created a query from scratch that uses nested Inner Joins and works perfectly in Access and creates the data adapter with no problem but when I try to create the dataset it removes all the parenthesis and says there are syntax errors. My current query I'm using is:

SELECT
    m.MediaID,
    m.MediaNum,
    m.MediaSpeaker,
    m.MediaTitle,
    m.MediaDate,
    m.MediaComments,
    m.MediaEventID,
    m.MediaTopicID,
    m.MediaTypeID,
    m.MediaLocationID
   
FROM
    ((((Media m INNER JOIN MediaTopic mt On m.MediaTopicID = mt.MediaTopicID) INNER JOIN MediaType mty On m.MediaTypeID = mty.MediaTypeID) INNER JOIN MediaEvent me On m.MediaEventID = me.MediaEventID) INNER JOIN MediaLocation ml On m.MediaLocationID = ml.MediaLocationID)
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9665094
does your dataadapter work if you just select from media?
0
 
LVL 12

Author Comment

by:guidway
ID: 9665117
I just tried using select * from media and it created the adapter and generated the dataset also. strange that it is having problems with the query above...
0
 
LVL 12

Author Comment

by:guidway
ID: 9665130
the error I get in the dataset (when I attempt to create it) goes something like

retrieving the schema for OleDbDataAdapter1 failed

syntax error (missing operator) in query expression 'm.MediaTopicID = mt.MediaTopicID INNER JOIN MediaType mty On m.MediaTypeID = mty.MediaTypeID INNER JOIN MediaEvent me On m.MediaEventID = me.MediaEventID INNER JOIN MediaLocation ml On m.MediaLocationID = ml.MediaLocationID'.
0
 
LVL 5

Expert Comment

by:tgannetts
ID: 9665378
Try using this:

SELECT Media.*, MediaEvent.*, MediaLocation.*, MediaTopic.*, MediaType.*
FROM (((Media INNER JOIN MediaEvent ON [Media].[MediaEventID]=[MediaEvent].[MediaEventID]) INNER JOIN MediaLocation ON [Media].[MediaLocationID]=[MediaLocation].[MediaLocationID]) INNER JOIN MediaTopic ON [Media].[MediaTopicID]=[MediaTopic].[MediaTopicID]) INNER JOIN MediaType ON [Media].[MediaTypeID]=[MediaType].[MediaTypeID];

Tom.
0
 
LVL 12

Author Comment

by:guidway
ID: 9666551
Hi Tom,

I tried that also and got the same error. Could it be that because I'm trying to load multiple tables into the dataset it is having problems? Do you think I need to do something like this: http://www.c-sharpcorner.com/FAQ/MultiTablesViewData.asp ?

I'm tempted to try it since I'm not sure if what I'm doing will work for multiple tables anyway. thanks again everyone
0
 
LVL 2

Accepted Solution

by:
sedmans earned 200 total points
ID: 9672217
MS Access does not like the first query as for some reason it needs the nesting.  The Visual Studio data adapter wizard changes the select statement so that it does not include the nesting.

What you need to do is look in the code page and change the CommandText property to something like

this.oleDbSelectCommand1.CommandText = @"SELECT Media.*, MediaEvent.*, MediaLocation.*, MediaTopic.*, MediaType.* FROM MediaType INNER JOIN (MediaTopic INNER JOIN (MediaLocation INNER JOIN (MediaEvent INNER JOIN Media ON MediaEvent.MediaEventID=Media.MediaEventID) ON MediaLocation.MediaLocationID=Media.MediaLocationID) ON MediaTopic.MediaTopicID=Media.MediaTopicID) ON MediaType.MediaTypeID=Media.MediaTypeID;";

This property will be hidden in the Windows Form Designer generated code region.  Just click on the + on the left to expand it.
0
 
LVL 12

Author Comment

by:guidway
ID: 9672261
thanks sedmans but is that going to affect my ability to graphically view the components on the designer if I alter the statement?

On the other hand I created adapters for each of my tables and merged them all into one dataset and they appear to be working fine. I'll try your code when I get home tonight and see if it works since I would prefer to have only one adapter than 5 different ones. However I might have to stick with 5 if I can't get it working. I'll let you know how it goes...

guid
0
 
LVL 12

Author Comment

by:guidway
ID: 9780695
whoops! forgot to close this one out. It seems sedmans' idea worked so he gets the points. thanks everyone
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 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