CODEWITHSUNDEEP
c#asp.netforeachdatatabledataset
user123456789
user123456789

Reputation: 2004

Foreach loop only updating first row

I am trying to create an insert statement for certain records in the database. So the DataTablecould return 100 rows but in the foreach loop it is running the insert 100 times into the first row. I want the insert to run for each row in the DataTable.

string status = @"select j.*, " +
                 " (SELECT Status FROM jobstat WHERE jobstat.JobID = j.ID ORDER BY Sequence DESC LIMIT 0,1)  AS CurrentStatusCode " +
                 " from job_new j " +
                 " left join jobstat js on js.jobid=j.id " +
                 " where j.delivermemberid=j.jobownerid and j.jobdate= '2016-05-20' " +
                 " and j.isactive=1 and j.IsOnManifest = 1 " +
                 " Group by j.ID " +
                 " HAVING  CurrentStatusCode NOT IN ('DEL' )";

DataTable dtStatus = BaseDisplaySet.CustomFill(status, param);
DataSet dsStat = new DataSet();
dsStat.Tables.Add(dtStatus);

if (dsStat.Tables[0].Rows.Count > 0)
{
    foreach (DataRow dr in dtStatus.Rows)
    {
        string InsertStatusSQL = @"INSERT INTO jobstat (JobID,CompanyID,Status,Descrip,Sequence,CompCode,StatusID,StatusDate,Comment1,Comment2, CreateMethod) 
                VALUES (" + int.Parse(dtStatus.Rows[0]["ID"].ToString()) + ", 171 , '" + dtStatusfield.Rows[0]["StatusCode"].ToString() + "' , '" + dtStatusfield.Rows[0]["Text"].ToString() + "' , '" + dtStatusfield.Rows[0]["ListOrder"].ToString() +
        "' ,0 , '" + int.Parse(dtStatusfield.Rows[0]["ID"].ToString()) + "' , '" + DateTime.Now + "' ,'WebService', '' , 'WebService')";

        BaseDisplaySet.CustomNonQuery(InsertStatusSQL, param);
    }
}

I also tried foreach (DataRow dr in dsStat.Tables[0].Rows) but that didn't work either. It still only updated the first row.

Upvotes: 1

Views: 1116

Answers (2)

Beldi Anouar
Beldi Anouar

Reputation: 2180

Use your variable dr instead of dtStatus.Rows[0] like : 

 foreach (DataRow dr in dtStatus.Rows)
     {
         string InsertStatusSQL = @"INSERT INTO jobstat (JobID,CompanyID,Status,Descrip,Sequence,CompCode,StatusID,StatusDate,Comment1,Comment2, CreateMethod) 
         VALUES (" + int.Parse(dr["ID"].ToString()) + ", 171 , '" + dr ["StatusCode"].ToString() + "' , '" + dr["Text"].ToString() + "' , '" + dr ["ListOrder"].ToString() +
           "' ,0 , '" + int.Parse(dr["ID"].ToString()) + "' , '" + DateTime.Now + "' ,'WebService', '' , 'WebService')";

          BaseDisplaySet.CustomNonQuery(InsertStatusSQL, param);
     }

Upvotes: 1

Vladimir
Vladimir

Reputation: 1390

It looks like you should use dataRow instead of dtStatus.Rows[0]. You simply not using foreach loop. 

            foreach (DataRow dataRow in dtStatus.Rows)
            {
                string InsertStatusSQL = @"INSERT INTO jobstat (JobID,CompanyID,Status,Descrip,Sequence,CompCode,StatusID,StatusDate,Comment1,Comment2, CreateMethod) 
                 VALUES (" + int.Parse(dataRow["ID"].ToString()) + ", 171 , '" + dataRow["StatusCode"].ToString() + "' , '" + dataRow["Text"].ToString() + "' , '" + dataRow["ListOrder"].ToString() +
                "' ,0 , '" + int.Parse(dataRow["ID"].ToString()) + "' , '" + DateTime.Now + "' ,'WebService', '' , 'WebService')";

                BaseDisplaySet.CustomNonQuery(InsertStatusSQL, param);
            }

Upvotes: 1

Related Questions