need to save changes from dataset to mysql database using VB.NET

As the title says. I would like to save any changes I make to my dataset or datatable through my datagrid to the original MySQL database I am pulling it from.  I dont have any code right now, but the few i have tried from online dont seem to work. I am probably doing soemthing wrong or missing a step.  

I pull the info off the database to a datagrid where i can inherently make changes to the numbers or whatever.  When I make changes or insert new rows or delete rows, I want these to be saved when i push my button named "btnSave". if you need more info to help just ask.
arikambAsked:
Who is Participating?
 
TorrwinCommented:
If you already have a dataset and command, then the following code should work.  This would be in your btnSave clicked event code.  This is what I do:

Dim myID as String
Dim RecordCount, RecordIndex As Integer

RecordCount = dsTemp.Tables(0).Rows.Count

While (RecordCount > RecordIndex)
            If Not (dsTemp.Tables(0).Rows(RecordIndex).Item("ID") Is System.DBNull.Value) Then
                myID = dsTemp.Tables(0).Rows(RecordIndex).Item("ID")
            End If

           myQuery = "(UPDATE DCS_CUSTOMER_CORRECTED_ADDS SET COLUMN_NAME = 'VALUE', NEXT_COLUMN_NAME = 'VALUE', ...etc... WHERE ID = '" & myID & "')"

           Try
                        myCommand.executenonquery
           Catch
                        MessageBox.Show(Err.Description)
           End Try

            RecordIndex += 1
End While
0
 
TorrwinCommented:
Instead of 'VALUE' you would put in your columns, like:

...SET COLUMN_NAME = '" & dsTemp.Tables(0).Rows(RecordIndex).Item("COLUMN_0") & "', NEXT_COLUMN_NAME = '" & dsTemp.Tables(0).Rows(RecordIndex).Item("COLUMN_1") & "', ...etc
0
 
arikambAuthor Commented:
ok...looks exactly like what i want...just need a few more tips on this one...

The table that could be in the dataset is dynamic. I have a large database with many tables and I could run any number of queries on my stuff.  How can I pull the column names out of the dataset information?  Becuase I see the headers within the dataset and there must be a way to pull an array or string of those column names right?  Once I have that, i'm in the clear!  
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
TorrwinCommented:
Ok, here's how I do that, I don't pull it from the dataset, I just pull it straight from the database/table itself:

To get your database names, your MS SQL query would be:
SELECT SYSDATABASES.NAME FROM SYSDATABASES ORDER BY NAME

To get your table names, your query would be:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  <---(your connection string would have the database name in it)

To get your column names, your query would be:
SELECT COLUMN NAME FROM [DATABASE_NAME].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABLE_NAME'
0
 
arikambAuthor Commented:
ok got the column names to work...

now how do i DELETE records from the dataset and have that be reflected in the actual database??

some code ive TRIED...doesnt work though on the DATABASE end of things. it does reflect on the dataSET...
I also get an error on the "tempSQL = "DELETE FROM" & myTable & " WHERE " & lineArray(0) & " = '" & myID & "';" " line...that error says to check the syntax of my SQL command?  but i look at it and i dont see anything wrong with it...maybe i am just overlooking something.



        While (RecordCount > RecordIndex)
            If myData.Tables(0).Rows(RecordIndex).Item(lineArray(0)) Is System.DBNull.Value Or myData.Tables(0).Rows(RecordIndex).Item("Delete") = True Then
                myID = myData.Tables(0).Rows(RecordIndex).Item(lineArray(0))
                tempSQL = "DELETE FROM" & myTable & " WHERE " & lineArray(0) & " = '" & myID & "';"
                myData.Tables(0).Rows(RecordIndex).Delete()
                myData.GetChanges.AcceptChanges()
                myData.AcceptChanges()
                RecordIndex -= 1
                RecordCount -= 1
                'GoTo NEXTER
            Else
                myID = myData.Tables(0).Rows(RecordIndex).Item(lineArray(0))
                tempSQL = "UPDATE " & myTable & " SET "
                For j = 0 To lineArray.GetUpperBound(0)
                    If j <> lineArray.GetUpperBound(0) Then
                        tempSQL &= lineArray(j) & " = '" & myData.Tables(0).Rows(RecordIndex).Item(lineArray(j)) & "', "
                    Else
                        tempSQL &= lineArray(j) & " = '" & myData.Tables(0).Rows(RecordIndex).Item(lineArray(j))
                    End If
                Next
                tempSQL &= "' WHERE " & lineArray(0) & " = '" & myID & "';"
            End If

            'SQL = SQL & vbCrLf & tempSQL
            'MsgBox(SQL)
            Try
                myCommand.CommandText = tempSQL 'MISSED THIS FIRST TIME AROUND
                myCommand.ExecuteNonQuery()
0
 
arikambAuthor Commented:
ok...now i feel dumb...

this line was incorrect...
tempSQL = "DELETE FROM" & myTable & " WHERE " & lineArray(0) & " = '" & myID & "';"

no space after the FROM so it errored out every single time. very simple mistake that i overlooked for the past 13 hours.
0
 
TorrwinCommented:
It looks ok at first glance, I did see one error though,  "DELETE FROM" should be "DELETE FROM "

You need the space at the end, or else if your table name is "my_table" your delete statement will look like: "DELETE FROMmy_table"

If that doesn't completely fix it let me know, and I'll take a deeper look at the code. =)
0
 
TorrwinCommented:
Hehe, guess we found it at the same time.  Anything else?
0
 
arikambAuthor Commented:
No. That sohuld be it~! THANKS!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.