Update Query Is Not Working In VB.NET With MS Access

Question

Imports System.Data.OleDb

Public Class Form1

THIS IS MY CONNECTION STRING

Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|datadirectory|STORE.accdb")

Private Sub PictureBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PictureBox1.Click
    con.Open()

    Dim cmd As New OleDbCommand("SELECT        [QTY OTHER], [HQTY PL3], [HQTY PL5], [TOTAL QTY] FROM(MATERIAL)WHERE        ([MAT CODE] = @a)", con)


    cmd.Parameters.AddWithValue("@a", TextBox1.Text)
    Dim dr As OleDbDataReader = cmd.ExecuteReader
    Dim qtyotherm, hqtypl3m, hqtypl5m, totalqtym As New Double
    Dim qtyotherr, rqtypl3r, rqtypl5r, qtyr As New Double
    Dim totalqtyfinal As New Double

    Dim cmdins As New OleDbCommand("INSERT INTO RECEPT ([MAT CODE], QTY, RATE, TAXES, [RQTY PL3], [RQTY PL5], [RQTY OTHER]) VALUES        (@b, @c, @d, @e, @f, @g, @h)", con)


    cmdins.Parameters.AddWithValue("@b", TextBox1.Text)
    cmdins.Parameters.AddWithValue("@c", TextBox2.Text)
    cmdins.Parameters.AddWithValue("@d", TextBox10.Text)
    cmdins.Parameters.AddWithValue("@e", TextBox9.Text)
    cmdins.Parameters.AddWithValue("@f", TextBox3.Text)
    cmdins.Parameters.AddWithValue("@g", TextBox4.Text)
    cmdins.Parameters.AddWithValue("@h", TextBox5.Text)
    Dim checkinsert As New Integer
    checkinsert = cmdins.ExecuteNonQuery

THIS EXECUTE NON QUERY IS WORKING PROPERLY

    If checkinsert > 0 Then




        If dr.HasRows Then


            While dr.Read
                qtyotherm = dr(0)
                hqtypl3m = dr(1)
                hqtypl5m = dr(2)
                totalqtym = dr(3)
            End While

THE TEXT BOX IS SHOWING THE ADDITION VALE

            totalqtyfinal = totalqtym + TextBox2.Text
            TextBox15.Text = totalqtyfinal



            MessageBox.Show("chk text box value")

PROBLEM STARTS FROM HERE

            Dim cmdupdate As New OleDbCommand("UPDATE MATERIAL SET [TOTAL QTY] = @j  WHERE ([MAT CODE] = @i)", con)


            cmdupdate.Parameters.AddWithValue("@i", TextBox1.Text)
            cmdupdate.Parameters.AddWithValue("@j", TextBox15.Text)
            Dim chkupdate As New Integer
            chkupdate = cmdupdate.ExecuteNonQuery


            If chkupdate > 0 Then
                MessageBox.Show("update done   :)  ")

ALWAYS THIS ELSE STATEMENT MESSAGE IS SHOWN

            Else
                MessageBox.Show("fail to update  :(")
            End If




            MessageBox.Show("quer 1 and 2 is successfully executed")

        Else
            MessageBox.Show("dr not working.")
        End If
    Else
        MessageBox.Show("insert query fail")
    End If
    con.Close()

End Sub

End Class

score 3 · Answer 1 · edited May 23 '17 at 12:21

See this answer. OleDbCommand does not support named parameters. You need to add the parameters in the same order that you have them in within your SQL statement.

In your update statement, you have @j followed by @i, and you're adding @i followed by @j. OleDbCommand effectively sees this as ? followed by ? in the SQL statement, and when you add the parameters, it simply adds parameter 1 followed by parameter 2.

Update Query Is Not Working In VB.NET With MS Access

1 Answers1