Solved

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

Posted on 2010-11-11
6
972 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
ID: 34117330
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
ID: 34120109
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
ID: 34120170
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:sgaggerj
ID: 34120189
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
ID: 34120457
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
ID: 34120468
Got me looking in the right direction, Thanks!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

932 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

9 Experts available now in Live!

Get 1:1 Help Now