Using foreach for insert gridview data into sql database

Question

I have a client side gridview data and I want to insert gridview data to sql database . at first I import data from excel to gridview and now I want to insert it into sql database .

I use foreach loop to insert records one by one . but foreach loop just select first record and I can't increase row index . how can I do this? and select other records ?

protected void btnInsertIntoDatabase_Click(object sender, EventArgs e)
    {
    A:
        string Name = string.Empty;
        string CarType = string.Empty;
        string TechnicalNo = string.Empty;
        string ProductionDate = string.Empty;
        string EngaineType = string.Empty;
        string NoInStock = string.Empty;
        string NoForCar = string.Empty;
        string Price = string.Empty;
        string Image = string.Empty;
        string Desc = string.Empty;
        string PartType = string.Empty;
        string Level = string.Empty;
        string Unit = string.Empty;
        string Ratio = string.Empty;
        string Dirham = string.Empty;
        string ExtraMoney = string.Empty;

        int GVCount = GridView1.Rows.Count;

        foreach (GridViewRow GVRow in GridView1.Rows)
        {
            Name = GVRow.Cells[1].Text;
            CarType = GVRow.Cells[2].Text;
            TechnicalNo = GVRow.Cells[3].Text;
            ProductionDate = GVRow.Cells[4].Text;
            EngaineType = GVRow.Cells[5].Text;
            NoInStock = GVRow.Cells[6].Text;
            NoForCar = GVRow.Cells[7].Text;
            Price = GVRow.Cells[8].Text;
            Image = GVRow.Cells[9].Text;
            Desc = GVRow.Cells[10].Text;
            PartType = GVRow.Cells[11].Text;
            Level = GVRow.Cells[12].Text;
            Unit = GVRow.Cells[13].Text;
            Ratio = GVRow.Cells[14].Text;
            Dirham = GVRow.Cells[15].Text;
            ExtraMoney = GVRow.Cells[16].Text;
            break;
        }

        SqlConnection scn = new SqlConnection(clspublic.GetConnectionString());
        SqlCommand scm = new SqlCommand();
        scm.Connection = scn;
        scm.CommandText = @"INSERT INTO tblProduct
                          (fName, fxCarType, fProductionDate, fEngineType, fNoinStock, fNoforCar, fPrice,fRatio,fDirham,fExtraMoney, fImage, fDesc, fxPartType, fxLevel,fUnitType,fTechnicalNo)
               VALUES     (@fName,@fxCarType,@fProductionDate,@fEngineType,@fNoinStock,@fNoforCar,@fPrice,@fRatio,@fDirham,@fExtraMoney,@fImage,@fDesc,@fxPartType,@fxLevel,@fUnitType,@fTechnicalNo)";

        scm.Parameters.AddWithValue("@fName", Name.ToString());
        scm.Parameters.AddWithValue("@fxCarType", CarType.ToString());
        scm.Parameters.AddWithValue("@fTechnicalNo", TechnicalNo.ToString());
        scm.Parameters.AddWithValue("@fProductionDate", ProductionDate.ToString());
        scm.Parameters.AddWithValue("@fEngineType", EngaineType.ToString());
        scm.Parameters.AddWithValue("@fNoinStock", NoInStock.ToString());
        scm.Parameters.AddWithValue("@fNoforCar", NoForCar.ToString());
        scm.Parameters.AddWithValue("@fPrice", Price.ToString());
        scm.Parameters.AddWithValue("@fRatio", Ratio.ToString());
        scm.Parameters.AddWithValue("@fDirham", Dirham.ToString());
        scm.Parameters.AddWithValue("@fExtraMoney", ExtraMoney.ToString());
        scm.Parameters.AddWithValue("@fImage", Image.ToString());
        scm.Parameters.AddWithValue("@fDesc", Desc.ToString());
        scm.Parameters.AddWithValue("@fxPartType", PartType.ToString());
        scm.Parameters.AddWithValue("@fUnitType", Unit.ToString());
        scm.Parameters.AddWithValue("@fxLevel", Level.ToString());

        goto A;
    }

becaues of the break you code is not working check my code that i have pasted for you....if you use break like this you will get the first record only every time — Pranay Rana
– Pranay Rana, Commented May 31, 2011 at 9:58

Pranay Rana · Accepted Answer · 2011-05-31 09:53:53Z

It's taking first record only because there is break written in you foreach loop...

following is code for you

    foreach (GridViewRow GVRow in GridView1.Rows)
    {
        Name = GVRow.Cells[1].Text;
        CarType = GVRow.Cells[2].Text;
        TechnicalNo = GVRow.Cells[3].Text;
        ProductionDate = GVRow.Cells[4].Text;
        EngaineType = GVRow.Cells[5].Text;
        NoInStock = GVRow.Cells[6].Text;
        NoForCar = GVRow.Cells[7].Text;
        Price = GVRow.Cells[8].Text;
        Image = GVRow.Cells[9].Text;
        Desc = GVRow.Cells[10].Text;
        PartType = GVRow.Cells[11].Text;
        Level = GVRow.Cells[12].Text;
        Unit = GVRow.Cells[13].Text;
        Ratio = GVRow.Cells[14].Text;
        Dirham = GVRow.Cells[15].Text;
        ExtraMoney = GVRow.Cells[16].Text;


    SqlConnection scn = new SqlConnection(clspublic.GetConnectionString());
 using(con)
 {  
    SqlCommand scm = new SqlCommand();
    scm.Connection = scn;
    scm.CommandText = @"INSERT INTO tblProduct
                      (fName, fxCarType, fProductionDate, fEngineType, fNoinStock, fNoforCar, fPrice,fRatio,fDirham,fExtraMoney, fImage, fDesc, fxPartType, fxLevel,fUnitType,fTechnicalNo)
           VALUES     (@fName,@fxCarType,@fProductionDate,@fEngineType,@fNoinStock,@fNoforCar,@fPrice,@fRatio,@fDirham,@fExtraMoney,@fImage,@fDesc,@fxPartType,@fxLevel,@fUnitType,@fTechnicalNo)";

    scm.Parameters.AddWithValue("@fName", Name.ToString());
    scm.Parameters.AddWithValue("@fxCarType", CarType.ToString());
    scm.Parameters.AddWithValue("@fTechnicalNo", TechnicalNo.ToString());
    scm.Parameters.AddWithValue("@fProductionDate", ProductionDate.ToString());
    scm.Parameters.AddWithValue("@fEngineType", EngaineType.ToString());
    scm.Parameters.AddWithValue("@fNoinStock", NoInStock.ToString());
    scm.Parameters.AddWithValue("@fNoforCar", NoForCar.ToString());
    scm.Parameters.AddWithValue("@fPrice", Price.ToString());
    scm.Parameters.AddWithValue("@fRatio", Ratio.ToString());
    scm.Parameters.AddWithValue("@fDirham", Dirham.ToString());
    scm.Parameters.AddWithValue("@fExtraMoney", ExtraMoney.ToString());
    scm.Parameters.AddWithValue("@fImage", Image.ToString());
    scm.Parameters.AddWithValue("@fDesc", Desc.ToString());
    scm.Parameters.AddWithValue("@fxPartType", PartType.ToString());
    scm.Parameters.AddWithValue("@fUnitType", Unit.ToString());
    scm.Parameters.AddWithValue("@fxLevel", Level.ToString());

   scm.ExecuteNonQuery();
 }

}

Yes I break because I have more than 50 record and then my variables change .. I want register first record and then second record and ....

Pleun · Accepted Answer · 2011-05-31 09:54:13Z

0

Wow, a Goto....

Well, you break your foreach after the first record. And after that you start your foreach again from the first one one....

answered May 31, 2011 at 9:54

Pleun

8,9102 gold badges32 silver badges51 bronze badges

Comments

danyolgiax · Accepted Answer · 2011-05-31 09:54:34Z

 using (SqlConnection scn = new SqlConnection(clspublic.GetConnectionString()))
        {

            foreach (GridViewRow GVRow in GridView1.Rows)
            {
                Name = GVRow.Cells[1].Text;
                CarType = GVRow.Cells[2].Text;
                TechnicalNo = GVRow.Cells[3].Text;
                ProductionDate = GVRow.Cells[4].Text;
                EngaineType = GVRow.Cells[5].Text;
                NoInStock = GVRow.Cells[6].Text;
                NoForCar = GVRow.Cells[7].Text;
                Price = GVRow.Cells[8].Text;
                Image = GVRow.Cells[9].Text;
                Desc = GVRow.Cells[10].Text;
                PartType = GVRow.Cells[11].Text;
                Level = GVRow.Cells[12].Text;
                Unit = GVRow.Cells[13].Text;
                Ratio = GVRow.Cells[14].Text;
                Dirham = GVRow.Cells[15].Text;
                ExtraMoney = GVRow.Cells[16].Text;

                using (SqlCommand scm = scn.CreateCommand())
                {

                    scm.CommandText = @"INSERT INTO tblProduct
                  (fName, fxCarType, fProductionDate, fEngineType, fNoinStock, fNoforCar, fPrice,fRatio,fDirham,fExtraMoney, fImage, fDesc, fxPartType, fxLevel,fUnitType,fTechnicalNo)
       VALUES     (@fName,@fxCarType,@fProductionDate,@fEngineType,@fNoinStock,@fNoforCar,@fPrice,@fRatio,@fDirham,@fExtraMoney,@fImage,@fDesc,@fxPartType,@fxLevel,@fUnitType,@fTechnicalNo)";

                    scm.Parameters.AddWithValue("@fName", Name.ToString());
                    scm.Parameters.AddWithValue("@fxCarType", CarType.ToString());
                    scm.Parameters.AddWithValue("@fTechnicalNo", TechnicalNo.ToString());
                    scm.Parameters.AddWithValue("@fProductionDate", ProductionDate.ToString());
                    scm.Parameters.AddWithValue("@fEngineType", EngaineType.ToString());
                    scm.Parameters.AddWithValue("@fNoinStock", NoInStock.ToString());
                    scm.Parameters.AddWithValue("@fNoforCar", NoForCar.ToString());
                    scm.Parameters.AddWithValue("@fPrice", Price.ToString());
                    scm.Parameters.AddWithValue("@fRatio", Ratio.ToString());
                    scm.Parameters.AddWithValue("@fDirham", Dirham.ToString());
                    scm.Parameters.AddWithValue("@fExtraMoney", ExtraMoney.ToString());
                    scm.Parameters.AddWithValue("@fImage", Image.ToString());
                    scm.Parameters.AddWithValue("@fDesc", Desc.ToString());
                    scm.Parameters.AddWithValue("@fxPartType", PartType.ToString());
                    scm.Parameters.AddWithValue("@fUnitType", Unit.ToString());
                    scm.Parameters.AddWithValue("@fxLevel", Level.ToString());

                    scm.ExecuteNonQuery();
                }
            }
        }

Mahesh KP · Accepted Answer · 2011-05-31 10:06:32Z

0

Try like this ,

If all the controls are label

foreach (GridViewRow GVRow in GridView1.Rows)
    {

  Label lbl = (Label)GVRow.FindControl("labelID");

  string data=lbl.Text;

}

answered May 31, 2011 at 10:06

Mahesh KP

6,47613 gold badges54 silver badges71 bronze badges

Comments

KatieK · Accepted Answer · 2012-12-18 05:43:04Z

using (SqlConnection scn = new SqlConnection(clspublic.GetConnectionString()))
    {

        foreach (GridViewRow GVRow in GridView1.Rows)
        {
            Name = GVRow.Cells[1].Text;
            CarType = GVRow.Cells[2].Text;
            TechnicalNo = GVRow.Cells[3].Text;
            ProductionDate = GVRow.Cells[4].Text;
            EngaineType = GVRow.Cells[5].Text;
            NoInStock = GVRow.Cells[6].Text;
            NoForCar = GVRow.Cells[7].Text;
            Price = GVRow.Cells[8].Text;
            Image = GVRow.Cells[9].Text;
            Desc = GVRow.Cells[10].Text;
            PartType = GVRow.Cells[11].Text;
            Level = GVRow.Cells[12].Text;
            Unit = GVRow.Cells[13].Text;
            Ratio = GVRow.Cells[14].Text;
            Dirham = GVRow.Cells[15].Text;
            ExtraMoney = GVRow.Cells[16].Text;

            using (SqlCommand scm = scn.CreateCommand())
            {

                scm.CommandText = @"INSERT INTO tblProduct
              (fName, fxCarType, fProductionDate, fEngineType, fNoinStock, fNoforCar, fPrice,fRatio,fDirham,fExtraMoney, fImage, fDesc, fxPartType, fxLevel,fUnitType,fTechnicalNo)
   VALUES     (@fName,@fxCarType,@fProductionDate,@fEngineType,@fNoinStock,@fNoforCar,@fPrice,@fRatio,@fDirham,@fExtraMoney,@fImage,@fDesc,@fxPartType,@fxLevel,@fUnitType,@fTechnicalNo)";

                scm.Parameters.AddWithValue("@fName", Name.ToString());
                scm.Parameters.AddWithValue("@fxCarType", CarType.ToString());
                scm.Parameters.AddWithValue("@fTechnicalNo", TechnicalNo.ToString());
                scm.Parameters.AddWithValue("@fProductionDate", ProductionDate.ToString());
                scm.Parameters.AddWithValue("@fEngineType", EngaineType.ToString());
                scm.Parameters.AddWithValue("@fNoinStock", NoInStock.ToString());
                scm.Parameters.AddWithValue("@fNoforCar", NoForCar.ToString());
                scm.Parameters.AddWithValue("@fPrice", Price.ToString());
                scm.Parameters.AddWithValue("@fRatio", Ratio.ToString());
                scm.Parameters.AddWithValue("@fDirham", Dirham.ToString());
                scm.Parameters.AddWithValue("@fExtraMoney", ExtraMoney.ToString());
                scm.Parameters.AddWithValue("@fImage", Image.ToString());
                scm.Parameters.AddWithValue("@fDesc", Desc.ToString());
                scm.Parameters.AddWithValue("@fxPartType", PartType.ToString());
                scm.Parameters.AddWithValue("@fUnitType", Unit.ToString());
                scm.Parameters.AddWithValue("@fxLevel", Level.ToString());

                scm.ExecuteNonQuery();
            }
        }
    }

The question has been answered more than one year ago. Why did you post nearly the same code again, without any further explanation?

Collectives™ on Stack Overflow

Using foreach for insert gridview data into sql database

5 Answers 5

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related