[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

problems with data adapter configuration wizard in C#

Posted on 2003-11-01
14
Medium Priority
?
355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
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.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

650 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