How to Update and Add Item in database from Listview

[JModak]

I use the this code. showing "Update" msg but not update and if i add an item then show "Update" not "save"
Please help me.....

Private Sub Button14_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button14.Click
        Dim sql = "select Count(*) from pur_invdet where purinvid= txtpurinvid.text"
        Dim cmd = New OleDb.OleDbCommand(sql, cn)
        cmd.Parameters.AddWithValue("@purinvid", TextBox1.Text)
        Dim result As Integer = Convert.ToInt32(cmd.ExecuteScalar())
        cmd.Parameters.Clear()
        cmd.Dispose()

        If result > 0 Then

            For Each x As ListViewItem In ListView1.Items
                Dim sln As Integer = x.SubItems(0).Text
                Dim qty As Integer = Convert.ToDecimal(x.SubItems(4).Text)
                Dim itmid As Integer = x.SubItems(12).Text
            Next
            sql = "Update pur_invdet set [slno] = [slno] + sln where purinvid= txtpurinvid.text"
            cmd = New OleDb.OleDbCommand(sql, cn)
            cmd.Parameters.AddWithValue("@slno", Val(txtitemsl.Text))
            cmd.Parameters.AddWithValue("@ItemIDpur", txtitemid.Text)
            cmd.Parameters.AddWithValue("@purinvid", txtpurinvid.Text)
            cmd.ExecuteNonQuery()
            MsgBox("Updated")
        Else

            sql = "Insert into pur_invdet (slno, ItemIDpur,purinvid) values (@slno, @ItemIDpur, @purinvid)"
            cmd = New OleDb.OleDbCommand(sql, cn)
            cmd.Parameters.AddWithValue("@slno", Val(txtitemsl.Text))
            cmd.Parameters.AddWithValue("@ItemIDpur", txtitemid.Text)
            cmd.Parameters.AddWithValue("@purinvid", txtpurinvid.Text)
            cmd.ExecuteNonQuery()
            MsgBox("Save")

        End If

[xrjf]

Change lines #21 and 22 into the following to see how many registers are really updated:

           dim n as int32 = cmd.ExecuteNonQuery() 'n=The number of rows affected'
           MsgBox("Updated "+n.tostring+" rows")

Change lines #30 and 31 into the following and see how many registers are really saved:

           dim n as int32= cmd.ExecuteNonQuery() 'n=The number of rows affected'
           MsgBox("Saved " + n.tostring + " rows")

[xrjf]

You are setting just one parameter in the Update statement while adding later 3 parameters. Can't you include the execute cmd.executeNonQuerys inside a try - catch to see if there is any error message?

[xrjf]

The code had severall errors. Maybe this other way you can make it work:

        Dim sql = "select Count(*) from pur_invdet where purinvid=@purinvid"
        Dim cmd = New OleDb.OleDbCommand(sql, cn)
        cmd.Parameters.AddWithValue("@purinvid", TextBox1.Text)
        Dim result As Integer = Convert.ToInt32(cmd.ExecuteScalar())
        cmd.Parameters.Clear()

        Try
            If result > 0 Then

                For Each x As ListViewItem In ListView1.Items
                    Dim sln As Integer = x.SubItems(0).Text
                    Dim qty As Integer = Convert.ToDecimal(x.SubItems(4).Text)
                    Dim itmid As Integer = x.SubItems(12).Text
                Next
                sql = "Update pur_invdet set [slno] = @slno, [ItemIDpur]=@ItemIDpur  where purinvid=@purinvid"
                cmd = New OleDb.OleDbCommand(sql, cn)
                cmd.Parameters.AddWithValue("@slno", Val(txtitemsl.Text))
                cmd.Parameters.AddWithValue("@ItemIDpur", txtitemid.Text)
                cmd.Parameters.AddWithValue("@purinvid", txtpurinvid.text)
                Dim n As Int32 = cmd.ExecuteNonQuery()
                If n Then
                    MsgBox("Updated ")
                End If
            Else

                sql = "Insert into pur_invdet (slno, ItemIDpur,purinvid) values (@slno, @ItemIDpur, @purinvid)"
                cmd = New OleDb.OleDbCommand(sql, cn)
                cmd.Parameters.AddWithValue("@slno", Val(txtitemsl.Text))
                cmd.Parameters.AddWithValue("@ItemIDpur", txtitemid.Text)
                cmd.Parameters.AddWithValue("@purinvid", txtpurinvid.text)
                Dim n As Int32 = cmd.ExecuteNonQuery()
                If n Then
                    MsgBox("Saved")
                End If
            End If
            cmd.Dispose()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

[Santanu.Das]

@Jayanta_1: In a parameterised sql statement you must have to use the parameter before adding/creating the parameter. In your first sql statement you didn't use the parameter into the statement. use of parameterised sql statement is the best practice to prevent your database from sql injections.
The codes should be as @xrj already discussed i.e.

        Dim sql = "select Count(*) from pur_invdet where purinvid=@purinvid"
        Dim cmd = New OleDb.OleDbCommand(sql, cn)
        cmd.Parameters.AddWithValue("@purinvid", TextBox1.Text)
        Dim result As Integer = Convert.ToInt32(cmd.ExecuteScalar())

But do not understand why did not you do the update and save codes for new items in the for loop to run between the listviewitems and the updates should be run for every items and save for new items. So previous veriable you declared as resulthas no proper functionality to check and do your updates /save.

And I do not understand what you want to do. Please explain briefly what you want to save/update for every listview items or store a cumulative value for a item if there has duplicate value if any.

[xrjf]

@Shark purinvid seems to be the table's key, so if it does not exist resultwill be 0 out from executeScalarand the row will be added. In the other case the row will be updated.

[dspnhn]

Not much of development lately but i think u need to match ur field change to ur recordset and then just replace it with the field value....

For save u just need to match record count and insert all fields.