Datagridview Problem

Hi Experts!!

Please I need your help once more.
I'm coding in Windows forms, vb .net .
While testing my screens, I found this exception coming up regularly:

"The following exception occurred in the DataGridView:  

System.ArgumentException: Parameter is not valid.

 at System.Drawing.Image.FromStream(Stream stream, Boolean useEmbeddedColorManagement, Boolean validateImageData)
 at System.Drawing.ImageConverter.ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
 at System.Windows.Forms.Formatter.FormatObjectInternal(Object value, Type targetType, TypeConverter sourceConverter, TypeConverter targetConverter, String formatString, IFormatProvider formatInfo, Object formattedNullValue)
 at System.Windows.Forms.Formatter.FormatObject(Object value, Type targetType, TypeConverter sourceConverter, TypeConverter targetconverter, String formatString, IFormatProvider formatInfo, Object formattedNullValue, Object dataSourceNullValue)
 at System.Windows.Forms.DataGridViewCell.GetFormattedValue(Object value, Int32 rowIndex, DataGridViewCellStyle& cellStyle, TypeConverter valueTypeConverter, TypeConverter formattedValueTypeConverter, DataGridVewDataErrorContexts context)

To replace this default dialog please handle the DataError event."

I was trying to use a datagridview with a queries that do not query images, so I'm currently confused about this.

So far, I only got around to know it has something to do with my query having a IF(... IS NULL, ...), because on a simpler query i managed to remove the if is null part without affecting the result I need, and the exception ceased appearing.

I do not know if you need all the following data, but just in case I'm attaching all I can think of...

My query is the following:


queryInputs = "SELECT '' AS iinputid, 'INPUTS' AS sinputdescription, '' AS stabforminputunit, '' AS unitprice, " & _
"'' AS dtabforminputqty, '' AS amount " & _
"UNION " & _
"SELECT ptfi.iinputid, i.sinputdescription, ptfi.stabforminputunit, IF(pp.dinputfinalprice IS NULL, cipp.dinputfinalprice, pp.dinputfinalprice) AS unitprice, " & _
"ptfi.dtabforminputqty, IF(SUM(ptfi.dtabforminputqty*pp.dinputfinalprice) IS NULL, SUM(ptfi.dtabforminputqty*cipp.dinputfinalprice), SUM(ptfi.dtabforminputqty*pp.dinputfinalprice)) AS amount " & _
"FROM projecttabforminputs ptfi " & _
"JOIN inputs i ON ptfi.iinputid = i.iinputid " & _
"JOIN inputtypes it ON i.iinputid = it.iinputid " & _
"LEFT JOIN (SELECT * FROM (SELECT * FROM projectprices ORDER BY iupdatedate DESC, supdatetime DESC) pp GROUP BY iinputid, iprojectid) pp ON ptfi.iprojectid = pp.iprojectid AND i.iinputid = pp.iinputid " & _
"LEFT JOIN (SELECT ptfi.iprojectid, ptfi.iinputid, cipp.iupdatedate, cipp.supdatetime, cipp.dinputpricewithoutIVA, cipp.dinputprotectionpercentage, SUM(ptfci.dcompoundinputqty*cipp.dinputfinalprice) AS dinputfinalprice FROM projecttabforminputs ptfi JOIN projecttabformcompoundinputs ptfci ON ptfci.iprojectid = ptfi.iprojectid AND ptfci.itabformid = ptfi.itabformid JOIN compoundinputs ci ON ci.icompoundinputid = ptfci.icompoundinputid AND ci.iinputid = ptfi.iinputid JOIN inputs i2 ON i2.iinputid = ci.icompoundinputid LEFT JOIN (SELECT * FROM (SELECT * FROM projectprices ORDER BY iupdatedate DESC, supdatetime DESC) cipp GROUP BY iinputid, iprojectid) cipp ON cipp.iprojectid = ptfci.iprojectid AND cipp.iinputid = ci.icompoundinputid WHERE ptfci.iprojectid = " & iprojectid & " AND ptfci.itabformid = " & itabformid & " GROUP BY ptfci.iprojectid, ptfci.itabformid, ptfi.iinputid) cipp ON ptfi.iprojectid = cipp.iprojectid AND i.iinputid = cipp.iinputid " & _
"WHERE ptfi.iprojectid = " & iprojectid & " AND ptfi.itabformid = " & itabformid & " AND it.sinputtypedescription = 'TOOLS' " & _
"GROUP BY ptfi.iprojectid, ptfi.iinputid " & _
"UNION " & _
"SELECT '', '', '', '', 'INPUTS TOTAL', IF(SUM(ptfi.dtabforminputqty*pp.dinputfinalprice) IS NULL, 0, SUM(ptfi.dtabforminputqty*pp.dinputfinalprice))+IF(SUM(ptfi.dtabforminputqty*cipp.dinputfinalprice) IS NULL, 0, SUM(ptfi.dtabforminputqty*cipp.dinputfinalprice)) AS amount " & _
"FROM projecttabforminputs ptfi " & _
"JOIN inputs i ON ptfi.iinputid = i.iinputid " & _
"JOIN inputtypes it ON i.iinputid = it.iinputid " & _
"LEFT JOIN (SELECT * FROM (SELECT * FROM projectprices ORDER BY iupdatedate DESC, supdatetime DESC) pp GROUP BY iinputid, iprojectid) pp ON ptfi.iprojectid = pp.iprojectid AND i.iinputid = pp.iinputid " & _
"LEFT JOIN (SELECT ptfi.iprojectid, ptfi.iinputid, cipp.iupdatedate, cipp.supdatetime, cipp.dinputpricewithoutIVA, cipp.dinputprotectionpercentage, SUM(ptfci.dcompoundinputqty*cipp.dinputfinalprice) AS dinputfinalprice FROM projecttabforminputs ptfi JOIN projecttabformcompoundinputs ptfci ON ptfci.iprojectid = ptfi.iprojectid AND ptfci.itabformid = ptfi.itabformid JOIN compoundinputs ci ON ci.icompoundinputid = ptfci.icompoundinputid AND ci.iinputid = ptfi.iinputid JOIN inputs i2 ON i2.iinputid = ci.icompoundinputid LEFT JOIN (SELECT * FROM (SELECT * FROM projectprices ORDER BY iupdatedate DESC, supdatetime DESC) cipp GROUP BY iinputid, iprojectid) cipp ON cipp.iprojectid = ptfci.iprojectid AND cipp.iinputid = ci.icompoundinputid WHERE ptfci.iprojectid = " & iprojectid & " AND ptfci.itabformid = " & itabformid & " GROUP BY ptfci.iprojectid, ptfci.itabformid, ptfi.iinputid) cipp ON ptfi.iprojectid = cipp.iprojectid AND i.iinputid = cipp.iinputid " & _
"WHERE ptfi.iprojectid = " & iprojectid & " AND ptfi.itabformid = " & itabformid & " AND it.sinputtypedescription = 'TOOLS' " '& _



(I copied and pasted from Visual Studio).

The result of the query in SQL is a row stating "INPUTS", the next rows having the input list (I'm doing this for a construction company BTW), and the final stating "INPUTS TOTAL" and the total of course.

The query runs OK in SQL. When displaying it in the datagridview its the problem. Specifically, the problem comes when it gets to the column "stabforminputunit".

Here's the code I use to set the datagridview (Maybe there's my problem):


    Public Function setDataGridView(ByVal dgv As DataGridView, ByVal query As String, ByVal sololectura As Boolean) As DataTable

        Dim objCon As New MySqlConnection(StringConnection())
        Dim objDA As MySqlDataAdapter
        Dim objCmd As New MySqlCommand(query, objCon)
        Dim myBindingSource As New BindingSource
        Dim dsDatos As New DataSet

        Try

            objDA = New MySqlDataAdapter(objCmd)

            objDA.Fill(dsDatos)

            myBindingSource.DataSource = dsDatos.Tables(0).DefaultView

            dgv.DataSource = myBindingSource
            dgv.ReadOnly = sololectura

            'Returning DataTable for Charting sourcing purposes
            Return dsDatos.Tables(0)

        Catch ex As Exception

            Return dsDatos.Tables(0)

        End Try


    End Function



And i just call the function like this:

setDataGridView(dgvUnitaryList, queryUnitaryList, False).



Oh, almost forgot, the database is MySQL. Yes, MySQL, not MSSQL.
The app uses this databases in other screens and it functions perfectly, but this. Maybe it's just a setting I forgot to turn on, or maybe tweaking the function above... Well, you should know more about it.

Thanks in advance, really.

Memo


Can you help me find my mistake? Pleaseee



memozebaduaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

G0ggyCommented:
Looks awful! Start stripping it back and then add lines in until you find the faulty bit of code. Too messy to debug sorry.
0
CodeCruiserCommented:
>Specifically, the problem comes when it gets to the column "stabforminputunit".

How do you know that?
0
memozebaduaAuthor Commented:
Hi there!
Thanks for the responses!
Mmm first let me say I got around the problem (not solved though), and since EE is about solving problems, and points are no problem for me to give them to you, if you feel like it, we can continue this journey, since my solution is not optimal. I will state what I've done today and answer your questions for anyone who comes across this question afterwards.
CodeCruiser, my apologies, I forgot to make visible the first column of my datagridview. Every error starts from there.
My query as you can see above has a UNION statement, and the first query returns a blank as the first column (where the id is), where the second query returns the input id.
Answering G0ggy, my question was posted yesterday night after getting a headache of this. Today with a clear head, i stripped the query to only the second part (where it returns the inputid) and everything works smoothly. It also works smoothly when i only put the first part, the problem comes when i use them together with the UNION. I think it has something to do with the second part query having the first column as an integer (from database) and the first part query as a string (since it does not comes from any table), something there mess up the datagridview and interpret it as an image error (blob or something?).
What I did to go around the problem is to drop and recreate a new "temp" table, having the same columns but every column is a varchar type, so it does not goes checking to convert anything, and the i just query it and display the data correctly.


So, what's next on this question? Well, I just want to know why could this happen... maybe pointing me to a msdn o a webpage so i can understand a little more about this issue. I will grant the points to those who help me with this. (PS: Since I know that maybe this is a difficult thing to put in a google sentence (how can you search for this? :S), if nothing comes up guys, just say that you had no luck and I'll split the points).


Thanks for everything!
Hope I didn't spoil the fun :S




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
G0ggyCommented:
You are correct, union needs to match columns exactly.

Glad you got it sorted.
0
memozebaduaAuthor Commented:
My question seems abandoned now.  * Memo shrugs *  Oh, well... Accepting my own answer...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.