SQL-question

[meester]

In my application, when selecting a table in a database. The application should show the same record as the user was viewing in an earlier session.
Therefore in my database, i have a field called Actualcard.

It's supposed to be set to '-1' when the user selects it, and all the others should be set to '0'.


this is the script I use

// when the user selects a tabel,  the record is shown where the value of the ActualCard is -1
rs.Find("ActualCard='-1'");
// the recordnumber is saved to a var ActualItem
ActualItem = rs.Fields["number"].Value

***

and the code when the user selects the record

// when the user saves a new card, this is my code
SelectedItem = rs.Fields["number"].Value
if(SelectedItem!=ActualItem){
     trace("New actual card")
     // set the 'ActualCard'value of the newly selected record to -1
     rs.Fields["actuelekaart"].Value="-1";

// go to the old ActualItem and set the value of ActualCard to 0
     rs.Find("number='"+ActualItem + "'");
     rs.Fields["ActualCard"].Value ="0";
}

***
But when I test the movie, the olod Actualcards aren't set to 0.
Does anybody know what I'm doing wrong?

[Umpa]

It doesn't look like you are updating the db when setting the actual card value. You would want to do something similar to:

dbconn.execute("UPDATE mytablename SET [actuelekaart] = -1 WHERE [uniqueidofrecord] = mylastid")

What type of database are you connecting to? How many records are you returning with each database call? I'm assuming that you are pulling a record with each next/previous action as your users flip through the records. You would also want to unset the last page with each progressive retrieval, otherwise you'll end up eventually with all your records set to -1.

If you are using MS SQL, you can setup stored procedures to do that process for you, and could even do it in one call if bundled with the sql statement that returns you main recordset.

Hope this helps some.

[meester]

I'm using an acces database.

this is my code

var db = "klasboek.mdb";
trace(db);
var conn = new ActiveXObject("ADODB.Connection");
conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + db);
box = "box"+BoxNr
rs.CursorType = 2;
rs.CursorLocation  = 3;
sql = "select * from "+ box;
rs.Open(sql , conn, 1, 2);
rs.MoveFirst();
pla.bindData("number","volgorde","datum","rubriek1", "rubriek2", "rubriek3", "rubriek4","rubriek5","rubriek6","ActualCard");

When I use the update query like you suggested, it seems that I have to do another select query before the changes are take place.
The other items of the database are saved without the update query.
I use this function and it works fine:

function Aanpassen(pla, rs)
{
***rs.Fields["rubriek1"].Value******=pla.rubriek1;*********
   rs.Fields["rubriek2"].Value******=pla.rubriek2;
***rs.Fields["rubriek3"].Value******=pla.rubriek3;
***rs.Fields["rubriek4"].Value******=pla.rubriek4;
***rs.Fields["rubriek5"].Value******=pla.rubriek5;
***rs.Fields["rubriek6"].Value******=pla.rubriek6;
***rs.Fields["volgorde"].Value******=pla.volgorde;
***rs.Fields["ActualCard"].Value***=pla.actuelekaart;
***
***
***pla.updateData(false);
}

So I wonder: why can't I use the same method?

[Umpa]

OK,
I've mostly dealt with disconnected recordsets, so I've never really tried updated them w/o calling an update, then a subsequent select statement.

Part of your problem may be related to the cursor postion. The rs.Find() method appears to search forward only in my tests, so if I wanted to find an item that appeared before my current record, I would have to do a rs.MoveFirst(); command before calling the find.

I messed around with it for a while and could never get it the overall update to function. I could get a sample field updated after I changed the cursorlocation to 2, but after the update, the existing recordset I had became useless.

best of luck.

[SWFKit]

Maybe you need to call the update method after you change the value of the field.

"In immediate mode, changes to a record are propagated to the data source as soon as you declare the work on a row complete by calling the Update method.

If you move from the record you are adding or editing before calling the Update method, ADO will automatically call Update to save the changes. You must call the CancelUpdate method before navigation if you want to cancel any changes made to the current record or discard a newly added record.

The current record remains current after you call the Update method." (taken from ADO manual)

// when the user saves a new card, this is my code
SelectedItem = rs.Fields["number"].Value
if(SelectedItem!=ActualItem){
    trace("New actual card")
    // set the 'ActualCard'value of the newly selected record to -1
    rs.Fields["actuelekaart"].Value="-1";

// go to the old ActualItem and set the value of ActualCard to 0
    rs.Find("number='"+ActualItem + "'");
    rs.Fields["ActualCard"].Value ="0";
    rs.update();
}

[meester]

That does the trick
Thanks!