New topic: Possible bug? Relational select has different behaviour
<http://forums.realsoftware.com/viewtopic.php?t=34359> Page 1 of 1 [ 2 posts ] Previous topic | Next topic Author Message RichardF Post subject: Possible bug? Relational select has different behaviourPosted: Tue Jun 22, 2010 2:05 pm Joined: Tue Jun 22, 2010 1:27 pm Posts: 1 I have a database with a few tables, all related. In the main one, Stations (meaning satellite ground stations) is a boolean field called Selected. I have a function which can identify all records according to several criteria -- one is a field in the main table identifying the station type (regular, laser, DORIS etc) and another criterion is a reference to another table listing the network affiliations. The job of the function under discussion is to mark all stations in a category as selected. There are 3 cases: select all, select according to the type in the table and select according to the network affiliation in the relational table. In each case the records are properly selected, and all change the state of the field Selected to true (I checked in the debugger -- all cases behave the same). But, when it come to the Commit statement the last case throws up an error (can't commit 'cos no transaction). But the fields in the record set *were* changed. If I add a begin transaction first I don't get the error, but there's no update to the records either. This seems like it shouldn't happen, or is there something I'm doing wrong? Here's some code: Code: if StationTypes.ListIndex = 0 then // All selected so don't alter filter // Build the SQL statement that will be used to select the records sql = "SELECT S.StationName, S.Selected FROM Stations S" elseif StationTypes.ListIndex <= 5 then // it's a type, like Laser, in the main table filter = "upper(StationType) = '" + uppercase(StationTypes.text) + "'" // Build the SQL statement that will be used to select the records sql = "SELECT S.StationName, S.Selected FROM Stations S" // Add the filter to the SQL statement if filter <> "" then sql = sql +" WHERE "+ filter end else // it's a network, stored in a different table, so we need a different type of SQL statement // Build the SQL statement that will be used to select the records sql = "SELECT S.StationName, S.Selected FROM Stations S, Networks N " + _ " WHERE N.IDofStation = S.ID AND N.Network = '" + str(StationTypes.ListIndex-6) + "'" end if App.StationDB.SQLExecute "begin transaction" rs = app.StationDB.sqlSelect( sql ) if rs <> Nil then While Not rs.EOF rs.Edit If App.StationDB.error then App.DisplayStationDatabaseError True End if // station should be selected independently of previous state // find record in database and update rs.Field("Selected").BooleanValue = True // Update the record in the database rs.Update If App.StationDB.Error then // handle errors App.DisplayStationDatabaseError True End if rs.MoveNext // move to the next record Wend // Commit changes to the database app.StationDB.Commit If App.StationDB.Error then // handle errors App.DisplayStationDatabaseError True End if Thanks for any help, Richard Top npalardy Post subject: Re: Possible bug? Relational select has different behaviourPosted: Tue Jun 22, 2010 2:38 pm Joined: Sat Dec 24, 2005 8:18 pm Posts: 5952 Location: Canada, Alberta, Near Red Deer make sure your select includes the primary key of the Stations table as part of the query _________________ My web site Great White Software RBLibrary.com REALbasic learning Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 2 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]
