Solved

problems with data adapter configuration wizard in C#

Posted on 2003-11-01
14
346 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Visual Studio 2013 debugging 2 35
Error when loading the database 16 58
Not showing JavaScript in the list 5 40
Error on link 14 39
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…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

867 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

21 Experts available now in Live!

Get 1:1 Help Now