looping between the colums on of every row each cells

Question

Dear I have table in sql server like this studentid name marks1 marks2 marks3 marks4 marks5 marks6 marks7 Total Sub Need_Percentage of Marks 1 Sanjeev 78 87 76 78 95 5 ? 2 Sandeep 78 87 76 78 95 59 6 ? 3 Sumit 78 78 95 52 4 ? 4 Sudhir 78 85 0 78 95 52 6 ? 5 Ram AB 85 23 85 12 5 ? i want the percentage of total marks obtained by the students when i am fetching the data using sqldatareader it shows me the dbnull error because it is not getting the value kindly tell the solution any one.. how to loop between the coloums here blank cell means not having value........... i cannot assign any cell value 0 because it also come in marks it take the null then there is conversion issue so kindly tell me the solution as soon as possible......... Regards Sanjeev

Sanjeev Kumar Chaudhary · Answer

Dear Rahul Thank you for your replay but there is also one issue that if i will set the value -1 then it give effect at the time adding the all the marks taken by particular student basicly i want to write a programe which will not have any effect on parent table and after creating when i search by some unique id from the table it will give as it is data with Total % of marks for each student and the rest data should be same is there any way to loop between marks1 from marks 7 and when the value get null value of emty value or ab it will assume it like 0 without effecting table I have write a query like this if(dr[marks1]==null || dr[marks1]==){dr[marks1]=Convert.TOSingle(0); } but showing error of conversion or there is any way that if the dr[marks] found any char type data like AB it should skip that value...

rahul ahuja · Answer

if this exam not having -ve marking then put -1 value for avoid these error. But instead of looping why don't you do this at sql server by creating store procedure it will increase performance.

studentid	name	marks1	marks2	marks3	marks4	marks5	marks6	marks7	Total Sub	Need_Percentage of Marks
1	Sanjeev	78	87	76	78	95			5	?
2	Sandeep	78	87	76	78	95	59		6	?
3	Sumit	78			78	95	52		4	?
4	Sudhir	78	85	0	78	95	52		6	?
5	Ram	AB	85	23	85	12			5	?