Results 1 to 11 of 11

Thread: [RESOLVED] Incorrect integer value: Product1 for column: at row1 when using a ListView in VS

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2018
    Posts
    46

    Resolved [RESOLVED] Incorrect integer value: Product1 for column: at row1 when using a ListView in VS

    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.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Incorrect integer value: Product1 for column: at row1 when using a ListView in V

    Argh! Why do so many people want to make their lives harder by using a ListView instead of am actual grid control? Just use a DataGridView. Call Fill on a data adapter to populate a DataTable and assign that to the DataSource of a DataGridView. That will automatically display all your data with no code at all. You can edit it in place or externally and then you simply call Update on the same data adapter to save the changes. Nice and easy and fewer moving parts to break.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Incorrect integer value: Product1 for column: at row1 when using a ListView in V

    To be clear, what you should be doing is adding a DataGridView, BindingSource and BindingNavigator to your form along with the TextBoxes, etc, and setting the BindingSource property of the BindingNavigator there. You would also set ReadOnly to True on the grid. In the Load event handler of the form, you call Fill on your data adapter to populate your DataTable, bind that to the BindingSource and then bind that to the DataGridView and the other controls too. When the user selects a row in the grid, the TextBoxes, etc, will be automatically populated with the data and any edits you make in those individual controls will be pushed back to the DataTable and be displayed in the grid. There will be some delay in the grid being updated because changes aren't necessarily committed to the DataTable immediately. When you're ready to save, you call EndEdit on the BindingSource to commit any pending changes and then call Update on the data adapter to save the changes from the DataTable back to the database.

  4. #4

    Thread Starter
    Member
    Join Date
    Aug 2018
    Posts
    46

    Re: Incorrect integer value: Product1 for column: at row1 when using a ListView in V

    Hi jmc,

    I have updated my code. My form has textboxes, a combobox, a checkbox and a datagridview. The end goal is to update, add new records and save changes back to MySQL database.

    When I click on a row in the datagridview (say record 7) and I then make an update the quantity textbox for record 7, and I click the update button, it throws an exception message:-

    Code:
    exception thrown 'system.invalidoperationexception' in system.data.dll (the SelectCommand property has not been initilalized before calling Fill vba.net

    My vba.net code is: -

    Code:
    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("MetricID").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
    
            txtMetricID.DataBindings.Add("Text", Me.OrderBindingSource, "MetricID")
            txtOrders.DataBindings.Add("Text", Me.OrderBindingSource, "Orders")
            ChkCheckedStatus.DataBindings.Add("Text", Me.OrderBindingSource, "CheckedStatus")
            txtOrderQuantity.DataBindings.Add("Text", Me.OrderBindingSource, "Quantity")
    
            ' Bind the DataGridView to the BindingSource
            ' and load the data from the database.
    
            OrderBindingSource.DataSource = OrderDataGridView.Rows
            OrderBindingNavigator.BindingSource = OrderBindingSource
            Me.OrderDataGridView.DataSource = Me.OrderBindingSource
            GetOrderData("select * from Orders")
    
            'instead of adding event handler for MoveFirst,MoveLast,MoveNext,MovePrevious
            'chose this one. it will fire anyway
            AddHandler BindingNavigatorPositionItem.TextChanged, AddressOf bindingnavigator_PostionChanged
    
    
      'if it didn't find the key, position = 1
            'you can also try any else proper event
    OrderBindingSource.Position = OrderBindingSource.Find("MetricID", IIf(txtMetricID.Text = "", 0, txtMetricID.Text))
    End Sub
    
    Private Sub UpdateOrders()
            Dim conn As New MySqlConnection
            conn.ConnectionString =
           "server=localhost;Port=3306;database=database;userid=root;password=password;persistsecurityinfo=True"
            Dim daOrders As MySqlDataAdapter = New MySqlDataAdapter("SELECT * From Orders", conn)
            Dim myBuilder = New MySqlCommandBuilder(daOrders)
            conn.Open()
            daOrders.Fill(dsOrders, "Orders")
            daOrders.Update(dsOrders, "Orders")
            MsgBox("Data Updated", MsgBoxStyle.OkOnly)
    
            With CboProductID_fk
                .DisplayMember = "Product"
                .ValueMember = "ProductID"
                .DataSource = OrderBindingSource
            End With
    
    
            txtOrders.DataBindings.Clear()
            ChkCheckedStatus.DataBindings.Clear()
            txtOrderQuantity.DataBindings.Clear()
    
            OrderBindingNavigator.BindingSource = OrderBindingSource
    
            With Me
                .txtOrders.DataBindings.Add("Text", OrderBindingSource, "Orders", True, DataSourceUpdateMode.OnValidation, vbNullString)
                .ChkCheckedStatus.DataBindings.Add("CheckState", OrderBindingSource, "CheckedStatus", True, DataSourceUpdateMode.OnValidation, CheckState.Unchecked)
                .txtOrderQuantity.DataBindings.Add("Text", OrderBindingSource, "Quantity", True, DataSourceUpdateMode.OnValidation, vbNullString)
            End With
    
            OrderBindingSource.EndEdit()
            daOrders.Update(dtOrders)
    End Sub
    Private Sub DgvOrders()
            con = New MySqlConnection
            con.ConnectionString = conString
            Dim MySqldaOrders As New MySqlDataAdapter
            Dim OrdersBindingSource As New BindingSource
            Try
                con.Open()
                Dim queryOrders As String = "select * from database.Orders"
                cmd = New MySqlCommand(queryOrders, con)
                MySqldaOrders.Fill(dtOrders)
                OrdersBindingSource.DataSource = dtOrders
                OrderDataGridView.DataSource = OrderBindingSource
                MySqldaOrders.Update(dtOrders)
    
                con.Close()
    
            Catch ex As Exception
    
            End Try
        End Sub
    Private Sub BtnProductUpdate_Click(sender As Object, e As EventArgs) Handles BtnProductUpdate.Click
    
            UpdateOrders()
            DgvOrders()
    
            
        End Sub
    
    
    Private Sub BtnOrderSave_Click(sender As Object, e As EventArgs) Handles BtnOrderSave.Click
            Dim dsOrders As New DataSet
            Me.Validate()
    
            Me.OrderBindingSource.EndEdit()
    
            Me.daOrders.Update(dsOrders, "Orders")
    
    End Sub

    Another problem which I am experiencing is if I add a record, using the binding navigator new record control and I fill in the details of the new record and click the save button, the following error message occurs: -

    Code:
    System.InvalidOperationException: 'Update unable to find TableMapping['Orders'] or DataTable 'Orders'.'

    Thank you for your help in advance.

  5. #5

    Thread Starter
    Member
    Join Date
    Aug 2018
    Posts
    46

    Re: Incorrect integer value: Product1 for column: at row1 when using a ListView in V

    deleted post as it is a double post
    Last edited by wire_jp; Feb 17th, 2019 at 10:20 PM. Reason: ignore post

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Incorrect integer value: Product1 for column: at row1 when using a ListView in V

    exception thrown 'system.invalidoperationexception' in system.data.dll (the SelectCommand property has not been initilalized before calling Fill vba.net
    So... did you check whether the SelectCommand property had been initialised before calling Fill, because that seems like to be the reason for the exception. The error message is explicitly stating what the problem is. Here's the relevant code:
    Code:
            Dim MySqldaOrders As New MySqlDataAdapter
            Dim OrdersBindingSource As New BindingSource
            Try
                con.Open()
                Dim queryOrders As String = "select * from database.Orders"
                cmd = New MySqlCommand(queryOrders, con)
                MySqldaOrders.Fill(dtOrders)
    Where exactly are you expecting that SQL code to get into that data adapter? Given that you are successfully creating data adapters and calling Fill on them elsewhere, why are you doing anything differently here? If you're trying to do the same thing two different ways in two different places and one works while the other doesn't, you shouldn't really need prompting to change the one that doesn't work to use the same method as the one that does.
    System.InvalidOperationException: 'Update unable to find TableMapping['Orders'] or DataTable 'Orders'.'
    It seems like that that would because the DataTable you're trying to save changes from doesn't exist. Why would it, given that you just created an empty DataSet instead of using one that you had already populated and made changes to?

  7. #7

    Thread Starter
    Member
    Join Date
    Aug 2018
    Posts
    46

    Re: Incorrect integer value: Product1 for column: at row1 when using a ListView in V

    I updated this section of VB.NET code

    Code:
      Private Sub DgvOrders()
            con = New MySqlConnection
            con.ConnectionString = conString
            Dim MySqldaOrders As New MySqlDataAdapter
            Dim OrdersBindingSource As New BindingSource
            Try
                con.Open()
                Dim queryOrders As String = "select * from Orders"
                cmd = New MySqlCommand(queryOrders, con)
                MySqldaOrders.SelectCommand = cmd
                MySqldaOrders.Fill(dtOrders)
                OrdersBindingSource.DataSource = dtOrders
                OrderDataGridView.DataSource = OrderBindingSource
                MySqldaOrders.Update(dtOrders)
    
                con.Close()
    
            Catch ex As Exception
    
            End Try
        End Sub
    Code:
    MySqldaOrders.SelectCommand = cmd.
    after the line
    Code:
     cmd = New MySqlCommand(queryOrders, con)
    when I built the solution and I press the update button, the message dialog box appeared and indicated that the update was successfully. However, the combobox's text data (e.g. Product1) immediately disappeared and it was replaced and filled by this phrase "system.data.datarowview (when I press the combobox's dropdown button, there were 23 rows of the phrase "system.data.datarowview").

    So I updated this VB.NET code again as follows:-

    Code:
       Private Sub UpdateOrders()
    MysqlConn = New MySqlConnection
            con.ConnectionString = "Server=localhost;Port=3306;Database=database;userid=root;password=password;persist security info=True"
            Dim MySqldaOrders As New MySqlDataAdapter
            daOrders.SelectCommand.Connection = MysqlConn
            MysqlConn.Open()
            daOrders.SelectCommand.CommandText = "Select * From Orders"
            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("MetricID").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
    
            MsgBox("Data Updated", MsgBoxStyle.OkOnly)
    
            MysqlConn.Close()
            
            OrderBindingSource.EndEdit()
            daOrders.Update(dtOrders)
    End Sub
    Now, when I build a solution, and I change the textbox data and then press the update button, it shows the following error message:-
    Code:
    MySql.Data.MySqlClient.MySqlException: 'Host '192.168.1.10' is not allowed to connect to this MySQL server' visual studio vb.net
    at the line: -

    Code:
       MysqlConn.Open()

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Incorrect integer value: Product1 for column: at row1 when using a ListView in V

    Quote Originally Posted by wire_jp View Post
    Now, when I build a solution, and I change the textbox data and then press the update button, it shows the following error message:-
    Code:
    MySql.Data.MySqlClient.MySqlException: 'Host '192.168.1.10' is not allowed to connect to this MySQL server' visual studio vb.net
    at the line: -

    Code:
       MysqlConn.Open()
    Read the title of this thread. This issue has nothing to do with the topic of this thread. You should start a new thread with a title that describes the current issue and provide all and only the information relevant to that current issue. As the issue is connecting to a MySQL server, you should post in the Database Development forum, because it's not an issue that relates to VB.NET specifically. It sounds like a MySQL configuration issue but I'm not very experienced with MySQL so I'm not 100% sure.

    Of course, you could just copy the error message into a search engine and likely see what solutions every other person who has encountered it has been recommended. You should always do that first.

  9. #9

    Thread Starter
    Member
    Join Date
    Aug 2018
    Posts
    46

    Re: Incorrect integer value: Product1 for column: at row1 when using a ListView in V

    Hi jmc,

    I am not setup to use a MySQL server, but I think the problem was due to the fact that I was using two different connection strings "con" versus "MySqlConn" in error. However, I have changed the names of the connection strings: -

    Code:
    Imports MySql
    Imports MySql.Data.MySqlClient
    Imports MySql.Data
    
    
    Public Class Form1
    Inherits Form
    
    Dim conString As String = "Server=localhost;Port=3306;Database=mydatabase;userid=root;password=mypassword;persist security info=True"
        Dim con As MySqlConnection = New MySqlConnection(conString)
        Dim adapter As MySqlDataAdapter
    
    Dim conString As String = "Server=localhost;Port=3306;Database=mydatabase;userid=root;password=mypassword;persist security info=True"
        Dim con As MySqlConnection = New MySqlConnection(conString)
    
    Private Sub UpdateOrders()
    
            con = New MySqlConnection
            con.ConnectionString = conString
            Dim MySqldaOrders As New MySqlDataAdapter
            daOrders.SelectCommand.Connection = con
            con.Open()
            daOrders.SelectCommand.CommandText = "Select * From Orders"
            daOrders = New MySqlDataAdapter("SELECT * FROM Orders", con)
            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", con)
            dtProducts = New DataTable("Products")
                daProducts.Fill(dtProducts)
                dsOrders.Tables.Add(dtProducts)
                cbOrders = New MySqlCommandBuilder(daProducts)
    
                dtOrders.Columns("MetricID").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
    
            MsgBox("Data Updated", MsgBoxStyle.OkOnly)
    
            con.Close()
            
    
            OrderBindingNavigator.BindingSource = OrderBindingSource
    
    
            OrderBindingSource.EndEdit()
            daOrders.Update(dtOrders)
    
        End Sub
    End Class
    When I build the solution, make any udpates to the textbox data and press the update button, the dialog message box pops up with the message: "updated successfully". However, when I check the MySQL database to see if the updates have occurred in the table's fields, there are no updates. The visual studio programme is not showing any errors or exceptions when the code is ran.

  10. #10

    Thread Starter
    Member
    Join Date
    Aug 2018
    Posts
    46

    Re: Incorrect integer value: Product1 for column: at row1 when using a ListView in V

    deleted due to duplication of post.
    Last edited by wire_jp; Feb 19th, 2019 at 07:57 AM. Reason: deleted due to duplication of post.

  11. #11

    Thread Starter
    Member
    Join Date
    Aug 2018
    Posts
    46

    Re: Incorrect integer value: Product1 for column: at row1 when using a ListView in V

    Hi jmc,


    I got the issue resolved, when I use this vb.net code to save changes to the textbox data back to the MySQL database (thank you for all of your help): -

    Code:
    Imports MySql
    Imports MySql.Data.MySqlClient
    Imports MySql.Data
    
    Public Class Form1
        Inherits Form
    
       Public ConnectionString As String = ""
        Dim conString As String = "Server=localhost;Port=3306;Database=mydatabase;userid=root;password=mypassword;persist security info=True"
        Dim con As MySqlConnection = New MySqlConnection(conString)
    
    
    Private Sub EndEditOnAllBindingSources()
            Dim BindingSourcesQuery = From bindingsources In Me.components.Components
                                      Where (TypeOf bindingsources Is Windows.Forms.BindingSource)
                                      Select bindingsources
    
            For Each bindingSource As Windows.Forms.BindingSource In BindingSourcesQuery
                bindingSource.EndEdit()
            Next
        End Sub
    
    
    Private Sub BtnOrderSave_Click(sender As Object, e As EventArgs) Handles BtnOrderSave.Click
    
            Me.EndEditOnAllBindingSources()
    
            SaveOrders()
    
        End Sub
    
        Private Sub SaveOrders(Optional messages As Boolean = True)
    
            con = New MySqlConnection
            con.ConnectionString = conString
            Dim daOrders As New MySqlDataAdapter("select * from Orders", con)
            Dim dsOrders As New DataSet
    
            If Me.Validate Then
                Me.OrderBindingSource.EndEdit()
                Me.daOrders.Update(Me.dtOrders)
                dsOrders.EnforceConstraints = False
            Else
                System.Windows.Forms.MessageBox.Show(Me, "Validation _
                                                  errors occurred.",
                  "Save", System.Windows.Forms.MessageBoxButtons.OK,
                  System.Windows.Forms.MessageBoxIcon.Warning)
            End If
    
            Try
                dsOrders.EnforceConstraints = True
            Catch e As System.Data.ConstraintException
                ' Process exception and return.
                Console.WriteLine("Exception of type {0} occurred.",
                e.GetType().ToString())
            End Try
    
    
        End Sub
    
    
    Private Sub BtnOrderSave_Click(sender As Object, e As EventArgs) Handles BtnOrderSave.Click
    
            Me.EndEditOnAllBindingSources()
    
           SaveOrders()
      End Sub
    
        Private Sub SaveOrders(Optional messages As Boolean = True)
    
            con = New MySqlConnection
            con.ConnectionString = conString
            Dim daOrders As New MySqlDataAdapter("select * from Orders", con)
            Dim dsOrders As New DataSet
    
            If Me.Validate Then
                Me.OrderBindingSource.EndEdit()
                Me.daOrders.Update(Me.dtOrders)
                dsOrders.EnforceConstraints = False
            Else
                System.Windows.Forms.MessageBox.Show(Me, "Validation _
                                                  errors occurred.",
                  "Save", System.Windows.Forms.MessageBoxButtons.OK,
                  System.Windows.Forms.MessageBoxIcon.Warning)
            End If
    
            Try
                dsOrders.EnforceConstraints = True
            Catch e As System.Data.ConstraintException
                ' Process exception and return.
                Console.WriteLine("Exception of type {0} occurred.",
                e.GetType().ToString())
            End Try
    
    
        End Sub
    End Class

Tags for this Thread

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