I have to use view instead of the following query. But temp table
DTLineInfo is used in the query at where condition. I can create simple query. But I can not use loop like
DTLineInfo.
Please look at where condition of the queries strSQL=""; I am eagerly wait for the answer. please help me
private void LoadManpower()
{
DataTable DTLineInfo = new DataTable();
DataTable DTLineWiseOT = new DataTable();
DataTable DTLineWiseOT1H = new DataTable();
string strLineName = string.Empty;
string strLocation = string.Empty;
for (int i = 0; i < grdviewDailyProduction.Rows.Count; i++)
{
CheckBox chkSelect = (CheckBox)grdviewDailyProduction.Rows[i].Cells[0].FindControl("chkSelect");
if (chkSelect != null)
{
if (chkSelect.Checked)
{
strLineName = grdviewDailyProduction.Rows[i].Cells[2].Text.Trim();
strLocation = grdviewDailyProduction.Rows[i].Cells[6].Text.Trim();
}
}
}
strSQL = "select distinct LineCode, LineName, Designation, DesigCode, Location, DayFlag from tblManpowerCollection where LineName='" + strLineName + "' and Location='" + strLocation + "' and ATTdate='" + dptProductionDate.Text.Trim() + "'";
ConManager.DBConnection("TERMSPDN");
ConManager.OpenDataTableThroughAdapter(strSQL, out DTLineInfo, true);
ConManager.CloseConnection();
DTLineInfo.TableName = "DTLineInfo";
if (DTLineInfo.Rows.Count > 0)
{
for (int j = 0; j < DTLineInfo.Rows.Count; j++)
{
string strSQL1 = "";
if (DTLineInfo.Rows[j]["DayFlag"].ToString().Trim() == "HD")
{
strSQL1 = "select distinct" + _
" (select count(ATTdate) from tblManpowerCollection where ATTDate='" + dptProductionDate.Text.Trim() + "' and lineName='" + DTLineInfo.Rows[j]["LineName"].ToString() + "'and Location='" + DTLineInfo.Rows[j]["Location"].ToString() + "' ) as '1H'," + _
" (convert(Int,(select count(ATTdate) from tblManpowerCollection where ATTDate='" + dptProductionDate.Text.Trim() + "' and TotalOTHour>=9 and lineName='" + DTLineInfo.Rows[j]["LineName"].ToString() + "'and Location='" + DTLineInfo.Rows[j]["Location"].ToString() + "' ))) as '2H'," + _
" (convert(Int,(select count(ATTdate) from dbo.tblManpowerCollection where TotalOTHour >=10 and ATTdate='" + dptProductionDate.Text.Trim() + "' and lineName='" + DTLineInfo.Rows[j]["LineName"].ToString() + "'and Location='" + DTLineInfo.Rows[j]["Location"].ToString() + "'))) as '3H'," + _
" (convert(Int,(select count(ATTdate) from dbo.tblManpowerCollection where TotalOTHour>=11 and ATTdate='" + dptProductionDate.Text.Trim() + "' and lineName='" + DTLineInfo.Rows[j]["LineName"].ToString() + "'and Location='" + DTLineInfo.Rows[j]["Location"].ToString() + "' ))) as '4H'," + _
" (Convert(Int,(select count(ATTdate) from dbo.tblManpowerCollection where TotalOTHour =12 and ATTdate='" + dptProductionDate.Text.Trim() + "'and lineName='" + DTLineInfo.Rows[j]["LineName"].ToString() + "'and Location='" + DTLineInfo.Rows[j]["Location"].ToString() + "' ))) as '5H'," + _
" (convert(INT,(select count(ATTdate) from dbo.tblManpowerCollection where TotalOTHour >12 and ATTdate='" + dptProductionDate.Text.Trim() + "' and lineName='" + DTLineInfo.Rows[j]["LineName"].ToString() + "'and Location='" + DTLineInfo.Rows[j]["Location"].ToString() + "' ))) as '6H'," + _
" ATTdate from tblManpowerCollection where ATTdate= '" + dptProductionDate.Text.Trim() + "'";
}
}
}
}