arikamb
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
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].INFORMATIO
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].INFORMATIO
ASKER
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(Reco
myID = myData.Tables(0).Rows(Reco
tempSQL = "DELETE FROM" & myTable & " WHERE " & lineArray(0) & " = '" & myID & "';"
myData.Tables(0).Rows(Reco
myData.GetChanges.AcceptCh
myData.AcceptChanges()
RecordIndex -= 1
RecordCount -= 1
'GoTo NEXTER
Else
myID = myData.Tables(0).Rows(Reco
tempSQL = "UPDATE " & myTable & " SET "
For j = 0 To lineArray.GetUpperBound(0)
If j <> lineArray.GetUpperBound(0)
tempSQL &= lineArray(j) & " = '" & myData.Tables(0).Rows(Reco
Else
tempSQL &= lineArray(j) & " = '" & myData.Tables(0).Rows(Reco
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(
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(Reco
myID = myData.Tables(0).Rows(Reco
tempSQL = "DELETE FROM" & myTable & " WHERE " & lineArray(0) & " = '" & myID & "';"
myData.Tables(0).Rows(Reco
myData.GetChanges.AcceptCh
myData.AcceptChanges()
RecordIndex -= 1
RecordCount -= 1
'GoTo NEXTER
Else
myID = myData.Tables(0).Rows(Reco
tempSQL = "UPDATE " & myTable & " SET "
For j = 0 To lineArray.GetUpperBound(0)
If j <> lineArray.GetUpperBound(0)
tempSQL &= lineArray(j) & " = '" & myData.Tables(0).Rows(Reco
Else
tempSQL &= lineArray(j) & " = '" & myData.Tables(0).Rows(Reco
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(
ASKER
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.
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.
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. =)
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. =)
Hehe, guess we found it at the same time. Anything else?
ASKER
No. That sohuld be it~! THANKS!
...SET COLUMN_NAME = '" & dsTemp.Tables(0).Rows(Reco