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 now added two more test functions and it's behaving even more
strangely.

I now have four ways of building the commands for my dataadapter:

1. BuildCommandsAutomatically

This uses the OleDbCommandBuilder and it works nicely. However, I need
to generate my command objects dynamically, and from a source that OleDb
doesn't recognize as having a Primary key field.

2. BuildCommandsManually

I dynamically build the command strings from column information
returned from GetOleDbSchemaTable. This is the method I must use for the
final app.

At the moment I still get the following error when I call the
dataadapter.Update method:

"System.Data.DBConcurrencyException: Concurrency violation: the
UpdateCommand affected 0 records.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

3. BuildCommandsAutomaticallyAndCopy error:

The code for this function is below. I use the OleDbCommandBuilder as in
(1), but then copy it's generated commands to my dataadapter. I get this
error: (looks like the command string has been cut short)

"System.Data.OleDb.OleDbException: Syntax error (missing operator) in query
expression '( (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 '.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

4. BuildCommandsHardcoded error:

I hardcode the command strings and parameters collections using the
precisely the output automatically generated by (1). The code for this
function is below. I get the following error:

"System.Data.OleDb.OleDbException: No value given for one or more required
parameters.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at PearTech.PinPoint.Data.ExcelDataMarshaller.GetData() in C:\Documents
and Settings\Sam Bourton\My Documents\My
Projects\PinPoint\Data\ExcelDataMarshaller.vb:line 819"


I am now completely lost. Functions 3 and 4 have just confused me even more,
and I'm no closer to finding out why 2 raises the Concurrency Exception.

Any ideas?

Thanks, Sam

Code:

Public Function BuildCommandsAutomaticallyAndCopy()

Dim dataAdapter As OleDb.OleDbDataAdapter
Dim sql As String
Dim cmd As OleDbCommand

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)

' Insert
cmd = new OleDbCommand
cmd = _dataCommandBuilder.GetInsertCommand
dataAdapter.InsertCommand = cmd

' Update
cmd = _dataCommandBuilder.GetDeleteCommand
dataAdapter.DeleteCommand = cmd

' Delete
cmd = _dataCommandBuilder.GetUpdateCommand
dataAdapter.UpdateCommand = cmd

Return dataAdapter

Catch ex As Exception
Throw ex
End Try
End Function

Public Function BuildCommandsHardcoded()

Dim dataAdapter As OleDb.OleDbDataAdapter
Dim sql As String
Dim param As OleDbParameter
Dim cmd As OleDbCommand
Dim i As Integer

Try
' Initialize the DataAdapter with the Select string
sql = String.Format("Select * From [{0}]", _parent.TableName)
dataAdapter = New OleDb.OleDbDataAdapter(sql, _dataConnection)

' Insert
sql = "INSERT INTO [Fields]" + _
"( [FieldId] , [Address] , [Occupant] , [Tel No] , [Rent]"
+ _
", [Review Month] , [Maint Year] ) VALUES ( ? , ? , ? , ?
, ? , ? , ? )"
cmd = New System.Data.OleDb.OleDbCommand
cmd.Connection = _dataConnection
cmd.CommandText = sql

param = New OleDbParameter("@p1", OleDbType.VarWChar, 0,
"FieldID")
param.SourceVersion = DataRowVersion.Current
cmd.Parameters.Add(param)

param = New OleDbParameter("@p2", OleDbType.VarWChar, 0,
"Address")
param.SourceVersion = DataRowVersion.Current
cmd.Parameters.Add(param)

param = New OleDbParameter("@p3", OleDbType.VarWChar, 0,
"Occupant")
param.SourceVersion = DataRowVersion.Current
cmd.Parameters.Add(param)

param = New OleDbParameter("@p4", OleDbType.Double, 0, "Tel No")
param.SourceVersion = DataRowVersion.Current
cmd.Parameters.Add(param)

param = New OleDbParameter("@p5", OleDbType.VarWChar, 0, "Rent")
param.SourceVersion = DataRowVersion.Current
cmd.Parameters.Add(param)

param = New OleDbParameter("@p6", OleDbType.Double, 0, "Review
Month")
param.SourceVersion = DataRowVersion.Current
cmd.Parameters.Add(param)

param = New OleDbParameter("@p7", OleDbType.Double, 0, "Maint
Year")
param.SourceVersion = DataRowVersion.Current
cmd.Parameters.Add(param)

dataAdapter.InsertCommand = cmd

' Update
sql = "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] = ?)) )"

cmd = New System.Data.OleDb.OleDbCommand
cmd.Connection = _dataConnection
cmd.CommandText = sql

param = New OleDbParameter("@p1", OleDbType.VarWChar, 0,
"FieldID")
param.SourceVersion = DataRowVersion.Current
cmd.Parameters.Add(param)

param = New OleDbParameter("@p2", OleDbType.VarWChar, 0,
"Address")
param.SourceVersion = DataRowVersion.Current
cmd.Parameters.Add(param)

param = New OleDbParameter("@p3", OleDbType.VarWChar, 0,
"Occupant")
param.SourceVersion = DataRowVersion.Current
cmd.Parameters.Add(param)

param = New OleDbParameter("@p4", OleDbType.Double, 0, "Tel No")
param.SourceVersion = DataRowVersion.Current
cmd.Parameters.Add(param)

param = New OleDbParameter("@p5", OleDbType.VarWChar, 0, "Rent")
param.SourceVersion = DataRowVersion.Current
cmd.Parameters.Add(param)

param = New OleDbParameter("@p6", OleDbType.Double, 0, "Review
Month")
param.SourceVersion = DataRowVersion.Current
cmd.Parameters.Add(param)

param = New OleDbParameter("@p7", OleDbType.Double, 0, "Maint
Year")
param.SourceVersion = DataRowVersion.Current
cmd.Parameters.Add(param)

param = New OleDbParameter("@p8", OleDbType.VarWChar, 0,
"FieldID")
param.SourceVersion = DataRowVersion.Original
cmd.Parameters.Add(param)

param = New OleDbParameter("@p9", OleDbType.Integer, 0)
param.SourceVersion = DataRowVersion.Current
param.Value = 1
cmd.Parameters.Add(param)

param = New OleDbParameter("@p10", OleDbType.VarWChar, 0,
"Address")
param.SourceVersion = DataRowVersion.Original
cmd.Parameters.Add(param)

param = New OleDbParameter("@p11", OleDbType.Integer, 0)
param.SourceVersion = DataRowVersion.Current
param.Value = 1
cmd.Parameters.Add(param)

param = New OleDbParameter("@p12", OleDbType.VarWChar, 0,
"Occupant")
param.SourceVersion = DataRowVersion.Original
cmd.Parameters.Add(param)

param = New OleDbParameter("@p13", OleDbType.Integer, 0)
param.SourceVersion = DataRowVersion.Current
param.Value = 1
cmd.Parameters.Add(param)

param = New OleDbParameter("@p14", OleDbType.Double, 0, "Tel
No")
param.SourceVersion = DataRowVersion.Original
cmd.Parameters.Add(param)

param = New OleDbParameter("@p15", OleDbType.Integer, 0)
param.SourceVersion = DataRowVersion.Current
param.Value = 1
cmd.Parameters.Add(param)

param = New OleDbParameter("@p16", OleDbType.VarWChar, 0,
"Rent")
param.SourceVersion = DataRowVersion.Original
cmd.Parameters.Add(param)

param = New OleDbParameter("@p17", OleDbType.Integer, 0)
param.SourceVersion = DataRowVersion.Current
param.Value = 1
cmd.Parameters.Add(param)

param = New OleDbParameter("@p18", OleDbType.Double, 0, "Review
Month")
param.SourceVersion = DataRowVersion.Original
cmd.Parameters.Add(param)

param = New OleDbParameter("@p19", OleDbType.Integer, 0)
param.SourceVersion = DataRowVersion.Current
param.Value = 1
cmd.Parameters.Add(param)

param = New OleDbParameter("@p20", OleDbType.Double, 0, "Maint
Year")
param.SourceVersion = DataRowVersion.Original
cmd.Parameters.Add(param)

dataAdapter.UpdateCommand = cmd

' Delete
sql = "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]" + _
"= ?)) )"

cmd = New System.Data.OleDb.OleDbCommand
cmd.Connection = _dataConnection
cmd.CommandText = sql

param = New OleDbParameter("@p1", OleDbType.VarWChar, 0,
"FieldID")
param.SourceVersion = DataRowVersion.Original
cmd.Parameters.Add(param)

param = New OleDbParameter("@p2", OleDbType.Integer, 0)
param.SourceVersion = DataRowVersion.Current
param.Value = 1
cmd.Parameters.Add(param)

param = New OleDbParameter("@p3", OleDbType.VarWChar, 0,
"Address")
param.SourceVersion = DataRowVersion.Original
cmd.Parameters.Add(param)

param = New OleDbParameter("@p4", OleDbType.Integer, 0)
param.SourceVersion = DataRowVersion.Current
param.Value = 1
cmd.Parameters.Add(param)

param = New OleDbParameter("@p5", OleDbType.VarWChar, 0,
"Occupant")
param.SourceVersion = DataRowVersion.Original
cmd.Parameters.Add(param)

param = New OleDbParameter("@p6", OleDbType.Integer, 0)
param.SourceVersion = DataRowVersion.Current
param.Value = 1
cmd.Parameters.Add(param)

param = New OleDbParameter("@p7", OleDbType.Double, 0, "Tel No")
param.SourceVersion = DataRowVersion.Original
cmd.Parameters.Add(param)

param = New OleDbParameter("@p8", OleDbType.Integer, 0)
param.SourceVersion = DataRowVersion.Current
param.Value = 1
cmd.Parameters.Add(param)

param = New OleDbParameter("@p9", OleDbType.VarWChar, 0, "Rent")
param.SourceVersion = DataRowVersion.Original
cmd.Parameters.Add(param)

param = New OleDbParameter("@p10", OleDbType.Integer, 0)
param.SourceVersion = DataRowVersion.Current
param.Value = 1
cmd.Parameters.Add(param)

param = New OleDbParameter("@p11", OleDbType.Double, 0, "Review
Month")
param.SourceVersion = DataRowVersion.Original
cmd.Parameters.Add(param)

param = New OleDbParameter("@p12", OleDbType.Integer, 0)
param.SourceVersion = DataRowVersion.Current
param.Value = 1
cmd.Parameters.Add(param)

param = New OleDbParameter("@p13", OleDbType.Double, 0, "Maint
Year")
param.SourceVersion = DataRowVersion.Original
cmd.Parameters.Add(param)

dataAdapter.DeleteCommand = cmd

Return dataAdapter

Catch ex As Exception
Throw ex
End Try
End Function

Seems to have something to do with data types.

I created a small test table in the same access db:

Field Name Data Type
Id Number
age Number
height Number

And for the first time ever, when I changed a field value, my da.Update call
worked!

Then when I added in two Text data type columns:

Field Name Data Type
Id Number
sometext Text
age Number
height Number
moretext Text

Now I get the concurrency violation error again.

So it looks like something to do with datatypes. Any ideas?

Sam


"sam" <(E-Mail Removed)> wrote in message
news:eD$(E-Mail Removed)...
> 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, ,
>
>