Hi All, In a bigger project where creating, editing and deleting records works as a charm, only one table is giving me trouble and just won't delete a record with reason 'No primary key'.
Now the table in question 'ICzakgewichten' was migrated from an Access database to MySql and in Access it was no problem to delete a record from a Windows VB client. I'm in the process, next to the existing Windows VB-clients, writing Linux Gambas-clients. Hence the move of data from Access to MySql. The VB-clients get connected to the tables in MySql and will co-exist with the Gambas written clients for a while, until migration complete on all client PCs. The table has a one to many releation (being at the many side) with a 'mother' table 'IC' that holds (amongst others) following relevant fields: | ID | .... | NumItems | .... ID is a primary key The table ICzakgewichten looks like this: | ID | Gewicht | Note: gewicht (Dutch) means weight. Content in 'ICzakgewicht' can be like this: | ID | Gewicht | 345 100 345 100 345 50 347 200 347 200 347 200 347 200 In IC the records would look like: | ID | .... | NumItems | .... 345 ..... 3 347 ..... 4 NumItems holds the number of corresponding records in ICzakgewicht. Now along the road the NumItems decrements until it reaches 0. Corresponding records in ICzakgewicht need to be deleted as well. And this is where stuff won't work, as I am not allowed to delete the records because of no primary key in tabe 'ICzakgewicht'. I could add a primary key, but that would mean adapting the VB-clients to the new table format and I don't really want to go there as they are on their way out. I have created a small example project and exported 'ICzakgewichten' from MySql. To make example work you need: 1. A working MySql server 2. A empty database named 'test' 3. A user account and password to the database In database test import the added 'ICzakgewichten.sql' It was exported from MySql Server version: 5.5.31 In the Gambas added example, change user and password (and all else that might be needed) to meet your needs. You will find the code in following procedure: Public Procedure Connect() MyConn.Close() MyConn.Type = "MySQL" MyConn.Host = "127.0.0.1" MyConn.Login = "willy" '<- change to your situation MyConn.Port = "3306" MyConn.Name = "test" MyConn.Password = "9876" '<- change to your situation MyConn.Open() If Error Then Message("Can't open database: " & Error.Text) Return Endif End Next run the application, select an ID in the combobox and add a corresponding weight (in example no check if weight exist in table, in real project there is, so pick one that exists!). Next click button 'Delete' to get the error. My question, how to prevent this error from happening, preferably without having to add a primary key to the table? -- Kind regards, Willy (aka gbWilly) http://gambasshowcase.org/ http://howtogambas.org http://gambos.org
-- phpMyAdmin SQL Dump -- version 3.4.11.1deb2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 02, 2013 at 07:28 PM -- Server version: 5.5.31 -- PHP Version: 5.4.4-14+deb7u4 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `test` -- -- -------------------------------------------------------- -- -- Table structure for table `ICzakgewichten` -- CREATE TABLE IF NOT EXISTS `ICzakgewichten` ( `ID` int(11) DEFAULT NULL, `Gewicht` decimal(18,2) DEFAULT NULL, KEY `ID` (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `ICzakgewichten` -- INSERT INTO `ICzakgewichten` (`ID`, `Gewicht`) VALUES (662, 100.00), (662, 100.00), (662, 100.00), (645, 500.00), (645, 500.00), (645, 500.00), (645, 500.00), (645, 500.00), (645, 322.00), (645, 300.00), (647, 250.00), (647, 254.00), (648, 100.00), (648, 100.00), (648, 100.00), (648, 100.00), (648, 100.00), (648, 100.00), (648, 100.00), (648, 100.00), (648, 100.00), (648, 100.00), (648, 100.00), (649, 100.00), (649, 100.00), (649, 100.00), (649, 100.00), (649, 100.00), (649, 100.00), (649, 100.00), (649, 100.00), (649, 100.00), (649, 100.00), (650, 500.00), (650, 500.00), (650, 500.00), (650, 500.00), (650, 500.00), (651, 500.00), (651, 250.00), (651, 233.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (652, 100.00), (653, 100.00), (653, 100.00), (653, 100.00), (653, 100.00), (653, 100.00), (653, 100.00), (653, 100.00), (653, 100.00), (653, 100.00), (653, 100.00), (656, 100.00), (656, 100.00), (656, 100.00), (656, 100.00), (656, 100.00), (656, 100.00), (656, 100.00), (656, 100.00), (656, 100.00), (656, 100.00), (656, 100.00), (656, 100.00), (656, 100.00), (656, 100.00), (656, 100.00), (656, 100.00), (657, 200.00), (657, 200.00), (657, 200.00), (657, 200.00), (657, 100.00), (657, 100.00), (659, 500.00), (659, 500.00), (659, 500.00), (659, 500.00), (659, 300.00), (659, 200.00), (661, 200.00), (662, 100.00), (662, 100.00), (662, 100.00), (662, 100.00), (662, 100.00), (662, 100.00), (662, 100.00), (663, 500.00), (663, 500.00), (664, 200.00), (664, 200.00), (664, 200.00), (664, 200.00), (664, 200.00), (666, 500.00), (666, 500.00), (666, 500.00), (683, 100.00), (683, 100.00), (684, 100.00), (684, 100.00), (684, 100.00), (684, 100.00), (684, 100.00), (685, 500.00), (685, 500.00), (686, 100.00), (686, 100.00), (686, 100.00), (686, 100.00), (686, 100.00), (687, 500.00), (688, 100.00), (688, 100.00), (688, 100.00), (689, 100.00), (689, 100.00), (690, 100.00), (690, 50.00), (691, 500.00); /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
DatabaseEditProblem-0.0.1.tar.gz
Description: application/compressed-tar
------------------------------------------------------------------------------ October Webinars: Code for Performance Free Intel webinars can help you accelerate application performance. Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from the latest Intel processors and coprocessors. See abstracts and register > http://pubads.g.doubleclick.net/gampad/clk?id=60134791&iu=/4140/ostg.clktrk
_______________________________________________ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user