Hello,

I am using a winform application in Visual Studio Enterprise. The goal is to insert, retrieve, update and save data in a listview and return the saved data back to the mysql database. I would also like to search and filter data in the listview.This is a followup post to prior posting: - http://www.vbforums.com/showthread.p...33#post5358333. I have form with a listview and some textboxes, a combobox (for a foreign key) and a checkbox, along with four buttons. The layout is shown below:-
Textboxes:-
OrderID (primary key, autoincremented)
Orders (textbox, VarChar 45)
Quantity (textbox, VarChar 45)

Checkbox:
CheckedStatus (Boolean, T/F)

combobox
ProductID (foreign key, textbox, VarChar 45)

The buttons
Add
Update
Retrieve
Clear

My complete vb.net code is: -
Code:
Imports MySql
Imports MySql.Data.MySqlClient

Public Class Form1
    Inherits Form

  Dim dsProducts As New DataSet
  Dim dsOrders As New DataSet
Dim dtProducts As New DataTable
    Dim dtOrders As New DataTable
Dim daProducts As New MySqlDataAdapter
    Dim daOrders As New MySqlDataAdapter
Public ConnectionString As String = ""
    Dim conString As String = "Server=localhost;Port=3306;Database=database;userid=root;password=password;persist security info=True"
 Dim con As MySqlConnection = New MySqlConnection(conString)

Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown

daOrders = New MySqlDataAdapter("SELECT * FROM Orders", MysqlConn)
        daOrders.MissingSchemaAction = MissingSchemaAction.AddWithKey
        Dim dsOrders As New DataSet
        dtOrders = New DataTable("Orders")
        daOrders.Fill(dtOrders)
        dsOrders.Tables.Add(dtOrders)
        Dim cbOrders As New MySqlCommandBuilder(daOrders)
        daProducts = New MySqlDataAdapter("SELECT * FROM Products", MysqlConn)
        dtProducts = New DataTable("Products")
        daProducts.Fill(dtProducts)
        dsOrders.Tables.Add(dtProducts)
        cbOrders = New MySqlCommandBuilder(daProducts)

        dtOrders.Columns("OrderID").AutoIncrement = True
        dtProducts.Columns("ProductID").AutoIncrement = True

        dtOrders.Columns(0).AutoIncrementSeed = dtProducts.Rows.Cast(Of DataRow).Max(Function(dr As DataRow) CInt(dr.Item(0))) + 1
        dtProducts.Columns(0).AutoIncrementStep = 1

        dtProducts.Columns(0).AutoIncrementSeed = dtProducts.Rows.Cast(Of DataRow).Max(Function(dr) CInt(dr.Item(0))) + 1
        dtProducts.Columns(0).AutoIncrementStep = 1

        dsOrders.Relations.Add(New DataRelation("relation", dsOrders.Tables("Products").Columns("ProductID"), dsOrders.Tables("Orders").Columns("ProductID_fk")))

        ProductBindingSource = New BindingSource(dsOrders, "Products")

        CboProductID_fk.DisplayMember = "Product"
        CboProductID_fk.ValueMember = "ProductID"
        CboProductID_fk.DataSource = ProductBindingSource

        OrderBindingSource = New BindingSource(ProductBindingSource, "relation")

        'bind the Product's foreign key to the combobox's "SelectedValue"
        Me.CboProductID_fk.DataBindings.Add(New System.Windows.Forms.Binding("SelectedValue", Me.OrderBindingSource, "ProductID_fk", True))

        'Bind the DataTable to the UI via a BindingSource.
        OrderBindingSource.DataSource = dtOrders
        Me.OrderBindingNavigator.BindingSource = Me.OrderBindingSource

        txtOrderID.DataBindings.Add("Text", Me.OrderBindingSource, "OrderID")
        txtOrders.DataBindings.Add("Text", Me.OrderBindingSource, "Orders")
        ChkCheckedStatus.DataBindings.Add("Text", Me.OrderBindingSource, "CheckedStatus")
        txtOrderQuantity.DataBindings.Add("Text", Me.OrderBindingSource, "Quantity")

With OrderListView
            If .Columns.Count = 0 Then
                .View = View.Details
                .FullRowSelect = True
                .CheckBoxes = True
                .Columns.Add("OrderID", 60)
                .Columns.Add("ProductID", 60)
                .Columns.Add("Orders", 150)
                .Columns.Add("CheckedStatus", 100)
                .Columns.Add("Quantity", 100)
            End If
        End With

        Dim cn As New MySqlConnection()
        Dim Dtr As MySqlDataReader

        'Open a connection to the mysql database.
        cn.ConnectionString = "server=localhost;Port=3306;database=database;userid=root;password=password;persist security info=True"
        cn.Open()

        Dim sSQL As String = "Select  OrderID, ProductID_fk, Orders, CheckedStatus, Quantity From database.Orders "


        Dim cmd As New MySqlCommand(sSQL, cn)
        Dtr = cmd.ExecuteReader

        With OrderListView
            If .Columns.Count = 0 Then
                .View = View.Details
                .Columns.Add("OrderID", 60, HorizontalAlignment.Left)
                .Columns.Add("ProductID_fk", 60, HorizontalAlignment.Right)
                .Columns.Add("Orders", 150, HorizontalAlignment.Right)
                .Columns.Add("CheckedStatus", 100, HorizontalAlignment.Right)
                .Columns.Add("Quantity", 100, HorizontalAlignment.Right)
            End If
            .Items.Clear()
            .BeginUpdate()

            Do While Dtr.Read
                Dim Li As New ListViewItem
                Li.UseItemStyleForSubItems = False
                Li.Text = Dtr("OrderID").ToString
                Li.SubItems.Add(Dtr("ProductID_fk").ToString)
                Li.SubItems.Add(Dtr("Orders").ToString)
                Li.SubItems.Add(Dtr("CheckedStatus").ToString)
                Li.SubItems.Add(Dtr("Quantity").ToString)

                If Dtr("CheckedStatus").ToString = "True" Then
                    Li.Checked = True
                    Li.SubItems(3).Text = "Yes"
                Else
                    Li.Checked = False
                    Li.SubItems(3).Text = "No"
                    Li.SubItems(3).ForeColor = Color.AntiqueWhite
                    Li.SubItems(3).BackColor = Color.SlateGray
                End If
                .Items.Add(Li)
            Loop
            .EndUpdate()
        End With
        Dtr.Close()

OrderBindingSource.Position = OrderBindingSource.Find("OrderID", IIf(txtOrderID.Text = "", 0, txtOrderID.Text))

End Sub        

    Private Sub Add()
        'SQL Statement
        Dim CheckedStatusStr As String

        If ChkCheckedStatus.Checked Then
            CheckedStatusStr = ChkCheckedStatus.Text
        Else
            CheckedStatusStr = ""
        End If


        Dim sql As String = "INSERT INTO Orders(ProductID_fk,Orders,CheckedStatus,Quantity) VALUES((@PRODUCTID_fk,NULL),@ORDERS,@CHECKEDSTATUSStr,@QUANTITY)"
        cmd = New MySqlCommand(sql, con)

        'Parameters
        cmd.Parameters.AddWithValue("@PRODUCTID_fk", CboProductID_fk.Text)
        cmd.Parameters.AddWithValue("@ORDERS", txtOrders.Text)
        cmd.Parameters.AddWithValue("@CHECKEDSTATUSStr", ChkCheckedStatus.Text)
        cmd.Parameters.AddWithValue("@QUANTITY", txtOrderQuantity.Text)

        'Open connection and Insert
        Try
            con.Open()

            If cmd.ExecuteNonQuery > 0 Then
                MsgBox("Successfully inserted")
                Cleartxt()
            End If

            con.Close()

            Retrieve()
        Catch ex As Exception
            MsgBox(ex.Message)
            con.Close()
        End Try

    End Sub

'Populate ListView
    Private Sub Populate(OrderID As String, ProductID As String, Orders As String, CheckedStatus As String, Quantity As String)
        'Row Array

        Dim row As String() = New String() {OrderID, ProductID, Orders, CheckedStatus, Quantity}

        Dim item As New ListViewItem(row)

        OrderListView.Items.Add(item)
    End Sub
    'Retrieve from MySql database
    Private Sub Retrieve()
        OrderListView.Items.Clear()
        'SQL Statement
        Dim sql As String = "SELECT * FROM database.Orders"
        cmd = New MySqlCommand(sql, con)

        'Open, Retrieve, Fill ListView
        Try
            con.Open()
            daOrders = New MySqlDataAdapter(cmd)

            daOrders.Fill(dtOrders)

            For Each row In dtOrders.Rows
                Populate(row(0), row(1), row(3), row(4), row(5))
            Next
            con.Close()

            dtOrders.Rows.Clear()

        Catch ex As Exception
            MsgBox(ex.Message)
            con.Close()
        End Try

    End Sub
    'Update Mysql database
    Private Sub UpdateLV(OrderID As String)
        Dim sql As String = "UPDATE Orders SET ProductID_fk='" + CboProductID_fk.Text + "',Orders='" + txtOrders.Text + "', CheckedStatus='" + ChkCheckedStatus.Text + "', Quantity'" + txtOrderQuantity.Text + "' WHERE ORDERID = '" + OrderID + "'"
        cmd = New MySqlCommand(sql, con)

        Try
            con.Open()
            daOrders.UpdateCommand = con.CreateCommand()
            daOrders.UpdateCommand.CommandText = sql

            If daOrders.UpdateCommand.ExecuteNonQuery() > 0 Then
                MsgBox("Successfully Updated")
                Cleartxt()
            End If

            con.Close()
            Retrieve()

        Catch ex As Exception
            MsgBox(ex.Message)
            con.Close()
        End Try
    End Sub

    Private Sub OrderListView_MouseClick(sender As Object, e As MouseEventArgs) Handles OrderListView.MouseClick

        Dim Orders As String = OrderListView.SelectedItems(0).SubItems(2).Text
        Dim CheckedStatus As String = OrderListView.SelectedItems(0).SubItems(3).Text
        Dim Quantity As String = OrderListView.SelectedItems(0).SubItems(4).Text

        txtOrders.Text = Orders
        ChkCheckedStatus.Text = CheckedStatus
        txtOrderQuantity.Text = Quantity


    End Sub

    Private Sub BtnOrderAdd_Click(sender As Object, e As EventArgs) Handles BtnOrderAdd.Click
        Add()
    End Sub

    Private Sub BtnOrderUpdate_Click(sender As Object, e As EventArgs) Handles BtnOrderUpdate.Click
        Dim OrderID As String = OrderListView.SelectedItems(0).SubItems(0).Text
        UpdateLV(OrderID)
    End Sub

    Private Sub BtnOrderRetrieve_Click(sender As Object, e As EventArgs) Handles BtnOrderRetrieve.Click
        Retrieve()
    End Sub

    Private Sub BtnOrderClear_Click(sender As Object, e As EventArgs) Handles BtnOrderClear.Click
        Cleartxt()
    End Sub

End Class
On clicking the Add button, I receive the pop-up dialog box message: -

Code:
Incorrect integer value: Product1 for column:'ProductID_fk' at row1

Code:
        Dim sql As String = "INSERT INTO Orders(ProductID_fk,Orders,CheckedStatus,Quantity) VALUES(@PRODUCTID_fk,@ORDERS,@CHECKEDSTATUSStr,@QUANTITY)"
When I updated the vb.net code above, I received the following message:
Code:
        Dim sql As String = "INSERT INTO Orders(ProductID_fk,Orders,CheckedStatus,Quantity) VALUES((@PRODUCTID_fk,NULL),@ORDERS,@CHECKEDSTATUSStr,@QUANTITY)"
I received the following pop-up dialog box message, on loading the solution: -
Code:
  Operand should contain 1 column(s)
Another issue which I have experienced is if I make a change to the data in the Quantity textbox and I click the Update button, I receive the following error message:-

Code:
System.ArgumentOutOfRangeException: 'InvalidArgument=Value of '0' is not valid for 'index'.
Parameter name: index'
I would like to select a record in the listview and when it populates the textboxes, combobox and checkbox, for the binding navigator record selector to show the record number, e.g. if I select record 7, the binding navigator record selector should show record 7 out of 10 records instead of remaining as 1 out of 10 records.

Thank you in advance for your help.