SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple different select statement in a for loop.

    Dear All,
    I got a list of product where based on a datareader I run a for loop. The based on each of the productID i do a number of select statement from various table around 24 different types of selects. So at some times my code give me an error saying that "unable to connect to any of the specified mysql hosts. So below is how my connection query looks like. I have tried to change to pooling=true the effect is quite the same. The error comes on a random basis sometimes during runing the 13th statement or 17th statement etc. So any solution for this type of multiples queries? I have 2 separate connection query one for the outler loop and one inner loop for each of the select statement once done I close and open the connection. Thanks.

    public globalConnectionLocal2()
    {
    this.connection1 = new MySqlConnection("Address='localhost';Database='db1';User Name='root';Password='12345';Pooling='false'");
    this.command = this.connection1.CreateCommand();
    this.connection1.Open();
    }

    // destructor - explicitly
    ~globalConnectionLocal2()
    {
    this.command.Dispose();
    this.connection1.Close();
    this.connection1.Dispose();
    }

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you're opening a new connection for every query?

    and you're running a query in a loop?

    two strikes against you there.

    why not use a single query with joins and unions?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    not just "a" query in a loop, but 24 of them

    i'd want to have a look at why there are 24 different tables that need to have thier data brought together to return significant information for a product
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    that, too.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Longneck,
    Ok let me explain why it comes out to so many first I divide into type of product cause I have different productType example serial based and quantity based. Then I do accordingly a search for all those tables which have transactions linked with serial based. So I repeat the same for quantity based then. Then I have a date selected. Based on the selected date I will try to get the quantity sold, return, transfer before the date. Then another query is for the the quantity between the 2 dates. So I guess now you know why I need many different queries is it ? Thanks.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i really hesitate to ask this, because i have a feeling it may take the conversation into a direction you don't want it to go, but could you please do a SHOW CREATE TABLE for your product table, and then again for a couple of the other related tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Longneck,
    Below is 3 of my tables.

    CREATE TABLE `tblproduct` (
    `productID` int(5) NOT NULL auto_increment,
    `productName` varchar(100) NOT NULL,
    `productPrice` double(10,2) NOT NULL default '0.00',
    `productType` enum('Serial','Quantity') NOT NULL,
    `productTotalStock` int(10) NOT NULL default '0',
    `productTotalAmount` double(10,2) NOT NULL default '0.00',
    `productBooked` int(10) NOT NULL default '0',
    `productStatus` enum('y','n') NOT NULL default 'y',
    PRIMARY KEY (`productID`),
    UNIQUE KEY `productName` (`productName`),
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    CREATE TABLE `tbltransferdetails` (
    `transferDetailsID` int(10) NOT NULL auto_increment,
    `transferID` int(10) NOT NULL default '0',
    `outletID` int(2) NOT NULL default '0',
    `stockID` int(10) default NULL,
    `productID` int(5) NOT NULL default '0',
    `productType` enum('Serial','Quantity') NOT NULL,
    `stockQuantity` int(10) NOT NULL,
    `stockSIQ` varchar(20) NOT NULL,

    PRIMARY KEY (`transferDetailsID`,`transferID`,`outletID`,`stockSIQ`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    CREATE TABLE `tblreceiptdetails` (
    `receiptDetailsID` int(10) NOT NULL auto_increment,
    `receiptID` int(10) NOT NULL default '0',
    `outletID` int(2) NOT NULL default '0',
    `stockID` int(10) NOT NULL,
    `productID` int(10) NOT NULL default '0',
    `productType` enum('Serial','Quantity') NOT NULL default 'Accessory',
    `productQuantity` int(5) NOT NULL,
    `productSIQ` varchar(20) NOT NULL default '0',
    PRIMARY KEY (`receiptDetailsID`,`receiptID`,`outletID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •