Question dynamic updaterow

[rorib]

Hi,

my app connects to a VisualFoxpro database (using oledb provider).
There are about 150 tables in this database.
I list the tables in a listbox.
The user can select a table and the content is displayed in a datagridview.
I can also connect to various databases (local machine or network).
So the connectionstring, listbox and datagridview are all more or less dynamic.
Now I want to update a cell in the grid (and from there update the table in the dataset), but I don't want to write code for 150 tableadapters.
I've done this for 1 table and all works fine.
Here is the code:

        Dim FPconn As OleDb.OleDbConnection
        Dim FPadap As OleDb.OleDbDataAdapter
        Dim cmd As OleDb.OleDbCommand
        [B]Dim updRow As <dataset>.<table>Row[/B]
        Dim kol, rij, antw As Integer
        Dim strOud, strNew, strUn, strDbTable_SQL As String
        kol = DGinh.CurrentCell.ColumnIndex
        strUn = DGinh.CurrentRow.Cells("unid").Value
        strOud = DGinh.CurrentCell.Value
        strNew = InputBox("Wijzig de waarde in", "Wijzigen", strOud)
        DGinh.CurrentCell.Value = strNew
        DGinh.Update()
        FPconn = New OleDb.OleDbConnection(My.Settings.<conn.strin>)
        Me.<dataset>.Tables(strTabelnaam).Rows.Clear()

        strDbTable_SQL = "SELECT * FROM " & ListBox1.SelectedItem
        FPadap = New OleDb.OleDbDataAdapter(strDbTable_SQL, FPconn)
        FPadap.Fill(Me.<dataset>.Tables(strTabelnaam))
        For rij = 0 To Me.<dataset>.Tables(strTabelnaam).Rows.Count - 1
            If Me.<dataset>.Tables(strTabelnaam).Rows(rij).Item(0) = strUn Then
                Exit For
            End If
        Next
        updRow = Me.<dataset>.Tables(strTabelnaam).Rows(rij)
        ZetCurrCell(kol, strUn)
        antw = MsgBox(strOud & " wijzigen in " & strNew & "?", MsgBoxStyle.YesNo)
        cmd = New OleDb.OleDbCommand
        If antw = MsgBoxResult.Yes Then
            updRow.Item(DGinh.CurrentCell.ColumnIndex) = strNew
            cmd.CommandText = "UPDATE '" & strTabelnaam & _
            "' SET " & DGinh.Columns(kol).HeaderText & " = '" & strNew & "' WHERE unid = '" & strUn & "'"
            cmd.Connection = FPconn
            FPadap.UpdateCommand = cmd
            Me.Validate()
            Me.<bindingsource>.EndEdit()
            Try
                FPadap.Update(Me.<dataset>.Tables(strTabelnaam))
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
        FPconn.Close()

The question is: how do I set updRow to be an updaterow of the selected table without writing code for 150 tables.
As far as I know, I need an updaterow with te oledbdataadapter (without it there is no update in the database).
Or is there another way to do this.
Until now reading the posts on this forum helped me to find what i need.
Can't find anything on this.
Appreciate your help.

 

[jmcilhinney]

Create your DataAdapter like this:

Dim adapter As New OleDbDataAdapter("SELECT * FROM " & tableName, connection)
Dim builder As New OleDbCommandBuilder(adapter)

adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

Now you can simply call Update on the adapter and the required SQL will be generated for you. You MUST make sure that none of your table or column names are reserved words though, whicvh you should be doing anyway.

 

[rorib]

Hi,

I've altered the app according to your advice.
In debug mode I can see that I can update any field in any table now.
In the dataset, but changes are not made in the database.
My connectionstring contains a userid and a password.
Also in debugmode I can see that at the moment I use the connectionstring, the password section disappears from the connectionstring (???).
I've checked this in the previous app (with updaterow on a specific table) and there the same thing happens, but there the database is updated.
So I don't know if this matters.

You said "the required SQL will be generated for you."
Can I see this SQL somewhere?
The adapter.updatecommand is nothing.
If the SQL should be in the updatecommand, then there is something going wrong.
I think the adapter is missing something, but I don't know what.

 

[jmcilhinney]

Everything is working fine. Your password is not missing. It's still contained inside the connection object. It's just not included in the ConnectionString property value for security reasons. If you set PersistSecurityInfo to True in the connection string then the password will be visible.

The SQL code is not visible because it's generated by the CommandBuilder on demand. If you want to see it you can call GetUpdateCommand, etc. on the CommandBuilder but there's no need to actually use the command object it returns unless you want to alter it in some way.

If you're not getting an error then the changes to the data ARE being saved. If nothing is truly saved then there simply aren't any changes to save. If there are changes to save and you don't get an error then the changes ARE saved. You're just looking in the wrong place and/or the wrong time for the new data. Your working database should be in same folder as your EXE, not in the source folder. The one in the source folder is the original is doesn't get changed while debugging. The database that does get changed is overwritten each time you build by default, so your changes may simply be disappearing when you run again. In that case you can change that behaviour in the Properties for the database.

 

[rorib]

I think I know what you mean, but I don't have a copy of the database in the \bin\debug directory, because when I made the connection I didn't tell the app to do so.
Everytime I open the app I tell it which database to use (local or network) and this info is stored in the connectionstring (and saved to my.settings).
The foxpro database (DATABASE.DBC) is in a directory (database) and the tables ("table.DBF") are in a subdirectory (database\data).
In the previous app where I could update a table, I could see that changes were made, because the timestamp of the table changed.
Now there are no changes in the table timestamp, while there are changes in the dataset.
So I still think that there's something wrong with the dataadapter.
Maybe this just doesn't work on a Foxpro database and I have to write code for each table (there are 177 ).
Actually, I don't have to update all the tables, but I'm interested in methods of making the code useable for every table.
One day I'll try this on an Access database to see if there is a difference.

 

[jmcilhinney]

Fill and Update are functions, so they both return a value. Specifically, they return the number of records affected by the operation. When you call Update, what does it return?

 

[rorib]

update returns 0

 

[rorib]

This is the code:

Dim FPconn As OleDb.OleDbConnection
Dim FPadap As OleDb.OleDbDataAdapter
Dim iAntw, iUpd As Integer
Dim strOud, strNew, strDbTable_SQL As String

strOud = DGinh.CurrentCell.Value 'DGinh is datagridview
strNew = InputBox("Wijzig de waarde in", "Wijzigen", strOud)
FPconn = New OleDb.OleDbConnection(My.Settings.<connectionstring>)
FPconn.Open()

strDbTable_SQL = "SELECT * FROM " & ListBox1.SelectedItem 'name of table
FPadap = New OleDb.OleDbDataAdapter(strDbTable_SQL, FPconn)
FPadap.MissingSchemaAction = MissingSchemaAction.AddWithKey
Dim builder As New OleDb.OleDbCommandBuilder(FPadap)
iAntw = MsgBox(strOud & " wijzigen in " & strNew & "?", MsgBoxStyle.YesNo)
If iAntw = MsgBoxResult.Yes Then
DGinh.CurrentCell.Value = strNew
Try
Me.Validate()
Me.<bindingsource>.EndEdit()
iUpd = FPadap.Update(Me.<dataset>.Tables(strTabelnaam))

Catch ex As Exception
MsgBox(ex.Message)
End Try
End If
FPconn.Close()

Am I missing something?

 

[jmcilhinney]

Put this code before the Update call and tell me what you get:

Dim addedCount As Integer = 0
Dim modifiedCount As Integer = 0
Dim deletedCount As Integer = 0

For Each row As DataRow In Me.<dataset>.Tables(strTabelnaam).Rows
    Select Case row.RowState
        Case DataRowState.Added
            addedCount += 1
        Case DataRowState.Modified
            modifiedCount += 1
        Case DataRowState.Delete
            deletedCount += 1
    End Select
Next

MessageBox.Show(addedCount.ToString(), "Added")
MessageBox.Show(modifiedCount.ToString(), "Modified")
MessageBox.Show(deletedCount.ToString(), "Deleted")

 

[rorib]

everything is 0.
I checked the dataset before and after I made a change in a field.
The dataset is updated.
What I do is that I change something in a cell in the datagridview.
The datagridview is bound to the table that is selected (and displayed).
So when I make a change in the grid, the change is made in the dataset.
The row.rowstate property doesn't seem to recognize the change.
Why not?
I can understand that because VB.NET doesn't recognize the change, the update to the database isn't made.
Is it the grid, that's inbetween that's causing the trouble?

 

[jmcilhinney]

It's simply not possible that the DataSet gets changed and then the DataSet has no changes in it. If it seems to be that way then you must have more than one DataSet, or else you are clearing the changes at some point.

 

[rorib]

It's working now, although I do not understand everything.
The datagrids readonly property was set to true in an earlier stage of the app (I have to admit that I forgot about this).
When I hit the button which called the sub, I could change the value of the selected cell in an inputbox.
The value of the cell was updated and so was the dataset (How can this happen when the readonly property is set to true?).
Changes were not made in the database.

Now the readonly property is set to false and I update the grid directly in the cell (no inputbox).
This works fine.
I can update more than one row before calling the sub which updates the database.

I've also tried to update the cell through an inputbox (readonly set to false).
Something strange happens.
The last change is not made to the database.
When I change 2 rows, only row 1 is updated.
When I change 3 rows, row 1 and row 2 are updated.
When I change 2 cells in the same row then the entire row is updated.
When I change more than one row directly in the cell (no inputbox) then everything is updated.
I do not understand this yet, but I m going to investigate further.

For now I know how to make my app working.
Thanks for the help and explanations.
Maybe you can explain the things I do not understand yet.

 

[cjard]

It sounds like you're making 2 mistakes:

Not calling EndEdit on the grid, and possibly also on whatever the grid gets its data from (like the underlying binding source) if it has one. Remember that grids bound to tables are actually bound to that table's DefaultView property

Assuming that the grid holds the data; it doesnt. The datatable holds the data. If you want to change the data a grid shows, edit the data model itself, not the grid.


Your grid should sit on a BindingSource, that has a .Current property that returns the current DataRowView. That in turn has a .Row property that returns the current datatable row

Editing a value should be something like: DirectCast(myBS.Current, DataRowView).Row("Column name").Value = "hello world"

 

[jmcilhinney]

It sounds like you're making 2 mistakes:

Not calling EndEdit on the grid, and possibly also on whatever the grid gets its data from (like the underlying binding source) if it has one. Remember that grids bound to tables are actually bound to that table's DefaultView property

Assuming that the grid holds the data; it doesnt. The datatable holds the data. If you want to change the data a grid shows, edit the data model itself, not the grid.


Your grid should sit on a BindingSource, that has a .Current property that returns the current DataRowView. That in turn has a .Row property that returns the current datatable row

Editing a value should be something like: DirectCast(myBS.Current, DataRowView).Row("Column name").Value = "hello world"

Just one point to make here. There's really no point getting the DataRow because editing the DataRowView has the same effect. If you don't call BeginEdit on the DataRowView before making an edit then the change is pushed straight to the DataRow anyway. Without a BeginEdit there's no need for an EndEdit, but there's also no chance for a CancelEdit. That's why transactional editing is implemented through data-binding but there's no need for it in your own code.

 

[cjard]

There's really no point getting the DataRow

Unless you use typed datasets, where it is preferable to work in the typed environment. Here, the code sample is for untyped datasets so I concur that there would be no need to get the row.. Call it a muscle memory slip up