Specified cast is not valid

[skipper]

Hello all,
reading a database table with the SQL executeTable command and then accessing each single cell with

myVar=Table1.Cell(Table1.ColName(k),0)

I get the "Specified cast is not valid" error message if the cell contains a "null" value deriving from an empty field in the database.

How to avoid this message? Only providing a default value to the field? or is there any other solution?

Many thanks
Mimmo

 

[Erel]

You can use IsNull to first check if the value is null.

If IsNull(Table1.Cell(Table1.ColName(k),0)) Then myVar = 0 else myVar = Table1.Cell(Table1.ColName(k),0)

 

[skipper]

Hello Erel,
thanks for your reply but, I'm sorry, I forgot to mention that I already tried with IsNull and the error is the same. I splitted the IF statement just to be sure about the error line. Running the program, it stops at line with IF

if IsNull(table1.cell(table1.Colname(k),0)) then
..
else
..
end if

and raises the same error.

Mimmo

 

[Erel]

Can you upload a small example (with part of the database)?

 

[skipper]

Hi Erel,
please find attached an excerpt of what I was doing. Please don't care about the logic inside the FOR cycle. It has been semplified just to help in isolating the problem.

The strange thing is that the 3rd fields is also "null" (as you can see from the table component) but does not raise any error.

Many thanks for the time you will dedicate me.
Mimmo

 

[agraham]

The strange thing is that the 3rd fields is also "null" (as you can see from the table component) but does not raise any error.

It does if you optimise compile it "Unable to cast object of type 'System.DBNull to type 'System.String'" but not if you legacy compile or run in the IDE.

 

[skipper]

thanks Agraham,
I didn't notice that because I was testing in the IDE....

 

[Erel]

When you read a value from a table that value must not be null.
You can use the COALESCE keyword which replaces NULLs with a default value.

SqlCommand.CommandText="Select COALESCE(cod_asl,0) FROM T_Certificati where ID_certificato=1"