Question help: update data in Excel file using OLEDB and adapter

[giodepa]

hi all,
I would like to understand how to update a cell value of excel file using ace.oledb and adapter.
I wrote following code:

Excelconnection = New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; data source=" + mypath + "; Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'")
Excelconnection.Open()
mySelectQuery = "SELECT * FROM [Sectors$]" + " WHERE " + "[Sector UID]" + " LIKE 'MN064U%';"
Dim MYTABLE As New DataSet
Dim adapter As New OleDbDataAdapter(mySelectQuery, Excelconnection)
adapter.Fill(MYTABLE)
Dim aaa As DataRow = MYTABLE.Tables(0).Rows(0)
aaa.BeginEdit()
aaa.Item(0) = "test"
aaa.EndEdit()
Dim updatecommand = New OleDbCommand("UPDATE [Sectors$] SET" + " [Sector UID]" + " =? WHERE " + "[Sector UID]" + " = ?;", Excelconnection)
updatecommand.Parameters.Add("Sector UID", OleDbType.Variant, 10, "Sector UID")
adapter.UpdateCommand = updatecommand
adapter.Update(MYTABLE)
Excelconnection.Close()
adapter.Dispose()

I get error on:

adapter.Update(MYTABLE)

this is message from VS: "no specified value for some needed parameters"


Legend:
[Sectors$] is sheet of excel file
[Sector UID] is column that contains strings "MN064U1", "MN064U2" and "MN064U3"


I would like to change i.e. string "MN064U1" with "test" and update excel file


I believe that issue is on command:

Dim updatecommand = New OleDbCommand("UPDATE [Sectors$] SET" + " [Sector UID]" + " =? WHERE " + "[Sector UID]" + " = ?;", Excelconnection)

but I cannot understand how to set it.


thanks in advanced
giodepa

 

[giodepa]

Hi all,
I finally found solution!!!!
I post it:

Excelconnection = New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; data source=" + mypath + "; Extended Properties='Excel 12.0;HDR=Yes;'")
Excelconnection.Open()
mySelectQuery = "SELECT [Sector UID] FROM [Sectors$] WHERE " + "[Sector UID]" + " = 'MN064U1' ;"
Dim MYTABLE As DataSet = New DataSet
Dim adapter = New OleDbDataAdapter(mySelectQuery, Excelconnection)
adapter.Fill(MYTABLE)
adapter.UpdateCommand = New OleDbCommand("UPDATE [Sectors$] SET" + " [Sector UID]" + " = ? where [Sector UID] = 'MN064U1';", Excelconnection)
adapter.UpdateCommand.Parameters.Add("Sector UID", OleDbType.Char, 255, "Sector UID")
MYTABLE.Tables(0).Rows(0).Item(0) = "test"
adapter.Update(MYTABLE)
 Excelconnection.Close()
adapter.Dispose()

Now I would like to improve my code......
In excel file, that contains more than 29000 rows, I have 3 rows as following:
MN064U1....
MN064U2
MN064U3


I would like to change MN064U1 with string test1, MN064U2 with string test2 and MN064U3 with string test3.


I used following code:

Excelconnection = New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; data source=" + mypath + "; Extended Properties='Excel 12.0;HDR=Yes;'")
Excelconnection.Open()
mySelectQuery = "SELECT [Sector UID] FROM [Sectors$] WHERE [Sector UID] LIKE 'MN064U%' ;"
Dim MYTABLE As DataSet = New DataSet
Dim adapter = New OleDbDataAdapter(mySelectQuery, Excelconnection)
adapter.Fill(MYTABLE)
adapter.UpdateCommand = New OleDbCommand("UPDATE [Sectors$] SET" + " [Sector UID]" + " = ? where [Sector UID] LIKE 'MN064U%';", Excelconnection)
adapter.UpdateCommand.Parameters.Add("Sector UID", OleDbType.Char, 255, "Sector UID")
For i = 0 To MYTABLE.Tables(0).Rows.Count - 1
         MYTABLE.Tables(0).Rows(i).Item(0) = "test" + i.ToString
Next
adapter.Update(MYTABLE)
Excelconnection.Close()
adapter.Dispose()

but I get error :"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records" on line

adapter.Update(MYTABLE)

I read several posts here and I understood that modified DataRows have both old and new values, so if old values compared with the values in the DB
are not the same this normally means someone else has modified the values in the DB between the last Fill and the current Update and therefore it throws a Concurrency Violation.


Can someone help me please?


thanks in advanced
gio

 

[giodepa]

Hi all,
I found solution to my issue.
I improved my code and I had to change update string:

adapter.UpdateCommand = New OleDbCommand(" UPDATE [Sectors$] SET" + " [Site ID]=?, [Sector ID]= ?, [Sector UID] =? where ([Site ID]=? and [Sector ID]= ?) ;", Excelconnection)
dapter.UpdateCommand.Parameters.Add("@Site ID", OleDbType.Char, 255, "Site ID")
adapter.UpdateCommand.Parameters.Add("@Sector ID", OleDbType.Char, 255, "Sector ID")
adapter.UpdateCommand.Parameters.Add("@Sector UID", OleDbType.Char, 255, "Sector UID")
adapter.UpdateCommand.Parameters.Add("@oldSite ID", OleDbType.Char, 255, "Site ID").SourceVersion = DataRowVersion.Original
adapter.UpdateCommand.Parameters.Add("@oldSector ID", OleDbType.Char, 255, "Sector ID").SourceVersion = DataRowVersion.Original
adapter.UpdateCommand.Parameters.Add("@oldSector UID", OleDbType.Char, 255, "Sector UID").SourceVersion = DataRowVersion.Original




        For i = 0 To MYTABLE.Tables(0).Rows.Count - 1
          
            site = Microsoft.VisualBasic.Left(MYTABLE.Tables(0).Rows(i).Item(0), 5)
            sector = MYTABLE.Tables(0).Rows(i).Item(1)
            MYTABLE.Tables(0).Rows(i).Item(2) = site + sector


        Next

"Site ID" and "Sector ID" are two columns present in excel file.
but it takes around 8 minutes to update "Sector UID" parameter for 5341 rows.
Is it possible?