Database not updating!

[adsc]

Hi All,

Any boady know what is rong in this simble code, my DataRow and DataSet is update, but Database is not updating.
*** Note: I am using Sql - MSDE

myDR("bankname_a") = "US First Bank"
myDA.Update(myDS, "Bank")
myDS.AcceptChanges()

Thanks in advance

[Athley]

Do you get any errors or is it just not updating?

The code looks OK to me so if you dont get any error that means you forgot something elswhere in your code.

So....
if error, then what error?
if no error, more of your code will be needed to solve this I guess.

For example, is your UpdateCommand correctly written or did you use a commandbuilder to creat it?

/Leyan

[adsc]

Athley, Thank you for your reply,

Yes I am using CommandBuilder, below is my related code in brief:

' Initiolize ....
Dim mySql = "SELECT * FROM AN_Bank"
Dim myDA As New SqlDataAdapter(mySql, myConn.oConnection)
Dim myCB As New SqlCommandBuilder(myDA)

Dim myDS As New DataSet()
Dim myDR As DataRow

Private Sub frm_Bank_Load
myDA.UpdateCommand = myCB.GetUpdateCommand
myDA.Fill(myDS, "Bank")

' Fill the DataRow
myDR = myDS.Tables("Bank").Rows(0)

Call DataBinding()
End Sub

Private Sub DataBinding()
Me.cbSelect.DataSource = myDS.Tables("bank").DefaultView
Me.cbSelect.DisplayMember = "BankName_e"

Me.txtBankName_e.DataBindings.Add("Text", myDS.Tables("Bank").DefaultView, "BankName_e")
Me.txtBankName_a.DataBindings.Add("Text", myDS.Tables("Bank").DefaultView, "BankName_a")
End Sub

Private Sub btnSave_Click
myDR("bankname_a") = "First Bank" ' This as test
myDA.Update(myDS, "Bank")
myDS.AcceptChanges()
End Sub

Thanks again

[CoMMiE]

Hi
You should have done databinding before you try to initializing a new command builder

[adsc]

Hi,
You think it will work if I moved the Command Builder intiolization after the databinding?

[CoMMiE]

Yes
If you put after the bindings the command builder will then detect the tables that is being bind and automatically generate sql statement to update the database

[Athley]

That shouln't make a difference as the commandbuilder builds the command from the adapter Select Query. At least that is what MS says, so it might have a difference. =)

/Leyan

[adsc]

Thanks for the info.

I'll try out.

[Athley]

One of my questions still stands, do you get an error or does it just dont update?

/Leyan

[adsc]

Athley,
No, no errors, my dataset & data adapter are updated, when I move to next or previos record after the make the update i see the record is updated, but if I closed the form and re-connect and re-get my data from database I get the old data.

[CoMMiE]

Sorry my mistake
I think you should initialize your command builder after your dataset has been modifiy that is after the DS.AccepChanges , so that the command builder can generate the update command to update your database

[Athley]

Ahhhh, I see. I think it has to do with the building of the dataset before you filled it. Change that order.

myDA.Fill(myDS, "Bank")
myDA.UpdateCommand = myCB.GetUpdateCommand

Else the adapter could not know what fields there are in the table for example

/Leyan

[adsc]

I did what you said, but still the same problem.

Even I check the Sql database rights for update, everything is ok.!!!!!!!!!!

Any ieda what else could be the problem?

[Athley]

Hmmm, I Created a SQL database with 2 fields ("BankName_a, "BankName_e"), added 2 textboxes and a combobox, copy/pasted your code added some to get a connection and changed some to get my events to fire and ...

Dim myConn As New SqlConnection("Integrated Security=SSPI;Data Source=LEYAN" _
    & ";Initial Catalog=Snabel;")
Dim mySql = "SELECT * FROM AN_Bank"
Dim myDA As New SqlDataAdapter(mySql, myConn)
Dim myCB As New SqlCommandBuilder(myDA)

Dim myDS As New DataSet()
Dim myDR As DataRow

Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click

    myDR("BankName_a") = "First Bank"
    myDA.Update(myDS, "Bank")
    myDS.AcceptChanges()

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load

    myDA.Fill(myDS, "Bank")
    myDA.UpdateCommand = myCB.GetUpdateCommand
    myDR = myDS.Tables("Bank").Rows(0)

    Me.cbselect.DataSource = myDS.Tables("bank").DefaultView
    Me.cbselect.DisplayMember = "BankName_e"
    Me.txtBankName_e.DataBindings.Add("Text", myDS.Tables("Bank").DefaultView, "BankName_e")
    Me.txtBankName_a.DataBindings.Add("Text", myDS.Tables("Bank").DefaultView, "BankName_a")

End Sub

.... my database updates!!

Something but the code has to be wrong...

/Leyan

[adsc]

YES, it's working....

The code looks ok, I don't know what was the problem, I've deleted some lines & re-code it again.

But, a new thing now....
Binding a field to a text property in a textbox does not mean accepting the modifications correct.....

When I remove this line (myDR("BankName_a") = "First Bank")
should I write the following to accept the changes from the textbox:

myDR("bankname_a") = Me.txtBankName_a.Text
myDR("bankname_e") = Me.txtBankName_e.Text

In addition to that in the previos example always the first row in the database table is updated! this means the dataadapter can't point to the correct record in the database!!!

[Athley]

If you made changes and jumped to another record to make the changes to the dataset its just to make the update.

/Leyan