DataGrid, MS Access, and VB.NET Forms

[mivey4]

Can someone please help me?????

I have set a datagrid with a MS Access database set as its datasource.
I want to allow the user to perform dynamic updates via the dataAdapter
by using an update command.

I do this by passing variables to hold the CurrentCell row and column
properties of the datagrid and pass them to the Update command of the
dataAdapter to carry out the update to the dataset.

I presently use a commit button to execute the update
of data performed on whatever cell is currently selected and edited at the time the
user clicks on the button. Which works fine but I don't want the user to have to
click the button everytime a single cell is updated because this will only update the

currently selected cell immediately after the button is clicked.

I would instead like for the update to immediately take place after focus has
left the current cell that has been edited.

My proposed resolution for this problem was to take the code and assign it to
the Datagrids CurrentCellChanged event expecting that the code will be executed
upon moving to another cell which it does but not immediately. It instead seems
to delay the update Until the focus has changed from the initially edited cell
to an aligning cell and then back again to the originally edited cell.

I have therefore tried to programmatically perform this action but without any
success.

Has anyone encountered this problem before and found a solution to this issue?

Any help would be greatly appreciated.

Thanks,
MIVEY4

 

[TPM]

Can't say I've ever encountered the behavior you describe. Something I would point out though is when you user edits the last value they won't change cell afterwards, and thus it won't update. Personally I went with an update button which the use clicks when their finished.

TPM

 

[mivey4]

Thanks and I was already aware of that as well.
Personally, I wouldn't mind going with an update button that would update all the edited information when the user is finished as well but how would I create a command that would update all the changed fields in the table???

I am using the following code and SQL COMMAND to update the edited fields:

'Create the update command

Dim update As New OleDb.OleDbCommand("UPDATE DBTeam SET PrimaryContact = " & "'" & PrimaryContact & "', SecondaryContact = " & "'" & SecondaryContact & "'" & " WHERE ExpertiseArea = " & "'" & ExpertiseArea & "'")

'Assign the UPDATE command to the DataAdapters Update command property

daConsultants.UpdateCommand = update

'Create the connection for executing the UPDATE command to the data adapter

update.Connection = conn

'Perform the Update to the Dataset so it may update the table content

daConsultants.Update(dsConsultants, "DBTeam")

How would you update the entire table with the changed data fields without using the WHERE clause? Without it the all the rows would be affected.

 

[TPM]

Dim update AsNew OleDb.OleDbCommand("UPDATE DBTeam SET PrimaryContact =?, SecondaryContact =? WHERE ExpertiseArea = ?")
Then add 3 parameters to the dataadapter with id, length and source column.

 

[mivey4]

Datagrid

Okay TPM,

I believe I somewhat comprehend what you are telling me but if you could endure a bit more patience with me I still have a couple of questions on your reply.

1. The update command that you have recommended is identical to mine with exception of the "?" operator. This I suppose is a wildcard to be used instead of assigned variables?

2. The 3 parameters you are suggesting for me to pass to the dataAdapter----What method of the DataAdapter would accept these 3 arguments???
Do you have a small snippet of sample code that you could provide so I may better understand what you are saying?

Thanks,
MIVEY4

 

[TPM]

1. Yes exactly the ? will be replaced with the parameter value.

2.

MyDataAdapter.updatecommand = New OleDB.OleDBCommand
MyDataAdapter.updatecommand.commandtext="UPDATE DBTeam SET PrimaryContact =?, SecondaryContact =? WHERE ExpertiseArea = ?"
MyDataAdapter.updatecommand.connection=YourConnection
MyDataAdapter.updatecommand.parameters.add("firstParamter", OleDBType.Numeric, 8, "ColumnName")
MyDataAdapter.updatecommand.parameters.add("SecondParamter", OleDBType.Numeric, 8, "NextColumnName")
MyDataAdapter.updatecommand.parameters.add("ThirdParamter", OleDBType.Numeric, 8, "NextColumnName")
MyDataAdapter.update(YourDatatable)

Hope that helps

TPM