none
Using VB.NET Windows Form to update MS Access database RRS feed

  • Question

  • I have a simple Access database and want to use VB.NET windows form to update tbl data. I keep getting an error that cmd.ExecuteNonQuery() can not be executed.

    Here is the code. Any ideas?

     Protected Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

            Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={My Database}.mdb;Persist Security Info=False"
            Dim cnnOLEDB As New OleDbConnection(strConnectionString)
            Dim cmdOLEDB As New OleDbCommand
            Dim cmdInsert As New OleDbCommand
            Dim cmdUpdate As New OleDbCommand
            Dim cmdDelete As New OleDbCommand
            Dim InsertQuery As String

            cnnOLEDB.Open()
           
            InsertQuery = "INSERT tblItem SET (Indate, StkLoc ,UPC,Desc, SN, Cond, RetPrice, Cost) VALUES (@Indate, @StkLoc , @UPC, @Desc, @SN, @Cond, @RetPrice, @Cost)WHERE Item=@TextBox2.Text)"
            Dim cmd As OleDbCommand = New OleDbCommand(InsertQuery, cnnOLEDB)

            cmd.Parameters.AddWithValue("@InDate", TextBox1.Text)
            cmd.Parameters.AddWithValue("@Item", TextBox2.Text)
            cmd.Parameters.AddWithValue("@StkLoc", TextBox3.Text)
            cmd.Parameters.AddWithValue("@UPC", TextBox9.Text)
            cmd.Parameters.AddWithValue("@Desc", TextBox4.Text)
            cmd.Parameters.AddWithValue("@SN", TextBox5.Text)
            cmd.Parameters.AddWithValue("@Cond", TextBox6.Text)
            cmd.Parameters.AddWithValue("@RetPrice", TextBox7.Text)
            cmd.Parameters.AddWithValue("@Cost", TextBox8.Text)

            cmd.ExecuteNonQuery()

            cnnOLEDB.Close()

            MessageBox.Show("Item Update Complete.")

            Me.Close()

    Thursday, April 12, 2018 8:23 PM

All replies

  • You need INSERT INTO rather than simply INSERT in the CommandText.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Thursday, April 12, 2018 8:31 PM
    Moderator
  • Thank you. Now a different error:

    Syntax error in INSERT INTO statement.

    InsertQuery = "INSERT  INTO tblItem SET(Indate, StkLoc ,UPC,Desc, SN, Cond, RetPrice, Cost) VALUES (@Indate, @StkLoc , @UPC, @Desc, @SN, @Cond, @RetPrice, @Cost)WHERE Item=@TextBox2.Text)"   

    Dim cmd As OleDbCommand = New OleDbCommand(InsertQuery, cnnOLEDB)



    • Edited by gs1344 Thursday, April 12, 2018 8:49 PM
    Thursday, April 12, 2018 8:47 PM
  • Yep that is indeed an issue, you have @TextBox2.Text inside a string, that is sent exactly "as is" rather than the content of TextBox2. So you need a parameter for WHERE Item = e.g.

    WHERE Item = @Item

    Where @Item is done the same way as the other parameters you did.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, April 12, 2018 9:17 PM
    Moderator
  • I had a minutes to provide what your code should look like and note the connection string you have seems a bit odd.

    Imports System.Data.OleDb
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim identfier as Integer = 0
            '
            ' The connection string part Data Source={My Database} seems a bit odd in that
            ' there are {} brackets being used so if the connection is not working them feel
            ' free to ask me about that
            '
            Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & 
                                      "Data Source={My Database}.mdb;Persist Security Info=False"
    
            Dim InsertQuery = "INSERT INTO tblItem " & 
                              "SET (Indate, StkLoc ,UPC,Desc, SN, Cond, RetPrice, Cost) " & 
                              "VALUES (@Indate, @StkLoc , @UPC, @Desc, @SN, @Cond, @RetPrice, @Cost) " & 
                              "WHERE Item=@Item)"
    
            Using cnnOLEDB As new OleDbConnection With{.ConnectionString = strConnectionString}
                Using cmd As new OleDbCommand With{.Connection = cnnOLEDB, .CommandText = insertQuery}
                    cmd.Parameters.AddWithValue("@InDate", TextBox1.Text)
                    cmd.Parameters.AddWithValue("@StkLoc", TextBox3.Text)
                    cmd.Parameters.AddWithValue("@UPC", TextBox9.Text)
                    cmd.Parameters.AddWithValue("@Desc", TextBox4.Text)
                    cmd.Parameters.AddWithValue("@SN", TextBox5.Text)
                    cmd.Parameters.AddWithValue("@Cond", TextBox6.Text)
                    cmd.Parameters.AddWithValue("@RetPrice", TextBox7.Text)
                    cmd.Parameters.AddWithValue("@Cost", TextBox8.Text)                
                    cmd.Parameters.AddWithValue("@Item", TextBox2.Text)
    
                    cnnOLEDB.Open()
    
                    Dim affected As Integer = cmd.ExecuteNonQuery()
                    If affected = 1 Then
                        ' get new primary key
                        cmd.CommandText = "Select @@Identity"
                        identfier = CInt(cmd.ExecuteScalar)
                    End If
    
                End Using
            End Using
        End Sub
    End Class
    

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Thursday, April 12, 2018 10:30 PM
    Moderator

  •        
            InsertQuery = "INSERT tblItem SET (Indate, StkLoc ,UPC,Desc, SN, Cond, RetPrice, Cost) VALUES (@Indate, @StkLoc , @UPC, @Desc, @SN, @Cond, @RetPrice, @Cost)WHERE Item=@TextBox2.Text)"

            cmd.Parameters.AddWithValue("@InDate", TextBox1.Text)
            cmd.Parameters.AddWithValue("@Item", TextBox2.Text)
            cmd.Parameters.AddWithValue("@StkLoc", TextBox3.Text)
            cmd.Parameters.AddWithValue("@UPC", TextBox9.Text)
            cmd.Parameters.AddWithValue("@Desc", TextBox4.Text)
            cmd.Parameters.AddWithValue("@SN", TextBox5.Text)
            cmd.Parameters.AddWithValue("@Cond", TextBox6.Text)
            cmd.Parameters.AddWithValue("@RetPrice", TextBox7.Text)
            cmd.Parameters.AddWithValue("@Cost", TextBox8.Text)

    Hi,

    You Have 8 Columns in insert query and you want To Insert 9 values you have to delete this line:

        cmd.Parameters.AddWithValue("@Item", TextBox2.Text)


    I Hope this .

    Bajtitou

    • Edited by Bajtitou Thursday, April 12, 2018 11:10 PM
    Thursday, April 12, 2018 11:06 PM

  •        
            InsertQuery = "INSERT tblItem SET (Indate, StkLoc ,UPC,Desc, SN, Cond, RetPrice, Cost) VALUES (@Indate, @StkLoc , @UPC, @Desc, @SN, @Cond, @RetPrice, @Cost)WHERE Item=@TextBox2.Text)"

            cmd.Parameters.AddWithValue("@InDate", TextBox1.Text)
            cmd.Parameters.AddWithValue("@Item", TextBox2.Text)
            cmd.Parameters.AddWithValue("@StkLoc", TextBox3.Text)
            cmd.Parameters.AddWithValue("@UPC", TextBox9.Text)
            cmd.Parameters.AddWithValue("@Desc", TextBox4.Text)
            cmd.Parameters.AddWithValue("@SN", TextBox5.Text)
            cmd.Parameters.AddWithValue("@Cond", TextBox6.Text)
            cmd.Parameters.AddWithValue("@RetPrice", TextBox7.Text)
            cmd.Parameters.AddWithValue("@Cost", TextBox8.Text)

    Hi,

    You Have 8 Columns in insert query and you want To Insert 9 values you have to delete this line:

        cmd.Parameters.AddWithValue("@Item", TextBox2.Text)


    I Hope this .

    Bajtitou

    Not true, there are 8 columns and one parameter for the where condition (which they did wrong and I showed how to do it right which is the ninth parameter)

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, April 12, 2018 11:24 PM
    Moderator

  •        
            InsertQuery = "INSERT tblItem SET (Indate, StkLoc ,UPC,Desc, SN, Cond, RetPrice, Cost) VALUES (@Indate, @StkLoc , @UPC, @Desc, @SN, @Cond, @RetPrice, @Cost)WHERE Item=@TextBox2.Text)"

            cmd.Parameters.AddWithValue("@InDate", TextBox1.Text)
            cmd.Parameters.AddWithValue("@Item", TextBox2.Text)
            cmd.Parameters.AddWithValue("@StkLoc", TextBox3.Text)
            cmd.Parameters.AddWithValue("@UPC", TextBox9.Text)
            cmd.Parameters.AddWithValue("@Desc", TextBox4.Text)
            cmd.Parameters.AddWithValue("@SN", TextBox5.Text)
            cmd.Parameters.AddWithValue("@Cond", TextBox6.Text)
            cmd.Parameters.AddWithValue("@RetPrice", TextBox7.Text)
            cmd.Parameters.AddWithValue("@Cost", TextBox8.Text)

    Hi,

    You Have 8 Columns in insert query and you want To Insert 9 values you have to delete this line:

        cmd.Parameters.AddWithValue("@Item", TextBox2.Text)


    I Hope this .

    Bajtitou

    Not true, there are 8 columns and one parameter for the where condition (which they did wrong and I showed how to do it right which is the ninth parameter)


    Hi ,

    When I Posted your answer wasn 't here or it wasn't claire.

    Sory.

    Best Regards, Bajtitou.

    Thursday, April 12, 2018 11:37 PM
  • Hi gs1344,

    You said that you want to update tb1 data in Access database, why do you don't use Update in statement? like this:

     Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={My Database}.mdb;Persist Security Info=False"
            Dim cnnOLEDB As New OleDbConnection(strConnectionString)
            Dim InsertQuery As String
    
            cnnOLEDB.Open()
    
            InsertQuery = "update tblItem SET Indate=@Indate, StkLoc=@StkLoc, UPC=@UPC, Desc=@Desc, SN=@SN, Cond=@Cond, RetPrice=@RetPrice, Cost=@Cost WHERE Item=@Item)"
            Dim cmd As OleDbCommand = New OleDbCommand(InsertQuery, cnnOLEDB)
    
            cmd.Parameters.AddWithValue("@InDate", TextBox1.Text)
            cmd.Parameters.AddWithValue("@Item", TextBox2.Text)
            cmd.Parameters.AddWithValue("@StkLoc", TextBox3.Text)
            cmd.Parameters.AddWithValue("@UPC", TextBox9.Text)
            cmd.Parameters.AddWithValue("@Desc", TextBox4.Text)
            cmd.Parameters.AddWithValue("@SN", TextBox5.Text)
            cmd.Parameters.AddWithValue("@Cond", TextBox6.Text)
            cmd.Parameters.AddWithValue("@RetPrice", TextBox7.Text)
            cmd.Parameters.AddWithValue("@Cost", TextBox8.Text)
            cmd.ExecuteNonQuery()
            cnnOLEDB.Close()
            MessageBox.Show("Item Update Complete.")
    
            Me.Close()

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 13, 2018 3:03 AM
    Moderator
  • Hi Thank you for taking the time to help!

    I am still getting the same error using your code.

    Syntax error in UPDATE statement.
    cmd.ExecuteNonQuery()

    The connection string is:

    Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Sales-VB\Sales-Vb.mdb;Persist Security Info=False"

    Friday, April 13, 2018 11:42 AM
  • Hello,

    I hope you take your time reading the following to better educate yourself as any ongoing replies are not going to be helpful. If you follow what I have written below you can solve the error.

    Your connection and connection string would only be an issue if the connection failed to open while your issue is a syntax error with the SQL of the Command object.

    Let's try and do some education. With MS-Access parameters are ordinal based and not named based.

    Example 1 the parameters are named yet are also in ordinal position and this INSERT statement works, completely ignore the connection.

    Public Function AddNewRowVersion1(
        pName As String, 
        pContact As String, 
        ByRef pIdentfier As Integer) As Boolean
    
        Dim success As Boolean = True
        Dim affected As Integer = 0
    
        Try
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName) " & 
                                        "Values(@CompanyName,@ContactName)"
    
                    cmd.Parameters.AddWithValue("@CompanyName", pName)
                    cmd.Parameters.AddWithValue("@ContactName", pContact)
    
                    cn.Open()
    
                    affected = cmd.ExecuteNonQuery()
                    If affected = 1 Then
                        cmd.CommandText = "Select @@Identity"
                        pIdentfier = CInt(cmd.ExecuteScalar)
                    End If
                End Using
            End Using
        Catch ex As Exception
            success = False
        End Try
    
        Return success
    
    End Function

    Now here is the positioning

    Here the values are out of order, in this case there is no syntax issue per-say as both columns are strings yet if they were not there would be a syntax error

    So you would need to see the following which means that the parameter names are meaningless for MS-Access.

    cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName) " & 
                        "Values(@CompanyName,@ContactName)"
    
    
    cmd.Parameters.AddWithValue("?", pName)
    cmd.Parameters.AddWithValue("?", pContact)

    If we were dealing with the odd-ball Oracle you would use named parameters, place them out of order and we have the same issue as MS-Access unless a property was set then they become named parameters.

    Next up: Let's say TextBox1.Text is not a valid date or is a valid date not formatted properly, this could cause an issue which is why in the case of InDate you need to validate that TextBox1.Text is a valid date or use a DateTimePicker. Same goes for each parameter value needs to be valid for the data type in the database table e.g. if someone passed in "A1" for a integer value there would be a syntax error while you the developer needs to validate the value is a integer.

    A start to fixing things, create a function which you pass in values, if the values are incorrect then you will not be able to compile which now you need to contend with compile issues which makes for less errors as in the following example it's not possible to pass a wrong data type for the parameter pBirthday. Note the parameters are strictly ordinal based too.

    Public Function AddNewRowVersion2(
        pName As String, 
        pContact As String,
        pBirthDay As Date, 
        ByRef pIdentfier As Integer) As Boolean
    
        Dim success As Boolean = True
        Dim affected As Integer = 0
    
    
        Try
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
    
                    cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName,BirthDay) " & 
                                        "Values(?,?,?)"
    
    
                    cmd.Parameters.AddWithValue("?", pName)
                    cmd.Parameters.AddWithValue("?", pContact)
                    cmd.Parameters.AddWithValue("?", pBirthDay)
    
                    cn.Open()
    
                    affected = cmd.ExecuteNonQuery()
                    If affected = 1 Then
                        cmd.CommandText = "Select @@Identity"
                        pIdentfier = CInt(cmd.ExecuteScalar)
                    End If
                End Using
            End Using
        Catch ex As Exception
            success = False
        End Try
    
        Return success
    
    End Function


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, April 13, 2018 12:55 PM
    Moderator
  • Thank you so much for your help. Going to review this and work through a solution. It is so much easier coding to an SQL database than Access. Will respond after I have time to work on this. Thank you again! 
    Friday, April 13, 2018 1:54 PM
  •  "It is so much easier coding to an SQL database than Access."  Amen!

    Mary

    Saturday, April 14, 2018 9:12 PM
  • Hi gs1344,

    If you have solved your issue now, please remember to close your thread by marking the helpful post as answer, it is beneficial to other community members who face the same issue.

    Thanks for your understanding.

    best regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 19, 2018 9:13 AM
    Moderator