DBNull Problem

[Behrooz]

I have a datagrid and in mouse-click event of the dataGrid I have the code bellow for sending the value of current cell to a textbox, But the problem is when the cell of the datagrid which I am selecting is filled with null. Note that I have designed two columns in my db as "ID" and "Desc" which the "ID" does not allow NULL.
In this case, How can I make an IF clause for checking if the cell is NULL or not, because I have lost all my tries to do this matter. There Is the DBNull keyword and I can`t handle that even by IsNot or IsNot Nothing or Not or else
This is my code:

Me.txtID.Text = Trim(CStr(Me.myGrid.CurrentRow.Cells(0).Value))

and this the error message:

Conversion from type 'DBNull' to type 'String' is not valid.

 

[Anti-Rich]

try this...

if not mygrid.currownrow.cells(0) is nothing
          Me.txtID.Text = Trim(CStr(Me.myGrid.CurrentRow.Cells(0).Value))
else
          me.txtID.text=""
end if

hope it helps
regards
adam

 

[clweeks]

I'm no expert, but I'd take the problem back further and design the database with no nullable columns. If I didn't have that option, I'd use the COALESCE function in my query to see to it that my grid never got a DBNULL.

 

[Anti-Rich]

how do you use that function 'coalesce'? i have never heard of that function...

cheers
adam

 

[clweeks]

Pass a number of terms seperated by commas into COALESCE() and it will return the first non-null value. The most common thing that I've seen goes like this:

SELECT key, COALESCE(datum1, 0), COALESCE(datum2, 0) FROM table

or

SELECT lName, fName, COALESCE(midIn, "") FROM table

http://msdn2.microsoft.com/en-us/library/ms190349.aspx

 

[Anti-Rich]

ah ok, interesting. but it returns the first non null value, and only the first. so maybe that function might not be a good idea.

im not sure, maybe it does return more than one row. i dont have time to test it.

have a good one

 

[clweeks]

Oh, misunderstanding. It's returning the first non-null value from the list of parameters that you've passed in.

SELECT productName, COALESCE(mostRecentSalePrice, msrp, 2 * purchasePrice, 100) AS bestPrice FROM vintageLPs

will return a result for every record in the table and return some "bestPrice" checking mostRecentSalePrice for a non-null value before checking msrp before checking purchasePrice. If all three fields are null for a given productName, that bestPrice will default to 100.

 

[besmart2000]

If you use datagridview (vs2005) , you can handle the DataGridView.CellFormatting event to display the NullValue property value.
In general , The format event fires before the data is shown, an in this case you can convert a dbnull value to 0 or "" (based on data type) before it is displayed and prevent the error .
For a demo example review:
http://msdn2.microsoft.com/en-us/library/system.windows.forms.datagridview.cellformatting(VS.80).aspx

i hope it is useful.

 

[cjard]

I'm no expert, but I'd take the problem back further and design the database with no nullable columns. If I didn't have that option, I'd use the COALESCE function in my query to see to it that my grid never got a DBNULL.

Nah.. thats too much work, and some things should be null.. Better that you test for null:

[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] Me.myGrid.CurrentRow.Cells(0).Value.Equals(System.DBNull.Value) [/SIZE]
[SIZE=2] ...[/SIZE]
[SIZE=2]Else[/SIZE]
[SIZE=2] ...[/SIZE]
[SIZE=2]EndId[/SIZE]
[SIZE=2]

[/SIZE]
Only thing im not sure on, is whether in "Cells(0).Value", the .Value is needed..

 

[Anti-Rich]

Nah.. thats too much work, and some things should be null.. Better that you test for null:

[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] Me.myGrid.CurrentRow.Cells(0).Value.Equals(System.DBNull.Value) [/SIZE]
[SIZE=2]...[/SIZE]
[SIZE=2]Else[/SIZE]
[SIZE=2]...[/SIZE]
[SIZE=2]EndId[/SIZE]

is this a better way of doing what i suggested a few posts earlier? i know of the system.dbnull class but i would have thought it wasnt necessary for this problem.
feel free to correct
regards
adam

 

[cjard]

System.DBNull is Something, and it cannot be equated or "Is"ed with Nothing. The OP's original post indicated the cell Value was DBNull because when he attempted to CStr it he was told "Conversion from type 'DBNull' to type 'String' is not valid"

He also said he failed to Is it; DBNull is a type, it cannot be used as an expression

Program defensively; equate things with certainty.. We are certain that the Cell Value is the value of whatever DBNull's Value is. Because DBNull.Value is shared, single instance, then either Equals() or ReferenceEquals() should work, but the equals syntax is cleaner.


Actually, you know what? I would write this:

If DBNull.Value.Equals(...Cell.Value)

Because we know for certain that DBNull.Value is never Nothing, so this code cannot throw an Obj Ref Not Set exception, in the same way that:

If "".Equals(stringVariableThatMightBeNOTHING) Then

can never throw Obj Not Set..



Look at what happened in my immediate window when I selected a null cell in one of my apps:

?Me.APPS_PREFSDataGridView.SelectedCells()
{System.Windows.Forms.DataGridViewSelectedCellCollection}
    Count: 1
    IsReadOnly: False
    IsSynchronized: False
    Item: In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.
    SyncRoot: {System.Windows.Forms.DataGridViewSelectedCellCollection}
?Me.APPS_PREFSDataGridView.SelectedCells(0).Value
{System.DBNull}
    System.DBNull: {System.DBNull}
?Me.APPS_PREFSDataGridView.SelectedCells(0).Value Is Nothing
False
[B]?Me.APPS_PREFSDataGridView.SelectedCells(0).Value.Equals(DBNull.Value)
True[/B]
?Me.APPS_PREFSDataGridView.SelectedCells(0).Value Is DBNull
'DBNull' is a type and cannot be used as an expression.