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.

Answers


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.


Need Your Help

Summing the distinct elements of query result

sql tsql

I have following three tables representing a tree structure. Every row in #A is ancestor of zero or more rows in #B. Similarly every row in #B is ancestor of zero or more rows in #C. Table #B conta...

Trouble in using jquery hide() function?

jquery css struts2

In the code given below, I am displaying images which opens in prettyphoto viewer after clicking on it.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.