New topic: A little help with an update error on mysql
<http://forums.realsoftware.com/viewtopic.php?t=46834> Page 1 of 1 [ 8 posts ] Previous topic | Next topic Author Message HMARROQUINC Post subject: A little help with an update error on mysqlPosted: Tue Feb 05, 2013 11:26 pm Joined: Sun Jan 25, 2009 5:11 pm Posts: 413 Location: Guatemala, Central America I usually don't have this type of mental block but I can't figure out this one: When calling the update method of a class I created I get the following error: 9000 - The RecordSet cannot be edited because the uniqueness of its rows cannot be verified error on this code. I'm pretty sure I'm only returning one record and that the record is the correct one. I'm not updating the ID Key and I do not have set any rows to not accept duplicates, except for the key row of course. dim s as string dim rs as RecordSet dim se as string s = "SELECT * FROM calls WHERE call_id = " + cstr(CallID) + " ;" // Checked this one in the debug window and the sql is OK rs = db.SQLSelect(s) if db.Error then msgbox "Se encontro un error en la aplicación. " + EndOfLine + _ "cCall - Method: Update - " + str(db.ErrorCode) + " - " + db.ErrorMessage return false end if if rs = nil then MsgBox "Error al cargar la información del Evento." + EndOfLine + _ "cCall - Method: Update" return false end if if rs.EOF = true then MsgBox "No se encontro el Evento en la Base de Datos y fue imposible actualizarlo." + EndOfLine + _ "cCall - Method: Update" return false end if rs.Edit if db.Error then se = "Error al actualizar la información. " + EndOfLine + _ "cCall - Method: Update " + EndOfLine +_ CStr(db.ErrorCode) + " - " + db.ErrorMessage wEventDetails.txtErrorMessage.Text = se + " From rs.Edit" return false end if rs.Field("call_action").StringValue = me.action rs.Field("call_date").DateValue = me.CallDate rs.Field("call_to").IntegerValue = me.CallTo rs.Field("customer_id").IntegerValue = me.CustomerID rs.Field("call_description").StringValue = me.Description rs.Field("call_enteredby").IntegerValue = me.EnteredBy rs.Field("call_lastsavedby").IntegerValue = me.LastSavedBy rs.Field("call_lastsaveddate").DateValue = me.LastSavedDate rs.Field("call_resolved").IntegerValue = me.Resolved rs.Field("call_resolveddate").DateValue = me.ResolvedDate rs.Field("call_type").StringValue = me.Type + "" rs.Update if db.Error then dim se as string se = "Error al actualizar la información. " + EndOfLine + _ "cCall - Method: Update " + EndOfLine +_ CStr(db.ErrorCode) + " - " + db.ErrorMessage wEventDetails.txtErrorMessage.Text = se return false end if db.Commit return true Sorry about the spanglish code, it's just a habit of mine. Hector _________________ Future RS guru. Ride the world! Top timhare Post subject: Re: A little help with an update error on mysqlPosted: Wed Feb 06, 2013 12:18 am Joined: Fri Jan 06, 2006 3:21 pm Posts: 12051 Location: Portland, OR USA rs.Edit/Update requires the primary key value to be present in the recordset. Since you select "*", that would indicate that you don't have an integer autoincrement primary key on this table. So you would have to get the ROWID along with * (* does not include the rowid, you have to ask for it explicitly). I would recommend adding an autoincrement primary key to the table, however. Top HMARROQUINC Post subject: Re: A little help with an update error on mysqlPosted: Wed Feb 06, 2013 12:33 am Joined: Sun Jan 25, 2009 5:11 pm Posts: 413 Location: Guatemala, Central America Thanks Tim I was knocking my head against the wall over this one. I do have a primary key CREATE TABLE `calls` ( `call_id` int(11) NOT NULL AUTO_INCREMENT, `call_date` date NOT NULL, `call_description` longtext COLLATE utf8_bin NOT NULL, `call_action` longtext COLLATE utf8_bin NOT NULL, `call_to` int(11) NOT NULL, `customer_id` int(11) NOT NULL, `call_enteredby` int(11) NOT NULL, `call_lastsavedby` int(11) NOT NULL, `call_lastsaveddate` date NOT NULL, `call_resolved` tinyint(4) NOT NULL, `call_resolveddate` date NOT NULL, `call_type` mediumtext COLLATE utf8_bin NOT NULL, KEY `call_id` (`call_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin$$ Since I got frustrated I was typing a SQL UPDATE statement but I'm not one to give up so I'll test what you suggest and report back. _________________ Future RS guru. Ride the world! Top timhare Post subject: Re: A little help with an update error on mysqlPosted: Wed Feb 06, 2013 12:47 am Joined: Fri Jan 06, 2006 3:21 pm Posts: 12051 Location: Portland, OR USA You have an autoincrement field, but I don't think you're creating a primary key. Don't know innodb well enough to be sure. Top HMARROQUINC Post subject: Re: A little help with an update error on mysqlPosted: Wed Feb 06, 2013 1:01 am Joined: Sun Jan 25, 2009 5:11 pm Posts: 413 Location: Guatemala, Central America Even when I explicitly specify the columns I get the same 9000 error. s = "SELECT call_id, call_date, call_description, call_action, call_to, customer_id, call_enteredby, call_lastsavedby, " + _ "call_lastsaveddate, call_resolved, call_resolveddate, call_type FROM calls WHERE call_id = " + cstr(CallID) + " ;" rs = db.SQLSelect(s) ... The rest of the code Same 9000 error. SQL UPDATE works as expected s = "UPDATE calls " s = s + "SET call_description = '" + me.Description + "' " s = s + ", call_action = '" + me.action + "' " s = s + ", call_date = '" + me.CallDate.SQLDate + "' " s = s + ", call_to = " + cstr(me.CallTo) + " " s = s + ", customer_id = " + cstr(me.CustomerID) + " " s= s + ", call_description = '" + me.Description + "' " s = s + ", call_enteredby = " + CStr(me.EnteredBy) + " " s = s + ", call_lastsavedby = " + CStr(me.LastSavedBy) + " " s = s + ", call_lastsaveddate = '" + me.LastSavedDate.SQLDate + "' " s = s + ", call_resolved = " + CStr(me.Resolved) + " " s = s + ", call_resolveddate = '" + me.ResolvedDate.SQLDate + "' " s = s + ", call_type = '" + me.Type + "' " s = s + " WHERE call_id = '" + cstr(me.CallID) + "' ;" db.SQLExecute(s) if db.error then MsgBox "Error al guardar los datos del evento " + EndOfLine + _ "cCall - Method: Update " + EndOfLine + _ CStr(db.ErrorCode) + " - " + db.ErrorMessage return false end if return true Now that I have this working, I'll investigate why the rs.edit is NOT working. Weird. _________________ Future RS guru. Ride the world! Top HMARROQUINC Post subject: Re: A little help with an update error on mysqlPosted: Wed Feb 06, 2013 1:02 am Joined: Sun Jan 25, 2009 5:11 pm Posts: 413 Location: Guatemala, Central America timhare wrote:You have an autoincrement field, but I don't think you're creating a primary key. Don't know innodb well enough to be sure. This should create the key: KEY `call_id` (`call_id`). But I'll double check Thanks! _________________ Future RS guru. Ride the world! Top HMARROQUINC Post subject: Re: A little help with an update error on mysqlPosted: Wed Feb 06, 2013 1:08 am Joined: Sun Jan 25, 2009 5:11 pm Posts: 413 Location: Guatemala, Central America doh! Now I feel dumb.... CREATE TABLE `calls` ( `call_id` int(11) NOT NULL AUTO_INCREMENT, `call_date` date NOT NULL, `call_description` longtext COLLATE utf8_bin NOT NULL, `call_action` longtext COLLATE utf8_bin NOT NULL, `call_to` int(11) NOT NULL, `customer_id` int(11) NOT NULL, `call_enteredby` int(11) NOT NULL, `call_lastsavedby` int(11) NOT NULL, `call_lastsaveddate` date NOT NULL, `call_resolved` tinyint(4) NOT NULL, `call_resolveddate` date NOT NULL, `call_type` mediumtext COLLATE utf8_bin NOT NULL, PRIMARY KEY (`call_id`), // << THIS IS HOW I SHOULD CREATE A KEY!!! FORGOT TO ADD "PRIMARY" :-) KEY `call_id` (`call_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin$$ Now it works perfectly. Thanks for pointing that out Tim, I thought I had the key but didn't double check until you pointed it out. I blame it on the amarula Hector _________________ Future RS guru. Ride the world! Top HMARROQUINC Post subject: Re: A little help with an update error on mysqlPosted: Wed Feb 06, 2013 1:19 am Joined: Sun Jan 25, 2009 5:11 pm Posts: 413 Location: Guatemala, Central America Hmmm I just realized I hit my 400 post mark and of course it had to be a stupid question LOL! Anyways... Thanks again Tim! _________________ Future RS guru. Ride the world! Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 8 posts ]
-- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
