• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1253
  • Last Modified:

gridview bulk insert

how do i convert the following to do a bulk insert from a gridview to sql?

protected void loadData()
        {
            //insert data from a gridview: gvConverted

            SQLQryString = "insert into dbo.PL ([FY],[planType],[DS],[Account],[subAccount],[Date],[Description],[Amount]) values ";
            SQLQryString += "(@FY,@planType,@DS,@Account,@subAccount,@Date,@Description,@Amount)";

            SQLCmd = new SqlCommand(SQLQryString);

            using (SQLCon = new SqlConnection(SQLConnectionString))
            {
                SQLCmd.Connection = SQLCon;

                SQLCmd.Parameters.Add(new SqlParameter("@FY", SqlDbType.VarChar, 20));
                SQLCmd.Parameters.Add(new SqlParameter("@planType", SqlDbType.VarChar, 30));
                SQLCmd.Parameters.Add(new SqlParameter("@DS", SqlDbType.VarChar, 60));
                SQLCmd.Parameters.Add(new SqlParameter("@Account", SqlDbType.VarChar, 20));
                SQLCmd.Parameters.Add(new SqlParameter("@subAccount", SqlDbType.VarChar, 20));
                SQLCmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime));
                SQLCmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 60));
                SQLCmd.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Decimal));

                //convert date of string to date
                //Convert.ToDateTime(Date.Text);

                //convert amount of string to decimal
                //Convert.ToDecimal(Amount.Text);

//in sql table
//rowid is auto
//insert date is getdate()

                try
                {
                    SQLCmd.CommandTimeout = 0;

                    SQLCon.Open();
                    SQLCmd.ExecuteNonQuery();

                    lblMessage.Visible = true;
                    lblMessage.Text = "The data was loaded successfully.";
                }
                catch (Exception ex)
                {
                    lblMessage.Visible = true;
                    lblMessage.Text = ex.Message.ToString();
                }
            }
        }

Open in new window

0
fwstealer
Asked:
fwstealer
  • 6
  • 6
1 Solution
 
Rajar AhmedConsultantCommented:
0
 
fwstealerAuthor Commented:
what about the following? I haven't tried it as there are two values not seen due to being in the for loop

protected void loadData()
        {
            StringBuilder query = new StringBuilder();

            for (int i = 0; i < gvConverted.Rows.Count; i++)
            {
                GridViewRow row = gvConverted.Rows[i];

                string value1 = ((Label)row.Cells[0].FindControl("FY")).Text.Replace("'", "''");
                string value2 = ((Label)row.Cells[1].FindControl("Plan")).Text.Replace("'", "''");
                string value3 = ((Label)row.Cells[2].FindControl("DS")).Text.Replace("'", "''");
                string value4 = ((Label)row.Cells[3].FindControl("GLAccount")).Text.Replace("'", "''");
                string value5 = ((Label)row.Cells[4].FindControl("SubAccount")).Text.Replace("'", "''");
                string value6 = ((Label)row.Cells[5].FindControl("Date")).Text.Replace("'", "''");
                string value7 = ((Label)row.Cells[6].FindControl("Description")).Text.Replace("'", "''");
                string value8 = ((Label)row.Cells[7].FindControl("Amount")).Text.Replace("'", "''");

                query.Append("insert dbo.PL ([FY],[planType],[DS],[Account],[subAccount],[Date],[Description],[Amount]) values ")
                    .Append("@FY,@planType,@DS,@Account,@subAccount,@Date,@Description,@Amount");
            }

            SQLCmd = new SqlCommand(query.ToString());
            using (SQLCon = new SqlConnection(SQLConnectionString))
            {
                SQLCmd.Connection = SQLCon;

                SQLCmd.Parameters.Add(new SqlParameter("@FY", SqlDbType.VarChar, 20));
                SQLCmd.Parameters.Add(new SqlParameter("@planType", SqlDbType.VarChar, 30));
                SQLCmd.Parameters.Add(new SqlParameter("@DS", SqlDbType.VarChar, 60));
                SQLCmd.Parameters.Add(new SqlParameter("@Account", SqlDbType.VarChar, 20));
                SQLCmd.Parameters.Add(new SqlParameter("@subAccount", SqlDbType.VarChar, 20));
                SQLCmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime));
                SQLCmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 60));
                SQLCmd.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Decimal));

                SQLCmd.Parameters["@Date"].Value = Convert.ToDateTime(value6); //doesn't see value6
                SQLCmd.Parameters["@Amount"].Value = Convert.ToDecimal(value8); //doesn't see value8

                try
                {
                    SQLCon.Open();
                    SQLCmd.ExecuteNonQuery();

                    lblMessage.Visible = true;
                    lblMessage.Text = "The data was modified successfully.";
                }
                catch (Exception ex)
                {
                    lblMessage.Visible = true;
                    lblMessage.Text = ex.Message.ToString();
                }
            }
        }

Open in new window

0
 
Rajar AhmedConsultantCommented:
check this,
protected void loadData()
        {
            StringBuilder query = new StringBuilder();
        GridViewRow row;
        SqlCommand SQLCmd;
        string value1 = "", value2 = "", value3 = "", value4 = "", value5 = "", value6 = "", value7 = "", value8 = "";
            for (int i = 0; i < gvConverted.Rows.Count; i++)
            {
                 row = gvConverted.Rows[i];
                value1 = ((Label)row.Cells[0].FindControl("FY")).Text.Replace("'", "''");
                value2 = ((Label)row.Cells[1].FindControl("Plan")).Text.Replace("'", "''");
                value3 = ((Label)row.Cells[2].FindControl("DS")).Text.Replace("'", "''");
                value4 = ((Label)row.Cells[3].FindControl("GLAccount")).Text.Replace("'", "''");
                value5 = ((Label)row.Cells[4].FindControl("SubAccount")).Text.Replace("'", "''");
                value6 = ((Label)row.Cells[5].FindControl("Date")).Text.Replace("'", "''");
                value7 = ((Label)row.Cells[6].FindControl("Description")).Text.Replace("'", "''");
                value8 = ((Label)row.Cells[7].FindControl("Amount")).Text.Replace("'", "''");

                query.Append("insert dbo.PL ([FY],[planType],[DS],[Account],[subAccount],[Date],[Description],[Amount]) values ")
                    .Append("@FY,@planType,@DS,@Account,@subAccount,@Date,@Description,@Amount");
            

                SQLCmd = new SqlCommand(query.ToString());
                using (SQLCon = new SqlConnection(SQLConnectionString))
                {
                    SQLCmd.Connection = SQLCon;

                    SQLCmd.Parameters.Add(new SqlParameter("@FY", SqlDbType.VarChar, 20));
                    SQLCmd.Parameters.Add(new SqlParameter("@planType", SqlDbType.VarChar, 30));
                    SQLCmd.Parameters.Add(new SqlParameter("@DS", SqlDbType.VarChar, 60));
                    SQLCmd.Parameters.Add(new SqlParameter("@Account", SqlDbType.VarChar, 20));
                    SQLCmd.Parameters.Add(new SqlParameter("@subAccount", SqlDbType.VarChar, 20));
                    SQLCmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime));
                    SQLCmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 60));
                    SQLCmd.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Decimal));

                    SQLCmd.Parameters["@Date"].Value = Convert.ToDateTime(value6); //doesn't see value6
                    SQLCmd.Parameters["@Amount"].Value = Convert.ToDecimal(value8); //doesn't see value8

                    try
                    {
                        SQLCon.Open();
                        SQLCmd.ExecuteNonQuery();

                        lblMessage.Visible = true;
                        lblMessage.Text = "The data was modified successfully.";
                    }
                    catch (Exception ex)
                    {
                        lblMessage.Visible = true;
                        lblMessage.Text = ex.Message.ToString();
                    }
                }
             }  
    }

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
fwstealerAuthor Commented:
with that code i posted it tried and got:

value1 = ((Label)row.Cells[0].FindControl("FY")).Text.Replace("'", "''");

Open in new window


//Object reference not set to an instance of an object.
0
 
fwstealerAuthor Commented:
same with your code - //Object reference not set to an instance of an object.

think that is due to the values not being labels - thought that in the grid they would be rendered as labels.
0
 
Rajar AhmedConsultantCommented:
Try this , You need to have same  control id of label control on your gridview .
and also put query.append on top of the for loop.
protected void loadData()
        {
            StringBuilder query = new StringBuilder();
        GridViewRow row;
        SqlCommand SQLCmd;
        string value1 = "", value2 = "", value3 = "", value4 = "", value5 = "", value6 = "", value7 = "", value8 = "";
                query.Append("insert dbo.PL ([FY],[planType],[DS],[Account],[subAccount],[Date],[Description],[Amount]) values ")
                    .Append("@FY,@planType,@DS,@Account,@subAccount,@Date,@Description,@Amount");
            for (int i = 0; i < gvConverted.Rows.Count; i++)
            {
                 row = gvConverted.Rows[i];
                value1 = ((Label)row.FindControl("FY")).Text.Replace("'", "''");
                value2 = ((Label)row.FindControl("Plan")).Text.Replace("'", "''");
                value3 = ((Label)row.FindControl("DS")).Text.Replace("'", "''");
                value4 = ((Label)row.FindControl("GLAccount")).Text.Replace("'", "''");
                value5 = ((Label)row.FindControl("SubAccount")).Text.Replace("'", "''");
                value6 = ((Label)row.FindControl("Date")).Text.Replace("'", "''");
                value7 = ((Label)row.FindControl("Description")).Text.Replace("'", "''");
                value8 = ((Label)row.FindControl("Amount")).Text.Replace("'", "''");


              /*  query.Append("insert dbo.PL ([FY],[planType],[DS],[Account],[subAccount],[Date],[Description],[Amount]) values ")
                    .Append("@FY,@planType,@DS,@Account,@subAccount,@Date,@Description,@Amount");*/
            

                SQLCmd = new SqlCommand(query.ToString());
                using (SQLCon = new SqlConnection(SQLConnectionString))
                {
                    SQLCmd.Connection = SQLCon;

                    SQLCmd.Parameters.Add(new SqlParameter("@FY", SqlDbType.VarChar, 20));
                    SQLCmd.Parameters.Add(new SqlParameter("@planType", SqlDbType.VarChar, 30));
                    SQLCmd.Parameters.Add(new SqlParameter("@DS", SqlDbType.VarChar, 60));
                    SQLCmd.Parameters.Add(new SqlParameter("@Account", SqlDbType.VarChar, 20));
                    SQLCmd.Parameters.Add(new SqlParameter("@subAccount", SqlDbType.VarChar, 20));
                    SQLCmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime));
                    SQLCmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 60));
                    SQLCmd.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Decimal));

                    SQLCmd.Parameters["@Date"].Value = Convert.ToDateTime(value6); //doesn't see value6
                    SQLCmd.Parameters["@Amount"].Value = Convert.ToDecimal(value8); //doesn't see value8

                    try
                    {
                        SQLCon.Open();
                        SQLCmd.ExecuteNonQuery();

                        lblMessage.Visible = true;
                        lblMessage.Text = "The data was modified successfully.";
                    }
                    catch (Exception ex)
                    {
                        lblMessage.Visible = true;
                        lblMessage.Text = ex.Message.ToString();
                    }
                }
             }  
    }

Open in new window

0
 
fwstealerAuthor Commented:
i changed the code but get error: Incorrect syntax near '@FY'.

protected void loadData()
        {
            StringBuilder query = new StringBuilder();
            GridViewRow row;
            SqlCommand SQLCmd;
            string value1 = "", value2 = "", value3 = "", value4 = "", value5 = "", value6 = "", value7 = "", value8 = "";
            for (int i = 0; i < gvConverted.Rows.Count; i++)
            {
                row = gvConverted.Rows[i];

                value1 = row.Cells[0].Text; //FY
                value2 = row.Cells[1].Text; //Plan
                value3 = row.Cells[2].Text; //DS
                value4 = row.Cells[3].Text; //GLAccount
                value5 = row.Cells[4].Text; //SubAccount
                value6 = row.Cells[5].Text; //Date
                value7 = row.Cells[6].Text; //Description
                value8 = row.Cells[7].Text; //Amount

                //value1 = ((Label)row.Cells[0].FindControl("FY")).Text.Replace("'", "''");
                //value2 = ((Label)row.Cells[1].FindControl("Plan")).Text.Replace("'", "''");
                //value3 = ((Label)row.Cells[2].FindControl("DS")).Text.Replace("'", "''");
                //value4 = ((Label)row.Cells[3].FindControl("GLAccount")).Text.Replace("'", "''");
                //value5 = ((Label)row.Cells[4].FindControl("SubAccount")).Text.Replace("'", "''");
                //value6 = ((Label)row.Cells[5].FindControl("Date")).Text.Replace("'", "''");
                //value7 = ((Label)row.Cells[6].FindControl("Description")).Text.Replace("'", "''");
                //value8 = ((Label)row.Cells[7].FindControl("Amount")).Text.Replace("'", "''");

                query.Append("insert dbo.PL ([FY],[planType],[DS],[Account],[subAccount],[Date],[Description],[Amount]) values ")
                    .Append("@FY,@planType,@DS,@Account,@subAccount,@Date,@Description,@Amount"); // fails at @FY --  Incorrect syntax near '@FY'.

                SQLCmd = new SqlCommand(query.ToString());
                using (SQLCon = new SqlConnection(SQLConnectionString))
                {
                    SQLCmd.Connection = SQLCon;

                    SQLCmd.Parameters.Add(new SqlParameter("@FY", SqlDbType.VarChar, 20));
                    SQLCmd.Parameters.Add(new SqlParameter("@planType", SqlDbType.VarChar, 30));
                    SQLCmd.Parameters.Add(new SqlParameter("@DS", SqlDbType.VarChar, 60));
                    SQLCmd.Parameters.Add(new SqlParameter("@Account", SqlDbType.VarChar, 20));
                    SQLCmd.Parameters.Add(new SqlParameter("@subAccount", SqlDbType.VarChar, 20));
                    SQLCmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime));
                    SQLCmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 60));
                    SQLCmd.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Decimal));

                    SQLCmd.Parameters["@Date"].Value = Convert.ToDateTime(value6);
                    SQLCmd.Parameters["@Amount"].Value = Convert.ToDecimal(value8);

                    try
                    {
                        SQLCon.Open();
                        SQLCmd.ExecuteNonQuery();

                        lblMessage.Visible = true;
                        lblMessage.Text = "The data was modified successfully.";
                    }
                    catch (Exception ex)
                    {
                        lblMessage.Visible = true;
                        lblMessage.Text = ex.Message.ToString();
                    }
                }
            }
        }

Open in new window

0
 
Rajar AhmedConsultantCommented:
If its only databound column  on your gridview , then simply change like below.

value1 = row.Cells[0].Text;
value2 = row.Cells[1].Text;
value3 = row.Cells[2].Text;
value4 = row.Cells[3].Text;
value5 = row.Cells[4].Text;
value6 = row.Cells[5].Text;
value7 = row.Cells[6].Text;
value8 = row.Cells[7].Text;

Open in new window

0
 
fwstealerAuthor Commented:
error with the following due to:  Object reference not set to an instance of an object.
protected void loadData()
        {
            StringBuilder query = new StringBuilder();
            GridViewRow row;
            SqlCommand SQLCmd;
            string value1 = "", value2 = "", value3 = "", value4 = "", value5 = "", value6 = "", value7 = "", value8 = "";
            query.Append("insert dbo.PL ([FY],[planType],[DS],[Account],[subAccount],[Date],[Description],[Amount]) values ")
                .Append("(@FY,@planType,@DS,@Account,@subAccount,@Date,@Description,@Amount)");
            for (int i = 0; i < gvConverted.Rows.Count; i++)
            {
                row = gvConverted.Rows[i];
                value1 = ((Label)row.FindControl("FY")).Text.Replace("'", "''"); // errors here -- Object reference not set to an instance of an object.
                value2 = ((Label)row.FindControl("Plan")).Text.Replace("'", "''");
                value3 = ((Label)row.FindControl("DS")).Text.Replace("'", "''");
                value4 = ((Label)row.FindControl("GLAccount")).Text.Replace("'", "''");
                value5 = ((Label)row.FindControl("SubAccount")).Text.Replace("'", "''");
                value6 = ((Label)row.FindControl("Date")).Text.Replace("'", "''");
                value7 = ((Label)row.FindControl("Description")).Text.Replace("'", "''");
                value8 = ((Label)row.FindControl("Amount")).Text.Replace("'", "''");

                SQLCmd = new SqlCommand(query.ToString());
                using (SQLCon = new SqlConnection(SQLConnectionString))
                {
                    SQLCmd.Connection = SQLCon;

                    SQLCmd.Parameters.Add(new SqlParameter("@FY", SqlDbType.VarChar, 20));
                    SQLCmd.Parameters.Add(new SqlParameter("@planType", SqlDbType.VarChar, 30));
                    SQLCmd.Parameters.Add(new SqlParameter("@DS", SqlDbType.VarChar, 60));
                    SQLCmd.Parameters.Add(new SqlParameter("@Account", SqlDbType.VarChar, 20));
                    SQLCmd.Parameters.Add(new SqlParameter("@subAccount", SqlDbType.VarChar, 20));
                    SQLCmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime));
                    SQLCmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 60));
                    SQLCmd.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Decimal));

                    SQLCmd.Parameters["@Date"].Value = Convert.ToDateTime(value6); 
                    SQLCmd.Parameters["@Amount"].Value = Convert.ToDecimal(value8); 

                    try
                    {
                        SQLCon.Open();
                        SQLCmd.ExecuteNonQuery();

                        lblMessage.Visible = true;
                        lblMessage.Text = "The data was modified successfully.";
                    }
                    catch (Exception ex)
                    {
                        lblMessage.Visible = true;
                        lblMessage.Text = ex.Message.ToString();
                    }
                }
            }
        }

Open in new window

0
 
Rajar AhmedConsultantCommented:
your missing open & close bracket for values
query.Append("insert dbo.PL ([FY],[planType],[DS],[Account],[subAccount],[Date],[Description],[Amount]) values ")
                    .Append("(@FY,@planType,@DS,@Account,@subAccount,@Date,@Description,@Amount)");

Open in new window

0
 
fwstealerAuthor Commented:
okay did that but got error: The parameterized query '(@FY varchar(20),@planType varchar(30),@DS varchar(60),@Account ' expects the parameter '@FY', which was not supplied.

protected void loadData()
        {
            StringBuilder query = new StringBuilder();
            GridViewRow row;
            SqlCommand SQLCmd;
            string value1 = "", value2 = "", value3 = "", value4 = "", value5 = "", value6 = "", value7 = "", value8 = "";
            query.Append("insert dbo.PL ([FY],[planType],[DS],[Account],[subAccount],[Date],[Description],[Amount]) values ")
                .Append("(@FY,@planType,@DS,@Account,@subAccount,@Date,@Description,@Amount)");
            for (int i = 0; i < gvConverted.Rows.Count; i++)
            {
                row = gvConverted.Rows[i];
                value1 = row.Cells[0].Text;
                value2 = row.Cells[1].Text;
                value3 = row.Cells[2].Text;
                value4 = row.Cells[3].Text;
                value5 = row.Cells[4].Text;
                value6 = row.Cells[5].Text;
                value7 = row.Cells[6].Text;
                value8 = row.Cells[7].Text;
                                            
                SQLCmd = new SqlCommand(query.ToString());
                using (SQLCon = new SqlConnection(SQLConnectionString))
                {
                    SQLCmd.Connection = SQLCon;

                    SQLCmd.Parameters.Add(new SqlParameter("@FY", SqlDbType.VarChar, 20));
                    SQLCmd.Parameters.Add(new SqlParameter("@planType", SqlDbType.VarChar, 30));
                    SQLCmd.Parameters.Add(new SqlParameter("@DS", SqlDbType.VarChar, 60));
                    SQLCmd.Parameters.Add(new SqlParameter("@Account", SqlDbType.VarChar, 20));
                    SQLCmd.Parameters.Add(new SqlParameter("@subAccount", SqlDbType.VarChar, 20));
                    SQLCmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime));
                    SQLCmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 60));
                    SQLCmd.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Decimal));

                    SQLCmd.Parameters["@Date"].Value = Convert.ToDateTime(value6); 
                    SQLCmd.Parameters["@Amount"].Value = Convert.ToDecimal(value8); 

                    try
                    {
                        SQLCon.Open();
                        SQLCmd.ExecuteNonQuery();

                        lblMessage.Visible = true;
                        lblMessage.Text = "The data was modified successfully.";
                    }
                    catch (Exception ex)
                    {
                        lblMessage.Visible = true;
                        lblMessage.Text = ex.Message.ToString();
                    }
                }
            }
        }

Open in new window

0
 
Rajar AhmedConsultantCommented:
SQLCmd.Parameters.Add(new SqlParameter("@FY", SqlDbType.VarChar, 20)).Value = Convert.ToString(value1);
                SQLCmd.Parameters.Add(new SqlParameter("@planType", SqlDbType.VarChar, 30)).Value = Convert.ToString(value2);
                SQLCmd.Parameters.Add(new SqlParameter("@DS", SqlDbType.VarChar, 60)).Value = Convert.ToString(value3);
                SQLCmd.Parameters.Add(new SqlParameter("@Account", SqlDbType.VarChar, 20)).Value = Convert.ToString(value4);
                SQLCmd.Parameters.Add(new SqlParameter("@subAccount", SqlDbType.VarChar, 20)).Value = Convert.ToString(value5);
                SQLCmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime)).Value = Convert.ToDateTime(value6);
                SQLCmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 60)).Value = Convert.ToString(value7);
                SQLCmd.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Decimal)).Value = Convert.ToDecimal(value8);
//SQLCmd.Parameters["@Date"].Value = Convert.ToDateTime(value6); 
   //SQLCmd.Parameters["@Amount"].Value = Convert.ToDecimal(value8);

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now