?
Solved

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

Posted on 2005-04-15
9
Medium Priority
?
516 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:arikamb
  • 5
  • 4
9 Comments
 
LVL 13

Accepted Solution

by:
Torrwin earned 2000 total points
ID: 13793910
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
 
LVL 13

Expert Comment

by:Torrwin
ID: 13793938
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
 

Author Comment

by:arikamb
ID: 13799960
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 13

Expert Comment

by:Torrwin
ID: 13802586
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
 

Author Comment

by:arikamb
ID: 13809428
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
 

Author Comment

by:arikamb
ID: 13809618
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
 
LVL 13

Expert Comment

by:Torrwin
ID: 13809662
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
 
LVL 13

Expert Comment

by:Torrwin
ID: 13809666
Hehe, guess we found it at the same time.  Anything else?
0
 

Author Comment

by:arikamb
ID: 13810332
No. That sohuld be it~! THANKS!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

809 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