datagridview image instead of value in column

[ud2008]

I have a datagridview with a ms access database.
It shows all the records that I have in the database, but the thing I'm struggling with is, that a column in the database contains 0 or 1 (which I use for checkbox), now I want to change that value (or change the column into a imagecolumn.

At the moment I hide the original column and created an imagecolumn, it does show the created column, but with the red x as image. Also I get an error message:

unable to cast object of type 'system.drawing.bitmap' to type 'system.iconvertible' (see attachment image)


I use the following code:

Private Sub ShowAllButton_Click(sender As System.Object, e As System.EventArgs) Handles ShowAllButton.Click
        Try
            msOle.Clear()
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM songs ORDER BY songtitle ASC", con)
            con.Open()
            Dim msDa As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            msDa.Fill(msOle, "songs")
            con.Close()
            'MsgBox("number of Row(s)   -   " & dsOle.Tables(0).Rows.Count)
            'Me.DataGridViewX1.AutoGenerateColumns = False
            DataGridViewX1.DataSource = msOle.Tables("songs")
            Me.DataGridViewX1.Columns("Id").Visible = False
            Me.DataGridViewX1.Columns("Trumpet").Visible = False
            Me.DataGridViewX1.Columns("Songtext").Visible = False
            Me.DataGridViewX1.Columns("Audio").Visible = False
            Me.DataGridViewX1.Columns("AudioLink").Visible = False
            Me.DataGridViewX1.Columns("Scores").Visible = False

            'Me.DataGridViewX1.Columns(1).Width = 15
            Dim imageCol As New DataGridViewImageColumn()
            imageCol.Name = "Trumpet"
            'imageCol.SortMode = DataGridViewColumnSortMode.Automatic
            DataGridViewX1.Columns.Add(imageCol)
            For Each row As DataGridViewRow In DataGridViewX1.Rows
                If row.Cells("Trumpet").Value IsNot Nothing Then
                    If (Convert.ToBoolean(row.Cells("Trumpet").Value)) Then
                        ' Cells[3] is the position of the cell in the row
                        row.Cells(3).Value = trueImg
                    Else
                        row.Cells(3).Value = falseImg
                    End If
                End If
            Next
            Me.DataGridViewX1.Columns(2).Width = 276
            DataGridViewX1.ReadOnly = True
        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Source & ": " & ex.Message, "Songlist Editor 2 Error !!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
    End Sub

In the form_load event:

Private Sub Form2_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        trueImg = DirectCast(Image.FromFile(Application.StartupPath & "\Image\trumpet.png"), Bitmap)
        falseImg = DirectCast(Image.FromFile(Application.StartupPath & "\Image\no-trumpet.png"), Bitmap)
End Sub

Hope you guys can help.

 

[ud2008]

Anyone an idea?

 

[Dunfiddlin]

Er ... unless I'm very much mistaken you're attempting to have your cake and eat it. You can't hide "Trumpet" and call values from it and at the same time have an image column called "Trumpet" in the same place as the original "Trumpet" would be (I think, even I'm confused now!). It's like having two pigeonholes with exactly the same name. Which one do I put the letters in? I don't think it's Cell(3) anyway. You certainly haven't stipulated that that's where the column should be added. I suspect that if you made all the columns visible that would be apparent.

 

[ud2008]

You are kinda right, Dunfiddlin. The original column "Trumpet" is a 0,1 column. This is hiding, a new column is created and should check each row if in column "Trompet" 0 or 1, than the image is set to that specific row.

Now is it the thing that the original code (writing in C#), has the same thing and that works.

Here is the website with the original code:
Display Image instead of CheckBox in DataGridView ~ Research & Development

Thanks for any replies.

 

[Dunfiddlin]

It doesn't work according to the comment, unanswered, which gives exactly the same error as you are now getting.

 

[ud2008]

It doesn't work according to the comment, unanswered, which gives exactly the same error as you are now getting.

Well, I've changed the connectionstring. Now the test application (from original source) works. So my guess is that I have something wrong with the database column, (the original uses SQL and I need to use ms access 2007 database).

 

[Dunfiddlin]

Oops. Knew I was missing something. I was assuming you wanted to add the images to the database but all you want is to show them in the gridview. So your initialisation is way too complex. Where you have ...

trueImg = DirectCast(Image.FromFile(Application.StartupPath & "\Image\trumpet.png"), Bitmap)

.... all you need for the grid is

Dim trueImg as New Bitmap(Application.StartupPath & "\Image\trumpet.png")

 

[ud2008]

Oops. Knew I was missing something. I was assuming you wanted to add the images to the database but all you want is to show them in the gridview. So your initialisation is way too complex. Where you have ...

trueImg = DirectCast(Image.FromFile(Application.StartupPath & "\Image\trumpet.png"), Bitmap)

.... all you need for the grid is

Dim trueImg as New Bitmap(Application.StartupPath & "\Image\trumpet.png")

Thanks for your reply, I have changed the code, but still getting the same error message. I have no I idea why it works in C# and not in VB

 

[Dunfiddlin]

Well the error is definitely the one you get when you try to insert an image into a non-image column. I know because I inadvertently did that very thing working with a cut down version of your code! So I'm pretty sure the problem is that, for whatever reason, the "trumpet" column in the gridview is not Column 3. I suggest commenting out all the visible commands and any code after adding the column and running the program to see exactly where the column is being added (it's usually at the end of the row) and then correcting the index accordingly. You can change where the column is shown on the gridview but you cannot change its actual index.

 

[ud2008]

Thanks again for the reply, yeah I know, but the strange thing is, that the original code creates an image column (to display in the datagridview) with the same name as the column in the sql database. I have tested the original code and saw that it works, it does show a column with images instead of a combobox column (they have the same name), so I don't know why it works in C# and not in vb2010. (maybe C# is better in dealing with this).

 

[Dunfiddlin]

There are always differences between procedures. Possibly C# does index only the visible columns?

One small difference I did notice. Where you have ...

Dim imageCol As New DataGridViewImageColumn()

... which is the correct C# declaration, I have ....

Dim imageCol As New DataGridViewImageColumn

... which seems to be the correct VB version.

Might make a difference?

 

[ud2008]

There are always differences between procedures. Possibly C# does index only the visible columns?

One small difference I did notice. Where you have ...

Dim imageCol As New DataGridViewImageColumn()

... which is the correct C# declaration, I have ....

Dim imageCol As New DataGridViewImageColumn

... which seems to be the correct VB version.

Might make a difference?

Doesn't make any differents

 

[ud2008]

Well I've working a bit with my own code and got the error message removed.

But now only a red cross is shown (the default img of the imagecolumn).

Here is the code (the message.show line gives a false message, because that is the value in that column):

Private Sub ShowAllButton_Click(sender As System.Object, e As System.EventArgs) Handles ShowAllButton.Click
        Try
            msOle.Clear()
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM songs ORDER BY songtitle ASC", con)
            con.Open()
            Dim msDa As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            msDa.Fill(msOle, "songs")
            con.Close()
            'MsgBox("number of Row(s)   -   " & dsOle.Tables(0).Rows.Count)
            'Me.DataGridViewX1.AutoGenerateColumns = False
            DataGridViewX1.DataSource = msOle.Tables("songs")
            DataGridViewX1.Columns("Id").Visible = False
            DataGridViewX1.Columns("Trumpet").Visible = False
            DataGridViewX1.Columns("Songtext").Visible = False
            DataGridViewX1.Columns("Audio").Visible = False
            DataGridViewX1.Columns("AudioLink").Visible = False
            DataGridViewX1.Columns("Scores").Visible = False

            Dim imageCol As New DataGridViewImageColumn
            imageCol.Name = "TrumpetImg"
            DataGridViewX1.Columns.Add(imageCol)
            For Each row As DataGridViewRow In DataGridViewX1.Rows
                If row.Cells("Trumpet").Value IsNot Nothing Then
                    If row.Cells("Trumpet").Value = False Then
                        MessageBox.Show(row.Cells("Trumpet").Value)
                        row.Cells("TrumpetImg").Value = Image.FromFile(Application.StartupPath & "\Image\no_trumpet.png")
                    Else
                        'MessageBox.Show(row.Cells("Trumpet").Value)
                        row.Cells("TrumpetImg").Value = Image.FromFile(Application.StartupPath & "\Image\trumpet.png")
                    End If
                End If
            Next
        Catch ex As Exception
            MessageBox.Show("Error: " & ex.Source & ": " & ex.Message, "Songlist Editor 2 Error !!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
    End Sub

Why are the images I want are not showing?

 

[Dunfiddlin]

row.Cells("TrumpetImg").Value = Image.FromFile(Application.StartupPath & "\Image\no_trumpet.png") ??

Try ...

Dim NoImg as New Bitmap(Application.StartupPath & "\Image\trumpet.png") 'in declarations

row.Cells("TrumpetImg").Value =NoImg 'in relevant code section

 

[ud2008]

row.Cells("TrumpetImg").Value = Image.FromFile(Application.StartupPath & "\Image\no_trumpet.png") ??

Try ...

Dim NoImg as New Bitmap(Application.StartupPath & "\Image\trumpet.png") 'in declarations

row.Cells("TrumpetImg").Value =NoImg 'in relevant code section

Thanks, yeah that worked (after I turned virtualmode to false, don't know why it was true).

Thanks again.