Datagrid / Dataset / Update

[NeilA]

Hi

I have my vb app using a datagrid connecting to a SQL db to retrieve its data. No problem there. I also have a button which when a user selects a row it should change the last column of that row to be true from false.

I then call some code which checks if the dataset has changes, it says it has so the dataadapter calls the update. I can see an update has run as it displays in the output window.

If I then refresh the data to redisplay it all int he grid that last clumn for the row changes back to false!

Why is my dataadapter not doing what it should?

Neil.

my code:

User selects a row and click the button which does this:

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btnDeleteSession_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] btnDeleteSession.Click[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] selrow [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[SIZE=2]selrow = dtgDeleteEvent.CurrentRowIndex[/SIZE]
[SIZE=2]dsEventListDelete.Tables(0).Rows(selrow).Item(10) = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[SIZE=2]saveDeletedEvent()[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[COLOR=#0000ff][/COLOR] 
[COLOR=#0000ff]

[/COLOR]


The saveDeletedEvent() part looks like this:

[SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE][SIZE=2] saveDeletedEvent()[/SIZE]
[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] dsEventListDelete.HasChanges() [/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] data_adapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlDataAdapter[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] command_builder [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommandBuilder[/SIZE]
[SIZE=2]data_adapter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlDataAdapter(strSQLChildList, SQL_CONNECTION_STRING)[/SIZE]
[SIZE=2]data_adapter.TableMappings.Add("Table", "TblSession")[/SIZE]
[SIZE=2]command_builder = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommandBuilder(data_adapter)[/SIZE]
[SIZE=2]Debug.WriteLine(command_builder.GetUpdateCommand.CommandText)[/SIZE]
[SIZE=2]data_adapter.Update(dsEventListDelete)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE]

 

[cjard]

Why would you "refresh the data" - it already looks like what it is and shouldnt need refreshing? Do you mean that if you go and view the database data, then the change has not been committed? Can you post the UPDATE command in use?

Can I just double check that you are using VS 2002? It's a rare thing you see - wanna make sure you havent entered the setting wrongly in your profile

 

[NeilA]

Hi

I am on 2002. I actually managed to get round this by making the SQL which creates my dataset a lot simpler.

 

[vis781]

Function saveDeletedEvent()
If dsEventListDelete.HasChanges() Then
Dim data_adapter As SqlDataAdapter
Dim command_builder As SqlCommandBuilder
data_adapter = New SqlDataAdapter(strSQLChildList, SQL_CONNECTION_STRING)
data_adapter.TableMappings.Add("Table", "TblSession")
command_builder = New SqlCommandBuilder(data_adapter)
Debug.WriteLine(command_builder.GetUpdateCommand.CommandText)
data_adapter.Update(dsEventListDelete)
EndIf
EndFunction

I don't use a command builder all that much, but here are a couple of points that you need to address...

First : That should be a 'Void' not a 'Function' as it has no return value.
Second : You should really be using the same dataadapter to update the database that you used to populate the datatable.
Third : Have you stepped through the code to see if it is actually executing the update line.
Fourth : It deosn't look like you are using the command builder object correctly. (Though i may be mistaken) Here's a link, just check to see that you are doing 'the right way'

http://www.developer.com/lang/other/article.php/963511