dcsimg
Results 1 to 6 of 6

Thread: [RESOLVED] Don't (Throw Exception) on NullValue - your help is URGENTLY requested!

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Resolved [RESOLVED] Don't (Throw Exception) on NullValue - your help is URGENTLY requested!

    I'm having this problem but I don't understand why.

    I am running a stored procedure from my C# code called xsp_ProjectList. It gets a list of projects our employees are working on. Then there are filters so you can trim down the results to who the project administrator is, who the tech lead is, what the status of the project is, etc. The data is displayed in a grid.

    The stored procedure runs fine and returns the expected results.

    Then when the user starts filtering, there's an error saying "The value for column 'x' in table 'xsp_ProjectList' is DBNull.

    Why was it allowed the first time, but not after the filter? Is it how I am trying to apply the filters? I am building up a string to pass to the datatable's Select() method, like so:
    Code:
            private void PopulateProjectList()
            {
                using (dsProjectsTableAdapters.xsp_ProjectListTableAdapter taProjectList = new dsProjectsTableAdapters.xsp_ProjectListTableAdapter())
                {
                    using (dsProjects.xsp_ProjectListDataTable dtProjectList = new dsProjects.xsp_ProjectListDataTable())
                    {
                        taProjectList.Fill(dtProjectList);
    
                        StringBuilder sbFilter = new StringBuilder();
    
                        if ((string)lkupPA.EditValue != "ALL")
                            sbFilter.AppendFormat("EmpAssignedPA = '{0}' OR ", lkupPA.EditValue);
    
                        if ((string)lkupTechLead.EditValue != "ALL")
                            sbFilter.AppendFormat("EmpReviewTechnicalLead = '{0}' OR ", lkupTechLead.EditValue);
    
                        if ((string)lkupST.EditValue != "ALL")
                            sbFilter.AppendFormat("EmpSystemTechnician = '{0}' OR ", lkupST.EditValue);
    
                        if ((string)lkupSE.EditValue != "ALL")
                            sbFilter.AppendFormat("EmpSystemEngineer = '{0}' OR ", lkupSE.EditValue);
    
                        if ((string)lkupProjectStatus.EditValue != "ALL")
                        {
                            if (sbFilter.Length > 0)
                            {
                                sbFilter.Insert(0, String.Format("ProjectStatusName = '{0}' AND (", lkupProjectStatus.EditValue));
                                sbFilter.Append(")");
                            }
                            else
                            {
                                sbFilter.AppendFormat("ProjectStatusName = '{0}'", lkupProjectStatus.EditValue);
                            }
                        }
    
                        string sFilter = "";
                        if (sbFilter.Length > 0)
                        {
                            sbFilter.Replace("OR )", ")");
    
                            int idx = sbFilter.ToString().LastIndexOf("OR");
                            if (idx >= 0 && idx == sbFilter.Length - 3)
                                sFilter = sbFilter.ToString().Substring(0, idx - 1);
                            else
                                sFilter = sbFilter.ToString();
                        }
    
                        if (ckProjectsPrevailingWage.Checked)
                        {
                            if (sFilter != "")
                                sFilter = String.Format("bPrevailingWage = 1 AND ({0})", sFilter);
                            else
                                //sFilter = ("bPrevailingWage = 1");
                                sFilter = ("bPrevailingWage = true");
                        }
    
                        if (sFilter != "")
                        { 
                            string sSort = "";
                            DataRow[] filteredRows = dtProjectList.Select(sFilter, sSort);
                            gcProjectList.DataSource = filteredRows;
                        }
                        else
                           gcProjectList.DataSource = dtProjectList;
                    }
                }
    
            }
    I used to have nice checkboxes for my boolean columns, but since I had to change their datatype to Object (per the StackOverflow link) the columns display as True/False/blank text when the grid is initially loaded. Then, when I say show me only projects that are prevailing wage, it shows me the right results but the prevailing wage column is blank instead of True.

    So I'd like to back up and think about this instead of implementing my knee-jerk reaction. If the sproc's results are fine and cause no exceptions why do the rows I am filtering cause exceptions? It's the same data, only less of it!!!

    Thanks. Marked urgent because I have my boss and other users emailing me reporting this error...
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,150

    Re: Don't (Throw Exception) on NullValue - your help is URGENTLY requested!

    I don't have direct experience with this, but I suspect it can be solved by expanding the conditions a bit.

    Instead of this:
    Code:
    sbFilter.AppendFormat("EmpAssignedPA = '{0}' OR ", lkupPA.EditValue);
    try this:
    Code:
    sbFilter.AppendFormat("(EmpAssignedPA Is Not Null AND EmpAssignedPA = '{0}') OR ", lkupPA.EditValue);

  3. #3
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,322

    Re: Don't (Throw Exception) on NullValue - your help is URGENTLY requested!

    Maybe you can just not pass nulls from the SPROC. In MS SQL it would be:

    Select isnull(EmpAssignedPA,'') 'EmpAssignedPA'
    Please remember next time...elections matter!

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Re: Don't (Throw Exception) on NullValue - your help is URGENTLY requested!

    I know what you are saying, but I am not sure it will help. Here is my scenario right now. I chose PA with an empID of 80. So right here, I am on the bolded line exeptioning
    Code:
                        if (sFilter != "")
                        { 
                            string sSort = "";
                            DataRow[] filteredRows = dtProjectList.Select(sFilter, sSort);
                            gcProjectList.DataSource = filteredRows;
                        }
                        else
                           gcProjectList.DataSource = dtProjectList;
    The value of my filter is...
    ? sFilter
    "EmpAssignedPA = '80'"
    My exception is
    An unhandled exception of type 'System.Data.StrongTypingException' occurred in CmsDotNet.exe
    Additional information: The value for column 'ProjectDueDate' in table 'xsp_ProjectList' is DBNull.
    ProjectDueDate isn't even in my filter...
    Clearly I am missing something key to this whole thing...
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Re: Don't (Throw Exception) on NullValue - your help is URGENTLY requested!

    I figured out what I could do. (The hard part was explaining to the company VP, who was being my sounding board, why this worked and the other way didn't!)

    Since the code liked the full datatable I was binding to before any filtering, but didn't like when I binded to an array of datarows, I decided to create a table from my datarows, like this:

    Code:
                        if (sFilter != "")
                        { 
                            string sSort = "";
                            DataRow[] filteredRows = dtProjectList.Select(sFilter, sSort);
                            // gcProjectList.DataSource = filteredRows;
                            DataTable dtFiltered = filteredRows.CopyToDataTable();
                            gcProjectList.DataSource = dtFiltered;
                        }
                        else
                           gcProjectList.DataSource = dtProjectList;
    There seems to be something inherent in the table that says allowing the nulls is ok.

    And, because this is important and I didn't say anything about the stored procedure, I am doing a LEFT JOIN to our projects table. So of course I am expecting these nulls and want to allow them. When I don't have a row, null is what the columns will be.

    Thanks for listening and of course if anyone can say why the array didn't work but the table, which was the exact same data, did work, I'd like to understand it better!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Re: Don't (Throw Exception) on NullValue - your help is URGENTLY requested!

    To TysonLPrice, I apologize. I didn't even see your post. However, I did not want to change the sproc. I didn't believe that was the way to go, since the full dataset was fine yet the filtered dataset wasn't. But thank you for your input!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width