Solved

VB.NET (MySQL) Saving changes to a datatable to the db

Posted on 2010-11-11
6
971 Views
Last Modified: 2012-05-10
Hi Experts,

i'm using MySQL and VB.Net 2008

I created a dataset with the wizard

in my code i'm doing the following (see code)

What i'm confused about is that it is NOT updating this information to the database.

what am i missing or doing wrong?

the Update returns 0
Inventory_DataTable = New PEQ.inventoryDataTable

Inventory_TableAdapter = New PEQTableAdapters.inventoryTableAdapter

Inventory_TableAdapter.ClearBeforeFill = True

Inventory_TableAdapter.Connection = New MySql.Data.MySqlClient.MySqlConnection(PEQ_CON_STR)



Inventory_TableAdapter.FillByID(Inventory_DataTable, 3)



' now delete the item in slotid 13

Inventory_DataTable.RemoveinventoryRow(Inventory_DataTable.Select("slotid=13")(0))

Inventory_DataTable.AcceptChanges()



' now add a new item in slotid 13

Inventory_DataTable.AddinventoryRow(3, 13, 1868, 1, 4278190080, 0, 0, 0, 0, 0, 0)

Inventory_DataTable.AcceptChanges()

Inventory_TableAdapter.Update(Inventory_DataTable)







' My customization of the fill command

Option Strict Off

Option Explicit On



Imports System

Imports MySql.Data.MySqlClient



Namespace PEQTableAdapters

    Partial Public Class inventoryTableAdapter

        Inherits System.ComponentModel.Component

        Public Property SelectCommand() As MySqlCommand()



            Get

                If (Me._commandCollection Is Nothing) Then

                    Me.InitCommandCollection()

                End If

                Return Me._commandCollection

            End Get



            Set(ByVal value As MySqlCommand())

                Me._commandCollection = value

            End Set



        End Property

        Public Function FillByID(ByVal dataTable As PEQ.inventoryDataTable, ByVal ID As Integer) As Integer

            Dim stSelect As String



            stSelect = Me._commandCollection(0).CommandText

            Try

                Me._commandCollection(0).CommandText += " WHERE charid=" + ID.ToString

                Return Me.Fill(dataTable)

            Catch ex As Exception



            Finally

                Me._commandCollection(0).CommandText = stSelect

            End Try



        End Function

    End Class

End Namespace

Open in new window

0
Comment
Question by:sgaggerj
  • 5
6 Comments
 
LVL 13

Accepted Solution

by:
AngryBinary earned 500 total points
Comment Utility
After you call AcceptChanges on a DataTable, the state of all modified DataRows goes from "Modified" to "Unchanged". Perhaps the reason you aren't seeing your updates is because the data adapter doesn't think any data has changed. Comment out "AcceptChanges", or move those calls to AFTER the update call.

Also, it wouldn't hurt to make sure that the UpdateCommand for the DataAdapter is populated. Sometimes a command builder can generate the Update command for certain data sources automatically, but sometimes it can't (depending on the particular implementation of the DataAdapter - different DB clients will have different features).
0
 
LVL 1

Author Comment

by:sgaggerj
Comment Utility
The reason that i used the AcceptChanges was when i first tried it it gave me a duplicate key error, as there are two primary keys in the table.  The primary keys are the 1st two fields in the row (in the example above '3' and '13')

commenting them out did in fact give me the same error.

it appears that the update IS populated too.
0
 
LVL 1

Author Comment

by:sgaggerj
Comment Utility
i just tried updating the actual row data, rather than deleting and adding a new row, and it DOES update the database (Result of the update = 1)

0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 1

Author Comment

by:sgaggerj
Comment Utility
it appears that the deleting is not working, i tried to delete that single row, and it did delete it from my table, but upon update it did not remove the row from the database.
0
 
LVL 1

Author Comment

by:sgaggerj
Comment Utility
I think i figured it out.

the 'RemoveinventoryRow' only removes it from the datatable, it doesn't delete it.

i needed to use 'Delete' on the row

Inventory_DataTable.Select("slotid=13")(0).Delete

then it worked.
0
 
LVL 1

Author Closing Comment

by:sgaggerj
Comment Utility
Got me looking in the right direction, Thanks!!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now