Data Adapter / Datagridview basics

[DoJa]

I am trying to populate a datagridview from an sql statement (which I have achieved) but then want to be able to update the contents of the cells by entering data and saving the changes back to the database.

I am populating the datagridview with the following code:

Private Sub get_data()
        db.queryDb("SELECT customer.id, CONCAT (customer.forename, ' ', customer.surname) as name, customer.memberid, DATE_FORMAT(membercard.dateordered,'%d/%m/%Y') as dateordered, DATE_FORMAT(membercard.dateprinted,'%d/%m/%Y') as dateprinted FROM customer,membercard WHERE customer.id = membercard.customerid AND dateprinted = YEAR(0000)", dgvMembercard, 500)
    End Sub

And these are the functions in a separate class file:

'create data adapter
    Dim da As MySqlDataAdapter
    'create dataset
    Public ds As DataSet = New DataSet

    Public Function queryDb(ByVal sqlQuery As String, ByRef grid As DataGridView, ByVal limit As String) As Boolean

        Try
              da = New MySql.Data.MySqlClient.MySqlDataAdapter(sqlQuery, dbxConn)

            'create command builder
            Dim cb As MySql.Data.MySqlClient.MySqlCommandBuilder = New MySql.Data.MySqlClient.MySqlCommandBuilder(da)

            'fill dataset
            da.Fill(ds, "Customers")

            grid.DataSource = ds
            grid.DataMember = "Customers"

            Return True
        Catch ex As MySqlException
            MessageBox.Show(ex.ToString)
            Return False
        End Try
       
    End Function

    Public Function Update_DGV()
        Try
            'update customers table
            da.Update(ds, "Customers")
        Catch ex As Exception
            Return False
            MessageBox.Show(ex.ToString)
        End Try

        Return True
    End Function

At the moment it will only let me update the memberid.

If I try to change the id, name, dateordered, or dateprinted columns and then run the update function I do not get any errors but when i reload the data the changes have not been saved.

Could someone point me in the right direction here?

Thanks.

 

[jmcilhinney]

The problem is that the data you're retrieving is not the data that's in the database. It's some transformation of what's in the database. If you want to edit the data that's in the database then you have to actually display the data that's in the database, i.e. just get the column values with no additional transformation. For instance, if you want to format dates a particular way then you do that in the UI, not in the query.

 

[DoJa]

Does that mean I table joins are out of the question too? or just the date formatting that is too much?

I think the reason I added the date format was because without it I was getting an error without it saying that the date types were incompatible (as i seem to recall mysql stores dates by default in a different format to the default of vb

 

[jmcilhinney]

Ah, dates and databases. If I had a $ for every person who makes their life more difficult by trying to solve a problem that doesn't exist by messing about unnecessarily with dates in database then I'd be a rich man. There is no MySQL default format and there is no VB default format. Both the database and the application store dates in binary form. There is no format at all. You store a binary date in the database and you retrieve a binary date into your application. Format is a UI issue only. Once you have retrieved a binary date and you want to display it to the user, only then do you convert it to text and therefore only then does format become an issue. You can choose any format you want at the point that you convert it to text for display. The most logical thing to do is to use the default format for the current system, so that every user sees the format that they expect to see. That means that multiple users around the world might see different formats from the same data from the same database.

As for saving the data, retrieving data from more than one table does not preclude you from saving it but it can make the saving more complex, depending on the situation. You will definitely have to write the SQL code yourself and if you want to save to multiple tables then, depending on the provider, you may need to use two separate data adapters.

 

[DoJa]

I understand what you are saying .. partially. Just unsure of the implementation.

I realise that if I read a row from the database I can format that data however I like before choosing how and where it is displayed, but if using the 'automated' features of vb.net to populate datagridviews by binding directly to a data source I do not understand how I intercept that process and change the format of the displayed date to keep everything happy. Are you saying that I should code everything manually when working with dates in this way or is there a way to adapt the more automated method to do what I need it to do?

With regard to using two data adapters, am I right in thinking that rather than using a join in the SQL statement I should have one statement per adaptor with each adaptor pulling the relevant fields from a different table and then combining the columns on the datagridview? - to give the same appearence to the user as a single sql statement with a table join.

 

[jmcilhinney]

I realise that if I read a row from the database I can format that data however I like before choosing how and where it is displayed, but if using the 'automated' features of vb.net to populate datagridviews by binding directly to a data source I do not understand how I intercept that process and change the format of the displayed date to keep everything happy. Are you saying that I should code everything manually when working with dates in this way or is there a way to adapt the more automated method to do what I need it to do?

You don't have to intercept anything. It's done in the grid. Each column has a DefaultCellStyle property and that has a Format property. You set that to the appropriate format specifier and you're done. You can add the columns in the designer and set that sort of thing up ahead of time or you can get access to the columns after binding has created them.

With regard to using two data adapters, am I right in thinking that rather than using a join in the SQL statement I should have one statement per adaptor with each adaptor pulling the relevant fields from a different table and then combining the columns on the datagridview? - to give the same appearence to the user as a single sql statement with a table join.

One grid can only be bound to one list, e.g. one DataTable. If you want to display data from multiple database tables in one grid then you need to populate one DataTable with data from those multiple tables. If you want to let the user edit and save that data then you have to do the extra work required to save data from one DataTable back to multiple database tables. That may be able to be done with one data adapter or it may require multiple.

 

[DoJa]

Thanks Jmcilhinney, that has cleared a few things up! I tried your suggestion using the default cell style and after a bit of experimentation discovered the previous error I experienced was due to zero date values in one of my database columns. Changed these to default as Null instead and that solved a lot of problems.

I have now added an additional column to the datagridview and I would like to add some text to each row in this column. I think i know how to add it directly to the grid but I would prefer to add it to the dataset/table that lies beneath so all the data is stored in the same place.

How should I go about doing this? I'm struggling to visualise in my head where the data table is stored and how it relates to the datagridview and the dataset and thus how I go about referencing it. The fact that the code is in a separate class is adding to my state of confusion.. i dont currently understand if the data is being stored in the database class file, or in the form that calls function in that class.

Please would you try to explain this to me in a way I can understand - or link me to a suitable resource. If I can get my head around these concepts I should be able to answer more questions for myself, or at the very least then be able to ask more coherent questions on here!

 

[jmcilhinney]

In the code in post #1, 'ds' is a DataSet and it's Tables property is a collection of DataTables. That collection contains a DataTable named "Customers". Get that DataTable and add to its Columns collection.

 

[DoJa]

I follow thus far , however surely if I call the same function again for another purpose in the program then 'ds' will contain something different and will no longer be relevant? How do I ensure this doesn't become a problem?

I was thinking perhaps I should change the function from a boolean to a datatable, returning that datatable and then somehow linking the grid to that.

Am I heading in the right direction with this? ...

Public Function queryDb(ByVal sqlQuery As String, ByRef grid As DataGridView) As DataTable

        Try
            da = New MySql.Data.MySqlClient.MySqlDataAdapter(sqlQuery, dbxConn)

            'create command builder
            Dim cb As MySql.Data.MySqlClient.MySqlCommandBuilder = New MySql.Data.MySqlClient.MySqlCommandBuilder(da)

            'fill dataset
            da.Fill(ds, "Customers")

            grid.DataSource = ds
            grid.DataMember = "Customers"

            Return ds.Tables("Customers")
        Catch ex As MySqlException
            MessageBox.Show(ex.ToString)
            Return ds.Tables("Customers")
        End Try

 

[Caraldur]

Hi Doja,

Usually I will return a dataset from a class function and then bind that dataset to the datagridview.

Form Code

Public Class frmTestForm
    Dim _inventory As New Inventory

    Private Sub frmTestForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ds As New DataSet

        ds = _inventory.getInventory

        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "Parts"
    End Sub
End Class

Class Code

I fill my datatable in a private function which is called from the public function getInventory where the table is the added to the Dataset. I know it could be done in one function but its cleaner for me. Hope this helps.

   Public Function getInventory() As DataSet
        Dim dtParts As DataTable
    
        dtParts = getPartsInventory()
    
        dsInventory.Tables.Add(dtParts)
    
        Return dsInventory
    End Function

  Private Function getPartsInventory() As DataTable

        Dim dt As New DataTable("Parts")

        Try
            Dim taParts As New MySqlDataAdapter
            Dim sql As String = "SELECT products.ProductID, products.PartName, productinventory.PartQty "
            sql += "FROM products "
            sql += "INNER JOIN productinventory ON (products.ProductID = productinventory.ProductID)"

            con.ConnectionString = ConStr

            taParts.SelectCommand = New MySqlCommand(sql, con)
            con.Open()
            taParts.Fill(dt)

        Catch ex As MySqlException
            MsgBox(ex.Message.ToString)
        Finally
            con.Close()
        End Try
        Return dt
    End Function