sqlrecord_set problem.

[trevordevore]

[This post is an archive from the old SQL Yoga forums]

Hey, so far SQLyoga has been working out great (once i figured out how to read the errors it gives!).

But, I am trying to update a record and I am getting this error. I am not sure what this one means. It doesn't generate anything in the query logger beyond the sqlrecord_find call.

619,0,0,you must assign a valid table to a record

619,0,0,sqlrecord_set (5373)

619,0,0,stack "C:/Rev/FOM/components/sql_yoga.rev"

619,296,0,CommitUpdates

619,296,0,card id 1002 of stack "Edit Fields" of stack "C:/Rev/FOM/components/LeaseStack.rev"

619,5,0,mouseUp

353,5,0,button id 1018 of card id 1002 of stack "Edit Fields" of stack "C:/Rev/FOM/components/LeaseStack.rev"

Code: Select all

on CommitUpdates pID

global gUFieldName, gUAcresGIS, gUAcresCrops, gUCounty, gUFSANo, gUTownshipName, gUTownshipNumber, gUSection, gURange, gUCropInsID, gUInsLocNo

put "LeaseID is '" & pID & "'" into theParamsA["conditions"]

try

sqlrecord_find "tleasefields", theParamsA, theRecordA

put the result into theError

FieldsToUVars --Collect data from fields and put them into gUVars

sqlrecord_set theRecordA, "FieldName", gUFieldName --varchar(40) NOT NULL

sqlrecord_set theRecordA, "AcresGIS", gUAcresGIS --float NOT NULL

sqlrecord_set theRecordA, "AcresCrops", gUAcresCrops --float NOT NULL

sqlrecord_set theRecordA, "County", gUCounty --varchar (15) NOT NULL

sqlrecord_set theRecordA, "FSANo", gUFSANo --varchar(15) NOT NULL

sqlrecord_set theRecordA, "TownshipName", gUTownshipName --varchar(50)

sqlrecord_set theRecordA, "TownshipNumber", gUTownshipNumber --varchar(15)

sqlrecord_set theRecordA, "Section", gUSection --smallint(5) NOT NULL

sqlrecord_set theRecordA, "Range", gURange --varchar

sqlrecord_set theRecordA, "CropInsID", gUCropInsID--varchar

sqlrecord_set theRecordA, "InsLocNo", gUInsLocNo --int

catch e

put e into field "test"

end try

answer printkeys(theRecordA)

end CommitUpdates

It finds the record fine, but when attempting sqlrecord_set it gives the error I listed. Any leads on to what I need to look at to find what is causing this?

After reading another post on these forums, I am suspicious it might be that I am trying to set conditions of the sqlrecord_find and telling it what the primary key is instead of letting it handle it?

EDIT: Ok I tried redoing this with a sqlquery_update instead of a sqlrecord_update and am getting this error.

sqlyoga_executesql_err,0,0,Cannot add or update a child row: a foreign key constraint fails (`development`.`tleasefields`, CONSTRAINT `tleasefields_ibfk_10` FOREIGN KEY (`FieldName`) REFERENCES `tsufields` (`FieldName`)) (UPDATE tleasefields SET FieldName = :1 WHERE LeaseID = '529')

I inherited this database from my predecessor. I assume that he had it set up to change all the related fields in other tables on update or delete. His was a MS-SQL db and I imported the data into mySQL with all the relationships intact.

When viewing information on my table I see:

CONSTRAINT `tleasefields_ibfk_10` FOREIGN KEY (`FieldName`) REFERENCES `tsufields` (`FieldName`),

So what do i need to do in order to make the changes to the rest of the tables as needed or get rid of them so I can do it pragmatically? Any advice? I am trying to insert random data into the field FieldName that doesn't exist in tsufields. Maybe I can only update based on what fieldnames are in tsufields ?

2nd EDIT:

Suspicions confirmed. I'm a nub. It updates fine when I get it strings that match a name in tsufields.FieldName

Lol, I fight with these things for hours and post stuff and end up solving my own problem with a little nudge in the right direction. Gah, sorry.

I still would like to figure out how to to sqlrecord_updates though.

[trevordevore]

Regarding sqlrecord_set - When you pass in a record array to sqlrecord_set, SQL Yoga verifies that you passed in a valid record array. A valid array has a key named "@table" that tells SQL Yoga the table the record is attached to. If theRecordA array doesn't have that key the you get the error message that you are receiving.

You probably need to add some code that checks whether or not a record was found. The most likely cause of the error is that sqlrecord_find isn't finding any records. You then end up with theRecordA being an empty variable.

Just add a check like this:

Code: Select all

if the keys of theRecordA are not empty then

....

end if

Regarding the error message with sqlquery_update: This is the MySQL engine complaining here. Not sure what you need to do here but perhaps this page in the MySQL docs can help: http://dev.mysql.com/doc/refman/5.1/en/ ... aints.html