DataSet/DataTable cell loads as DBNull

[JimOfRose]

Hello all,
First, if I may, a comment. This is my first post. I have been a member of another forum and found that users are treated with little respect... assumptions are made, stupid questions are not allowed patience, etc.

I work for a large IT corporation (if I told you the name you'd all know it) and I'm a completely self taught web programmer. I've made it to my position through the blessings of God and a lot of sweat and hard work. So when I come onto a forum looking for an answer, I've already done my home work.

So, I'm looking for a forum that will treat people kindly, without that competitive pride that can so easily creep it's way into the programmer's world. This site is recommended on the web, while, interestingly enough, the one I belong(ed) to was not even mentioned. So, I'm hoping to find the community here that I'm looking for.

Here's my issue:

I am defining a DataSet and adding a DataTable to it and filling the DataTable with values from a spreadsheet. The cells on the xls(x) can be empty.

I am finding that the DataTable, when loaded with the spreadsheet values, will replace the empty cells with DBNull. The issue is that those values with DBNull will sometimes need to be changed to a String value. When I attempt to change to String, of course, I get an error.

How can I set up my table so that it will accept String values where the spreadsheet has had empty cells (now DBNull).

Here's some code:

    OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [CRPRList$]", XLSConnect);
    DataSet ds = new DataSet();
    //somewhere near here (I think) I need to set a configuration so that the xls(x) data is loaded correctly and will not replace cells with DBNull... I could be wrong though
    adapter.Fill(ds);
    DataTable thisTable = ds.Tables[0];
    thisTable.Columns.Add("Summary");

    for (int rowNum = 0; rowNum < thisTable.Rows.Count; rowNum++) {
        DataRow row = thisTable.Rows[rowNum];
        string pr = (row["PR"] == System.DBNull.Value) ? null : row["PR"].ToString();
        string cr = (row["CR"] == System.DBNull.Value) ? null : row["CR"].ToString();
        try {
            if ((pr != "" && pr != null) && (cr == null || cr == "")) {
                cr = this.searchForCRNumber(pr.ToString());
                row["CR"] = cr; // here is where I get the error
                row["PR"] = pr;
            }
            row["Summary"] = this.searchForSummary(cr);
        } catch (Exception ex) {
            throw new Exception("PR = " + pr + " and typeof = " + pr.GetType().ToString() + " && CR = " + cr + " and typeof = " + cr.GetType().ToString() + "\nCell typeof = " + row["CR"].GetType().ToString());
            //with this try/catch set up I get an error value of "PR = [my xsl value] and typeof = System.String && CR = [string value from method] and typeof = System.String
              Cell typeof = System.DBNull"

        }
    }
    thisTable.AcceptChanges();
    this.showResults(thisTable);

Thank you, in advance, for the help and I look forward to building a community here!

[LastMitch]

How can I set up my table so that it will accept String values where the spreadsheet has had empty cells (now DBNull).

Spreadsheets? Where is the code for that?

Right now base on your code have you thought about using xml?

Something like:

http://support.microsoft.com/kb/315906

I notice you have a query fetching the data from the database.

Using XML is a string value.

You can read more about here:

https://www.simple-talk.com/sql/database-administration/converting-string-data-to-xml-and-xml-to-string-data/

If you do that I think the code you provide will work.

[JimOfRose]

Spreadsheets? Where is the code for that? Right now base on your code have you thought about using xml? Something like: http://support.microsoft.com/kb/315906 I notice you have a query fetching the data from the database. Using XML is a string value. You can read more about here: https://www.simple-talk.com/sql/database-administration/converting-string-data-to-xml-and-xml-to-string-data/ If you do that I think the code you provide will work.

nope... by the time you get to this function the xls is loaded up and working. It is exactly as I've stated, the empty cells from the xls(x) that come up as empty are loaded in the data table as DBNull

Yes, I've considered XML. And it won't work, which is why I'm using Xsl

so, I'm sorry, What you've suggested won't help :)

[LastMitch]

It is exactly as I've stated, the empty cells from the xls(x) that come up as empty are loaded in the data table as DBNull

OK read this:

http://bytebeats.com/2011/06/28/check-dbnull-value-for-datatable-column-for-vb-net/

You know if someone from here

http://stackoverflow.com/questions/15507675/change-a-datarow-item-from-dbnull-to-a-string-value

mention something and you don't accept and you come on Daniweb doing this over again.

What are you trying to accomplished?

I work for a large IT corporation (if I told you the name you'd all know it) and I'm a completely self taught web programmer. I've made it to my position through the blessings of God and a lot of sweat and hard work. So when I come onto a forum looking for an answer, I've already done my home work.

You don't really necessary to tell people who you work for. By you saying something like that it make members look away from your thread.

[JimOfRose]

Thank you for the advice. I didn't think it would turn people off from viewing my thread.

Uhg, I'm too old for this stuff. I've got great social graces IRL, but on the web? Whole other story.

Yeah, that's my thread over there. I was hoping for help over here since the site was rated higher.

All along I've had a feeling that I'm setting up my DataSet or DataTable wrong. You've no idea how much I've combed the web looking for the resolution. So far all I know is that I've setting up the upload and parsing of the spreadsheet correctly. But I still feel like I'm missing something.

So, truly, thank you for your help. I'll take a look at the link you gave me.

[JimOfRose]

Oh and btw, I didn't accept the answer over there, because it wasn't the correct answer... so the only thing I'm trying to accomplish is functional code.

[LastMitch]

All along I've had a feeling that I'm setting up my DataSet or DataTable wrong. You've no idea how much I've combed the web looking for the resolution. So far all I know is that I've setting up the upload and parsing of the spreadsheet correctly. But I still feel like I'm missing something.

Read this (it involve with a query which you already have):

http://forums.asp.net/t/1621164.aspx

Oh and btw, I didn't accept the answer over there, because it wasn't the correct answer... so the only thing I'm trying to accomplish is functional code.

You have to understand you are getting PAY to do your job not coming on Daniweb or any forum telling people to give you the answer and do the work. There NO free lunch!

I don't care what company you work for and all the other crap.

There no excuse how you present yourself like this (here) and over the other forum.

[JimOfRose]

Wow, this is amazing. Hello to you too. The only thing I've ever asked is if there's something I'm doing wrong, is there something I'm missing. Never have I asked for someone to do my work for me. This is an area of asp.net that I do not understand and asked if someone could help me understand what is going on. And you are flaming me for things I have not done.

Thank you for the welcome... nearly senior poster. I know how these forums work and they have value. I had hoped that by coming to Daniweb I'd find someone else who could look at this code that I have put together, and written and find what I've done wrong. Other than commiting an apparently HUGE sin of presenting the same question on two sites and confessing where I work, I've done nothing wrong. Are not these sites set up for professional as well as programming enthusiasts? I qualify on both counts, so sorry.

Thank you for the kindness you've shown. You make it sound like I go trolling the web looking for someone to do my work. Nice. Now I will consider whether I should just simply switch to parsing a csv file and forget trying to use MS Excel as a data loader... And whether or not Daniweb is worth it.

[BMXDad]

On the databind event, have you tried checking for and converting any null values to an empty string?