MYSql With C# in Multiple Query

Posted on 2004-12-01
Last Modified: 2008-02-01
Halo, the following are my code.

string sqlString = "select * FROM JobInfo WHERE JobNo='135'; select * FROM TraderRelated WHERE JobNo='135';select * FROM DeliveryInfo WHERE JobNo='135'; ";

MySqlDataAdapter Adapter = new MySqlDataAdapter();
MySqlCommand cmdSelect   = new MySqlCommand();

cmdSelect.CommandText = sqlString;
cmdSelect.CommandType = CommandType.Text;
cmdSelect.Connection  = GetConnection;
Adapter.SelectCommand = cmdSelect;

The following coding is work fine with MYSql version 4.0.22, but when i upgrade my MYSql server to Version 4.1, then i'm getting error
[ You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';' at line 1]

Please help....
Question by:carrotchua
    LVL 15

    Accepted Solution

    You have several seperate select commands in your sqlString (separated by ';'. that is not always a legal thing to do. How should mySQL determine how to return a single resultset with rows found on that query.

    Generally I would combine the 3 queries into a single query that acces a join of all 3 tables:

    string sqlString = "select * FROM JobInfo JOIN TraderRelated USING JobNo JOIN DeliveryInfo USING JobNo WHERE JobNo='135'; ";

    You may need to say  JobInfo.JobNo  instead of just JobNo in the WHERE clause, but try without first.

    regards JakobA
    LVL 15

    Expert Comment

    I paste your reply in here as it mistakenly arrived in my personal feedback.

    soory for the late response, I do not check my feedback often. (it is intended for personal feedback, ie complaint if I am abusive, or praise if I manage to be exceptionally helpfull)


          Thanks for ur comment, but i want to fill up  a DataSet with 3 DataTable. This code is work fine in Version 4.0 . It return a dataset with 3 datatable, but it not work in Version 4.1.

          Please advice


    Unfortunately I cannot adwice further as I do not knoe the C# interface to MySQL well enough to comment on version-problems.

    The error you get point to ';' as the problem supporting the original guess, but as you no doubt know, errormessages are not always reliable so It may well be something else.

    regards jakobA

    Featured Post

    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

    Join & Write a Comment

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 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

    18 Experts available now in Live!

    Get 1:1 Help Now