I see spaces in the field 'Initial IC' and 'Actual IC'. If you're using a space in the field name - DONT!!! Use _ underscore instead. Don't use a spaces in any names. You complicate life with them and make you want to pull your hair out.
This should work myString = "SELECT IC.ID, Product.Description " myString.= "FROM IC, Product " myString.= "WHERE IC.ProductID = Product.ProductID " myString.= "ORDER BY IC.ID DESC" rData = ConMyData.Exec(myString) This is not correct: rData = ConMyData.Exec("SELECT &1 FROM IC", "'Actueel IC'") Should be: rData = ConMyData.Exec("SELECT `Actual IC` FROM IC") Again, PLEASE don't use spaces embedded in the field. ---------- Original Message ----------- From: Willy Raets <wi...@earthshipbelgium.be> To: mailing list for gambas users <gambas-user@lists.sourceforge.net> Sent: Wed, 28 Aug 2013 22:18:52 +0200 Subject: [Gambas-user] SQL query for DB.Exec() question > Hello all, > > I've been searching the Gambas mailing list archives and documentation > for a answer to a problem I have. Tried all kinds of suggestions but > haven't solved the problem yet. > > This is the situation simplified: > > A MySql database named MyData with a two tables named IC and Products. > Both tables have a field ProductID with a relation inbetween. > > Table IC has fields > ID -> Primary Key, Indexed > ProductID -> Indexed > Initial IC > Actual IC > ValueInitial > ValueActual > > Table Product has fields: > ProductID -> Primary Key, Indexed > Description > > In a query I need to retrieve all fields in IC except ProductID and > Description in Products > I manage to get the query done except for the fields Initial IC and > Actual IC (because of the blanc in the field name). > > As I understand in MySql I need to query: > SELECT 'Actual IC' FROM IC to get Actual IC > > I have tried it like this in Gambas (rData being a Result and ConMyData > being the connection) > > rData = ConMyData.Exec("SELECT &1 FROM IC", "'Actueel IC'") > > But the syntax is not accepted by MySql > > To make the matter more complicated when querying more than one table > you need to add the table to the query syntax like this > SELECT IC.ID, Product.Description FROM IC LEFT JOIN Product ON > IC.ProductID=Product.ProductID ORDER BY IC.ID DESC > > Or in Gambas: > > rData = ConMyData.Exec(SELECT IC.ID, Product.Description FROM IC LEFT > JOIN Product ON IC.ProductID=Product.ProductID ORDER BY IC.ID DESC) > > This works perfect, but when I want to add field IC.Actual IC to that > query in a db.Exec() what is the proper format? > I tried with: > IC.'Actual IC' > 'IC'.'Actual IC' > 'IC.Actual IC' > They all give back syntax errors from MySql > > Any suggestions welcome > > -- > Kind regards, > > Willy (aka gbWilly) > > http://gambasshowcase.org/ > http://howtogambas.org > http://gambos.org > > ------------------------------------------------------------------------------ > Learn the latest--Visual Studio 2012, SharePoint 2013, SQL 2012, more! > Discover the easy way to master current and previous Microsoft technologies > and advance your career. Get an incredible 1,500+ hours of step-by-step > tutorial videos with LearnDevNow. Subscribe today and save! > http://pubads.g.doubleclick.net/gampad/clk?id=58040911&iu=/4140/ostg.clktrk > _______________________________________________ > Gambas-user mailing list > Gambas-user@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/gambas-user ------- End of Original Message ------- ------------------------------------------------------------------------------ Learn the latest--Visual Studio 2012, SharePoint 2013, SQL 2012, more! Discover the easy way to master current and previous Microsoft technologies and advance your career. Get an incredible 1,500+ hours of step-by-step tutorial videos with LearnDevNow. Subscribe today and save! http://pubads.g.doubleclick.net/gampad/clk?id=58040911&iu=/4140/ostg.clktrk _______________________________________________ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user