Connecting to mySQL database from ASP.Net (vb)
HI, I am a fairly experienced ASP developer making the move to ASP.net.
I am trying to open a database connection to a MySQL database but I am finding it overly complicated and not at all user friendly.
Is there no way to open a database connection, get a recordset and Move, Insert, and Delete on the fly? Is there no .AddNew, .MoveNext or .Update functionality?
At the moment I am using the MySQLDataAdapter to fill a Dataset but I can't see how to update changes back to the Table.
Do I really have to construct INSERT and UPDATE sqlCommands and execute them?
Any example code (in VB) would be gratefully received.
As you are transitioning from classic ASP, I would recommend getting familiar with ADO.NET basics, this article on MSDN will answer a lot of your questions. This article in particular will help a lot. I know it might seem like a wall of text, but coming from classic asp and Recordsets, etc, these links will prove beneficial.
After reading some very dull MSDN Articles I managed to do it.
Dim dbConnection As New MySqlConnection("server=xxx; user id=xxx; password=xxx; database=xxx; pooling=false;") Dim dbDataAdapter As New MySqlDataAdapter("SELECT * FROM table", dbConnection) Dim dbDataSet As DataSet = New DataSet dbDataAdapter.Fill(dbDataSet, "table") Dim dbNewRow As DataRow = dbDataSet.Tables("table").NewRow dbNewRow("data1") = "Some Data" dbNewRow("data2") = "Some More Data" dbNewRow("data3") = "Even More Data" dbDataSet.Tables("table").Rows.Add(dbNewRow) dbDataAdapter.InsertCommand = New MySqlCommand("INSERT into table(data1, data2, data3) VALUES (@data1, @data2, @data3)", dbConnection) dbDataAdapter.InsertCommand.Parameters.Add("data1", MySqlDbType.VarChar, 25, "data1") dbDataAdapter.InsertCommand.Parameters.Add("data2", MySqlDbType.VarChar, 25, "data2") dbDataAdapter.InsertCommand.Parameters.Add("data3", MySqlDbType.VarChar, 25, "data3") dbDataAdapter.Update(dbDataSet, "table")
Thanks to everyone for their help.