[RESOLVED] Replace Missing DataTable Values

[zuhalterr]

Hi - I am having a problem trying to replace the missing values in my Dataset tables with a default string. The columns in my datatables should be all strings.

Here's the code I am using, but I am getting the DBNull to string conversion error.


For i = 0 To MyDs.Tables(j).Columns.Count - 1
For k = 0 To MyTable.Rows.Count - 1
IIf(IsDBNull(Trim(MyDs.Tables(j).Rows(k)(i).Value)), "None", Trim(MyDs.Tables(j).Rows(k)(i).Value))
Next
Next

[HanneSThEGreaT]

You can't replace the DBNULL value to a string, because it is actually a special value. I'd recommend keeping it as it is. Much easier to test for IsDBNull values.

Can I just add. Why are there an allowance for Null values then in the first place ¿ What I mean is, why is The fielkd in the Database set up to Allow Null values, if you don't want them ¿
You could've easily modified the empty values in the database, by making use of a Default value for the field.

If that was done, this problem above, wouldn't have existed :)

I hope my post was useful. :)

[zuhalterr]

Thanks for your reply Hannes.

The reason why I am filling up the missing values is because my data source is originally an Excel document and that document, before importing its data, is unknown to my application user to how many columns each sheet has or how many sheets the workbook has.

The code is dynamic to import any workbook and populate its sheets, each in an instantly created datatable in the dataset. I posted the dynamic import mechanism of the whole Excel workbook in another thread here :)

The data in my datatables in the dataset cannot have empty strings or null values due it's usage down the line in the application.

Anyway, I figured out the nested loop issue and below is how I wrote it, and it works fine:


For i = 0 To MyDs.Tables(j).Columns.Count - 1
For k = 0 To MyDs.Tables(j).Rows.Count - 1
If MyDs.Tables(j).Rows(k)(i).ToString().Trim = "" Then
MyDs.Tables(j).Rows(k)(i) = "None"
End If
Next
Next

[HanneSThEGreaT]

Ah, Good, then it makes sense. Thanx for sharing your great solution with us! Well done. :thumb:

If you feel your issue has been solved, remember to mark this thread As Resolved, by clicking on the Thread Tools menu and selecting Mark Thread Resolved. :)