Count the number of columns 'is not zero' for each row and place this information in another column

advertisements

I am new to SQL and have started a kind of test project to get my hands dirty working with SQL and C#. I have been using VS2012's database wizards to set everything up. So here's the rundown of my setup.

SQL CE table named ACDTable and it is connected to my C# project by a dataset dataSet1 and a table adapter dataTableAdapter

ACDTable has 22 columns and what I am trying to do is for each row that has a value (is not null) for each column I specify it adds one to a counter and then adds the sum to another column I specify.

Essentially I need to find how many columns are not null for each row and turn that number into something usable in C#.

Example:

 |Col1 | Col2 | Col3| Col4| Col5|
 |     |      |     |     |     |
 |  x  |   x  |     |  x  |  3  |
 |     |   x  |  x  |     |  2  |
 |  x  |      |     |     |  1  |

I am using the example Count of non-null columns in each row and it does work great if I just run it as a query via SQL.

The example I am using:

SELECT   Col1,
         Col2,
         Col3,
         Col4,

     CASE WHEN Col1 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN  Col2 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN  Col3 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN  Col4 IS NOT NULL THEN 1 ELSE 0 END AS Col5
 FROM     ACDTable

The problem I am running into is when I use Visual Studio to create a method based on this Query and I try to get the values of Col5 via that method I am getting a NullDB exception. If I run the debugger and look at the table after I have called the method the Col5 is completely null. I have a feeling the problem is the query itself is not actually changing the 5th column but my inexperience with SQL is showing and I cannot figure out the correct syntax to fix this.

Fixed typo where I had Col5 in the SELECT list

Edit to show the code I am using

The method itself is the one generated by Visual Studio using the Add Query option in the Table Adapter. It is if I understand correctly supposed to run the above query and then put the values into Col5 for me. I then just want to to see those values represented in a richtextbox using

GetTotals(); //The Generated method from VS that runs my query
var totals = from p in dataTable select p.Col5;
   foreach (var total in totals)
     {
       testTextBox.Text = total
     }


If I understood your comments correctly, then I see where the problem is. At this time you're only selecting values from your table, not writing any values into it. One way you could do this is by creating an UPDATE query and setting the value for Col5 to the case statements.

UPDATE ACDTable
SET Col5 = CASE WHEN....

I think you know how to finish this. If you have to, you can still add a WHERE clause as well at the end.