• Hello and welcome to our new forums. We upgraded our forum sites to a more robust and modern system which we hope you will enjoy. Be sure to check out your profile by clicking the button on the top right and configure your preferences, signature, time zone, avatar, etc. as you wish. If you need help with using this new forum'ware try the help link on the bottom right.

    Click here to review your account now.

Question CRUD and ListView control

vicdeveloper

Member
Joined
Jun 26, 2016
Messages
11
Programming Experience
Beginner
Hello community,


I want to know how can I create a complete-fully crud operations using MySQL as a database and show/populate data in a ListView control. Also, in the process, connect to the database using a module with Ado.net or ODBC.


Please, Im a new on this kind of topics and currently Im studying computer science and I want to become a Visual Basic .Net programmer.


See ya,
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,031
Location
Sydney, Australia
Programming Experience
10+
Firstly, don't use a ListView. Use a DataGridView. It is a genuine grid control. As for the data access code, here's an example I previously wrote for SQL Server:
Private connection As New SqlConnection("connection string here")
Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
                                      connection)
Private table As New DataTable

Private Sub InitialiseDataAdapter()
    Dim delete As New SqlCommand("DELETE FROM StockItem WHERE ID = @ID", Me.connection)
    Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", Me.connection)
    Dim update As New SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", Me.connection)

    delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")

    insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")

    update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
    update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")

    Me.adapter.DeleteCommand = delete
    Me.adapter.InsertCommand = insert
    Me.adapter.UpdateCommand = update

    Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
End Sub

Private Sub GetData()
    'Retrieve the data.
    Me.adapter.Fill(Me.table)

    'The table can be used here to display and edit the data.
    'That will most likely involve data-binding but that is not a data access issue.
End Sub

Private Sub SaveData()
    'Save the changes.
    Me.adapter.Update(Me.table)
End Sub
The code for MySQL would be pretty much identical, but using types from the MySql.Data.MySqlClient namespace instead of the System.Data.SqlClient namespace. To have access to those types, you will need to download and install Connector/Net from the MySQL web site.

Note that that code was written as though it would be added to a form. You can make some small adjustments if it's not, e.g. make GetData a function that returns the DataTable and SaveData take the DataTable as an argument.
 
Top Bottom