CODEWITHSUNDEEP
vb.netvisual-studio-2010excel
Jigar patel
Jigar patel

Reputation: 215

Update an Excel worksheet using VB.net

I am able to insert and read data of an Excel file but unable to update the data using article_no as trigger,

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.

Upvotes: 0

Views: 7826

Answers (2)

Jigar patel
Jigar patel

Reputation: 215

finally got my query right for updating excel sheet, code goes like 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\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

Upvotes: 1

Sai Avinash
Sai Avinash

Reputation: 4753

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

Upvotes: 0

Related Questions