0

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
     }
Community
  • 1
  • 1
Nabbic
  • 419
  • 3
  • 22
  • You are returning 2 columns named Col5? – Ralf Sep 20 '13 at 13:19
  • Is this a sql question or Linq-To-DataSet question? So can you change the sql or do you want to get it in memory on an existing DataTable? That are completely different approaches and questions. – Tim Schmelter Sep 20 '13 at 13:23
  • @Ralf Hmm no should only be 1 Col5. Col5 is like the Totals column that represents the sum of the non-null columns prior to 5. – Nabbic Sep 20 '13 at 13:23
  • @TimSchmelter I initially wanted to pull it from memory but failed assuming due to my inexperience with queries so i decided to try it from and SQL point of view first. But yes doing it from memory would be best. – Nabbic Sep 20 '13 at 13:26
  • 1
    Could you show us your code? The method and the config file would help a lot. – Danexxtone Sep 20 '13 at 13:36
  • The method is the one generated by Visual Studio via the "Add Query" dialogs which is supposed (if i am understanding correctly) to run the above query and then change `Col5` with the proper values. I then am simply trying to get it to display those totals in a textbox. `var totals = from p in dataTable select p.Col5 foreach (var total` `in totals) {testBox.Text = total;} ` – Nabbic Sep 20 '13 at 13:47
  • Did you create a stored procedure for the 'count null' select query? Your code seems correct for getting the value you're looking for (except that your textbox will only display the last value) – Danexxtone Sep 20 '13 at 16:57
  • @Danexxtone Nope its not a Sproc and yeah just trying to get a value period in my textbox right now i am just getting a DBNull exception at run-time. If i break as it goes into the foreach loop it calls DBNull when it checks Col5 for values. It successfully calls the GetTotals() Method which is what runs that query. – Nabbic Sep 20 '13 at 17:07
  • @Danexxtone As i mentioned above i can run that query to the SQL table myself and it works but it seems to be like "read-only" once i close out of the Query Editor and show the data in the table its gone. It's as if the above query is not actually writing that data to Col 5 – Nabbic Sep 20 '13 at 17:09
  • @Nabbic If you do a Preview Data, it shows up? Can you select any of the other columns and print them (Col4 instead of Col5)? – Danexxtone Sep 20 '13 at 17:59
  • @Danexxtone Yes if i do a Preview Data it shows up as expected with all the values in Col5 BUT it does not actually save those values to Col5 which is what i am looking for. If after executing the query or previewing data i look at the table data Col5 is null. I am looking for something that will write the values to Col5. And yes i have successful printed from several of the other columns in the same way just different SQL commands. – Nabbic Sep 20 '13 at 18:09

3 Answers3

1

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.

Danexxtone
  • 773
  • 4
  • 11
0

You have Column 5 in the select list twice. Once as a named column, once as a computed column. Did you make that typo creating this post, or is it in your code that way?

BitWise
  • 78
  • 6
0

Because there is no case (condition) for Col5. You have col1,col2,col3,col4 and no col5 you just asign col4 name as col5, which is a bad idea in this case.

Dariss
  • 1,258
  • 1
  • 12
  • 27