We help IT Professionals succeed at work.

Delete DBNull columns from DataTable problem in VB.NET

samazary
samazary asked
on
1,199 Views
Last Modified: 2008-03-10
Been working for days on this to correctly delete the DBNull columns consistently...

I have isolated the problem to (I think) the code below which deletes about half of the columns but approx the other half will not be deleted nomatter what there value is (either DBNull or not). I know that all of the columns in the row correctly = "" when they come out of the CheckDBNull() function but only about half are being removed... ita a mystery to me and I would be very very appreciative of absolutely any ideas at all...

Enum enumObjectType
StrType = 0
IntType = 1
DblType = 2
End Enum

Public Function CheckDBNull(ByVal obj As Object, _
Optional ByVal ObjectType As enumObjectType = enumObjectType.StrType) As Object
Dim objReturn As Object
objReturn = obj
If ObjectType = enumObjectType.StrType And IsDBNull(obj) Then
objReturn = "" 
ElseIf ObjectType = enumObjectType.IntType And IsDBNull(obj) Then
objReturn = 0
ElseIf ObjectType = enumObjectType.DblType And IsDBNull(obj) Then
objReturn = 0.0
End If
Return objReturn
End Function

Private Sub RemoveNullColumns(ByVal myDataSet As DataSet)

Try
' Initialise data objects
Dim myDataTable As DataTable = myDataSet.Tables(0)
Dim myDataColumn As DataColumn
Dim myDataRow As DataRow = myDataSet.Tables(0).Rows(0)
' Get the DataColumnCollection from a DataTable in a DataSet.
Dim cols As DataColumnCollection = myDataSet.Tables(0).Columns
' Initialise counter and start value
Dim cCount As Integer = 0

'Declare objects to compare
Dim obj1 As Object
Dim obj2 As Object

' Loop through columns in datarow and delete all null columns from datarow
'While column counter is less than total columns
While cCount < myDataTable.Columns.Count

' If datacolumn at the current counter value is null
obj1 = "" 
obj2 = CheckDBNull(rows.Item(0).Item(cCount))
' Check if null or not
If obj1.ToString.CompareTo(obj2.ToString) = 0 Then

'datacolumn becomes the column at the current counter value
myDataColumn = myDataTable.Columns(cCount)

cols.Remove(myDataColumn)

' increment the counter to next column
cCount = cCount + 1
End While

' DataBind
DataGrid1.DataSource = myDataSet
DataGrid1.DataBind()

Catch ex As Exception
Response.Write(ex.message.ToString)
End Try

End Sub
Comment
Watch Question

Rounded IT Consultant / Projects manager / Cloud Consultant / IT Planning
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I think I love you Silvers5. will you marry me? ha ha mate its probably a reflection on my VB.NET knowledge (i.e. not good) but I've been looking at this for days... now you fixed it up in 5 seconds and I've changed the code and it works BEAUTIFULLY!!! THANKYOU SOOOOOO MUCH

Jon
Michel SakrRounded IT Consultant / Projects manager / Cloud Consultant / IT Planning
CERTIFIED EXPERT

Commented:
hehe.. nice comment .. but sorry..i'm married..glad 2 help anyways.. i got this one by intuition.. try to look from different angles into things..

Author

Commented:
it was much appreciated so well done. I also have a second problem entitled "Dynamically created Datagrid Event Handler problem VB.NET" at the link below which you may like to take a look at but thats up to you... thanks for your help champion...

https://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20881389.html
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.