S
sam
Hi,
I've read loads of posts in here and articles on the internet about this,
but for some reason, I just can't get it working. My app needs to read from
multiple Excel data sources, and it doesn't know any of the column name or
types, only the PrimaryID field name (and the table/worksheet name of
course).
I can't use the CommandBuilder to generate my Insert, Update and Delete
commands, because OleDB can't read the primaryID info from Excel.
My idea was first to copy the Excel data into an Access db to use for
testing. Then use the CommandBuilder to automatically generate Update,
Insert, Delete commands against the Access datasource, then create a
function to dynamically build my command strings, based on column data
returned from GetOleDbSchemaTable.
I optimised my command strings a bit, I didn't like the syntax of the
commandBuilder-generated ones.
My dynamically built strings, returned by my function, are as follows:
Select * From [Fields]
UPDATE [Fields] SET [Address] = @Address, [Occupant] = @Occupant, [Tel No] =
@Tel_No, [Rent] = @Rent, [Review Month] = @Review_Month, [Maint Year] =
@Maint_Year WHERE [FieldId] = @FieldId
INSERT INTO [Fields] ([FieldId], [Address], [Occupant], [Tel No], [Rent],
[Review Month], [Maint Year]) VALUES (@FieldId, @Address, @Occupant,
@Tel_No, @Rent, @Review_Month, @Maint_Year)
DELETE FROM [Fields] WHERE [FieldId] = @FieldId
However, I get "Concurrency violation: the UpdateCommand affected 0 records"
when I try to call the DataAdapter.Update method after making a change to my
data. Someone somewhere on here suggested this error is returned also when
the SQL queries are badly formed.
The three functions are below. The first one, called GetData, creates a
dataadapter by calling either BuildCommandsManually or
BuildCommandsAutomatically (which uses the CommandBuilder). If I build the
commands automatically, I don't get an error and the update works. If I use
the ..Manually function, I get the pesky Concurrency Violation.
Also below the functions are the outputs from the Debug.Write( ) calls in
GetData, including all the parameters info. I have a feeling it is to do
with the way I am setting the parameters in the BuildCommandsManually
function. Possibly to do with datatypes etc.
Any help would be extremely appreciated.
Sam
nb. Ignore the bits regarding the '$' signs in the ..Manually function.
Excel needs the this at the end of the table name, I strip them out in this,
the Access version.
Code:
Public Function GetData() As System.Data.DataTable Implements
IDataMarshaller.GetData
Dim dataTable As System.Data.DataTable
Dim dtChanges As dataTable
' Debug variables
Dim datarow As datarow
Dim datacolumn As datacolumn
Dim s As String
Dim param As OleDbParameter
Dim i As Integer
Try
' Create connection to the excel datasource
_dataConnection = New OleDb.OleDbConnection(_connectionString)
' Create DataAdapter, either Manually or Automatically
' Comment out one of the lines below
_dataAdapter = BuildCommandsAutomatically()
_dataAdapter = BuildCommandsManually()
' DEBUG: View the generated command strings
Debug.Write(vbCrLf +
_dataAdapter.SelectCommand.CommandText.ToString + vbCrLf)
Debug.Write(vbCrLf +
_dataAdapter.UpdateCommand.CommandText.ToString + vbCrLf)
Debug.Write(vbCrLf +
_dataAdapter.InsertCommand.CommandText.ToString + vbCrLf)
Debug.Write(vbCrLf +
_dataAdapter.DeleteCommand.CommandText.ToString + vbCrLf)
' DEBUG: View the generated command parameters
Dim cmds() As OleDbCommand = {_dataAdapter.UpdateCommand,
_dataAdapter.InsertCommand, _dataAdapter.DeleteCommand}
Dim strs() As String = {"Update", "Insert", "Delete"}
For i = 0 To 2
Debug.Write(vbCrLf + vbCrLf + strs(i) + " Params: " + vbCrLf
+ vbCrLf)
For Each param In cmds(i).Parameters
Debug.Write("ParameterName: " + vbTab + vbTab +
param.ParameterName + vbCrLf)
Debug.Write("OleDbType: " + vbTab + vbTab +
param.OleDbType.ToString + vbCrLf)
Debug.Write("DbType: " + vbTab + vbTab +
param.DbType.ToString + vbCrLf)
Debug.Write("Size: " + vbTab + vbTab +
param.Size.ToString + vbCrLf)
Debug.Write("SourceColumn: " + vbTab + vbTab +
param.SourceColumn + vbCrLf)
Debug.Write("SourceVersion: " + vbTab + vbTab +
param.SourceVersion.ToString + vbCrLf)
Debug.Write(vbCrLf)
Next param
Next i
' Fill the datatable
dataTable = New System.Data.DataTable(_parent.Name)
_dataAdapter.Fill(dataTable)
' TEST: Change a value
dataTable.Rows(0).Item("Occupant") = Now.ToLongTimeString
' Get the changes
dtChanges = dataTable.GetChanges()
' DEBUG: View the contents of the changes datatable
For Each datarow In dtChanges.Rows
Debug.Write(vbCrLf + vbCrLf)
For Each datacolumn In dtChanges.Columns
If (IsDBNull(datarow(datacolumn))) Then s = " " Else s =
CStr(datarow(datacolumn))
Debug.Write(s + ", ")
Next
Next
Try
' Now try to update the db with the changes
_dataAdapter.Update(dtChanges)
Catch ex As DBConcurrencyException
System.Windows.Forms.MessageBox.Show(ex.ToString)
End Try
' Accept the datatable changes
dataTable.AcceptChanges()
Return dataTable
Catch ex As Exception
Throw ex
Finally
' Tidy up
If Not _dataConnection Is Nothing AndAlso _dataConnection.State
= ConnectionState.Open Then _dataConnection.Close()
If Not _dataConnection Is Nothing Then _dataConnection.Dispose()
If Not _dataCommandBuilder Is Nothing Then
_dataCommandBuilder.Dispose()
_dataConnection = Nothing
_dataCommandBuilder = Nothing
End Try
End Function
Public Function BuildCommandsAutomatically()
Dim dataAdapter As OleDb.OleDbDataAdapter
Dim sql As String
Try
' Initialize the DataAdapter with the Select string
sql = String.Format("Select * From [{0}]", _parent.TableName)
dataAdapter = New OleDb.OleDbDataAdapter(sql, _dataConnection)
' Initialize the OleDbCommandBuilder object
_dataCommandBuilder = New OleDb.OleDbCommandBuilder(dataAdapter)
_dataCommandBuilder.QuotePrefix = "["
_dataCommandBuilder.QuoteSuffix = "]"
' Automatically build the commands
dataAdapter.InsertCommand = _dataCommandBuilder.GetInsertCommand
dataAdapter.DeleteCommand = _dataCommandBuilder.GetDeleteCommand
dataAdapter.UpdateCommand = _dataCommandBuilder.GetUpdateCommand
Return dataAdapter
Catch ex As Exception
Throw ex
End Try
End Function
Public Function BuildCommandsManually()
' Data objects
Dim dataTable As System.Data.DataTable
Dim dvColumns As System.Data.DataView
Dim dataAdapter As OleDb.OleDbDataAdapter
Dim drvColumn As DataRowView
' Column variables
Dim columnName As String
Dim columnNameNoSpaces As String
Dim columnDataType As Integer
Dim columnOLEDataType As System.Data.OleDb.OleDbType
Dim columnSize As Integer
' String builders to hold the command texts
Dim sbInsert As System.Text.StringBuilder
Dim sbInsertValues As System.Text.StringBuilder
Dim sbUpdate As System.Text.StringBuilder
Dim sbUpdateWhere As System.Text.StringBuilder
Dim sbDelete As System.Text.StringBuilder
Dim sql As String
Dim para As OleDb.OleDbParameter
Try
' Get the columns in the database, via GetOleDbSchemaTable, and
sort by column order
dataTable = GetColumnTable()
dvColumns = New DataView(dataTable)
dvColumns.Sort = "ORDINAL_POSITION"
' Create DataAdapter
'sql = String.Format("Select * From [{0}$]", _parent.TableName)
sql = String.Format("Select * From [{0}]", _parent.TableName)
dataAdapter = New OleDb.OleDbDataAdapter(sql, _dataConnection)
' Create insert, delete and update commands
OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand
OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand
OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand
' Buil the command strings dynamically
sbInsert = New System.Text.StringBuilder
sbInsertValues = New System.Text.StringBuilder
sbInsert.Append("INSERT INTO [")
sbInsert.Append(_parent.TableName)
sbInsert.Append("$] (")
sbUpdate = New System.Text.StringBuilder
sbUpdateWhere = New System.Text.StringBuilder
sbUpdate.Append("UPDATE [")
sbUpdate.Append(_parent.TableName)
sbUpdate.Append("$] SET ")
sbDelete = New System.Text.StringBuilder
sbDelete.Append("DELETE FROM [")
sbDelete.Append(_parent.TableName)
sbDelete.Append("$] WHERE ")
' Loop through each column, dyanamically building the command
strings
For Each drvColumn In dvColumns
' Get the column name, datatype and size
columnName = drvColumn.Item("COLUMN_NAME")
columnNameNoSpaces = columnName.Replace(" ", "_")
columnDataType = drvColumn.Item("DATA_TYPE")
If IsDBNull(drvColumn.Item("CHARACTER_MAXIMUM_LENGTH")) Then
columnSize = 0
Else
columnSize = drvColumn.Item("CHARACTER_MAXIMUM_LENGTH")
End If
sbInsert.Append("[")
sbInsert.Append(columnName)
sbInsert.Append("], ")
sbInsertValues.Append("@")
sbInsertValues.Append(columnNameNoSpaces)
sbInsertValues.Append(", ")
If columnName = _parent.PrimaryIdField Then
sbDelete.Append("[")
sbDelete.Append(columnName)
sbDelete.Append("] = @")
sbDelete.Append(columnNameNoSpaces)
Else
sbUpdate.Append("[")
sbUpdate.Append(columnName)
sbUpdate.Append("] = @")
sbUpdate.Append(columnNameNoSpaces)
sbUpdate.Append(", ")
End If
' Create and attach the command parameters
para = New OleDb.OleDbParameter("@" + columnNameNoSpaces,
columnDataType, columnSize)
para.SourceVersion = DataRowVersion.Current
para.SourceColumn = columnName
OleDbInsertCommand1.Parameters.Add(para)
If columnName = _parent.PrimaryIdField Then
' PrimaryID parameter needs the DataRowVersion.Original
SourceVersion
para = New OleDb.OleDbParameter("@" +
columnNameNoSpaces, columnDataType, columnSize)
para.SourceVersion = DataRowVersion.Original
para.SourceColumn = columnName
OleDbUpdateCommand1.Parameters.Add(para)
para = New OleDb.OleDbParameter("@" +
columnNameNoSpaces, columnDataType, columnSize)
para.SourceVersion = DataRowVersion.Original
para.SourceColumn = columnName
OleDbDeleteCommand1.Parameters.Add(para)
Else
' Non-primaryId parameters use the
DataRowVersion.Current SourceVersion
para = New OleDb.OleDbParameter("@" +
columnNameNoSpaces, columnDataType, columnSize)
para.SourceVersion = DataRowVersion.Current
para.SourceColumn = columnName
OleDbUpdateCommand1.Parameters.Add(para)
End If
Next drvColumn
' Complete the command strings
sbInsert.Remove(sbInsert.Length - 2, 2)
sbInsert.Append(") VALUES (")
sbInsertValues.Remove(sbInsertValues.Length - 2, 2)
sbInsertValues.Append(")")
sbInsert.Append(sbInsertValues.ToString)
sbUpdate.Remove(sbUpdate.Length - 2, 2)
sbUpdate.Append(" WHERE [")
sbUpdate.Append(_parent.PrimaryIdField)
sbUpdate.Append("] = @")
sbUpdate.Append(_parent.PrimaryIdField)
' Set the DataAdapter's command objects
OleDbInsertCommand1.Connection = _dataConnection
OleDbInsertCommand1.CommandText =
Strings.Replace(sbInsert.ToString, "$", String.Empty)
OleDbUpdateCommand1.Connection = _dataConnection
OleDbUpdateCommand1.CommandText =
Strings.Replace(sbUpdate.ToString, "$", String.Empty)
OleDbDeleteCommand1.Connection = _dataConnection
OleDbDeleteCommand1.CommandText =
Strings.Replace(sbDelete.ToString, "$", String.Empty)
dataAdapter.DeleteCommand = Me.OleDbDeleteCommand1
dataAdapter.InsertCommand = Me.OleDbInsertCommand1
dataAdapter.UpdateCommand = Me.OleDbUpdateCommand1
Return dataAdapter
Catch ex As Exception
Throw ex
Finally
' Tidy up
If Not dvColumns Is Nothing Then dvColumns.Dispose()
If Not dataTable Is Nothing Then dataTable.Dispose()
sbInsert = Nothing
sbInsertValues = Nothing
sbUpdate = Nothing
sbUpdateWhere = Nothing
sbDelete = Nothing
para = Nothing
dvColumns = Nothing
dataTable = Nothing
drvColumn = Nothing
End Try
End Function
DEBUG OUTPUT:
' Automatic
Select * From [Fields]
UPDATE [Fields] SET [FieldId] = ? , [Address] = ? , [Occupant] = ? , [Tel
No] = ? , [Rent] = ? , [Review Month] = ? , [Maint Year] = ? WHERE (
([FieldId] = ?) AND ((? = 1 AND [Address] IS NULL) OR ([Address] = ?)) AND
((? = 1 AND [Occupant] IS NULL) OR ([Occupant] = ?)) AND ((? = 1 AND [Tel
No] IS NULL) OR ([Tel No] = ?)) AND ((? = 1 AND [Rent] IS NULL) OR ([Rent] =
?)) AND ((? = 1 AND [Review Month] IS NULL) OR ([Review Month] = ?)) AND ((?
= 1 AND [Maint Year] IS NULL) OR ([Maint Year] = ?)) )
INSERT INTO [Fields]( [FieldId] , [Address] , [Occupant] , [Tel No] , [Rent]
, [Review Month] , [Maint Year] ) VALUES ( ? , ? , ? , ? , ? , ? , ? )
DELETE FROM [Fields] WHERE ( ([FieldId] = ?) AND ((? = 1 AND [Address] IS
NULL) OR ([Address] = ?)) AND ((? = 1 AND [Occupant] IS NULL) OR ([Occupant]
= ?)) AND ((? = 1 AND [Tel No] IS NULL) OR ([Tel No] = ?)) AND ((? = 1 AND
[Rent] IS NULL) OR ([Rent] = ?)) AND ((? = 1 AND [Review Month] IS NULL) OR
([Review Month] = ?)) AND ((? = 1 AND [Maint Year] IS NULL) OR ([Maint Year]
= ?)) )
Update Params:
ParameterName: @p1
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: FieldId
SourceVersion: Current
ParameterName: @p2
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Address
SourceVersion: Current
ParameterName: @p3
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Occupant
SourceVersion: Current
ParameterName: @p4
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Tel No
SourceVersion: Current
ParameterName: @p5
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Rent
SourceVersion: Current
ParameterName: @p6
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Review Month
SourceVersion: Current
ParameterName: @p7
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Maint Year
SourceVersion: Current
ParameterName: @p8
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: FieldId
SourceVersion: Original
ParameterName: @p9
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p10
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Address
SourceVersion: Original
ParameterName: @p11
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p12
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Occupant
SourceVersion: Original
ParameterName: @p13
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p14
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Tel No
SourceVersion: Original
ParameterName: @p15
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p16
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Rent
SourceVersion: Original
ParameterName: @p17
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p18
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Review Month
SourceVersion: Original
ParameterName: @p19
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p20
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Maint Year
SourceVersion: Original
Insert Params:
ParameterName: @p1
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: FieldId
SourceVersion: Current
ParameterName: @p2
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Address
SourceVersion: Current
ParameterName: @p3
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Occupant
SourceVersion: Current
ParameterName: @p4
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Tel No
SourceVersion: Current
ParameterName: @p5
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Rent
SourceVersion: Current
ParameterName: @p6
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Review Month
SourceVersion: Current
ParameterName: @p7
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Maint Year
SourceVersion: Current
Delete Params:
ParameterName: @p1
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: FieldId
SourceVersion: Original
ParameterName: @p2
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p3
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Address
SourceVersion: Original
ParameterName: @p4
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p5
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Occupant
SourceVersion: Original
ParameterName: @p6
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p7
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Tel No
SourceVersion: Original
ParameterName: @p8
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p9
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Rent
SourceVersion: Original
ParameterName: @p10
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p11
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Review Month
SourceVersion: Original
ParameterName: @p12
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p13
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Maint Year
SourceVersion: Original
45/034, Paddock No. 2 Rear Church Rd, 13:06:13, 239345, , 9, ,
' Manual
Select * From [Fields]
UPDATE [Fields] SET [Address] = @Address, [Occupant] = @Occupant, [Tel No] =
@Tel_No, [Rent] = @Rent, [Review Month] = @Review_Month, [Maint Year] =
@Maint_Year WHERE [FieldId] = @FieldId
INSERT INTO [Fields] ([FieldId], [Address], [Occupant], [Tel No], [Rent],
[Review Month], [Maint Year]) VALUES (@FieldId, @Address, @Occupant,
@Tel_No, @Rent, @Review_Month, @Maint_Year)
DELETE FROM [Fields] WHERE [FieldId] = @FieldId
Update Params:
ParameterName: @FieldId
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: FieldId
SourceVersion: Original
ParameterName: @Address
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: Address
SourceVersion: Current
ParameterName: @Occupant
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: Occupant
SourceVersion: Current
ParameterName: @Tel_No
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Tel No
SourceVersion: Current
ParameterName: @Rent
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: Rent
SourceVersion: Current
ParameterName: @Review_Month
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Review Month
SourceVersion: Current
ParameterName: @Maint_Year
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Maint Year
SourceVersion: Current
Insert Params:
ParameterName: @FieldId
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: FieldId
SourceVersion: Current
ParameterName: @Address
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: Address
SourceVersion: Current
ParameterName: @Occupant
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: Occupant
SourceVersion: Current
ParameterName: @Tel_No
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Tel No
SourceVersion: Current
ParameterName: @Rent
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: Rent
SourceVersion: Current
ParameterName: @Review_Month
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Review Month
SourceVersion: Current
ParameterName: @Maint_Year
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Maint Year
SourceVersion: Current
Delete Params:
ParameterName: @FieldId
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: FieldId
SourceVersion: Original
45/034, Paddock No. 2 Rear Church Rd, 13:04:26, 239345, , 9, ,
I've read loads of posts in here and articles on the internet about this,
but for some reason, I just can't get it working. My app needs to read from
multiple Excel data sources, and it doesn't know any of the column name or
types, only the PrimaryID field name (and the table/worksheet name of
course).
I can't use the CommandBuilder to generate my Insert, Update and Delete
commands, because OleDB can't read the primaryID info from Excel.
My idea was first to copy the Excel data into an Access db to use for
testing. Then use the CommandBuilder to automatically generate Update,
Insert, Delete commands against the Access datasource, then create a
function to dynamically build my command strings, based on column data
returned from GetOleDbSchemaTable.
I optimised my command strings a bit, I didn't like the syntax of the
commandBuilder-generated ones.
My dynamically built strings, returned by my function, are as follows:
Select * From [Fields]
UPDATE [Fields] SET [Address] = @Address, [Occupant] = @Occupant, [Tel No] =
@Tel_No, [Rent] = @Rent, [Review Month] = @Review_Month, [Maint Year] =
@Maint_Year WHERE [FieldId] = @FieldId
INSERT INTO [Fields] ([FieldId], [Address], [Occupant], [Tel No], [Rent],
[Review Month], [Maint Year]) VALUES (@FieldId, @Address, @Occupant,
@Tel_No, @Rent, @Review_Month, @Maint_Year)
DELETE FROM [Fields] WHERE [FieldId] = @FieldId
However, I get "Concurrency violation: the UpdateCommand affected 0 records"
when I try to call the DataAdapter.Update method after making a change to my
data. Someone somewhere on here suggested this error is returned also when
the SQL queries are badly formed.
The three functions are below. The first one, called GetData, creates a
dataadapter by calling either BuildCommandsManually or
BuildCommandsAutomatically (which uses the CommandBuilder). If I build the
commands automatically, I don't get an error and the update works. If I use
the ..Manually function, I get the pesky Concurrency Violation.
Also below the functions are the outputs from the Debug.Write( ) calls in
GetData, including all the parameters info. I have a feeling it is to do
with the way I am setting the parameters in the BuildCommandsManually
function. Possibly to do with datatypes etc.
Any help would be extremely appreciated.
Sam
nb. Ignore the bits regarding the '$' signs in the ..Manually function.
Excel needs the this at the end of the table name, I strip them out in this,
the Access version.
Code:
Public Function GetData() As System.Data.DataTable Implements
IDataMarshaller.GetData
Dim dataTable As System.Data.DataTable
Dim dtChanges As dataTable
' Debug variables
Dim datarow As datarow
Dim datacolumn As datacolumn
Dim s As String
Dim param As OleDbParameter
Dim i As Integer
Try
' Create connection to the excel datasource
_dataConnection = New OleDb.OleDbConnection(_connectionString)
' Create DataAdapter, either Manually or Automatically
' Comment out one of the lines below
_dataAdapter = BuildCommandsAutomatically()
_dataAdapter = BuildCommandsManually()
' DEBUG: View the generated command strings
Debug.Write(vbCrLf +
_dataAdapter.SelectCommand.CommandText.ToString + vbCrLf)
Debug.Write(vbCrLf +
_dataAdapter.UpdateCommand.CommandText.ToString + vbCrLf)
Debug.Write(vbCrLf +
_dataAdapter.InsertCommand.CommandText.ToString + vbCrLf)
Debug.Write(vbCrLf +
_dataAdapter.DeleteCommand.CommandText.ToString + vbCrLf)
' DEBUG: View the generated command parameters
Dim cmds() As OleDbCommand = {_dataAdapter.UpdateCommand,
_dataAdapter.InsertCommand, _dataAdapter.DeleteCommand}
Dim strs() As String = {"Update", "Insert", "Delete"}
For i = 0 To 2
Debug.Write(vbCrLf + vbCrLf + strs(i) + " Params: " + vbCrLf
+ vbCrLf)
For Each param In cmds(i).Parameters
Debug.Write("ParameterName: " + vbTab + vbTab +
param.ParameterName + vbCrLf)
Debug.Write("OleDbType: " + vbTab + vbTab +
param.OleDbType.ToString + vbCrLf)
Debug.Write("DbType: " + vbTab + vbTab +
param.DbType.ToString + vbCrLf)
Debug.Write("Size: " + vbTab + vbTab +
param.Size.ToString + vbCrLf)
Debug.Write("SourceColumn: " + vbTab + vbTab +
param.SourceColumn + vbCrLf)
Debug.Write("SourceVersion: " + vbTab + vbTab +
param.SourceVersion.ToString + vbCrLf)
Debug.Write(vbCrLf)
Next param
Next i
' Fill the datatable
dataTable = New System.Data.DataTable(_parent.Name)
_dataAdapter.Fill(dataTable)
' TEST: Change a value
dataTable.Rows(0).Item("Occupant") = Now.ToLongTimeString
' Get the changes
dtChanges = dataTable.GetChanges()
' DEBUG: View the contents of the changes datatable
For Each datarow In dtChanges.Rows
Debug.Write(vbCrLf + vbCrLf)
For Each datacolumn In dtChanges.Columns
If (IsDBNull(datarow(datacolumn))) Then s = " " Else s =
CStr(datarow(datacolumn))
Debug.Write(s + ", ")
Next
Next
Try
' Now try to update the db with the changes
_dataAdapter.Update(dtChanges)
Catch ex As DBConcurrencyException
System.Windows.Forms.MessageBox.Show(ex.ToString)
End Try
' Accept the datatable changes
dataTable.AcceptChanges()
Return dataTable
Catch ex As Exception
Throw ex
Finally
' Tidy up
If Not _dataConnection Is Nothing AndAlso _dataConnection.State
= ConnectionState.Open Then _dataConnection.Close()
If Not _dataConnection Is Nothing Then _dataConnection.Dispose()
If Not _dataCommandBuilder Is Nothing Then
_dataCommandBuilder.Dispose()
_dataConnection = Nothing
_dataCommandBuilder = Nothing
End Try
End Function
Public Function BuildCommandsAutomatically()
Dim dataAdapter As OleDb.OleDbDataAdapter
Dim sql As String
Try
' Initialize the DataAdapter with the Select string
sql = String.Format("Select * From [{0}]", _parent.TableName)
dataAdapter = New OleDb.OleDbDataAdapter(sql, _dataConnection)
' Initialize the OleDbCommandBuilder object
_dataCommandBuilder = New OleDb.OleDbCommandBuilder(dataAdapter)
_dataCommandBuilder.QuotePrefix = "["
_dataCommandBuilder.QuoteSuffix = "]"
' Automatically build the commands
dataAdapter.InsertCommand = _dataCommandBuilder.GetInsertCommand
dataAdapter.DeleteCommand = _dataCommandBuilder.GetDeleteCommand
dataAdapter.UpdateCommand = _dataCommandBuilder.GetUpdateCommand
Return dataAdapter
Catch ex As Exception
Throw ex
End Try
End Function
Public Function BuildCommandsManually()
' Data objects
Dim dataTable As System.Data.DataTable
Dim dvColumns As System.Data.DataView
Dim dataAdapter As OleDb.OleDbDataAdapter
Dim drvColumn As DataRowView
' Column variables
Dim columnName As String
Dim columnNameNoSpaces As String
Dim columnDataType As Integer
Dim columnOLEDataType As System.Data.OleDb.OleDbType
Dim columnSize As Integer
' String builders to hold the command texts
Dim sbInsert As System.Text.StringBuilder
Dim sbInsertValues As System.Text.StringBuilder
Dim sbUpdate As System.Text.StringBuilder
Dim sbUpdateWhere As System.Text.StringBuilder
Dim sbDelete As System.Text.StringBuilder
Dim sql As String
Dim para As OleDb.OleDbParameter
Try
' Get the columns in the database, via GetOleDbSchemaTable, and
sort by column order
dataTable = GetColumnTable()
dvColumns = New DataView(dataTable)
dvColumns.Sort = "ORDINAL_POSITION"
' Create DataAdapter
'sql = String.Format("Select * From [{0}$]", _parent.TableName)
sql = String.Format("Select * From [{0}]", _parent.TableName)
dataAdapter = New OleDb.OleDbDataAdapter(sql, _dataConnection)
' Create insert, delete and update commands
OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand
OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand
OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand
' Buil the command strings dynamically
sbInsert = New System.Text.StringBuilder
sbInsertValues = New System.Text.StringBuilder
sbInsert.Append("INSERT INTO [")
sbInsert.Append(_parent.TableName)
sbInsert.Append("$] (")
sbUpdate = New System.Text.StringBuilder
sbUpdateWhere = New System.Text.StringBuilder
sbUpdate.Append("UPDATE [")
sbUpdate.Append(_parent.TableName)
sbUpdate.Append("$] SET ")
sbDelete = New System.Text.StringBuilder
sbDelete.Append("DELETE FROM [")
sbDelete.Append(_parent.TableName)
sbDelete.Append("$] WHERE ")
' Loop through each column, dyanamically building the command
strings
For Each drvColumn In dvColumns
' Get the column name, datatype and size
columnName = drvColumn.Item("COLUMN_NAME")
columnNameNoSpaces = columnName.Replace(" ", "_")
columnDataType = drvColumn.Item("DATA_TYPE")
If IsDBNull(drvColumn.Item("CHARACTER_MAXIMUM_LENGTH")) Then
columnSize = 0
Else
columnSize = drvColumn.Item("CHARACTER_MAXIMUM_LENGTH")
End If
sbInsert.Append("[")
sbInsert.Append(columnName)
sbInsert.Append("], ")
sbInsertValues.Append("@")
sbInsertValues.Append(columnNameNoSpaces)
sbInsertValues.Append(", ")
If columnName = _parent.PrimaryIdField Then
sbDelete.Append("[")
sbDelete.Append(columnName)
sbDelete.Append("] = @")
sbDelete.Append(columnNameNoSpaces)
Else
sbUpdate.Append("[")
sbUpdate.Append(columnName)
sbUpdate.Append("] = @")
sbUpdate.Append(columnNameNoSpaces)
sbUpdate.Append(", ")
End If
' Create and attach the command parameters
para = New OleDb.OleDbParameter("@" + columnNameNoSpaces,
columnDataType, columnSize)
para.SourceVersion = DataRowVersion.Current
para.SourceColumn = columnName
OleDbInsertCommand1.Parameters.Add(para)
If columnName = _parent.PrimaryIdField Then
' PrimaryID parameter needs the DataRowVersion.Original
SourceVersion
para = New OleDb.OleDbParameter("@" +
columnNameNoSpaces, columnDataType, columnSize)
para.SourceVersion = DataRowVersion.Original
para.SourceColumn = columnName
OleDbUpdateCommand1.Parameters.Add(para)
para = New OleDb.OleDbParameter("@" +
columnNameNoSpaces, columnDataType, columnSize)
para.SourceVersion = DataRowVersion.Original
para.SourceColumn = columnName
OleDbDeleteCommand1.Parameters.Add(para)
Else
' Non-primaryId parameters use the
DataRowVersion.Current SourceVersion
para = New OleDb.OleDbParameter("@" +
columnNameNoSpaces, columnDataType, columnSize)
para.SourceVersion = DataRowVersion.Current
para.SourceColumn = columnName
OleDbUpdateCommand1.Parameters.Add(para)
End If
Next drvColumn
' Complete the command strings
sbInsert.Remove(sbInsert.Length - 2, 2)
sbInsert.Append(") VALUES (")
sbInsertValues.Remove(sbInsertValues.Length - 2, 2)
sbInsertValues.Append(")")
sbInsert.Append(sbInsertValues.ToString)
sbUpdate.Remove(sbUpdate.Length - 2, 2)
sbUpdate.Append(" WHERE [")
sbUpdate.Append(_parent.PrimaryIdField)
sbUpdate.Append("] = @")
sbUpdate.Append(_parent.PrimaryIdField)
' Set the DataAdapter's command objects
OleDbInsertCommand1.Connection = _dataConnection
OleDbInsertCommand1.CommandText =
Strings.Replace(sbInsert.ToString, "$", String.Empty)
OleDbUpdateCommand1.Connection = _dataConnection
OleDbUpdateCommand1.CommandText =
Strings.Replace(sbUpdate.ToString, "$", String.Empty)
OleDbDeleteCommand1.Connection = _dataConnection
OleDbDeleteCommand1.CommandText =
Strings.Replace(sbDelete.ToString, "$", String.Empty)
dataAdapter.DeleteCommand = Me.OleDbDeleteCommand1
dataAdapter.InsertCommand = Me.OleDbInsertCommand1
dataAdapter.UpdateCommand = Me.OleDbUpdateCommand1
Return dataAdapter
Catch ex As Exception
Throw ex
Finally
' Tidy up
If Not dvColumns Is Nothing Then dvColumns.Dispose()
If Not dataTable Is Nothing Then dataTable.Dispose()
sbInsert = Nothing
sbInsertValues = Nothing
sbUpdate = Nothing
sbUpdateWhere = Nothing
sbDelete = Nothing
para = Nothing
dvColumns = Nothing
dataTable = Nothing
drvColumn = Nothing
End Try
End Function
DEBUG OUTPUT:
' Automatic
Select * From [Fields]
UPDATE [Fields] SET [FieldId] = ? , [Address] = ? , [Occupant] = ? , [Tel
No] = ? , [Rent] = ? , [Review Month] = ? , [Maint Year] = ? WHERE (
([FieldId] = ?) AND ((? = 1 AND [Address] IS NULL) OR ([Address] = ?)) AND
((? = 1 AND [Occupant] IS NULL) OR ([Occupant] = ?)) AND ((? = 1 AND [Tel
No] IS NULL) OR ([Tel No] = ?)) AND ((? = 1 AND [Rent] IS NULL) OR ([Rent] =
?)) AND ((? = 1 AND [Review Month] IS NULL) OR ([Review Month] = ?)) AND ((?
= 1 AND [Maint Year] IS NULL) OR ([Maint Year] = ?)) )
INSERT INTO [Fields]( [FieldId] , [Address] , [Occupant] , [Tel No] , [Rent]
, [Review Month] , [Maint Year] ) VALUES ( ? , ? , ? , ? , ? , ? , ? )
DELETE FROM [Fields] WHERE ( ([FieldId] = ?) AND ((? = 1 AND [Address] IS
NULL) OR ([Address] = ?)) AND ((? = 1 AND [Occupant] IS NULL) OR ([Occupant]
= ?)) AND ((? = 1 AND [Tel No] IS NULL) OR ([Tel No] = ?)) AND ((? = 1 AND
[Rent] IS NULL) OR ([Rent] = ?)) AND ((? = 1 AND [Review Month] IS NULL) OR
([Review Month] = ?)) AND ((? = 1 AND [Maint Year] IS NULL) OR ([Maint Year]
= ?)) )
Update Params:
ParameterName: @p1
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: FieldId
SourceVersion: Current
ParameterName: @p2
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Address
SourceVersion: Current
ParameterName: @p3
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Occupant
SourceVersion: Current
ParameterName: @p4
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Tel No
SourceVersion: Current
ParameterName: @p5
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Rent
SourceVersion: Current
ParameterName: @p6
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Review Month
SourceVersion: Current
ParameterName: @p7
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Maint Year
SourceVersion: Current
ParameterName: @p8
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: FieldId
SourceVersion: Original
ParameterName: @p9
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p10
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Address
SourceVersion: Original
ParameterName: @p11
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p12
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Occupant
SourceVersion: Original
ParameterName: @p13
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p14
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Tel No
SourceVersion: Original
ParameterName: @p15
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p16
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Rent
SourceVersion: Original
ParameterName: @p17
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p18
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Review Month
SourceVersion: Original
ParameterName: @p19
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p20
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Maint Year
SourceVersion: Original
Insert Params:
ParameterName: @p1
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: FieldId
SourceVersion: Current
ParameterName: @p2
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Address
SourceVersion: Current
ParameterName: @p3
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Occupant
SourceVersion: Current
ParameterName: @p4
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Tel No
SourceVersion: Current
ParameterName: @p5
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Rent
SourceVersion: Current
ParameterName: @p6
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Review Month
SourceVersion: Current
ParameterName: @p7
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Maint Year
SourceVersion: Current
Delete Params:
ParameterName: @p1
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: FieldId
SourceVersion: Original
ParameterName: @p2
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p3
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Address
SourceVersion: Original
ParameterName: @p4
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p5
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Occupant
SourceVersion: Original
ParameterName: @p6
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p7
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Tel No
SourceVersion: Original
ParameterName: @p8
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p9
OleDbType: VarWChar
DbType: String
Size: 0
SourceColumn: Rent
SourceVersion: Original
ParameterName: @p10
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p11
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Review Month
SourceVersion: Original
ParameterName: @p12
OleDbType: Integer
DbType: Int32
Size: 0
SourceColumn:
SourceVersion: Current
ParameterName: @p13
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Maint Year
SourceVersion: Original
45/034, Paddock No. 2 Rear Church Rd, 13:06:13, 239345, , 9, ,
' Manual
Select * From [Fields]
UPDATE [Fields] SET [Address] = @Address, [Occupant] = @Occupant, [Tel No] =
@Tel_No, [Rent] = @Rent, [Review Month] = @Review_Month, [Maint Year] =
@Maint_Year WHERE [FieldId] = @FieldId
INSERT INTO [Fields] ([FieldId], [Address], [Occupant], [Tel No], [Rent],
[Review Month], [Maint Year]) VALUES (@FieldId, @Address, @Occupant,
@Tel_No, @Rent, @Review_Month, @Maint_Year)
DELETE FROM [Fields] WHERE [FieldId] = @FieldId
Update Params:
ParameterName: @FieldId
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: FieldId
SourceVersion: Original
ParameterName: @Address
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: Address
SourceVersion: Current
ParameterName: @Occupant
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: Occupant
SourceVersion: Current
ParameterName: @Tel_No
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Tel No
SourceVersion: Current
ParameterName: @Rent
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: Rent
SourceVersion: Current
ParameterName: @Review_Month
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Review Month
SourceVersion: Current
ParameterName: @Maint_Year
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Maint Year
SourceVersion: Current
Insert Params:
ParameterName: @FieldId
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: FieldId
SourceVersion: Current
ParameterName: @Address
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: Address
SourceVersion: Current
ParameterName: @Occupant
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: Occupant
SourceVersion: Current
ParameterName: @Tel_No
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Tel No
SourceVersion: Current
ParameterName: @Rent
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: Rent
SourceVersion: Current
ParameterName: @Review_Month
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Review Month
SourceVersion: Current
ParameterName: @Maint_Year
OleDbType: Double
DbType: Double
Size: 0
SourceColumn: Maint Year
SourceVersion: Current
Delete Params:
ParameterName: @FieldId
OleDbType: WChar
DbType: StringFixedLength
Size: 255
SourceColumn: FieldId
SourceVersion: Original
45/034, Paddock No. 2 Rear Church Rd, 13:04:26, 239345, , 9, ,