Update and RowState

I have some personnel data in a SQL database. I’m trying to use VB.Net 2003 to set up a front end for it.

< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

As a start, I created a form with just key value, name, SSN, and payroll number. I set up a data adapter which retrieves one employee’s record at a time. Using my SSN as a test field, I was able to fill the dataset, make changes to the SSN field, and use the data adapter’s Update method to save those changes to the SQL DB. Once I had that working, and thought I had the concept down, I added other fields to the form. After adding the other fields, the Update no longer worked.

I went back and started from scratch, creating the form with just the Employee Key and the SSN. The Update doesn’t work. I’ve tried having the data adapter generate the Update command, and I’ve tried doing it myself. In troubleshooting the problem, I put the following lines just before the Update code, and walked thru it in Debug mode:

For i = 0 To Me.DS_Persons1.Tables("Persons").Rows.Count - 1

m = Me.DS_Persons1.Tables("Persons").Rows(i).RowState.ToString

x = Me.DS_Persons1.Tables("Persons").Rows(i).Item("SSN")

Next

If Me.DS_Persons1.Tables("Persons").Rows(0).RowState = DataRowState.Unchanged Then

Me.DS_Persons1.Tables("Persons").Rows(0).Item("SSN") = "000000000"

End If

x = Me.DS_Persons1.Tables("Persons").Rows(0).Item("SSN")

m = Me.DS_Persons1.Tables("Persons").Rows(0).RowState.ToString

For i = 0 To Me.SqlDataAdapter2.UpdateCommand.Parameters.Count - 1

m = Me.SqlDataAdapter2.UpdateCommand.Parameters(i).ParameterName

x = Me.SqlDataAdapter2.UpdateCommand.Parameters(i).Value

Next

I discovered that

1) the RowState remains UnChanged, even after changing the value in code.

2) the Parameter values are all = Nothing.

So, What can I do to make the RowState change to Modified, and make the Update work

Also, I’ve read that using Table.AcceptChanges will cause the RowState to go back to UnChanged. I’m not using AcceptChanges, but I don’t completely understand its purpose. Is it supposed to carry a dataset’s changes back to the underlying SQL databasee If so, it’s not working for me either. And if not, then what’s its purpose

FWIW, I am able to take the values from the text boxes and create an update (or insert, or delete) statement query, and modify the SQL DB tables directly. If Update will work, I'd rather use Update. But, with the problems I've had with it, and some of the other threads I've read where users have tried using Update and it just doesn't work unless the planets are aligned, I'm not sure I trust it, especially when the user can't tell if the update happened. Are these problems common

Thanks.

Answer this question

Update and RowState

imok
Are you using VS 2003 that version of the Numeric Up-Down control does not bind to data properly; I had to manually handle all of the data io when using a NUD control. Try using a plain text box first.
Bjorne

Thanks for the reply.

Data table is bound to controls on the form. There's only one record in the table. User edits data then clicks Save. This is the code from the btnSave_Click.

Try
   Me.SqlConnection1.Open()
   Me.SqlDataAdapter1.Update(DS_Persons1, "Persons")
Catch ex As Exception
   MsgBox(ex.ToString)
Finally
   If Me.SqlConnection1.State = ConnectionState.Open Then
      Me.SqlConnection1.Close()
   End If
End Try

Me.Close()

IF I add
   x = Me.DS_Persons1.Tables(0).Rows(0).RowState.ToString
before the update command, it tells me the rowstate is unchanged.

Once, just to test, I updated the data in code, like
   Me.DS_Persons1.Tables("Persons").Rows(0).Item("SSN") = "000000000"
and it still showed as RowState unchanged, and the Update didn't happen.

Are you saying I need to use AcceptChanges after I do the Update

As I mentioned, I've tried several versions of an update command, both computer-generated and manually entered. Here is the most recent, simplest example of an update command that doesn't get applied. Just to make doubly sure, I've pasted this into QueryAnalyzer and it does update SSN to '555555555'.

UPDATE    Persons
SET   SSN = '555555555'
WHERE     (PD_Key = 124)

You asked if I was 'detaching' it before updating. No, I'm not. I'm not sure what I'd detach it from. As you can probably tell, this is my first venture into VB.Net/SQL. Up 'til now, I've worked mostly with Access. It's not supposed to be this tricky, is it

Thanks.
Parmesh A
Yes, the Persons table is table 0, since it is the only table open.

What I have discovered is that if I retrieve more than 1 row in the dataset, and I change a row AND move to the next row, THEN the update works. If I make a change and don't move to the next row, the update doesn't work.

I have decided to proceed with the project using parameters passed to SQL stored procedures. At least I'll be able to sleep nights.

Thanks for your suggestions.
delanean
I have noticed this same phenominon. I have a datasource that load a single datarow in a single dataset table, bound to Numeric up-down controls on the form. After changing the value in one of the controls I can see that the value in the field in the datarow field has indeed changed, but the rowstate is still unchanged.

Does anyone know why this is happening is there a workaround other that re-writing it to do the update to the database manually is this a bug
Rahul Shankar
Rowstate is updated after you move off the row; with a single row table you never move off. In these circumstances, you need to call EndEdit() on the bindingsource before calling the update, which will process changes to the single row and flag it as changed.
Lubor

Hi,

Yes your right, this should not be tricky. Actually the approach is quite similar. Have you tried creating everything manually As in creating your DataTable and DataAdapter yourself...

Yes, you should call AcceptChanges after calling the update. But you should be sure that the update have been successful...

This is just a hunch. But do you only have one table in your dataset Try checking it coz its possible that what your checking (i think is table 0) is not the table "persons" that you are referring to...

cheers,

Paul June A. Domag
imad_ouzoun

Hi,

How are you updating the rows in your datatable Could please you post a code snippet Are you dettaching it upon updating

AcceptChanges() is a method that marks all of your rows as unchanged. This is very useful when you are sure that the updates successfully executed. Coz if you'll not call AcceptChanges the rows would also be updated again when you call DataAdapter.Update the second time in your datatable or dataset...

cheers,

Paul June A. Domag
Shel Blauman MS

Hi,

It seems that you haven't specified the Commands needed for the adapter to be able to send your changes in your database. Try specifying your UpdateCommand, DeleteCommand and InsertCommand properties of your adapter. But if you want to do this automatically then just use the CommandBuilder class:

Dim cmdbldr As New SqlCommandBuilder(adapter)

after that your adapter would be then ready for any updates that you issue upon a datatable

cheers,

Paul June A. Domag
Alex_Spain
Started from scratch, created DataAdapter and DataSet manually. Just before "Update", RowState is still "Unchanged", so Update is unsuccessful.

Public Class Form1
Inherits System.Windows.Forms.Form

Dim sqlConnection1 As New SqlClient.SqlConnection("Integrated Security=SSPI;" & _
"Persist Security Info=False;Initial Catalog=mydatabase;" & _
"Data Source=myserver;" & _
"Packet Size=4096;Workstation ID=myID;")

Dim SqlDataAdapter1 As New SqlClient.SqlDataAdapter("Select * from Persons Where PD_Key = 124", sqlConnection1)

Dim DataSet1 As New DataSet

Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
'load
Try
   Me.sqlConnection1.Open()
   Me.DataSet1.Clear()
   Me.SqlDataAdapter1.Fill(DataSet1, "Persons")
Catch ex As Exception
   MsgBox(ex.ToString)
Finally
   Me.sqlConnection1.Close()
End Try
If Me.txtSSN.DataBindings.Count = 0 Then
   Me.txtSSN.DataBindings.Add("Text", Me.DataSet1.Tables(0), "SSN")
   Me.txtKey.DataBindings.Add("Text", Me.DataSet1.Tables("Persons"), "PD_Key")
End If
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim x, y As String

y = "Update Persons " & _
"Set SSN = '" & Me.txtSSN.Text & "' where PD_Key = " & _
Me.txtKey.Text & ";"

Dim com1 As New SqlClient.SqlCommand(y, Me.sqlConnection1)
Me.SqlDataAdapter1.UpdateCommand = com1

Try
   Me.sqlConnection1.Open()
  'I added the following lines for debugging
   'make sure there's only one row in question
   x = Me.DataSet1.Tables("Persons").Rows.Count.ToString

   'check row state
   x = Me.DataSet1.Tables("Persons").Rows(0).RowState.ToString

   'force row change
   Me.DataSet1.Tables("Persons").Rows(0).Item("SSN") = "000000000"

   'check row state
   x = Me.DataSet1.Tables("Persons").Rows(0).RowState.ToString

   'attempt to perform update
   Me.SqlDataAdapter1.Update(DataSet1, "Persons")

   'see if updates successful
   Me.txtSSN.DataBindings.Clear()
   Me.txtKey.DataBindings.Clear()

   Me.DataSet1.Clear()
   Me.SqlDataAdapter1.Fill(DataSet1, "Persons")
   If Me.txtSSN.Text = Me.DataSet1.Tables("Persons").Rows(0).Item("SSN") Then
      MsgBox("Update successful!")
   Else
      MsgBox("New = " & Me.txtSSN.Text & "; old = " & _
      Me.DataSet1.Tables("Persons").Rows(0).Item("SSN") & _
      Chr(10) & Chr(10) & "Update Failed.")
   End If
Catch ex As Exception
   MsgBox(ex.ToString)
Finally
   Me.sqlConnection1.Close()
End Try
End Sub
End Class

Unless something obvious jumps out to you that fixes this problem, I'm going to change gears, and use the direct sql commands to update the data.

Thanks for your help.
Update and RowState

Answer this question