[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.
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.
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.
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.
Re: Incorrect integer value: Product1 for column: at row1 when using a ListView in V
deleted post as it is a double post
Re: Incorrect integer value: Product1 for column: at row1 when using a ListView in V
Quote:
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.
Quote:
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?
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: -
Re: Incorrect integer value: Product1 for column: at row1 when using a ListView in V
Quote:
Originally Posted by
wire_jp
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: -
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.
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.
Re: Incorrect integer value: Product1 for column: at row1 when using a ListView in V
deleted due to duplication of post.
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
