Solved

problems with data adapter configuration wizard in C#

Posted on 2003-11-01
14
345 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
Comment Utility
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
Comment Utility
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
Comment Utility
which database system are you trying to run this against?
0
 
LVL 12

Author Comment

by:guidway
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
does your dataadapter work if you just select from media?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 12

Author Comment

by:guidway
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
whoops! forgot to close this one out. It seems sedmans' idea worked so he gets the points. thanks everyone
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Article by: Ivo
Anonymous Types in C# by Ivo Stoykov Anonymous Types are useful when  we do not need to follow usual work-flow -- creating object of some type, assign some read-only values and then doing something with them. Instead we can encapsulate this read…
Introduction                                                 Was the var keyword really only brought out to shorten your syntax? Or have the VB language guys got their way in C#? What type of variable is it? All will be revealed.   Also called…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now