none
ACE.oledb update does not update correct cell if the column does not start on row 1 RRS feed

  • Question

  • I'm trying to update a named range in Excel using ACE.oledb.  It works fine if my range starts in row 1.  if i have a title or the range starts a row down, it does not put the value in the correct cell.  It will grab the correct cell and perform the update, but the value is inserted in the wrong cell.  If my range IDRES is A1:B32, it works fine.  If I change it to A3:B34, it will not work.  For this sample, I used an id column with values numbered 1 through 31, and a value column with values also number 1 through 31. 

    With my range set to A3:B34, the update correctly updated the value to 6, but it updated cell B6, which corresponds to id = 3.  Should have updated cell B8.

    Dim

    m_sConn2 As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

                               "Data Source=" & Trim(strFileName) & ";Mode=ReadWrite;" & _

                               "Extended Properties=""Excel 12.0 xml;HDR=YES;"""

           

    Dim

    conn2 As New Data.OleDb.OleDbConnection(m_sConn2)

    Dim retval As Integer

                conn2.Open()

                cmd.Connection = conn2

                cmd.CommandText = "UPDATE [IDRES] set res = res + 1 where id = 5"

                retval = cmd.ExecuteNonQuery()

               

    If retval >= 0 ThenEnd If

             MsgBox(retval.ToString & " record(s) affected.")

              conn2.Close()


           


       

        

    Dim cmd As New System.Data.OleDb.OleDbCommand
    Thursday, August 30, 2012 3:44 PM

Answers

  • In the mean time I did a short version of your sheet with the exact results I expected, update column B where column A value is 3. So row 6 has column A as 3 and column B as 10 before the update then 100.5 for 6B after the update. I had this in real time i.e. Excel open in one monitor and VS open in the second monitor.

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Builder As New OleDbConnectionStringBuilder With
            {
                .DataSource = "Excel1.xlsx",
                .Provider = "Microsoft.ACE.OLEDB.12.0"
            }
        Builder.Add("Mode", "ReadWrite")
        Builder.Add("Extended Properties", "Excel 12.0; HDR=No")
        Dim SearchID As Integer = 3
        Dim NewValue As Decimal = 100.5D
        Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
            Using cmd As New OleDbCommand With {.Connection = cn}
                cn.Open()
                cmd.CommandText = <SQL>UPDATE [Sheet1$A3:B9] SET F2=@F2value WHERE F1=@F1value</SQL>.Value
                cmd.Parameters.AddWithValue("@F2value", NewValue)
                cmd.Parameters.AddWithValue("@F1value", SearchID)
                Dim Affected = cmd.ExecuteNonQuery
                Console.WriteLine("Affected = {0}", Affected)
                cmd.CommandText = <SQL>SELECT F1 As Identifier, F2 As Item FROM [Sheet1$A3:B34]</SQL>.Value
                Dim dt As New DataTable
                dt.Load(cmd.ExecuteReader)
                bsExcel.DataSource = dt
            End Using
        End Using
        DataGridView1.DataSource = bsExcel
    End Sub


    KSG

    • Proposed as answer by Doraemon_3 Wednesday, September 5, 2012 1:59 AM
    • Marked as answer by Allen_MSDNModerator Thursday, September 6, 2012 1:34 AM
    Friday, August 31, 2012 4:05 PM

All replies

  • Hello,

    When reading from a A3:B32 what happens if you set HDR=No as the range does not have column names.


    KSG

    Friday, August 31, 2012 11:40 AM
  • Same thing -- it's just shifted up 1 row.
    Friday, August 31, 2012 11:47 AM
  • Same thing -- it's just shifted up 1 row.

    I replicated your sheet where column A is numeric and column B string and worked fine as in the correct cell was updated. Note in both examples HDR=No.

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Builder As New OleDbConnectionStringBuilder With
            {
                .DataSource = "Excel1.xlsx",
                .Provider = "Microsoft.ACE.OLEDB.12.0"
            }
        Builder.Add("Mode", "ReadWrite")
        Builder.Add("Extended Properties", "Excel 12.0; HDR=No")
        Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
            Using cmd As New OleDbCommand With {.Connection = cn}
                cn.Open()
                cmd.CommandText = <SQL>UPDATE [Sheet1$A3:B34] SET F2=@F2value WHERE F1=@F1value</SQL>.Value
                cmd.Parameters.AddWithValue("@F2value", "Woohoo")
                cmd.Parameters.AddWithValue("@F1value", 12)
                Dim Affected = cmd.ExecuteNonQuery
                Console.WriteLine("Affected = {0}", Affected)
                cmd.CommandText = <SQL>SELECT F1 As Identifier, F2 As Item FROM [Sheet1$A3:B34]</SQL>.Value
                Dim dt As New DataTable
                dt.Load(cmd.ExecuteReader)
                bsExcel.DataSource = dt
            End Using
        End Using
        DataGridView1.DataSource = bsExcel
    End Sub

    Next I did column A numeric, column B numeric, still worked

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Builder As New OleDbConnectionStringBuilder With
            {
                .DataSource = "Excel1.xlsx",
                .Provider = "Microsoft.ACE.OLEDB.12.0"
            }
        Builder.Add("Mode", "ReadWrite")
        Builder.Add("Extended Properties", "Excel 12.0; HDR=No")
        Dim SearchID As Integer = 12
        Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
            Using cmd As New OleDbCommand With {.Connection = cn}
                cn.Open()
                cmd.CommandText = <SQL>UPDATE [Sheet1$A3:B34] SET F2=@F2value WHERE F1=@F1value</SQL>.Value
                cmd.Parameters.AddWithValue("@F2value", SearchID + 1)
                cmd.Parameters.AddWithValue("@F1value", SearchID)
                Dim Affected = cmd.ExecuteNonQuery
                Console.WriteLine("Affected = {0}", Affected)
                cmd.CommandText = <SQL>SELECT F1 As Identifier, F2 As Item FROM [Sheet1$A3:B34]</SQL>.Value
                Dim dt As New DataTable
                dt.Load(cmd.ExecuteReader)
                bsExcel.DataSource = dt
            End Using
        End Using
        DataGridView1.DataSource = bsExcel
    End Sub


    KSG

    Friday, August 31, 2012 2:08 PM
  • Still not working.  Keeps wanting to go the the excel row, and not the row with the value specified.  F1 value is 5 in the 8th row.  The code updates the 5th row where the F1 value is 2.

    Dim

    cm AsNewOleDb.OleDbCommand


                cm.Connection = conn2

                cm.CommandText =

    <SQL>UPDATE [Sheet1$D4:E34] set F2 = @F2value WHERE F1 = @F1value</SQL>.Value

                cm.Parameters.AddWithValue(

    "@F2value", 10)

                cm.Parameters.AddWithValue(

    "@F1value", 5)

     

               

    Dimaffected = cm.ExecuteNonQuery

                MsgBox(affected &

    " records affected")

    Friday, August 31, 2012 3:26 PM
  • Still not working.  Keeps wanting to go the the excel row, and not the row with the value specified.  F1 value is 5 in the 8th row.  The code updates the 5th row where the F1 value is 2.

    Dim

    cm AsNewOleDb.OleDbCommand


                cm.Connection = conn2

                cm.CommandText =

    <SQL>UPDATE [Sheet1$D4:E34] set F2 = @F2value WHERE F1 = @F1value</SQL>.Value

                cm.Parameters.AddWithValue(

    "@F2value", 10)

                cm.Parameters.AddWithValue(

    "@F1value", 5)

     

               

    Dimaffected = cm.ExecuteNonQuery

                MsgBox(affected &

    " records affected")


    I would recommend uploading your Excel file to SkyDrive but I have never been able to access it so if you want go to my personal site and at the bottom of the page there is a link for my email address. Zip up the file and send it to me, reply back here so I know to check my email and will see what I can do to assist. Also Indicate what version of Visual Studio you are using (I have VS2008 and VS2010).

    KSG

    Friday, August 31, 2012 3:47 PM
  • In the mean time I did a short version of your sheet with the exact results I expected, update column B where column A value is 3. So row 6 has column A as 3 and column B as 10 before the update then 100.5 for 6B after the update. I had this in real time i.e. Excel open in one monitor and VS open in the second monitor.

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Builder As New OleDbConnectionStringBuilder With
            {
                .DataSource = "Excel1.xlsx",
                .Provider = "Microsoft.ACE.OLEDB.12.0"
            }
        Builder.Add("Mode", "ReadWrite")
        Builder.Add("Extended Properties", "Excel 12.0; HDR=No")
        Dim SearchID As Integer = 3
        Dim NewValue As Decimal = 100.5D
        Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
            Using cmd As New OleDbCommand With {.Connection = cn}
                cn.Open()
                cmd.CommandText = <SQL>UPDATE [Sheet1$A3:B9] SET F2=@F2value WHERE F1=@F1value</SQL>.Value
                cmd.Parameters.AddWithValue("@F2value", NewValue)
                cmd.Parameters.AddWithValue("@F1value", SearchID)
                Dim Affected = cmd.ExecuteNonQuery
                Console.WriteLine("Affected = {0}", Affected)
                cmd.CommandText = <SQL>SELECT F1 As Identifier, F2 As Item FROM [Sheet1$A3:B34]</SQL>.Value
                Dim dt As New DataTable
                dt.Load(cmd.ExecuteReader)
                bsExcel.DataSource = dt
            End Using
        End Using
        DataGridView1.DataSource = bsExcel
    End Sub


    KSG

    • Proposed as answer by Doraemon_3 Wednesday, September 5, 2012 1:59 AM
    • Marked as answer by Allen_MSDNModerator Thursday, September 6, 2012 1:34 AM
    Friday, August 31, 2012 4:05 PM