Trying to get the data from database and load it into datatable got exception

I am trying to get the database values and binding to data table by using the following query

sql = @"SELECT member_Id, 30*memberToMship_ChargePerPeriod  / DateDiff(memberToMship_EndDate, 
         memberToMship_StartDate) As monthlyamount,
         PERIOD_DIFF(DATE_FORMAT(now(),'%Y%m'), 
         DATE_FORMAT(memberToMship_StartDate,'%Y%m'))  + (DAY(memberToMship_StartDate) < memberToMship_DueDay)+ (DAY(now()) > memberToMship_DueDay)-1  AS ExpPayments, 
         SUM(memberToMship_InductionFee+memberToMship_JoinFee+
   开发者_开发百科          (IF(mshipOption_Period='year',
             TIMESTAMPDIFF (YEAR ,memberToMship_StartDate, memberToMship_EndDate),
             TIMESTAMPDIFF (MONTH ,memberToMship_StartDate, memberToMship_EndDate)) * memberToMship_ChargePerPeriod)) as value 
        FROM membertomships 
        INNER JOIN mshipoptions on membertomships.mshipOption_Id = mshipoptions.mshipoption_Id";

and this is my code for getting the data to datable from database

string memberid;
double value = 0.0;
double expectedpayment=0.0;
double monthlypayamount=0.0;
int dueday = 0;

dt = xxxxxx.GetData(sql, mf);    
if (dt != null && dt.Rows.Count > 0)
{
      memberid = Convert.ToInt32(dt.Rows[0]["member_Id"]).ToString();
      monthlypayamount = Convert.ToDouble(dt.Rows[1]["monthlyamount"]);
      expectedpayment = Convert.ToDouble(dt.Rows[2]["ExpPayments"]);
      value = Convert.ToDouble(dt.Rows[3]["value"]);       
}

but I am getting an error

" index out of range exception"

and error like this

"there is no row at position 1"

Would any one please help on this...

You should use index [0] for Rows on every line of code if you want to get all the data from the first row that was returned. If you use Rows[1] and Rows[2] etc., then you are looking at the second row, and third row, etc., which is invalid if your query only returned one row of data.

You're checking for

dt.Rows.Count > 0

And then accessing rows 0, 1, 2, and 3, without checking if all of these rows exist. I think you've confused row numbers with columns, and should try

  memberid = Convert.ToInt32(dt.Rows[0]["member_Id"]).ToString();
  monthlypayamount = Convert.ToDouble(dt.Rows[0]["monthlyamount"]);
  expectedpayment = Convert.ToDouble(dt.Rows[0]["ExpPayments"]);
  value = Convert.ToDouble(dt.Rows[0]["value"]);      

if (dt != null && dt.Rows.Count > 0)
{

  memberid = Convert.ToInt32(dt.Rows[0]["member_Id"]).ToString();

  monthlypayamount = Convert.ToDouble(dt.Rows[1]["monthlyamount"]);
  expectedpayment = Convert.ToDouble(dt.Rows[2]["ExpPayments"]);
  value = Convert.ToDouble(dt.Rows[3]["value"]);       
}

Your if statement is only checking for the existence of 1 or more rows. However, when you set monthlypayamount you are assuming a second row (dt.Rows[1]["monthlyamount"]). I think you need to use a foreach loop to iterate the results

foreach(DataRow row in dt.Rows)
{
   memberid = Convert.ToInt32(row["member_Id"]).ToString();
   monthlypayamount = Convert.ToDouble(row["monthlyamount"]);
   expectedpayment = Convert.ToDouble(row["ExpPayments"]);
   value = Convert.ToDouble(row["value"]);

   //logic here to use variable values before moving to next row
}

This will make sure you get access to all rows

It seems that you are trying to access row 1 however, row 1 doesn't exist. So, it seems that your query is returning only one row that is row 0. Try

   if (dt != null && dt.Rows.Count > 0)     
{        
memberid = Convert.ToInt32(dt.Rows[0]["member_Id"]).ToString();        
monthlypayamount = Convert.ToDouble(dt.Rows[0]["monthlyamount"]);       
expectedpayment = Convert.ToDouble(dt.Rows[0]["ExpPayments"]);       
value = Convert.ToDouble(dt.Rows[0]["value"]);            
}