?
Solved

MYSql With C# in Multiple Query

Posted on 2004-12-01
4
Medium Priority
?
682 Views
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;
Adapter.Fill(ds);

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....
0
Comment
Question by:carrotchua
  • 2
2 Comments
 
LVL 15

Accepted Solution

by:
JakobA earned 500 total points
ID: 12724671
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
0
 
LVL 15

Expert Comment

by:JakobA
ID: 12802350
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
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month12 days, 17 hours left to enroll

578 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