Results 1 to 4 of 4

Thread: [RESOLVED] Excel UPDATE error

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jan 2009
    Posts
    232

    Resolved [RESOLVED] Excel UPDATE error

    I'm OK using INSERT to add a row such as this one:

    HTML Code:
    sql = "INSERT INTO [Sheet1$] (Surname, ForeName, IDcode, Title, Member, Fee) VALUES ('" & Me.TextBox1.Text & "', '" & Me.TextBox2.Text & "', '" & Me.TextBox4.Text & "', '" & Me.TextBox5.Text & "', '" & Me.TextBox3.Text & "', '" & Me.TextBox24.Text & "')"
    But I'm unsure of how to UPDATE a row using (WHERE IDcode =) as a condition for finding the correct row. I have values in text boxes. See the end of this UPDATE code. The plan is to identify a row through a column IDcode value.

    This fails. Do I use VALUES as in the INSERT INTO method?
    HTML Code:
    sql = "UPDATE [Sheet1$] SET (Surname, Forename, IDcode, Title, Member, Fee) VALUES ('" & Me.TextBox1.Text & "', '" & Me.TextBox2.Text & "', '" & Me.TextBox4.Text & "', '" & Me.TextBox3.Text & "', "',  '" & Me.TextBox24.Text & "') WHERE IDcode =( '" & Me.TextBox26.Text & "')"
    Help appreciated.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,500

    Re: Excel UPDATE error

    For an update the syntax is:
    Code:
    UPDATE table
    
    SET field1 = value1
      , field2 = value2
    
    WHERE condition
    You can find more information and examples via our Database Development FAQs/Tutorials (at the top of the Database Development forum)

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Jan 2009
    Posts
    232

    Smile Re: Excel UPDATE error

    Using parameters, the following code works for an Excel Sheet. Most searches on this forum don't quite do the Excel syntax. Here is the working example.

    HTML Code:
    Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                   "Data Source=" & myFolderName & "MyFilenamet.xlsx" & ";" &
                                   "Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
    
            con.ConnectionString = connstring
    
            Try
                'UPDATE a  Row via TextBoxes
                Me.BackColor = Color.Wheat
                Dim myCommand As New System.Data.OleDb.OleDbCommand
                Dim sql As String
                Dim SurVal As String = Me.TextBox1.Text
                Dim ForVal As String = Me.TextBox2.Text
                Dim IDVal As String = Me.TextBox5.Text
    
                sql = "UPDATE [Sheet1$] SET Surname = ?, Forename = ?, Instrument = ?, Title = ?, Member = ?, Fee = ?, Paid = ?, GiftAid = ?, Address1 = ?, Address2 = ?, Address3 = ?, PostCode = ?, Telephone = ?, Email = ?, NOVEMBER = ?, DECEMBER = ?, XMAS = ?, FEBRUARY = ?, MARCH = ?, JUNE = ?, HalfFULL = ?, Responded = ?, Electronic = ?, PAIDUNPAID = ?, NOTES = ? WHERE ID = ('" & Me.TextBox5.Text & "')"
    
    
                With myCommand.Parameters
                    'These must be in the same order as the UPDATE code in the line above(note ID is the last one)
                    'TIP - make sure that the actual Excel column header is exactly the same as anything you add
                    .AddWithValue(Surname, TextBox1.Text)
                    .AddWithValue(ForeName, TextBox2.Text)
                    .AddWithValue(Instrument, TextBox4.Text)
                    .AddWithValue(Title, TextBox3.Text)
                    .AddWithValue(Member, TextBox15.Text)
                    .AddWithValue(Fee, TextBox8.Text)
                    .AddWithValue(Paid, TextBox9.Text)
                    .AddWithValue(GiftAid, TextBox10.Text)
                    .AddWithValue(Address1, TextBox11.Text)
                    .AddWithValue(Address2, TextBox12.Text)
                    .AddWithValue(Address3, TextBox13.Text)
                    .AddWithValue(PostCode, TextBox14.Text)
                    .AddWithValue(Telephone, TextBox7.Text)
                    .AddWithValue(Email, TextBox6.Text)
                    .AddWithValue(NOVEMBER, TextBox16.Text)
                    .AddWithValue(DECEMBER, TextBox17.Text)
                    .AddWithValue(XMAS, TextBox18.Text)
                    .AddWithValue(FEBRUARY, TextBox19.Text)
                    .AddWithValue(MARCH, TextBox20.Text)
                    .AddWithValue(JUNE, TextBox21.Text)
                    .AddWithValue(HalfFULL, TextBox22.Text)
                    .AddWithValue(Responded, TextBox23.Text)
                    .AddWithValue(Electronic, TextBox24.Text)
                    .AddWithValue(PAIDUNPAID, TextBox25.Text)
                    .AddWithValue(NOTES, TextBox26.Text)
    
                    'ID is not used here
    
                End With
                myCommand.CommandText = sql
                myCommand.Connection = con
                con.Open()
                myCommand.ExecuteNonQuery()
                con.Close()
    .

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Jan 2009
    Posts
    232

    Re: [RESOLVED] Excel UPDATE error

    And for Newbies of course the following (especially Dim con) needs to go at the top of the Form.

    HTML Code:
    Public Class Form2
        Private da As New OleDb.OleDbDataAdapter
        Private dt As New System.Data.DataTable
    
        Dim con As New OleDb.OleDbConnection
    
        Dim OleDbType As Object
    Otherwise no connection string is established.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width