Excel UPDATE statement doesn't write to the Excel File

Discussion in '.NET' started by RnkG, Dec 22, 2008.

  1. RnkG

    RnkG

    Joined:
    Dec 22, 2008
    Messages:
    1
    Likes Received:
    0
    Private strUpdate As String = "UPDATE [Sheet1$] SET
    ItemDescription='@Item', Source='@Source', Customer='@Cust',
    Address='@Address', City='@City', State='@State', Zip='@Zip', Processed='@Processed', Used='@Used', Store='@Store' WHERE TagNumber='@TagNum'"

    Function updateExcel() As Boolean
    oleCmdWrite = New OleDbCommand
    oleCmdWrite.CommandText = strUpdate
    oleCmdWrite.Connection = oleConn
    olePram = oleCmdWrite.Parameters.Add("@TagNum", OleDbType.VarChar)
    olePram.SourceColumn = "TagNumber"
    olePram = oleCmdWrite.Parameters.Add("@Item", OleDbType.VarChar)
    olePram.SourceColumn = "ItemDescription"
    olePram = oleCmdWrite.Parameters.Add("@Source", OleDbType.VarChar)
    olePram.SourceColumn = "Source"
    olePram = oleCmdWrite.Parameters.Add("@Cust", OleDbType.VarChar)
    olePram.SourceColumn = "Customer"
    olePram = oleCmdWrite.Parameters.Add("@Address", OleDbType.VarChar)
    olePram.SourceColumn = "Address"
    olePram = oleCmdWrite.Parameters.Add("@City", OleDbType.VarChar)
    olePram.SourceColumn = "City"
    olePram = oleCmdWrite.Parameters.Add("@State", OleDbType.VarChar)
    olePram.SourceColumn = "State"
    olePram = oleCmdWrite.Parameters.Add("@Zip", OleDbType.VarChar)
    olePram.SourceColumn = "Zip"
    olePram = oleCmdWrite.Parameters.Add("@Processed", OleDbType.VarChar)
    olePram.SourceColumn = "Processed"
    olePram = oleCmdWrite.Parameters.Add("@Used", OleDbType.VarChar)
    olePram.SourceColumn = "Used"
    olePram = oleCmdWrite.Parameters.Add("@Store", OleDbType.VarChar)
    olePram.SourceColumn = "Store"

    If IsNothing(ds) = False Then
    dr = ds.Tables(0).NewRow
    dr("TagNumber") = dgvTags.CurrentRow.Cells(0).Value
    dr("ItemDescription") = tboxItemDescription.Text
    dr("Source") = getSourceInfo()
    dr("Customer") = tboxCustName.Text
    dr("Address") = tboxAddress.Text
    dr("City") = tboxCity.Text
    dr("State") = tboxState.Text
    dr("Zip") = tboxZip.Text
    dr("Processed") = isProcessed()
    dr("Used") = isNew()
    dr("Store") = getStore()
    oleAdpt = New OleDbDataAdapter
    oleAdpt.UpdateCommand = oleCmdWrite
    Dim str As String() = {"", "", "", "", "", "", "", "", "", "", ""}
    For x As Integer = x To ds.Tables(0).Columns.Count - 1
    str(x) = dr.Item(x)
    Next x
    Dim y As Integer = dgvTags.CurrentCell.RowIndex

    ds.Tables(0).Rows.RemoveAt(y)
    ds.Tables(0).Rows.InsertAt(dr, y)
    dgvTags.CurrentRow.SetValues(str)
    'oleConn.Open()
    'oleCmdWrite.ExecuteNonQuery()
    'oleConn.Close()
    oleAdpt.InsertCommand = oleCmdWrite
    Dim i As Integer = oleAdpt.Update(ds, "Sheet1")
    MessageBox.Show(i & " Row(s) Affected")
    disableFields()
    End If



    No errors are thrown when this block is ran, the message displays 0 rows affected, my data grid view is updated but no changes are actually writen to my excel file (the most important part).

    Any and all help is much appreciated!

    Thanks,

    R

    P.s. I have an INSERT statement that's somewhat similar to this that works perfectly, and I just don't have the expirence with writing to an Excel sheet to see where I errored. Thanks
     
    Last edited: Dec 24, 2008
    RnkG, Dec 22, 2008
    #1
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Luis Esteban Valencia
    Replies:
    1
    Views:
    1,603
    Carl Prothman [MVP]
    Jan 12, 2005
  2. kp2900@gmail.com
    Replies:
    1
    Views:
    1,980
    extentechjohn
    Nov 21, 2006
  3. Matthew Mueller

    file.encoding doesn't apply to file.write?

    Matthew Mueller, Jun 7, 2004, in forum: Python
    Replies:
    2
    Views:
    581
    Matthew Mueller
    Jun 7, 2004
  4. Evan M.
    Replies:
    1
    Views:
    1,418
    Evan M.
    Apr 18, 2007
  5. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    2,006
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
  6. David Shorthouse

    Update query doesn't update table

    David Shorthouse, Jun 17, 2005, in forum: ASP General
    Replies:
    6
    Views:
    472
    David Shorthouse
    Jun 17, 2005
  7. fniles
    Replies:
    6
    Views:
    889
    Karl E. Peterson
    Apr 27, 2009
  8. Dave Griffith

    Image::Magick->Write() doesn't want to write

    Dave Griffith, Nov 16, 2009, in forum: Perl Misc
    Replies:
    2
    Views:
    388
    Dave Griffith
    Nov 16, 2009
Loading...