Concurrency Violation Exception
Hi i am writing displaying a table using DataGridView. My Table is dynamic in the sense it is created only after the application is running so there is no chance i can know about the no of columns available. However, i am having useridauto, which will always be the primary key and it is also autoincrement. So, i need to write my own insertcommand and updatecommand. My code is as follows:
************************************************
'this is used to dynamically create a insert command according to the table
Dim ins As New OleDbCommand
'insert command
query = "INSERT INTO T_" & frmHome.strSelectedSet & "( "
j = Nothing
For Each j In gridviewAnswerData.Columns
'leave the first column which is useridauto
If Not j.Name = gridviewAnswerData.Columns(0).Name Then
query += j.Name & ","
End If
Next
query = query.Remove(query.Length - 1, 1)
query += ") VALUES( "
For Each j In gridviewAnswerData.Columns
'leave the first column which is useridauto
If Not j.Name = gridviewAnswerData.Columns(0).Name Then
query += "@" & j.Name & ","
End If
Next
query = query.Remove(query.Length - 1, 1)
query += ")"
MessageBox.Show(query)
'assigning the insert command
ins.CommandText = query
ins.Connection = New OleDbConnection(IshaData.My.Settings.ishaConnectionString)
daSet.InsertCommand = ins
For Each j In gridviewAnswerData.Columns
'leave the first column which is useridauto
If Not j.Name = gridviewAnswerData.Columns(0).Name Then
daSet.InsertCommand.Parameters.Add("@" + j.Name, OleDbType.VarChar).SourceColumn = j.Name
End If
Next
'Update command
query = "UPDATE T_" & frmHome.strSelectedSet & " SET "
j = Nothing
For Each j In gridviewAnswerData.Columns
'leave the first column which is useridauto
If Not j.Name = gridviewAnswerData.Columns(0).Name Then
query += j.Name & "=" & "@" & j.Name & ","
Else
MessageBox.Show(j.Name)
End If
Next
query = query.Remove(query.Length - 1, 1)
query += " WHERE Useridauto=@" + gridviewAnswerData.Columns(0).Name
MessageBox.Show(query)
MessageBox.Show(gridviewAnswerData.Columns(0).Name)
'assigning the insert command
ins.CommandText = query
ins.Connection = New OleDbConnection(IshaData.My.Settings.ishaConnectionString)
daSet.UpdateCommand = ins
For Each j In gridviewAnswerData.Columns
'leave the first column which is useridauto
If Not j.Name = gridviewAnswerData.Columns(0).Name Then
daSet.UpdateCommand.Parameters.Add("@" + j.Name, OleDbType.VarChar).SourceColumn = j.Name
End If
Next
daSet.UpdateCommand.Parameters.Add("@" + gridviewAnswerData.Columns(0).Name, OleDbType.Integer).SourceColumn = gridviewAnswerData.Columns(0).Name
daSet.UpdateCommand.Parameters("@Useridauto").SourceVersion = DataRowVersion.Original
************************************************
My Database is MS ACCESS. My insert command is working fine, but update command is not working and it is showing concurrency violation when i try to update. Any other details needed ill be happy to give. Please help.