I've searched around the web looking for samples on how to use MySqlHelper.UpdateDataSet
but all I've found is:
Public Shared Sub UpdateDataSet( _
ByVal connectionString As String, _
ByVal commandText As String, _
ByVal ds As DataSet, _
ByVal tablename As String _
)
I'll be grateful if someone will give me:
- an example of
commandText
because I didn't understand what kind of command I need to give; - an explanation of
tablename
because I need to know if is the name of a table of the DB or of the DataSet or both (with the same name); - a vb.net code example (to start testing).
I tryed to use the command this way:
Private Sub Btn_Mod_Dataset_Click(sender As Object, e As EventArgs) Handles Btn_Mod_Dataset.Click
Dim SqlStr$ = "SELECT * FROM MyTest.Users"
Using DS_Test As DataSet = DB_Functions.mQuery(SqlStr)
With DS_Test
.Tables(0).TableName = "Users"
Dim User$ = .Tables(0).Rows(0)("UserName").ToString
MsgBox(User)
.Tables(0).Rows(0)("User") = "Upd_Test"
User = .Tables(0).Rows(0)("UserName").ToString
MsgBox(User)
End With
Dim DB_Name = "MyTest"
Dim connectionString$ = "datasource=localhost;username=" + UserDB _
+ ";password=" + Password + ";database=" + DB_Name
MySqlHelper.UpdateDataSet(connectionString, _
"Update MyTest.Users Set UserName = 'Test_Ok' WHERE UserName = 'Steve'", _
DS_Test, "Users")
End Using
End Sub
This gives me
System.NullReferenceException' in System.Data.dll
EDIT (to explain my code):
a) DB_Functions
is a sepate class where I've stored some function to use on a MySql DataBase. mQuery
is a function who extract query result into a dataset;
b) 'User' is a field Name : I've changed it to 'UserName' but same result;
d) The code between With
and End With
is just a test to see what happens;
NOTE that the code gives error but my DB is updated as in the commandText
String. I don't understand what happens
This might get you part of the way.
First get rid of
DB_Functions
. MySQLHelper has a method to create the DataSet for you; in general, db Ops are so query-specific that there is very little that is generic and reusable. The exception to this is building the ConnectionString: MySQL has gobs of cool options you can enable/disable via the connection string. But for that you just need the standardMySqlConnectionStringBuilder
.Build a DataSet:
There does not appear to be a way to specify a tablename when you build it, so that is a separate step.
Update a Record
To update a single row, you want
ExecuteNonQuery
; this will also allow you to use Parameters:Again, this is not really any simpler than using a fully configured DataAdapter, which would be simply:
I am not exactly sure what value the
UpdateDataSet
method adds. I think it is the "helper" counterpart for the above, but since it doesn't provide for Parameters, I don't have much use for it. The docs for it are sketchy.The
commandtext
would appear to be the SQL for a single row. Note that the DataAdapter.Update method above would add any new rows added, delete the deleted ones and update values for any row with changed values - potentially dozens or even hundreds of db Ops with one line of code.