Unable to update DataGridView with static values..

[RNathan]

Hi,
I have a DataGridView that allows users update rows. The updates work fine. The datasource is bound to only one table. I have a new requirement that needs to be added. When a user updates rows in the grid, I have to update these rows with ModifiedBy and ModifiedDate values. The value of ModifiedBy is user's login name and ModifiedDate is the current date. These fields have to be displayed in the grid as well. I don't want the user to edit these two fields. I would like to feed the values programmatically. I am using VB.Net 2010, MS.NET Framework 4.0. If I use the following code, all the other fields get updated but not ModifiedBy and ModifiedDate.

masterDataAdapter.UpdateCommand = New OleDbCommand("UPDATE Job SET JobName = ?, JobTitle = ?, JobDelete = ?, Comments = ?, ModifiedBy = ?, ModifiedDate = ? WHERE JobName = ?oldJobName", objConnection)
'Create the parameters
masterDataAdapter.UpdateCommand.Parameters.Add("?ModifiedBy", OleDbType.Char, 8).Value = UserName
masterDataAdapter.UpdateCommand.Parameters.AddWithValue("?ModifiedDate", DateTime.Today)

masterDataAdapter.UpdateCommand.Parameters.Add("?JobName", OleDbType.Char, 8, "JobName")
masterDataAdapter.UpdateCommand.Parameters.Add("?oldJobName", OleDbType.Char, 8, "JobName").SourceVersion = DataRowVersion.Original

i = masterDataAdapter.Update(data, "Job")
MessageBox.Show("Number of records updated: " & i)

Thanks in advance...

Rita

 

[jmcilhinney]

Firstly, while it's not illegal, it's a bad idea to use an editable field as the primary key. I'm guessing that you're using Access, in which case you should be using an AutoNumber column as the PK.

Secondly, I see 7 parameters in your SQL code and yet I only see you adding 4 parameters to your command. Have you left some out for brevity? If not then that's an issue. If so then it's a good idea to indicate that rather than expect us to guess.

Thirdly, why are you using Add for ModifiedBy and AddWithValue for ModifiedDate? Neither is wrong but there's absolutely no reason to use different methods for the two of them. The obvious choice is to use AddWithValue for both.

Finally, if you are using Access, then you MUST add the parameters to the command in the same order as they appear in the SQL code. Think about it. All you have in the SQL code is ? so how is the system supposed to know which parameter added to the command corresponds to which ? in the SQL code other than by using position?

 

[RNathan]

Thanks for your response jmcilhinney. I am using Visual FoxPro 9.0 for database. This is the first time I am dealing with DataAdapters, parameters etc. Since the database was created by someone else I can't create a AutoNumber column this time. Good advise though. Based on your comments, I changed the code as follows but the ModifiedBy and ModifiedDate are still not getting updated with the value that I am specifying.

masterDataAdapter.UpdateCommand =New OleDbCommand( "UPDATE Job SET JobName = ?JobName, JobTitle = ?, JobDelete = ?, Comments = ?, ModifiedBy = ?, ModifiedDate = ? WHERE JobName = ?oldJobName", objConnection)
'Create the parameters
masterDataAdapter.UpdateCommand.Parameters.Add("?JobName", OleDbType.Char, 8, "JobName")
masterDataAdapter.UpdateCommand.Parameters.Add("?JobTitle", OleDbType.Char, 40, "JobTitle")
masterDataAdapter.UpdateCommand.Parameters.Add("?JobDelete", OleDbType.Boolean, 0, "JobDelete")
masterDataAdapter.UpdateCommand.Parameters.Add("?Comments", OleDbType.Char, 254, "Comments")
masterDataAdapter.UpdateCommand.Parameters.AddWithValue("?ModifiedBy", UserName)
masterDataAdapter.UpdateCommand.Parameters.AddWithValue("?ModifiedDate", DateTime.Today)
masterDataAdapter.UpdateCommand.Parameters.Add("?oldJobName", OleDbType.Char, 8, "JobName").SourceVersion = DataRowVersion.Original
masterDataAdapter.UpdateCommand = New OleDbCommandBuilder(masterDataAdapter).GetUpdateCommand()
i = masterDataAdapter.Update(data, "Job")
MessageBox.Show("Number of records updated: " & i)

Thanks,

RN

 

[jmcilhinney]

I've never actually tried to use explicit values for some parameters only. That code looks like it should work but I'll throw together a test app later today. I don't have FoxPro but I can test using Access and SQL Server.

 

[JohnH]

masterDataAdapter.UpdateCommand = New OleDbCommandBuilder(masterDataAdapter).GetUpdateCo mmand()

Why are you discarding the command object that you built?

 

[RNathan]

John, if you are talking about the following statement, pls. disregard. It wasn't meant to be there...

masterDataAdapter.UpdateCommand = New OleDbCommandBuilder(masterDataAdapter).GetUpdateCommand()

 

[RNathan]

I figuered out how to do this. Here is a sample code:
PS: I am using the colon because the database is Oracle.

' Add data from the Job table to the DataSet.
Dim cmd As New OleDb.OleDbCommand("Select JobName, JobTitle, ModifiedBy, ModifiedDate from Job_t Order By JobName", objConnection)
DataAdapter = New OleDbDataAdapter(cmd)
DataAdapter.Fill(data, "Job")

DataAdapter.UpdateCommand = New OleDbCommand( _
"UPDATE Job_t SET JobName = ?, JobTitle = ?, ModifiedBy = '" & UserName & "', ModifiedDate = SYSDATE WHERE JobName = ldJobName", objConnection)
DataAdapter.UpdateCommand.Parameters.Add(":JobName", OleDbType.Char, 8, "JobName")
DataAdapter.UpdateCommand.Parameters.Add(":JobTitle", OleDbType.Char, 40, "JobTitle")
DataAdapter.UpdateCommand.Parameters.Add("ldJobName", OleDbType.Char, 8, "JobName").SourceVersion = DataRowVersion.Original

DataAdapter.InsertCommand = New OleDbCommand( _
"INSERT INTO Job_t (JobName, JobTitle, ModifiedBy, ModifiedDate) VALUESpJobName, JobTitle, '" & UserName & "', SysDate)", objConnection)
DataAdapter.InsertCommand.Parameters.Add("pJobName", OleDbType.Char, 8, "JobName")
DataAdapter.InsertCommand.Parameters.Add("pJobTitle", OleDbType.Char, 40, "JobTitle")

DataAdapter.DeleteCommand = New OleDbCommand("Delete from Job_t Where JobName = :JobName", objConnection)
DataAdapter.DeleteCommand.Parameters.Add(":JobName", OleDbType.Char, 8, "JobName")
DataAdapter.DeleteCommand.Parameters(":JobName").SourceVersion = DataRowVersion.Original


For those of you who are still new to .Net and DataGridView, place the select, update, delete and insert commands in the Form Load event. When you want to update the DataGridView control, you can write something like

NoOfRecUpdated = DataAdapter.Update(data,"Job")Hope this helps....



RN