?
Solved

problems with data adapter configuration wizard in C#

Posted on 2003-11-01
14
Medium Priority
?
358 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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 800 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

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

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
If you are looking for an automated solution for backup single or multiple Office 365 user mailboxes to Outlook data file, then you can use Kernel Office 365 Backup & Restore tool. Go through the video to check out the steps to backup single or mult…

599 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