Insert data from gridview on click of a button

Question

1.00/5 (1 vote)

See more:

I am trying to insert every row from the gridview into a database through the click of a button. The code works with no errors, but nothing is actually being inserted into the database, can anyone spot what I've done wrong? Here is the c# code from my button event.

C#

protected void btnSubmit_Click(object sender, EventArgs e)
  {
     int index = 0;
     foreach (GridViewRow row in gvStockTake.Rows)
     {
      if (row.RowType == DataControlRowType.DataRow)
        {
         Label ID = gvStockTake.Rows[index].FindControl("itemId")as Label;
         TextBox BAR = gvStockTake.Rows[index].FindControl("txtBar") as TextBox;
         TextBox STORAGE = gvStockTake.Rows[index].FindControl("txtStorage") as TextBox;

                   SqlConnection con = new SqlConnection(connectionString);
                   SqlCommand cmd = new SqlCommand();
                   cmd.CommandType = CommandType.Text;
                   cmd.CommandText =
                        "insert into Stock_Take_Item(ItemID, BarQuantity, StorageQuantity) values(@ID, @BAR, @STORAGE) ";
                            

                   cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID.Text;
                   cmd.Parameters.Add("@BAR", SqlDbType.Int).Value = BAR.Text;
                   cmd.Parameters.Add("@STORAGE", SqlDbType.Int).Value = STORAGE.Text;

                        
                    }

                    index++;
                }

                Response.Write("Successfully inserted stock take items.");
            }

Here is the code for populating my Gridview:

C#

void PopulateGridview()
      {
          DataTable dtbl = new DataTable();
          using (SqlConnection sqlCon = new SqlConnection(connectionString))
          {
              sqlCon.Open();
              SqlDataAdapter sqlDa = new SqlDataAdapter("SELECT Item.ItemID, Item.ItemDesc, Stock_Take_Item.BarQuantity, Stock_Take_Item.StorageQuantity FROM Item LEFT JOIN Stock_Take_Item ON Item.ItemID = Stock_Take_Item.ItemID", sqlCon);
              sqlDa.Fill(dtbl);
          }
          gvStockTake.DataSource = dtbl;
          gvStockTake.DataBind();
      }

Here is the code for my Gridview (I have deleted all styling properties for shortening purposes)

ASP.NET

<pre><asp:GridView ID="gvStockTake" runat="server" AutoGenerateColumns="false" ShowHeaderWhenEmpty="true"

                <Columns>
                <asp:TemplateField HeaderText="Item ID">
                    <ItemTemplate>
                        <asp:Label id="itemId" Text='<%# Eval("ItemID")%>' runat="server" />
                </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Item Description">
                    <ItemTemplate>
                        <asp:Label Text='<%# Eval("ItemDesc")%>' runat="server" />
                    </ItemTemplate>
                </asp:TemplateField>
                 
                <asp:TemplateField HeaderText="Bar Quantity">
                    <ItemTemplate>
                        <asp:TextBox ID="txtBar" runat="server"></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
                 
                <asp:TemplateField HeaderText="Storage Quantity">
                    <ItemTemplate>
                        <asp:TextBox ID="txtStorage" runat="server"></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>

                </Columns>
            </asp:GridView>

I am still learning as i'am going, so any recommendations would be greatly appreciated!

What I have tried:

I have been playing with this code for ages and can't figure out why it's not inserting correctly.

Posted 10-Apr-18 11:00am

Member 13771716

Updated 10-Apr-18 16:21pm

Add a Solution

Comments

j snooze 10-Apr-18 17:06pm

lucky for you the fix is easy. You've got the insert statement and the parameters filled in...but you never actually told the command to execute :)
cmd.ExecuteNonQuery(); should do the trick assuming your SQL syntax is correct. (Right before the index++)

Member 13771716 10-Apr-18 17:14pm

Hey, Thanks for the quick reply!

I've added this into my btnSubmit_Click method and got this error -
"System.InvalidOperationException: 'ExecuteNonQuery: Connection property has not been initialized.'"

j snooze 10-Apr-18 17:19pm

Sure enough, look in your populategridview where you open your sqlconn. Also you have to tell the SQL command what sql connection you are using.
cmd.Connection = con.

so 1. don't forget to open you sql connection to your server and 2. make sure your sql command knows what connection it needs to use to execute the statement.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2018-04-10T16:23:00

You're missing both connection open and statement execute.

Perhaps something like

C#

int index = 0;
foreach (GridViewRow row in gvStockTake.Rows) {
   if (row.RowType == DataControlRowType.DataRow) {
      Label ID = gvStockTake.Rows[index].FindControl("itemId") as Label;
      TextBox BAR = gvStockTake.Rows[index].FindControl("txtBar") as TextBox;
      TextBox STORAGE = gvStockTake.Rows[index].FindControl("txtStorage") as TextBox;

      using (SqlConnection con = new SqlConnection(connectionString)) {
         using (SqlCommand cmd = new SqlCommand()) {
            try {
               cmd.CommandType = CommandType.Text;
               cmd.CommandText =
              "insert into Stock_Take_Item(ItemID, BarQuantity, StorageQuantity) values(@ID, @BAR, @STORAGE) ";
               cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID.Text;
               cmd.Parameters.Add("@BAR", SqlDbType.Int).Value = BAR.Text;
               cmd.Parameters.Add("@STORAGE", SqlDbType.Int).Value = STORAGE.Text;
               con.Open();
               cmd.EndExecuteNonQuery();
            } catch (System.Exception) {
               // In here properly inform about the exception
            }
         }
      }
   }

   index++;
}

Response.Write("Successfully inserted stock take items.");

Few things to note:
- Use of using to properly dispose the objects
- Try..catch to handle problems