I'm sure there are better answers than this one, however I'll lend a hand whenever I can. If you're using the data controls then someone else will have to lend a hand, but here's what I do (it entails no guesswork on the part of the programmer). Open a database connection and keep it open, assign the select query to a string then use the .exec method of the connection to pull the resultset back. Iterate through the result set and populate the form. The following is a code snippet from a form with 2 buttons on it, "ConnectToDB" and "ExecuteQuery" (creative huh?) There's no error checking on the database open (this is quick & dirty), exercise error checking on the db open and such, but you probably already know that.
' Gambas class file PUBLIC mDBConnection AS NEW Connection PUBLIC SUB ConnectDB_Click() IF NOT EstablishDatabaseConnection() THEN Message ("Unable to open Database","OK") ExecuteQuery.Enabled = False ELSE ExecuteQuery.Enabled = True END IF END PUBLIC SUB ExecuteQuery_Click() DIM SQLString AS String DIM RecordSet AS Result SQLString = "SELECT Quantity, "& "Price, " & "(Quantity * Price) AS SubTotal, " & "Description " & "FROM Components " & "ORDER BY Quantity, Price, SubTotal;" RecordSet = mDBConnection(SQLString) If Not IsEmpty(RecordSet) Then ' Whatever you do here is up to you, you have the Result, the values are there. ' Access them like this; ' [Object].Text = RecordSet["Quantity"] ' [Object].Text = RecordSet["Price"] ' [Object].Text = RecordSet["SubTotal"] ' [Object].Text = RecordSet["Description"] End IF END PRIVATE FUNCTION EstablishDatabaseConnection() AS Boolean DIM Success AS Boolean Success = False WITH mDBConnection .Type = "mysql" .Host = "192.168.1.55" ' Insert the IP of your server here .User = "dbuser" ' Insert your user here .Password = "dbpassword" ' Insert your password here .Name = "dbName" ' Insert the name of the db here TRY .Open IF NOT Error THEN Success = TRUE END IF END WITH RETURN (Success) END '*********************************************************** ' I never code multiple RETURNs in a function, or exit points ' in a SUB, there can be only one. ************************************************************ PUBLIC FUNCTION IsEmpty(pRecordSet AS Result) AS Boolean DIM Empty AS Boolean Empty = False ' I'm cynical. In this case False means the RecordSet is NOT empty (a good thing) ' This could also be written ' IF Not IsNull(pRecordSet) AND pRecordSet.Count = 0 THEN Empty = True IF Not IsNull(pRecordset) THEN IF pRecordset.Count = 0 then Empty = TRUE END IF END IF RETURN (Empty) END On 05/25/2015 03:57 AM, William Chapman wrote: > Hi Everyone! > > My first post to the forum and my first project! > > Being new to Gambas, I have got stuck on trying to use Gambas to create a > front-end for a MySQL database created with MySQL Workbench. I have trawled > the Internet without much success for an answer. > > The application that I am working on requires calculated fields. Using > MySQL > workbench the calculated field has been produced by using a MySQL query. > > For example: > > select quantity, price, (quantity * price) as subtotal > from components > order by quantity, price, subtotal desc > > Following a Youtube video showing how to "import" data held in a MySQL > database into a Gambas front-end, the form is constructed using the > DataSource and the DataBrowser options from the Toolbox. > > While the fields of "price" and "quantity" are shown, being obtained from > the > table "components", I have not been able to figure out how to source the > calculated field i.e. quantity * price and its value (as obtained via the > SQL > query) in order to place it into the Gambas front end. > > e.g:- > > amount price subtotal > 10 50 500 > > Any possible suggestions or reference to an available coding example would > be > greatly appreciated. > > Regards > William > Cape Town > South Africa > ------------------------------------------------------------------------------ > One dashboard for servers and applications across Physical-Virtual-Cloud > Widest out-of-the-box monitoring support with 50+ applications > Performance metrics, stats and reports that give you Actionable Insights > Deep dive visibility with transaction tracing using APM Insight. > http://ad.doubleclick.net/ddm/clk/290420510;117567292;y > _______________________________________________ > Gambas-user mailing list > Gambas-user@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/gambas-user > -- Kindest Regards Stephen A. Bungay, Prop. Smarts On Site Information Systems ------------------------------------------------------------------------------ One dashboard for servers and applications across Physical-Virtual-Cloud Widest out-of-the-box monitoring support with 50+ applications Performance metrics, stats and reports that give you Actionable Insights Deep dive visibility with transaction tracing using APM Insight. http://ad.doubleclick.net/ddm/clk/290420510;117567292;y _______________________________________________ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user