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
    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. Advertising

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. Evan M.
    Replies:
    1
    Views:
    1,238
    Evan M.
    Apr 18, 2007
  2. Peter Otten
    Replies:
    0
    Views:
    390
    Peter Otten
    Mar 3, 2008
  3. David Shorthouse

    Update query doesn't update table

    David Shorthouse, Jun 17, 2005, in forum: ASP General
    Replies:
    6
    Views:
    273
    David Shorthouse
    Jun 17, 2005
  4. fniles
    Replies:
    6
    Views:
    680
    Karl E. Peterson
    Apr 27, 2009
  5. Dave Griffith

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

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

Share This Page