C#

----------------------------------
CountryID CountryName DisplayOrder
----------------------------------
1         US          3
2         UK          2
3         India       1
----------------------------------

SELECT CountryID, CountryName, DisplayOrder FROM TblCountry ORDER BY DisplayOrder ASC;

cmd.Parameters.Add("@p_StartDate", SqlDbType.DateTime).Value = dtpSTART; //get date from date time picker

cmd.Parameters.Add("@p_StartDate", SqlDbType.DateTime).Value = dtpSTART.Value; //get date from date time picker
cmd.Parameters.Add("@p_EndDate", SqlDbType.DateTime).Value = dtpEND.Value;

cmd.Parameters.AddWithValue("@p_StartDate", dtpSTART.Value); //get date from date time picker
cmd.Parameters.AddWithValue("@p_EndDate", dtpEND.Value);

System.Data.SqlServerCe.SqlCeConnection con;
System.Data.SqlServerCe.SqlCeDataAdapter da;
DataSet ds1;

private void btnGetData_Click(object sender, EventArgs e)
{
    connectSQLserver();
    updateDS();
    closeSQLserver();
}

private void connectSQLserver()
{
     string strDataSource = @"Data Source=C:\Visual Studio 2010\Projects\UPNP_Form_F\UPNP_Form_F\bin\Debug\upnp_database.sdf";
     con = new System.Data.SqlServerCe.SqlCeConnection();
     con.ConnectionString = strDataSource;
     con.Open();

     ds1 = new DataSet();

     SqlCeCommand cmd = new SqlCeCommand();
     cmd.CommandText = "SELECT * FROM upnp_daily_energy WHERE Date >= @p_StartDate AND Date <= @p_EndDate";
     cmd.Parameters.AddWithValue("@p_StartDate", dtpSTART.Value); //get date from date time picker
     cmd.Parameters.AddWithValue("@p_EndDate", dtpEND.Value);
     cmd.Connection = con;
     da = new System.Data.SqlServerCe.SqlCeDataAdapter(cmd.CommandText, cmd.Connection);

     try //run the query
     {
     da.Fill(ds1, "upnp_daily"); //run the query
     }
     catch (Exception e2)
     {
     MessageBox.Show("Error 7: " + e2.ToString()); //<<--- Error here!
     }

     MessageBox.Show("Connection Open"); //<<---DEBUG
     MaxRows = ds1.Tables["upnp_daily"].Rows.Count; //get no of rows in DataSet
     MessageBox.Show("No of rows in DataSet = " + MaxRows.ToString());
 }//------------------------------------------------------------------------------------

private void updateDS()
{
    DataRow dRow = ds1.Tables["upnp_daily"].NewRow(); //Step 1: create new row in Dataset

    dRow[1] = dateTimeStmp; //Step 2: Assign value to columns
    dRow[2] = fIrrad;
    dRow[3] = fDPM_A;            dRow[4] = fPR_A;
    dRow[5] = fDPM_B;            dRow[6] = fPR_B;
    dRow[7] = fDPM_C;            dRow[8] = fPR_C;
    dRow[9] = fDPM_D;            dRow[10] = fPR_D;
    dRow[11] = fTotal;           dRow[12] = fPR;
    ds1.Tables["upnp_daily"].Rows.Add(dRow); //Step 3: Add the row

    UpdateDB();

    MaxRows++; //Step 4: increment row no
    inc = MaxRows - 1; //Step 5: set inc to max row no
    MessageBox.Show("Record is saved in Dataset.");
}//------------------------------------------------------------------------------

private void UpdateDB()
{
    System.Data.SqlServerCe.SqlCeCommandBuilder cb;
    cb = new System.Data.SqlServerCe.SqlCeCommandBuilder(da);
    cb.DataAdapter.Update(ds1.Tables["upnp_daily"]);// use DataAdapter of cb
}//----------------------------------------------------------------------

private void closeSQLserver()
{
    con.Close();//. close connection
}