Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP .Net (http://www.velocityreviews.com/forums/f29-asp-net.html)
-   -   Excel UPDATE statement doesn't write to the Excel File (http://www.velocityreviews.com/forums/t649695-excel-update-statement-doesnt-write-to-the-excel-file.html)

RnkG 12-22-2008 05:37 PM

Excel UPDATE statement doesn't write to the Excel File
 
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


All times are GMT. The time now is 11:09 PM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.