none
Excel spreadsheet wrong cell update from VB.net OleDB

    Question

  • Hi I'm new here

    I'm having problems in updating records from excel using VB 2010

    where its Header is at Row 5 in Excel.

    The Update part is fine what I'm having trouble is where it updates.

    Scenario: I want to update a record whose IDVal is at Row 5 in Datagridview but instead

    the value I put in row 5 was placed in Row 1 in Datagridview.

    Here's my code:

    'Select Query
    Dim MyConnection As New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & path & "'; Extended Properties=Excel 12.0;")
    Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$A5:R1048576] where [ITEM CODE] is not null", MyConnection)
    MyConnection.Open()
    adpt = New OleDbDataAdapter(cmd)
    Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adpt)
    DtSet = New DataSet()
    adpt.Fill(DtSet)
    DataGridView1.DataSource = DtSet.Tables(0).DefaultView
    MyConnection.Close()
    
    
    'Update Query
    Dim updt As OleDbCommand = New OleDbCommand("Update [Sheet1$A5:R1048576] Set [Inventory on Hand] = '" & Label4.Text & "' where [ITEM CODE] = '" & Label5.Text & "'", MyConnection)
    MyConnection.Open()
    updt.ExecuteNonQuery()
    MyConnection.Close()

    Thanks in advance


    • Edited by Zeronov Monday, December 09, 2013 8:21 AM
    Monday, December 09, 2013 8:12 AM