locked
Problem with MySQL Syntax for multiple tables in a single dataset RRS feed

  • Question

  • User903913242 posted

    Hi All,

     I am trying to return 3 datatables on in one dataset, using mysql as my database.  However I am getting a syntax error when I execute this code:

    // Get Connection String and initialize Connection
    string connString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
    OdbcConnection conn = new OdbcConnection(connString);

    // Create a CommandObject
    OdbcCommand cmd = new OdbcCommand();
    cmd.CommandType =
    CommandType.Text;
    cmd.Connection = conn;
    cmd.CommandText = "SELECT * FROM Table1 Where Var1 = ?; SELECT a.* FROM Table2 a INNER JOIN Table1 b on b.ID = a.ID WHERE b.Var2 = ?; SELECT * FROM Table3";
    cmd.CommandTimeout = 120;

    OdbcParameter parm = cmd.Parameters.Add("Var1", OdbcType.VarChar);
    parm.Value = "SomeVar";
    parm = cmd.Parameters.Add(
    "Var2", OdbcType.VarChar);
    parm.Value = "SomeOtherVar";

    // Use a DataAdapter to finish up
    OdbcDataAdapter da = new OdbcDataAdapter(cmd);
    da.TableMappings.Add(
    "Table0", "Athletes");
    da.TableMappings.Add(
    "Table1", "AthleteBio");
    da.TableMappings.Add(
    "Table2", "AthleteClass");
    AthleteWithChildren ds = new AthleteWithChildren();
    da.Fill(ds);

    Here is the exception I get:

    Exception Details: System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.41-community-nt]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 '; SELECT a.* FROM Table2 a INNER JOIN Table1 b on b.ID = a.ID WHERE ' at line 1

    Source Error:

    Line 373:            da.TableMappings.Add("Table2", "AthleteClass");
    Line 374:            AthleteWithChildren ds = new AthleteWithChildren();
    Line 375:            da.Fill(ds);
    Line 376:            return ds;
    Line 377:            

    I have tested out the MySQL commands and they work just fine in the MySQL Query Browser one at a time, but my syntax for selecting multiple tables doesn't seem to be working, even though the research I have done so far leads be to believe that I have to put a ';' inbetween the multiple select statements. 

     Does anyone know how to return multiple tables in one command using MySQL 5.1.11?

    Thanks,
    Brent

    Friday, October 19, 2007 3:18 PM

All replies

  • User-1052551871 posted

    U shuld use in your example 3 OdbcCommand because in MYSQL u can not use multy record reader like in MS sql

    So

    Friday, October 19, 2007 7:20 PM
  • User903913242 posted
    I got it to work doing this:

    // Get Connection String and initialize Connection
    string connString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
    OdbcConnection conn = new OdbcConnection(connString);

    // Create a CommandObject
    OdbcCommand cmd = new OdbcCommand();
    cmd.CommandType =
    CommandType.Text;
    cmd.Connection = conn;
    cmd.CommandText = Query1
    ;
    cmd.CommandTimeout = 120;

    OdbcParameter parm = cmd.Parameters.Add("Var1", OdbcType.VarChar);
    parm.Value = Var1;

    // Use a DataAdapter to finish up
    OdbcDataAdapter da = new OdbcDataAdapter(cmd);
    da.TableMappings.Add(
    "Table", "Athletes");
    AthleteWithChildren ds = new AthleteWithChildren();
    da.Fill(ds);

    // Table 2
    cmd.CommandText = Query2;

    parm = cmd.Parameters.Add("Var2", OdbcType.VarChar);
    parm.Value = Var2;

    // Use a DataAdapter to finish up
    da = new OdbcDataAdapter(cmd);
    da.TableMappings.Add(
    "Table", "AthleteBIO");
    da.Fill(ds);

    // Table 3
    cmd.CommandText = Query3;

    // Use a DataAdapter to finish up
    da = new OdbcDataAdapter(cmd);
    da.TableMappings.Add(
    "Table", "AthleteClass");
    da.Fill(ds);

    This method gets the end result that I want of all three tables being in my dataset...But it makes three calls to the Database, is there a better way?

    Thanks,
    Brent

    Friday, October 19, 2007 11:43 PM
  • User199727229 posted

    Hello

    Do you mean this ?

    i can't retrive  data  from multiple select statmenets ?

    I am using ODBC data reader, and in my proceure i have 3 select statemnts but unable to read the data frrom 2 & 3 select statements .

    can i know the reason behind that ? can you please respond me ASAP

    Sunday, June 17, 2012 4:45 PM