使用 VB.net 更新 Excel 工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20676234/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update an Excel worksheet using VB.net
提问by Jigar patel
I am able to insert and read data of an Excel file but unable to update the data using article_noas trigger,
我能够插入和读取 Excel 文件的数据，但无法使用article_no触发器更新数据，
I have tried this
我试过这个
Dim cn As New OleDbConnection
Dim cm As New OleDbCommand
cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\crys\Desktop\TEST\Book1.xls;Extended Properties=""Excel 12.0 Xml;HDR=YES""")
cn.Open()
With cm
.Connection = cn
.CommandText = "UPDATE [up$] SET name = '" & TextBox2.Text & "', QC_status ='" & ComboBox1.SelectedItem & "', reason='" & TextBox3.Text & "', date='" & DateTimePicker1.Text & "' WHERE article_no = '" & TextBox1.Text & "'"
If (ExecuteQuery(.CommandText) = True) Then
MsgBox("record updated")
End If
End With
cn.Close()
But it is showing me an error invalid object 'up$'.
Please someone help me fix this problem.
但它向我展示了一个错误invalid object 'up$'。请有人帮我解决这个问题。
回答by Jigar patel
finally got my query right for updating excel sheet, code goes like this :
终于让我的查询正确更新 excel 表，代码是这样的：
Dim cn As New OleDbConnection
Dim cm As New OleDbCommand
cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\crysol\Desktop\TEST\Book1.xls;Extended Properties=""Excel 12.0 Xml;HDR=YES""")
cn.Open()
With cm
.Connection = cn
.CommandText = "update [up$] set [name]=?, [QC_status]=?, [reason]=?, [date]=? WHERE [article_no]=?"
cm = New OleDbCommand(.CommandText, cn)
cm.Parameters.AddWithValue("?", TextBox2.Text)
cm.Parameters.AddWithValue("?", ComboBox1.SelectedItem)
cm.Parameters.AddWithValue("?", TextBox3.Text)
cm.Parameters.AddWithValue("?", DateTimePicker1.Text)
cm.Parameters.AddWithValue("?", TextBox1.Text)
cm.ExecuteNonQuery()
MsgBox("UPDATE SUCCESSFUL")
con.Close()
End With
回答by Sai Avinash
Generally , it is how its done. I am not able to find any error in your code:
一般来说，它是如何完成的。我在您的代码中找不到任何错误：
Imports System.Data
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Try
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim myCommand As New System.Data.OleDb.OleDbCommand
Dim sql As String
MyConnection = New System.Data.OleDb.OleDbConnection _
("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _
"'c:\testfile.xls';Extended Properties=Excel 8.0;")
MyConnection.Open()
myCommand.Connection = MyConnection
sql = "Update [Sheet1$] set name = 'New Name' where id=1"
myCommand.CommandText = sql
myCommand.ExecuteNonQuery()
MyConnection.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
MsgBox("Updated ")
End Sub
End Class
Hope this helps..
希望这可以帮助..
