Insert into MySql using GridView commandname imagebutton C#

Question

0.00/5 (No votes)

See more:

I want to fetch the data from labels in Gridview and insert them to MySql database. When users click the imagebutton, the data will be inserted to MySql DB. I can't seem to make it happen with these codes:

//The Backend

C#

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "Insert")
            {

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

                    string bookTitle = (row.FindControl("book") as Label).Text;
                    string date = (row.FindControl("date") as Label).Text;
                    string name = (row.FindControl("name") as Label).Text;
                    string memberID = (row.FindControl("memberID") as Label).Text;
                    string phone = (row.FindControl("phone") as Label).Text;

                    string Query = "insert into c_records (bookTitle, date, name, memberID, phone) values (@bookTitle, @date, @name, @memberID, @phone)";
                    MySqlCommand cmd = new MySqlCommand(Query, conn);
                    cmd.Parameters.AddWithValue("@bookTitle", bookTitle);
                    cmd.Parameters.AddWithValue("@date", date);
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@memberID", memberID);
                    cmd.Parameters.AddWithValue("@phone", phone);

                    
                    cmd.ExecuteNonQuery();

                    BindData();
                    
                }
            }

        }

//The Front End

ASP.NET

<asp:GridView ID="GridView1" runat="server" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" AutoGenerateColumns="False"  Width="721px" showfooter="true" OnRowUpdating="GridView1_RowUpdating" Height="411px" OnRowCommand="GridView1_InsDel">
           <Columns>
               <asp:TemplateField HeaderText="Id">
                   <ItemTemplate>
                       <asp:Label ID="Label5" runat="server" Text='<%# Eval("id") %>'></asp:Label>
                   </ItemTemplate>
               </asp:TemplateField>
               <asp:TemplateField HeaderText="Book Title">
                   <ItemTemplate>
                       <asp:Label ID="book" runat="server" Text='<%# Eval("bookTitle") %>'></asp:Label>
                   </ItemTemplate>
               </asp:TemplateField>
               <asp:TemplateField HeaderText="Date">
                   <ItemTemplate>
                       <asp:Label ID="date" runat="server" Text='<%# Eval("date") %>'></asp:Label>
                   </ItemTemplate>
               </asp:TemplateField>
               <asp:TemplateField HeaderText="Name">
                   <ItemTemplate>
                       <asp:Label ID="name" runat="server" Text='<%# Eval("name") %>'></asp:Label>
                   </ItemTemplate>
               </asp:TemplateField>
               <asp:TemplateField HeaderText="Member ID">
                   <ItemTemplate>
                       <asp:Label ID="memberID" runat="server" Text='<%# Eval("memberID") %>'></asp:Label>
                   </ItemTemplate>
               </asp:TemplateField>
               <asp:TemplateField HeaderText="Phone">
                   <ItemTemplate>
                       <asp:Label ID="phone" runat="server" Text='<%# Eval("phone") %>'></asp:Label>
                   </ItemTemplate>
               </asp:TemplateField>


               <asp:TemplateField HeaderText="Sign Out">
                   <ItemTemplate>
                       <asp:ImageButton OnClientClick="return abc(this);" ID="signOut" CommandName="Insert" AlternateText="Available" ImageUrl="~/Images/unclick.png" runat="server" />
                   </ItemTemplate>
               </asp:TemplateField>


               <asp:TemplateField>

                   <ItemTemplate>
                       <asp:LinkButton ID="submit" runat="server" Text="Submit" CommandName="Update"><img src="Images/save.jpg" /></asp:LinkButton>
                   </ItemTemplate>

               </asp:TemplateField>

Please don't hesitate to tell me if there are any other solution.

Posted 23-Jul-15 21:08pm

Hayashi Narumi

Updated 30-Jul-15 19:43pm

v5

Add a Solution

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 · Accepted Answer · 2015-07-23T21:17:00

You seem to combine both queries in a single command. DOn't do that. Instead run bith separately. You also seem to set the values for the parameters twice. At least when having a quick look I see no reason for that.

Also note that some of the parameters had a mismatch in naming.

So instead of

C#

conn.Open();
string q4 = "insert into c_records (bookTitle, date, name, memberID, phone) values (@bookTitle, @date, @name, @memberID, @phone);";
string q5 = "delete customers from customers where name = @name and memberID = @memberID;";
MySqlCommand cmd = new MySqlCommand(q4 + q5, conn);
cmd.Parameters.AddWithValue("@date", date);
cmd.Parameters.AddWithValue("@memberID", memberID);
cmd.Parameters.AddWithValue("@phone", phone);
cmd.Parameters.AddWithValue("@book", bookTitle);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters["@book"].Value = bookTitle;
cmd.Parameters["@date"].Value = date;
cmd.Parameters["@name"].Value = name;
cmd.Parameters["@memberID"].Value = memberID;
cmd.Parameters["@phone"].Value = phone;
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);

You should have something like

C#

conn.Open();

string q4 = "insert into c_records (bookTitle, date, name, memberID, phone) values (@bookTitle, @date, @name, @memberID, @phone);";
MySqlCommand cmd = new MySqlCommand(q4, conn);
cmd.Parameters.AddWithValue("@bookTitle", bookTitle);
cmd.Parameters.AddWithValue("@date", date);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@memberID", memberID);
cmd.Parameters.AddWithValue("@phone", phone);
cmd.ExecuteNonQuery();

string q5 = "delete customers from customers where name = @name and memberID = @memberID;";
cmd = new MySqlCommand(q5, conn);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@memberID", memberID);
cmd.ExecuteNonQuery();
...