0

This is my first time using c# and sql server to code a simple monthly reporting application which will print the report to paper. Everything just fine but there is a complicated problem for me when comparing two value.

Suppose there are 14 columns(originally 37 columns) and 12 rows for each month. The last column i am using for month name.

Columns are:
Name, col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,monthName.
Logically there will be 12 reports in a year.

col2+col5+col6
and
col7+col10+col11 sum of current month's
must be same as last month col4 and col9-12 rows

This is my main problem!

For example if monthName has January(as past month) and I am filling the current month February . There is a pushButton called "Verify".
If I click on verify then the February's 3 columns sum will be compared to January's 1 column sum as above.

I have tried some sql query like select * from table1 where sum(col4)=sum(col2+col5+col6) and sum(col9)=sum(col7+col10+col11) But my logic and knowledge is completely flawed since it did not work as expected.

Secondly, I thought appending data from sql query results to List<> and comparing the col4/col9 would be an idea but again flawed as there is no gurantee that Name column's value would not be shuffled which will lead to compare to wrong data, also I am not confident at it.

I need help from c# and sql database expert to get an idea to do this as I am not sure what is my options!

Update: I am doing some experiment in visual studio. All codes are just ugly but i will upload it on dropbox if someone care to try it.

user2715275
  • 35
  • 1
  • 8
  • Have u considering styling the cells for example with a red color conditionally insted of having a verify button? – Andrei Dvoynos Mar 27 '14 at 19:14
  • I tried to edit your question into shape. Can you add example data from that table you describe and what ouput you expect? – rene Mar 27 '14 at 19:16
  • You need to do some reading on table design with particular emphasis on first, second and third normal forms. Relational table normalization. It's MUCH MUCH simpler than the name implies. The table design you describe is insane. – Metaphor Mar 27 '14 at 19:41
  • @AndreiD, Yes highlighting the mismatch value or simply messagebox. – user2715275 Mar 29 '14 at 09:42
  • @rene, I don't have an example to start as i am just starting! – user2715275 Mar 29 '14 at 09:47
  • @Metaphor, I know, my logic and the design is f**kng flawed! – user2715275 Mar 29 '14 at 09:47

1 Answers1

0

I think your best solution here is to keep the business logic and the database query separate. If the user clicks the "verify" button on the "February" row, you just submit a database query along the lines of SELECT * FROM table1 WHERE monthName = 'january' although:

  • Probably you should actually use a date field to identify your months).
  • How you actually retrieve the data will depend on what type of data access you are using (ADO.NET, Entity Framework, etc.)

Once you get back that result, you just use straightforward C# logic to do the verification, along the lines of:

int priorMonthSum1 = priorMonthResultset.col4;
int priorMonthSum2 = priorMonthResultset.col9 + priorMonthResultset.col10 + priorMonthResultset.col11 + priorMonthResultset.col12;
int currentMonthSum1 = currentMonthResultset.col2 + currentMonthResultset.col5 + currentMonthResultset.col6;
int currentMonthSum2 = currentMonthResultset.col7 + currentMonthResultset.col10 + currentMonthResultset.col1;

if(priorMonthSum1 != currentMonthSum1 || priorMonthSum2 != currentMonthSum2) 
{
    // handle mismatch (highlight red?)
}
else
{
    // handle match (highlight green?)
}
Robert N
  • 1,156
  • 2
  • 14
  • 32
  • If i understood correctly: 1. placing a button in column(such as in the last column) for each row and the current row compare the value when user click on it? Oh this is sometime boring using ADO.NET as i need to open the connection for each query(ADO.NET). Your example seems helpful but can you kindly explain in details please? – user2715275 Mar 29 '14 at 10:46
  • I would be happy to answer in more detail, but it would help if you could edit your question to include the relevant sections of your own code. That would give me something to work from. Modifying your code to show you what I mean is likely to be a lot quicker for me and more helpful for you than if I have to make up a code sample completely from scratch. I saw you referenced uploading to dropbox... is there a link or something? – Robert N Apr 01 '14 at 21:40
  • The problem i have solved somehow. If you don't mind to look at my another post please: http://stackoverflow.com/questions/23227892/c-reportviewerrdlc-page-break-on-wrong-page-and-genrate-unnecessary-pages – user2715275 Apr 22 '14 at 18:59