Remove From My Forums

Strange error when saving Gridview to access database

Question
0

Sign in to vote

User-27249898 posted

I have a gridview in which I have entered data but when I try to save it to my Access database, the cell value is returning blanks even though there is data in them. Why?

In the below code in btnSubmit_Click, the value of row.Cells[1].Text shows blank when checked in debug mode when actually there is a value in every cell of the grid.

Here is my code:

aspx file

=====

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Attributes.aspx.cs" Inherits="Attributes" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<p>
<br />
Name :
<asp:TextBox ID="txtName" runat="server" style="margin-left: 21px" Width="378px"></asp:TextBox>
</p>
<p>
Location : 
<asp:TextBox ID="txtLocation" runat="server" Width="271px"></asp:TextBox>
</p>
<div>

<asp:GridView ID="grdAttributes" runat="server" ShowFooter="true" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="RowNumber" HeaderText="Row Number" />
<asp:TemplateField HeaderText="Attribute">
<ItemTemplate>
<asp:TextBox ID="txtAttribute" runat="server"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Verdict">
<ItemTemplate>
<asp:RadioButtonList ID="RBLVerdict"
runat="server" RepeatDirection="Horizontal">
<asp:ListItem Value="A">Agree</asp:ListItem>
<asp:ListItem Value="D">Disagree</asp:ListItem>
</asp:RadioButtonList>
</ItemTemplate>
<FooterStyle HorizontalAlign="Right" />
<FooterTemplate>
<asp:Button ID="btnAddAttribute" runat="server" Text="Add Attribute" OnClick="btnAddAttribute_Click"/>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

<br />
<asp:Button ID="btnSubmit" runat="server" Text="Submit" Width="81px" OnClick="btnSubmit_Click" />

</div>
</form>
</body>
</html>

code behind

========

protected void btnSubmit_Click(object sender, EventArgs e)
{
foreach (GridViewRow row in grdAttributes.Rows)
{
string constring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = D:\HP Backup 5-4\fbfriends2\fbfriends.accdb;Persist Security Info=False";
using (OleDbConnection con = new OleDbConnection(constring))
{
using (OleDbCommand cmd = new OleDbCommand("INSERT INTO Friend_Attributes VALUES(@Friend_name, @Location, @Attribute, @Agree_count, @Disagree_count)", con))
{
cmd.Parameters.AddWithValue("@Friend_name", txtName.Text);
cmd.Parameters.AddWithValue("@Location", txtLocation.Text);
//cmd.Parameters.AddWithValue("@CustomerId", row.Cells["Id"].Value);
cmd.Parameters.AddWithValue("@Attribute", row.Cells[1].Text);
if (row.Cells[2].Text == "A")
{
cmd.Parameters.AddWithValue("@Agree_count", 1);
cmd.Parameters.AddWithValue("@Disagree_count", 0);
}
else
{
cmd.Parameters.AddWithValue("@Agree_count", 0);
cmd.Parameters.AddWithValue("@Disagree_count", 1);

}

con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
//MessageBox.Show("Records inserted.");
}

Please help!!!

Friday, July 29, 2016 2:00 PM

Answers

User283571144 posted

Hi mcp111,

mcp111
I am doing the insert only to update the database. What I don't understand is, when data is present in the grid, when I reference row.Cells[2].Text which has a value "Honest" entered on the screen, why does the program see it as blank in debug mode?

According to your code and description, I find you have add a "textbox" and a "RadioButtonList" in column[1] and column[2].

But in your "btnSubmit_Click" event, you add "row.Cells[2].Text" value into database.

In each row.Cells[2] is "RadioButtonList", you couldn't get the row.Cells[2].Text value, you need to use "RadioButtonList.SelectedValue" to get the value, then insert it into database.

More details, you could refer to follow codes:

HTML:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm6.aspx.cs" Inherits="Grid.WebForm6" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
<html xmlns="http://www.w3.org/1999/xhtml">  
<head runat="server">  
    <title></title>  
</head>  
<body >  
    <form id="form1" runat="server">  
    <table style="color: White;margin-top:30px;margin-left:10px">  
        <tr>  
            <td>  
                Author Name  
            </td>  
            <td>  
                Book Name  
            </td>  
            <td>  
                Book Type  
            </td>  
            <td>  
                Publisher  
            </td>  
        </tr>  
        <tr>  
            <td>  
                <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>  
            </td>  
            <td>  
                <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>  
            </td>  
            <td>  
                <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>  
            </td>  
            <td>  
         <%--       <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>  --%>
            </td>  
        </tr>  
        <tr>  
            <td>  
            </td>  
            <td>  
            </td>  
            <td>  
            </td>  
            <td>  
                <asp:Button ID="AddProduct" runat="server" Style="color: White" Text="Add Product"  
                    OnClick="AddProduct_Click" BackColor="#999966" />  
            </td>  
        </tr>  
    </table>  
     
    <div style="margin-left:10px;margin-top:10px">  
     <asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server" CellPadding="4"  
        ForeColor="#333333" GridLines="None">  
        <AlternatingRowStyle BackColor="White" />  
        <Columns>  
            <asp:BoundField HeaderStyle-Width="120px" HeaderText="Author  Name" DataField="AuthorName" />  
            <asp:BoundField HeaderStyle-Width="120px" HeaderText=" Book Name" DataField="BookName" />  
            <asp:BoundField HeaderStyle-Width="120px" HeaderText=" Book Type" DataField="BookType" />  
   <%--         <asp:BoundField HeaderStyle-Width="120px" HeaderText="Publisher" DataField="Publisher" />  --%>

            <asp:TemplateField HeaderText="Attribute">
            <ItemTemplate>
            <asp:TextBox ID="txtAttribute" runat="server"></asp:TextBox>
            </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Verdict">
            <ItemTemplate>
            <asp:RadioButtonList ID="RBLVerdict" 
            runat="server" RepeatDirection="Horizontal">
            <asp:ListItem Value="A">Agree</asp:ListItem>
            <asp:ListItem Value="D">Disagree</asp:ListItem>
            </asp:RadioButtonList>
            </ItemTemplate>
            <FooterStyle HorizontalAlign="Right" />
            <FooterTemplate>
            <%--<asp:Button ID="btnAddAttribute" runat="server" Text="Add Attribute" OnClick="btnAddAttribute_Click"/>--%>
            </FooterTemplate>
            </asp:TemplateField>
        </Columns>  
        <EditRowStyle BackColor="#2461BF" />  
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />  
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />  
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />  
        <RowStyle BackColor="#EFF3FB" />  
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />  
        <SortedAscendingCellStyle BackColor="#F5F7FB" />  
        <SortedAscendingHeaderStyle BackColor="#6D95E1" />  
        <SortedDescendingCellStyle BackColor="#E9EBEF" />  
        <SortedDescendingHeaderStyle BackColor="#4870BE" />  
    </asp:GridView>  

        <hr/>

        <asp:Button ID="Button1" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
    </div>  
    </form>  
</body>  
</html>

Code-behind:

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

namespace Grid
{
    public partial class WebForm6 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                AddDefaultFirstRecord();
            }
        }
        protected void btnSubmit_Click(object sender, EventArgs e)
        {

            foreach (GridViewRow row in GridView1.Rows)
            {
                string constring = ConfigurationManager.ConnectionStrings["MysqlConnectionString2"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constring))
                {
                    using (SqlCommand cmd = new SqlCommand("INSERT INTO Friend_Attributes VALUES(@AuthorName, @BookName, @BookType, @Publisher)", con))
                    {
                        cmd.Parameters.AddWithValue("@AuthorName", row.Cells[0].Text);
                        cmd.Parameters.AddWithValue("@BookName", row.Cells[1].Text);
                        cmd.Parameters.AddWithValue("@BookType", row.Cells[2].Text);
                        //cmd.Parameters.AddWithValue("@Publisher", ((TextBox)row.Cells[2].FindControl("txtAttribute")).Text);
                        
                        if (((RadioButtonList)(row.Cells[3].FindControl("RBLVerdict"))).SelectedValue == "A")
                        {

                            cmd.Parameters.AddWithValue("@Publisher", "Agree");
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue("@Publisher", "Disagree");
                        }
                            
                        //cmd.Parameters.AddWithValue("@CustomerId", row.Cells["Id"].Value);
                        //cmd.Parameters.AddWithValue("@Attribute", row.Cells[1].Text);
                        //if (row.Cells[2].Text == "A")
                        //{
                        //    cmd.Parameters.AddWithValue("@Agree_count", 1);
                        //    cmd.Parameters.AddWithValue("@Disagree_count", 0);
                        //}
                        //else
                        //{
                        //    cmd.Parameters.AddWithValue("@Agree_count", 0);
                        //    cmd.Parameters.AddWithValue("@Disagree_count", 1);

                        //}

                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }
            }
            //MessageBox.Show("Records inserted.");
        }
        protected void AddProduct_Click(object sender, EventArgs e)
        {
            AddNewRecordRowToGrid();
        }

        private void AddDefaultFirstRecord()
        {
            //creating dataTable   
            DataTable dt = new DataTable();
            DataRow dr;
            dt.TableName = "AuthorBooks";
            dt.Columns.Add(new DataColumn("AuthorName", typeof(string)));
            dt.Columns.Add(new DataColumn("BookName", typeof(string)));
            dt.Columns.Add(new DataColumn("BookType", typeof(string)));
            dt.Columns.Add(new DataColumn("Publisher", typeof(string)));
            dr = dt.NewRow();
            dt.Rows.Add(dr);
            //saving databale into viewstate   
            ViewState["AuthorBooks"] = dt;
            //bind Gridview  
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
        private void AddNewRecordRowToGrid()
        {
            // check view state is not null  
            if (ViewState["AuthorBooks"] != null)
            {
                //get datatable from view state   
                DataTable dtCurrentTable = (DataTable)ViewState["AuthorBooks"];
                DataRow drCurrentRow = null;

                if (dtCurrentTable.Rows.Count > 0)
                {

                    for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
                    {
                        int rowIndex = 0;
                        //add each row into data table  
                        drCurrentRow = dtCurrentTable.NewRow();
                        drCurrentRow["AuthorName"] = TextBox1.Text;
                        drCurrentRow["BookName"] = TextBox2.Text;
                        drCurrentRow["BookType"] = TextBox3.Text;
                        drCurrentRow["Publisher"] = (RadioButtonList)GridView1.Rows[rowIndex].Cells[3].FindControl("RBLVerdict");
                        rowIndex++;

                    }


                    //for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
                    //{
                    //    //extract the TextBox values
                    //    TextBox box1 = (TextBox)grdAttributes.Rows[rowIndex].Cells[1].FindControl("txtAttribute");
                    //    RadioButtonList RBLVerdict =
                    //    (RadioButtonList)grdAttributes.Rows[rowIndex].Cells[2].FindControl("RBLVerdict");
                    //    //TextBox box3 = (TextBox)Gridview1.Rows[rowIndex].Cells[3].FindControl("TextBox3");

                    //    drCurrentRow = dtCurrentTable.NewRow();
                    //    drCurrentRow["RowNumber"] = i + 1;

                    //    dtCurrentTable.Rows[i - 1]["Attribute"] = box1.Text;
                    //    dtCurrentTable.Rows[i - 1]["Verdict"] = RBLVerdict.SelectedValue;
                    //    //dtCurrentTable.Rows[i - 1]["Column3"] = box3.Text;

                    //    rowIndex++;
                    //}

                    //Remove initial blank row  
                    if (dtCurrentTable.Rows[0][0].ToString() == "")
                    {
                        dtCurrentTable.Rows[0].Delete();
                        dtCurrentTable.AcceptChanges();

                    }

                    //add created Rows into dataTable  
                    dtCurrentTable.Rows.Add(drCurrentRow);
                    //Save Data table into view state after creating each row  
                    ViewState["AuthorBooks"] = dtCurrentTable;
                    //Bind Gridview with latest Row  
                    GridView1.DataSource = dtCurrentTable;
                    GridView1.DataBind();
                }
            }
        }
    }
}

Best Regards,

Brando

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Tuesday, August 2, 2016 8:08 AM

All replies

0

Sign in to vote

User475983607 posted

Most likely you forgot to rebind the the GridView after the update. You're probably seeing the previous GridView state as stored in ViewState whcih does not contain the newly added record.

Friday, July 29, 2016 2:27 PM
0

Sign in to vote

User-27249898 posted

It is an unbound gridview. I am databinding the gridview to the new datatable after adding a new row.

Here's that code

private void AddNewRowToGrid()
{
int rowIndex = 0;

if (ViewState["CurrentTable"] != null)
{
DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
DataRow drCurrentRow = null;
if (dtCurrentTable.Rows.Count > 0)
{
for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
{
//extract the TextBox values
TextBox box1 = (TextBox)grdAttributes.Rows[rowIndex].Cells[1].FindControl("txtAttribute");
RadioButtonList RBLVerdict =
(RadioButtonList)grdAttributes.Rows[rowIndex].Cells[2].FindControl("RBLVerdict");
//TextBox box3 = (TextBox)Gridview1.Rows[rowIndex].Cells[3].FindControl("TextBox3");

drCurrentRow = dtCurrentTable.NewRow();
drCurrentRow["RowNumber"] = i + 1;

dtCurrentTable.Rows[i - 1]["Attribute"] = box1.Text;
dtCurrentTable.Rows[i - 1]["Verdict"] = RBLVerdict.SelectedValue;
//dtCurrentTable.Rows[i - 1]["Column3"] = box3.Text;

rowIndex++;
}
dtCurrentTable.Rows.Add(drCurrentRow);
ViewState["CurrentTable"] = dtCurrentTable;

grdAttributes.DataSource = dtCurrentTable;
grdAttributes.DataBind();
}
}
else
{
Response.Write("ViewState is null");
}

//Set Previous Data on Postbacks
SetPreviousData();
}

Where else would I need to databind the gridview?

Friday, July 29, 2016 2:58 PM
0

Sign in to vote

User475983607 posted

As far as I can tell the code snippet doesn't do anything. The code retrieves a DataTable from ViewState, loops over the table then binds the result to the GridView. The only way AddRewRowToGrid() could possible add the new record is if you queried the table after updating the database and placed the results of that query in ViewState before calling the AddRewRowToGrid() method

All you have to do is create a method that retrieves the data from the database and use that method to bind the GridView after invoking the INSERT. don;t mess around with ViewState because you end up storing the same data in two different places and must write extra code to sync the two data sources.

Friday, July 29, 2016 4:06 PM
0

Sign in to vote

User-27249898 posted

I am doing the insert only to update the database. What I don't understand is, when data is present in the grid, when I reference row.Cells[2].Text which has a value "Honest" entered on the screen, why does the program see it as blank in debug mode?

Monday, August 1, 2016 2:10 PM