how to handle DBNULL value in grid datasource

I am using a datatable to assign the datasource to a grid.  How do I prevent it from trying to load the value when a field is null?

I am getting: "conversion from type 'DBNull' TO type 'String' is not valid" when it tries to load the gird.
Private Sub LoadGrid()
        Dim squery As String
        Dim cnDb As New OdbcConnection(sConn)
        squery = "select field1, field2, field3 from mytable"
 
        Dim cmd As New OdbcCommand(squery, cnDb)
        Dim dr As OdbcDataReader
            cnDb.Open()
            dr = cmd.ExecuteReader()
 
            Dim dt As New DataTable("mytable")
            dt.Load(dr)
            dr.Close()
 
            grdBoxInfo.DataSource = dt
    End Sub

Open in new window

Delta7428Asked:
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.

Jorge PaulinoIT Pro/DeveloperCommented:
What database you're using ?
0
Muhammad Ousama GhazaliSolution Analyst & ArchitectCommented:
On Grid level you can set message if there are no records using EmptyDataText property. However, for field level, you have to set a value whether "" (by default) for NullDisplayText property of each column. For more see this: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.boundfield.nulldisplaytext.aspx
However, the error you are getting might be due to some compuation within the RowDataBound or some other event where you are trying to convert a value from a null field into string before checking if the value is Null. Make use of IsDBNull(PassDataObjectHere) before converting any field value into string or other types.
0
Delta7428Author Commented:
It is a SQL table.
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Jorge PaulinoIT Pro/DeveloperCommented:
So you should use SqlConnection and not OdbcConnection.
What grid is it ? It is ASP,NET or Winforms ?
0
Delta7428Author Commented:
Connection is irrelevant to error.  I get the same error either way.  It is a dataview grid on a winform.

I am at absolute beginner level here, moving from vb6 to vb.net.  Please give me specific sample code if you can.

I know I need to check for null values somehow.  Moqhazali, thanks for the link.  I'm not sure I know how to make it work.
0
Jorge PaulinoIT Pro/DeveloperCommented:
SqlClient Namespace as allot of optimized classes to work with SQL, like SqlConnection, SqlDataAdapter, SqlCommand, etc.
You can do something like this to fill the grid and it supports null values.

' ADD IMPORTS TO Imports System.Data.SqlClient
 
        Dim myConnectionString As String = _
                        "Data Source=.\SQLEXPRESS;AttachDbFilename='C:\myDatabase.mdf';" & _
                        "Integrated Security=True;User Instance=True"
 
        Dim SQL As String = "select field1, field2, field3 from mytable"
        Using da As New SqlDataAdapter(SQL, myConnectionString)
 
            Using ds As New DataSet
                da.Fill(ds, "myTable")
                Me.DataGridView1.DataSource = ds.Tables("myTable").DefaultView
            End Using
 
        End Using

Open in new window

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
Muhammad Ousama GhazaliSolution Analyst & ArchitectCommented:
Earlier I provided solution which is for Web. Will come back with the solution for you on WinForms soon.
0
srikanthreddyn143Commented:
Make the query this way

select isnull(field1,''),isnull( field2,''), isnull(field3,'') from mytable
0
Jorge PaulinoIT Pro/DeveloperCommented:
Have you tried at least my code ? Datagridview handles null values ...
0
Delta7428Author Commented:
jpaulino, I initially couldn't get yours to work.  I just discovered it was my connection string.  It works fine now using a sql connection instead of odbc.  I think I would prefer not to have to convert every field in all my select statements with isnull(field1,'').

I was using odbc because another programmer in my company, more experienced in .net, recommended it over using sql connection because he said he had a lot of issues using sql that he didn't have with odbc.   But, this is one obstacle I would prefer not to have to deal with.

Thanks.  I'll request the question to be reopened to reassign points.
0
Jorge PaulinoIT Pro/DeveloperCommented:
Ok, SqlClient namespace as allot of advantages and you will see that.
Glad I could help and welcome to .NET world! :)
0
Delta7428Author Commented:
Thanks jpaulino.  The .NET growing pains are about to get the best of me.

But without Experts-Exchange, I'd probaly be jobless and homeless.
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
Visual Basic.NET

From novice to tech pro — start learning today.