Update Problem

[adshocker]

hi all,

i'm having a bit of a problem with the adapter.update command. it seems that when i navigate through my records, it updates all of it even though i didnt make any changes to some. for example, i have 15 records total, then i changed some values in the 3rd and 5th record, when i do this codes

    Public Sub Update_Record()
        Dim RecordsAffected As Int32
        Dim HasChanges As String = "Transaction Complete. "
        Dim Updated As String = " records applied and saved."
        Dim NoChanges As String = "No changes to save."

        _CurrencyManager.EndCurrentEdit()

        If _DataSet.HasChanges Then
            RecordsAffected = _DataAdapter.Update(_DataTable)
            SBarHeadMessage(HasChanges & RecordsAffected & Updated)
        Else
            SBarHeadMessage(NoChanges)
        End If
    End Sub

RecordsAffected returns 15 instead of only 2. Am I doing something wrong?
please help.

thanks.

 

[alander]

the update command will update all ur records and so, it returns 15

 

[adshocker]

i was experimenting with my application and i noticed something along the process. on my form i also have a next and previous button to allow me to navigate through the records.

so here are the few things i tried.

when the form loads the first time, i immediately click save then recordsaffected returns only 1.
then i try to navigate, then save... it seems that recordsaffected being returned is actually the number of records i have navigated through. why is that?

 

[adshocker]

ok i tried to handle the rowchange event of my datatable. it seems that everytime i navigate to the next or previous record, the RowChange events action is changed. so i tried to figure out what causing it by removing my databindings one by one. it seems that the databinding of my combo box is causing this because when i commented it out, i didnt get the changed action in my RowChange event.

let me show you my code for my databindings

cboSex.DataBindings.Add("SelectedItem", _DataTable, "sex") 
cboStatus.DataBindings.Add("SelectedItem", _DataTable, "status")

not sure if these info has anything to do with it but i used DropDownStyle = DropDownList for my combo boxes.

i also tried changing the databindings to this

cboSex.DataBindings.Add("Text", _DataTable, "sex") 
cboStatus.DataBindings.Add("Text", _DataTable, "status")

this doesnt give me the changed action in RowChanged Event but this binding doesnt update my database... i also tried different properties like the SelectedValue, SelectedText but no good.

can anyone help me figure it out?
thanks.

 

[alander]

This is because, if u bind text, ur combo will juz show the text that it is using, not any value u have defined in ur item collections, so if u change ur selected item, it wouldnt trigger row change event

use value member instead

 

[adshocker]

i tried using ValueMember as the property for the combo box databinding but i get an error this way.

do i need to set other properties or settings for my combo box before i can use ValueMember as the property?

 

[alander]

u have to set the datasource duh

 

[adshocker]

thanks for the info.

i have a suggestion for you though.

instead of saying this

u have to set the datasource duh

it would be better if you just said "u have to set the datasource".

 

[cjard]

the update command will update all ur records and so, it returns 15

Actually, for each row in the datatable, the adapter will look at the RowState. If it is Original then the row is skipped. If it is Added then the INSERT sql is run, if it is Modified the UPDATE is run, and if it is Deleted the DELETE is run. At the end of the operation, AcceptChanges is called and all rows become Original. Hence, the update() command should only return the number of I, U or D queries it ran

 

[cjard]

thanks for the info.

When you want to use a combo to edit data, and it must get its values from one table, but edit another, your setup looks like:

combo.DataSource = dataTableWhereItGetsItsValues
combo.DisplayMember = "String Column Name Of Column To Be Shown In list"
combo.ValueMember = "String COlumn Name Of COlumn Whose Value Is To Be Used"
combo.DataBindings.Add("SelectedValue", dataTableToBeUpdated, "column name to be updated")


Suppose we have a Sex table that is:
ID, Disp
1, "Male"
2, "Female"
0, "Unknown"

The valuemember is "ID", the displaymember is "Disp", Make sure ONLY selectedValue is bound.. binding anything else too will cause errors. Dont bind "Text" because then youre binding what is on display, not the ID

Hope this helps

it would be better if you just said "u have to set the datasource".

I would tend to agree, especially seeing as the advice you offer, alander, occasionally contains serious mistakes. There are quite a few times I could have picked you up on it with a "duh" too.. I know we all have bad days, and sometimes users just seem really clueless, but I've found that gentle rephrasing of advice, with a tiny hint of sarcasm is far more intellectually satisfying than outright inferring that the user is thick (from my point of view), and (if it's subtle enough) usually causes more amusement than offense

That said, nothing works better than an apology if you do overstep the mark - we were all in the same boat at one point!

 

[adshocker]

thanks for the advice... i appreciate it.

 

[adshocker]

When you want to use a combo to edit data, and it must get its values from one table, but edit another, your setup looks like:

combo.DataSource = dataTableWhereItGetsItsValues
combo.DisplayMember = "String Column Name Of Column To Be Shown In list"
combo.ValueMember = "String COlumn Name Of COlumn Whose Value Is To Be Used"
combo.DataBindings.Add("SelectedValue", dataTableToBeUpdated, "column name to be updated")


Suppose we have a Sex table that is:
ID, Disp
1, "Male"
2, "Female"
0, "Unknown"

The valuemember is "ID", the displaymember is "Disp", Make sure ONLY selectedValue is bound.. binding anything else too will cause errors. Dont bind "Text" because then youre binding what is on display, not the ID

Hope this helps



I would tend to agree, especially seeing as the advice you offer, alander, occasionally contains serious mistakes. There are quite a few times I could have picked you up on it with a "duh" too.. I know we all have bad days, and sometimes users just seem really clueless, but I've found that gentle rephrasing of advice, with a tiny hint of sarcasm is far more intellectually satisfying than outright inferring that the user is thick (from my point of view), and (if it's subtle enough) usually causes more amusement than offense

That said, nothing works better than an apology if you do overstep the mark - we were all in the same boat at one point!

hi,

this info help me a lot... it seems to be working now.
although, i have one tiny question when it comes to using myCurrencyManager.AddNew method.

how can i set the Value of my combobox to an empty string whenever i add a new record?

what seems to happen whenever i do the myCurrencyManager.AddNew method is my combobox selectedvalue is set to its first item.

any suggestions?

thanks.

 

[adshocker]

hi again,

i tried doing this to set it to null when ever i insert a new record. so i added these codes after the myCurrencyManager.AddNew

cboSex.SelectedIndex = -1

unlike textboxes, they are automatically set to empty when ever i do the addnew.
i was just wondering if this is how its supposed to be with combo boxes?

 

[cjard]

what seems to happen whenever i do the myCurrencyManager.AddNew method is my combobox selectedvalue is set to its first item.

any suggestions?

you can try making the defaultvalue of the column in question to be null.. but I'm not sure that you can effectively set a DropDownList style combo to anything other than one of the list entries.. Try aso setting combi.SelectedItem = Nothing

 

[cjard]

i was just wondering if this is how its supposed to be with combo boxes?

in drop down list mode, i think so.. i cant remember ever investigating it to be honest!