Ace.Oledb 12.0 when updating a range is putting the data on the wrong line

J Torres 1 Reputation point

All, I'm using Ace.Oledb 12.0 in a program to update a range on an Excel xlsm document we are using as a template but it's putting it on next line instead of the actual range line. For example if the range is E3:F3, it is putting the data on E4:F4. Searching it says add HDR=No and I did but that doesn't help. Help is appreciated as my code is below:

Connection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;data source=" & Source & ";" & "Extended Properties=""Excel 12.0;HDR=NO;""")

'get range info for center on info tab
DataAdpter = New System.Data.OleDb.OleDbDataAdapter("Select * from Center", Connection)
DataAdpter.Fill(DS, "Center")

If DS.Tables.Count > 0 Then
For j As Integer = 0 To DS.Tables("Center").Columns.Count - 1
strColAccumNames = strColAccumNames & DS.Tables("Center").Columns(j).ColumnName.ToString & " ,"
strParameters = strParameters & "?,"
End If

strColAccumNames = strColAccumNames.Substring(0, strColAccumNames.Length - 2)
strParameters = strParameters.Substring(0, strParameters.Length - 1)

DataAdpter.InsertCommand = New System.Data.OleDb.OleDbCommand("INSERT INTO Center ( " & strColAccumNames & ") VALUES ( " & strParameters & " )", Connection)

For cols As Integer = 0 To DS.Tables("Center").Columns.Count - 1 'each column is a parameter for an insert DataAdpter.InsertCommand.Parameters.Add(DS.Tables("Center").Columns(cols).ColumnName, System.Data.OleDb.OleDbType.VarChar, 5000, DS.Tables("Center").Columns.Item(cols).ColumnName)

''absolute address is used for quick development purpose
For i As Integer = 0 To dsRowsToInsert.Tables(0).Rows.Count - 1 DS.Tables("Center").Rows.Add(dsRowsToInsert.Tables(0).Rows(i).ItemArray)

DataAdpter.Update(DS, "Center")

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
2,428 questions
0 comments No comments
{count} votes