Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Cannot update identity column 'rec_ID'

Posted on 2009-12-16
5
Medium Priority
?
1,469 Views
Last Modified: 2012-05-08
Hi,
1. I'll describe first an error I am getting.
2.Then what I am trying to achive
3. attach screen display & full code of program.
The login connection string info is used for testing,
The final production program login info varies when run on different computer.
Thanks in Advance, Ian
Cannot update identity column 'rec_ID'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot update identity column 'rec_ID'.
Source Error:
Line 156:                if (Connection.State == ConnectionState.Closed)
Line 157:                    Connection.Open();
Line 158:                Command.ExecuteNonQuery();
Line 159:                Session["InsertFlag"] = (int)Session["InsertFlag"] == 1 ? 0 : 1;
Line 160:            //}
Source File: d:\aaaWebaaa\assignmentWebDev\assignmentWebData\app\aaaGridView.aspx.cs    Line: 158
Stack Trace:
[SqlException (0x80131904): Cannot update identity column 'rec_ID'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +317
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   Combine.aaaGridView.TableGridView_RowUpdating(Object sender, GridViewUpdateEventArgs e) in d:\aaaWebaaa\assignmentWebDev\assignmentWebData\app\aaaGridView.aspx.cs:158
   System.Web.UI.WebControls.GridView.OnRowUpdating(GridViewUpdateEventArgs e) +133
   System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +720
   System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +704
   System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +123
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.ImageButton.OnCommand(CommandEventArgs e) +111
   System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +176
   System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

The first datagrid displays the ConnectionString path in the first field and table name in second field. When selecting a record it then displays a second datagrid corresponding to the database & table selected in the first grid.
When selecting edit it shows an editable record.
When selecting update it causes the error as described above.
The error occures when selecting all databases in the system except when selecting
pubs database & table jobs, it edits & updates.
All database tables have the first column as primary key.
I am not sure why the error occures with some databases, but not with pubs sql database. Thanks in advance, Ian
//****************************************************
// codebehind aaaGridView.aspx.cs
//**************************************************** 
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.Linq;
using System.Xml.Linq;
using System.Data.Common;
using System.IO;
using System.Text;
using System.Drawing;
using System.Data.OleDb;
using System.Globalization;
using System.Threading;
using System.Reflection;
using System.Web.SessionState;
using System.Web.Configuration;
using System.Collections.Generic;
using System.Text.RegularExpressions;
 
namespace Combine
{
    public partial class aaaGridView : System.Web.UI.Page
    {
        public string odbName;
        public string dbTableName;
        public string sDBName = "AdminDB2";
        public string TempTable = "AdminTable";
        public string connectionString;
        public string MsgBoxDatabase = null;
        public string MsgBoxTable = null;
        public string MessageBox = null;
        public string MessageBoxProcedure = null;
        public bool bDatabaseExists = false;
        public bool bTableExists = false;
        public string strTable;
        public string strProcedure;
        public string server_tb;
        public string user_tb;
        public string pswrd_tb;
        public string database_tb;
        public string table_tb;
        #region Data members
 
        public static DataTable Table = new DataTable();
        ArrayList ParameterArray = new ArrayList();
 
        #endregion
 
        #region Events Handlers
 
        protected void Page_PreLoad(object sender, EventArgs e)
        {
            HttpContext.Current.Session["tablePage"] = "0";
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            //connection = new System.Data.SqlClient.SqlConnection(Combine.conn.GetConnectionString(sDBName));
            HttpContext.Current.Session["connStringValue"] = Combine.conn.GetConnectionString2();
            //HttpContext.Current.Session["TableSelected"] = TempTable;
            //Response.Write("#" + HttpContext.Current.Session["connStringValue"] + "#" + "<br/>"
            //****************************************************
            SqlConnection Connection = new System.Data.SqlClient.SqlConnection(HttpContext.Current.Session["connStringValue"] + ";Initial Catalog=" + sDBName);
            Connection.Open();
            SqlCommand comm = new SqlCommand("SELECT * FROM " + TempTable, Connection);
            SqlDataReader reader = comm.ExecuteReader();
            grid.DataSource = reader;
            grid.DataBind();
            //****************************************************
            HttpContext _Context = HttpContext.Current;
            if (Page.IsPostBack && (bool)Session["IsConnectionInfoSet"] == true)
            {
                CreateTemplatedGridView();
            }
            if (!Page.IsPostBack)
            {//msg_lbl.Text = "";
                HttpContext.Current.Session["Server"] = "";
                HttpContext.Current.Session["UserName"] = "";
                HttpContext.Current.Session["Password"] = "";
                HttpContext.Current.Session["DatabaseSelected"] = "";
                HttpContext.Current.Session["TableSelected"] = "";
                HttpContext.Current.Session["IsConnectionInfoSet"] = false;
                //CreateTemplatedGridView(); 
            }
        }
        public void TableGridView_RowEditing(object sender, GridViewEditEventArgs e)
        {
            TableGridView.EditIndex = e.NewEditIndex;
            TableGridView.DataBind();
            Session["SelecetdRowIndex"] = e.NewEditIndex;
        }
        public void TableGridView_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            TableGridView.EditIndex = -1;
            TableGridView.DataBind();
            Session["SelecetdRowIndex"] = -1;
 
        }
        protected void TableGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            string ServerName = (string)Session["Server"];
            string UserName = (string)Session["UserName"];
            string Password = (string)Session["Password"];
            string DatabaseName = (string)Session["DatabaseSelected"];
            SqlConnection Connection = new System.Data.SqlClient.SqlConnection("server=" + ServerName + ";uid=" + UserName + ";pwd=" + Password + ";Initial Catalog=" + DatabaseName + ";Asynchronous Processing=true");
            string Query = GenerateDeleteQuery(e.RowIndex);
            SqlCommand Command = new System.Data.SqlClient.SqlCommand(Query, Connection);
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();
                Command.ExecuteNonQuery();
            }
            catch (SqlException se)
            {
                msg_lbl.Text = se.ToString();
                MsgPanel.Visible = true;
                Connection.Close();
 
            }
            CreateTemplatedGridView();
        }
        public void TableGridView_RowUpdating(Object sender, GridViewUpdateEventArgs e)
        {
            string ServerName = (string)Session["Server"];
            string ServerType = (string)Session["ServerType"];
            string UserName = (string)Session["UserName"];
            string Password = (string)Session["Password"];
            string DatabaseName = (string)Session["DatabaseSelected"];
            SqlConnection Connection = new System.Data.SqlClient.SqlConnection("server=" + ServerName + ";uid=" + UserName + ";pwd=" + Password + ";Initial Catalog=" + DatabaseName + ";Asynchronous Processing=true");
            Label3.Text = "<h6>" + "Label3-Line141:" + "server=" + ServerName + ";uid=" + UserName + ";pwd=" + Password + ";Initial Catalog=" + DatabaseName + ";Asynchronous Processing=true" + "</h6>";
            GridViewRow row = TableGridView.Rows[e.RowIndex];
            for (int i = 0; i < Table.Columns.Count; i++)
            {
                string field_value = ((TextBox)row.FindControl(Table.Columns[i].ColumnName)).Text;
                ParameterArray.Add(field_value);
            }
            string Query = "";
            if ((int)Session["InsertFlag"] == 1)
                Query = GenerateInsertQuery();
            else
                Query = GenerateUpdateQuery();
            SqlCommand Command = new System.Data.SqlClient.SqlCommand(Query, Connection);
            //try
            //{
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();
                Command.ExecuteNonQuery();
                Session["InsertFlag"] = (int)Session["InsertFlag"] == 1 ? 0 : 1;
            //}
            //catch (SqlException se)
            //{
                //msg_lbl.Text = se.ToString();
                //MsgPanel.Visible = true;
                //Connection.Close();
            //}
            TableGridView.EditIndex = -1;
            CreateTemplatedGridView();
        }
        protected void TableGridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            //CreateTemplatedGridView();
            TableGridView.PageIndex = e.NewPageIndex;
            TableGridView.DataBind();
        }
        protected void msg_button_Click(object sender, EventArgs e)
        {
            MsgPanel.Visible = false;
        }
        protected void btnConnect_Click(object sender, EventArgs e)
        {
            Session["IsConnectionInfoSet"] = true;
            CreateTemplatedGridView();
        }
        protected void grid_SelectedIndexChanged(object sender, System.EventArgs e)
        {
            int selectedRowIndex;
            selectedRowIndex = grid.SelectedIndex;
            GridViewRow row = grid.Rows[selectedRowIndex];
            string name = row.Cells[2].Text;
            string name2 = row.Cells[3].Text;
            string connstr = "@" + row.Cells[2].Text;
            string[] str1 = connstr.Split(';');
            server_tb = str1[0].Split('=')[1];
            user_tb = str1[1].Split('=')[1];
            pswrd_tb = str1[2].Split('=')[1];
            database_tb = str1[3].Split('=')[1];
            table_tb = name2;
            detailsLabel.Text = "<h6>" + "detailsLabel-Line199:" + name + "&nbsp;&nbsp;&nbsp;" + name2 + "</h6>";
            Session["Server"] = server_tb;
            Session["UserName"] = user_tb;
            Session["Password"] = pswrd_tb;
            Session["DatabaseSelected"] = database_tb;
            Session["TableSelected"] = table_tb;
            Session["IsConnectionInfoSet"] = true;
            Label1.Text = "<h6>" + "Label1-Line206:#" + "server=" + server_tb + ";uid=" + user_tb + ";pwd=" + pswrd_tb + ";Initial Catalog=" + database_tb + "#" + table_tb + "#" + "</h6>";
            CreateTemplatedGridView();
        }
 
        #endregion
 
        #region Methods
        void PopulateDataTable()
        {
            Table = new DataTable();
            TableGridView.Columns.Clear();
            string ServerName = Session["Server"].ToString();
            string UserName = Session["UserName"].ToString();
            string Password = Session["Password"].ToString();
            string DatabaseName = Session["DatabaseSelected"].ToString();
            string TableName = Session["TableSelected"].ToString();
            SqlConnection Connection = new System.Data.SqlClient.SqlConnection("server=" + ServerName + ";uid=" + UserName + ";pwd=" + Password + ";Initial Catalog=" + DatabaseName + ";Asynchronous Processing=true");
            if (Connection.State == ConnectionState.Closed)
            Connection.Open();
            Label2.Text = "<h6>" + "Label2-Line225:#" + "server=" + ServerName + ";uid=" + UserName + ";pwd=" + Password + ";Initial Catalog=" + DatabaseName + "#" + TableName + "#" + "</h6>";
            SqlDataAdapter adapter = new SqlDataAdapter("Select * from " + TableName, Connection);
            try
            {
                adapter.Fill(Table);
            }
            catch (Exception ex)
            {
                msg_lbl.Text = ex.ToString();
                MsgPanel.Visible = true;
                Connection.Close();
           }
        }
        void CreateTemplatedGridView()
        {
            // fill the table which is to bound to the GridView
            PopulateDataTable();
            // add templated fields to the GridView
            TemplateField BtnTmpField = new TemplateField();
            BtnTmpField.ItemTemplate =
                new DynamicallyTemplatedGridViewHandler(ListItemType.Item, "...", "Command");
            BtnTmpField.HeaderTemplate =
                new DynamicallyTemplatedGridViewHandler(ListItemType.Header, "...", "Command");
            BtnTmpField.EditItemTemplate =
                new DynamicallyTemplatedGridViewHandler(ListItemType.EditItem, "...", "Command");
            TableGridView.Columns.Add(BtnTmpField);
            for (int i = 0; i < Table.Columns.Count; i++)
            {
                TemplateField ItemTmpField = new TemplateField();
                // create HeaderTemplate
                ItemTmpField.HeaderTemplate = new DynamicallyTemplatedGridViewHandler(ListItemType.Header,
                                                              Table.Columns[i].ColumnName,
                                                              Table.Columns[i].DataType.Name);
                // create ItemTemplate
                ItemTmpField.ItemTemplate = new DynamicallyTemplatedGridViewHandler(ListItemType.Item,
                                                              Table.Columns[i].ColumnName,
                                                              Table.Columns[i].DataType.Name);
                //create EditItemTemplate
                ItemTmpField.EditItemTemplate = new DynamicallyTemplatedGridViewHandler(ListItemType.EditItem,
                                                              Table.Columns[i].ColumnName,
                                                              Table.Columns[i].DataType.Name);
                // then add to the GridView
                TableGridView.Columns.Add(ItemTmpField);
            }
            // bind and display the data
            TableGridView.DataSource = Table;
            TableGridView.DataBind();
        }
        string GenerateUpdateQuery()
        {
            int i = 0;
            string tempstr = "";
            int temp_index = -1;
            string TableName = (string)Session["TableSelected"];
            string Query = "";
            Query = "Update  " + TableName + " set ";
            for (i = 0; i < Table.Columns.Count; i++)
            {
                switch (Table.Columns[i].DataType.Name)
                {
                    case "Boolean":
                    case "Int32":
                    case "Byte":
                    case "Decimal":
                        if ((string)ParameterArray[i] == "True")
                            ParameterArray[i] = "1";
                        else if ((string)ParameterArray[i] == "False")
                            ParameterArray[i] = "0";
                        if (i == Table.Columns.Count - 1)
                            Query = Query + Table.Columns[i].ColumnName + "=" + ParameterArray[i];
                        else
                            Query = Query + Table.Columns[i].ColumnName + "=" + ParameterArray[i] + ", ";
                        break;
                    case "String":
                    case "DateTime":
                        if (((string)ParameterArray[i]).Contains("'"))
                        {
                            tempstr = ((string)ParameterArray[i]);
                            ParameterArray[i] = ((string)ParameterArray[i]).Replace("'", "''");
                            temp_index = i;
                        }
                        if (i == Table.Columns.Count - 1)
                            Query = Query + Table.Columns[i].ColumnName + "='" + ParameterArray[i] + "' ";
                        else
                            Query = Query + Table.Columns[i].ColumnName + "='" + ParameterArray[i] + "', ";
                        break;
                }
            }
            if (temp_index > -1)
                ParameterArray[temp_index] = tempstr;
            if (Table.Columns[0].DataType.Name == "String" || Table.Columns[0].DataType.Name == "DateTime")
                Query = Query + " where " + Table.Columns[0].ColumnName + " = '" + ParameterArray[0] + "'";
            else
                Query = Query + " where " + Table.Columns[0].ColumnName + " = " + ParameterArray[0];
            return Query;
        }
        string GenerateInsertQuery()
        {
            int i = 0;
            string tempstr = "";
            int temp_index = -1;
            string TableName = (string)Session["TableSelected"];
            string Query = "";
            Query = "Insert into  " + TableName + "(";
            for (i = 0; i < Table.Columns.Count; i++)
            {
                if (i == Table.Columns.Count - 1)
                    Query = Query + Table.Columns[i].ColumnName;
                else
                    Query = Query + Table.Columns[i].ColumnName + ", ";
            }
            Query = Query + ")" + "Values (";
            for (i = 0; i < Table.Columns.Count; i++)
            {
                switch (Table.Columns[i].DataType.Name)
                {
                    case "Boolean":
                    case "Int32":
                    case "Byte":
                    case "Decimal":
                        if ((string)ParameterArray[i] == "True")
                            ParameterArray[i] = "1";
                        else if ((string)ParameterArray[i] == "False")
                            ParameterArray[i] = "0";
                        if (i == Table.Columns.Count - 1)
                            Query = Query + ParameterArray[i];
                        else
                            Query = Query + ParameterArray[i] + ", ";
                        break;
                    case "String":
                    case "DateTime":
                        if (((string)ParameterArray[i]).Contains("'"))
                        {
                            tempstr = ((string)ParameterArray[i]);
                            ParameterArray[i] = ((string)ParameterArray[i]).Replace("'", "''");
                            temp_index = i;
                        }
                        if (i == Table.Columns.Count - 1)
                            Query = Query + "'" + ParameterArray[i] + "' ";
                        else
                            Query = Query + "'" + ParameterArray[i] + "', ";
                        break;
                }
            }
            Query = Query + ")";
            return Query;
        }
        string GenerateDeleteQuery(int index)
        {
            string TableName = (string)Session["TableSelected"];
            string query = "";
            if (Table.Columns[0].DataType.Name == "String" || Table.Columns[0].DataType.Name == "DateTime")
                query = "Delete from " + TableName + " where " + Table.Columns[0].ColumnName + "='" + Table.Rows[index][0].ToString() + "'";
            else
                query = "Delete from " + TableName + " where " + Table.Columns[0].ColumnName + "=" + Table.Rows[index][0].ToString();
            return query;
        }
        #endregion
 
    }
}
<% 
    //****************************************************
    // code Page aaaGridView.aspx
    //****************************************************  
%>
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="aaaGridView.aspx.cs" Inherits="Combine.aaaGridView" %>
<!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 id="Head1" runat="server">
<title>Untitled Page</title>
<link rel="stylesheet" href="outpost.css" />
<script type="text/javascript">
</script>
</head>
<body style="background-image: url(./WebFront.jpg); background-repeat:repeat-x">
<!--START PAGE SECTION-->
<!--  
--> 
<table id="table1" style="z-index:99; position:absolute; top:25px; left:5px; width:auto">
<tr>
<td>
<div style="width:950px; height:555px; font-size:smaller; overflow:auto;  border: 0px solid #000000" id="DIV1" onclick="return DIV1_onclick()">
<form id="form1" runat="server" method="post">
<table class="menu">
                        <tr>
                            <td>
                                <asp:HyperLink ID="lnkHome" Runat="server" NavigateUrl="~/Default.aspx" Text="Home" />
                            </td>
                            <td>
                                <asp:Menu ID="Menu1" Runat="server" Orientation="Horizontal" DataSourceID="srcSiteMap" />
                                <asp:SiteMapDataSource ID="srcSiteMap" Runat="server" ShowStartingNode="false" />
                            </td>
                        </tr>
                    </table>   
<div id="news" class="news">
<asp:GridView id="grid" runat="server" Font-Size="XX-Small"
 AutoGenerateColumns="true" OnSelectedIndexChanged="grid_SelectedIndexChanged">
    <Columns>
      
    <asp:ButtonField CommandName="Select" Text="Select" />
    </Columns>
  </asp:GridView>
<asp:Label ID="detailsLabel" runat="Server"></asp:Label>
<asp:Label ID="Label1" runat="Server"></asp:Label>
<asp:Label ID="Label2" runat="Server"></asp:Label>
<asp:Label ID="Label3" runat="Server"></asp:Label>
<asp:GridView ID="TableGridView"  
    OnRowEditing ="TableGridView_RowEditing" 
    OnRowCancelingEdit="TableGridView_RowCancelingEdit" 
    OnRowUpdating="TableGridView_RowUpdating"
    OnRowDeleting="TableGridView_RowDeleting"
    OnPageIndexChanging="TableGridView_PageIndexChanging"      
    runat="server"  AutoGenerateColumns="False" 
    style="z-index: 100; left: 1px; position: absolute; top: 299px; font-size:smaller" 
    AllowPaging="True" AllowSorting="True" >
</asp:GridView>
<asp:Panel ID="MsgPanel" style="left: 1px; position:relative; top: 129px; z-index: 114;" runat="server" Height="51px" Width="643px" Visible="False">
<div style=" overflow:auto; width:731px; height:223px; left: 6px; position:relative; top: 3px; z-index: 116; ">
<asp:Label ID="msg_lbl" runat="server" Text="" style="left: 6px; position: absolute; top: 3px" ></asp:Label>
</div>
<asp:LinkButton ID="msg_button" runat="server" OnClick="msg_button_Click" style="z-index: 114; left: 1px; position: absolute; top: 176px">Hide Message</asp:LinkButton>
</asp:Panel>
</div>
</form>
</div>
</td>
</tr>
</table>
<!--END PAGE SECTION CODE-->
</body>
</html>
//****************************************************
// Class Conn.cs
//****************************************************
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using ASPEnterpriseManager;
namespace Combine
{
    public static class conn
    {
        //build connection
        public static string GetConnectionString(string catalogName)
        {
            string connectionString;
            string connectionString2;
            string MyString = null;
            string newVariable = "sqlexpress;uid=sa;pwd=h6web97;";
            string newVariable1 = "server=";
            string newVariable12 = "Initial Catalog=" + catalogName;
            string newVariable13 = ";Asynchronous Processing=true";
            connectionString = newVariable1 + System.Net.Dns.GetHostName() + "\\" + newVariable + newVariable12 + newVariable13;
            return connectionString;
        }
        public static string GetConnectionString2()
        {
            string connectionString;
            string connectionString2;
            string MyString = null;
            string newVariable = "sqlexpress;uid=sa;pwd=h6web97;";
            string newVariable1 = "server=";
            string newVariable13 = "Asynchronous Processing=true";
            connectionString2 = newVariable1 + System.Net.Dns.GetHostName() + "\\" + newVariable + newVariable13;
            return connectionString2;
        }
        public static string MyString()
        {
            throw new Exception("The method or operation is not implemented.");
        }
    }
}
//****************************************************
// App_Code DynamicallyTemplatedGridViewHandler.cs
//**************************************************** 
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Specialized;
using System.Data.SqlClient;
 
public class DynamicallyTemplatedGridViewHandler : ITemplate
{
    #region data memebers
    ListItemType ItemType;
    string FieldName;
    string InfoType;
   #endregion
    #region constructor
    public DynamicallyTemplatedGridViewHandler(ListItemType item_type, string field_name, string info_type)
    {
        ItemType = item_type;
        FieldName = field_name;
        InfoType = info_type;
    }
    #endregion
    #region Methods
    public void InstantiateIn(System.Web.UI.Control Container)
    {
        switch (ItemType)
        {
            case ListItemType.Header:
                Literal header_ltrl = new Literal();
                header_ltrl.Text = "<b>" + FieldName + "</b>";
                Container.Controls.Add(header_ltrl);
                break;
            case ListItemType.Item:
                switch (InfoType)
                {
                    case "Command":
                        ImageButton edit_button = new ImageButton();
                        edit_button.ID = "edit_button";
                        edit_button.ImageUrl = "~/images/edit.gif";
                        edit_button.CommandName = "Edit";
                        edit_button.Click += new ImageClickEventHandler(edit_button_Click);
                        edit_button.ToolTip = "Edit";
                        Container.Controls.Add(edit_button);
                        ImageButton delete_button = new ImageButton();
                        delete_button.ID = "delete_button";
                        delete_button.ImageUrl = "~/images/delete.gif";
                        delete_button.CommandName = "Delete";
                        delete_button.ToolTip = "Delete";
                        delete_button.OnClientClick = "return confirm('Are you sure to delete the record?')";
                        Container.Controls.Add(delete_button);
                        ImageButton insert_button = new ImageButton();
                        insert_button.ID = "insert_button";
                        insert_button.ImageUrl = "~/images/insert.bmp";
                        insert_button.CommandName = "Edit";
                        insert_button.ToolTip = "Insert";
                        insert_button.Click += new ImageClickEventHandler(insert_button_Click);
                        Container.Controls.Add(insert_button);
                        break;
                    default:
                        Label field_lbl = new Label();
                        field_lbl.ID = FieldName;
                        field_lbl.Text = String.Empty; //we will bind it later through 'OnDataBinding' event
                        field_lbl.DataBinding += new EventHandler(OnDataBinding);
                        Container.Controls.Add(field_lbl);
                        break;
                }
                break;
            case ListItemType.EditItem:
                if (InfoType == "Command")
                {
                    ImageButton update_button = new ImageButton();
                    update_button.ID = "update_button";
                    update_button.CommandName = "Update";
                    update_button.ImageUrl = "~/images/update.gif";
                    if ((int) new Page().Session["InsertFlag"] == 1)
                         update_button.ToolTip = "Add";
                    else
                         update_button.ToolTip = "Update";
                    update_button.OnClientClick = "return confirm('Are you sure to update the record?')";
                    Container.Controls.Add(update_button);
                    ImageButton cancel_button = new ImageButton();
                    cancel_button.ImageUrl = "~/images/cancel.gif";
                    cancel_button.ID = "cancel_button";
                    cancel_button.CommandName = "Cancel";
                    cancel_button.ToolTip = "Cancel";
                    Container.Controls.Add(cancel_button);
                }
                else// for other 'non-command' i.e. the key and non key fields, bind textboxes with corresponding field values
                {
                    TextBox field_txtbox = new TextBox();
                    field_txtbox.ID = FieldName;
                    field_txtbox.Text = String.Empty;
                    // if Inert is intended no need to bind it with text..keep them empty
                    if ((int)new Page().Session["InsertFlag"] == 0)
                        field_txtbox.DataBinding += new EventHandler(OnDataBinding);
                    Container.Controls.Add(field_txtbox);
                }
                break;
        }
    }
    #endregion
    #region Event Handlers
    //just sets the insert flag on so that we ll be able to decide in OnRowUpdating event whether to insert or update
    protected void insert_button_Click(Object sender, EventArgs e)
    {
        new Page().Session["InsertFlag"] = 1; 
    }
    //just sets the insert flag OFF so that we ll be able to decide in OnRowUpdating event whether to insert or update 
    protected void edit_button_Click(Object sender, EventArgs e)
    {
        new Page().Session["InsertFlag"] = 0;
    }
    private void OnDataBinding(object sender, EventArgs e)
    {
        object bound_value_obj = null;
        Control ctrl = (Control)sender;
        IDataItemContainer data_item_container = (IDataItemContainer)ctrl.NamingContainer;
        bound_value_obj = DataBinder.Eval(data_item_container.DataItem, FieldName);
        switch (ItemType)
        {
            case ListItemType.Item:
                Label field_ltrl = (Label)sender;
                field_ltrl.Text = bound_value_obj.ToString();
                break;
            case ListItemType.EditItem:
                TextBox field_txtbox = (TextBox)sender;
                field_txtbox.Text = bound_value_obj.ToString();
                break;
        }
    }
    #endregion
}

Open in new window

GridView021.jpg
0
Comment
Question by:homeshopper
  • 2
  • 2
5 Comments
 
LVL 8

Expert Comment

by:deepu chandran
ID: 26062103
Hi,

I guess you are trying to update Rec_Id Column,Which is an auto number, You cannot update auto number field,
GenerateUpdateQuery() has some bugs.

I recommend you to change the code such a way that all your database operations sparatly (Can be a separate file with Static member function). The readability will increase

Regards
Deepu

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26062109
>Cannot update identity column 'rec_ID'
exactly. you cannot update the value for that column .

so, the function: string GenerateUpdateQuery()
must exclude the identity column(s), in this case, rec_id
0
 

Author Comment

by:homeshopper
ID: 26062708
Hi guys,
I sort of get the gist of what you suggest. I may be gabbing at straws!!
Different tables give different errors.
1. Incorrect syntax near the keyword 'Database'
2. The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
3. Cannot update identity column 'UniqueID'
I need to update the records without referencing the first field in a given table!!
your suggestion: must exclude the identity column(s), in this case, rec_id
if (Table.Columns[0].DataType.Name == "String" || Table.Columns[0].DataType.Name == "DateTime")
Is the above line the one needing alteration or delete it?
Thanks in advance, Ian.
if (temp_index > -1)
                ParameterArray[temp_index] = tempstr;
            if (Table.Columns[0].DataType.Name == "String" || Table.Columns[0].DataType.Name == "DateTime")
                Query = Query + " where " + Table.Columns[0].ColumnName + " = '" + ParameterArray[0] + "'";
            else
                Query = Query + " where " + Table.Columns[0].ColumnName + " = " + ParameterArray[0];
            return Query;
The section of code above, is this the part that needs altering?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 26062916
>1. Incorrect syntax near the keyword 'Database'

use [tablename]  resp [columnname] instead of just tablename/columnname, becauzse you see to have reserved keywords, aka:
Query = Query + " where [" + Table.Columns[0].ColumnName + "] = '" + ParameterArray[0] + "'";
Query = Query + " [" + Table.Columns[i].ColumnName + "]='" + ParameterArray[i] + "' ";

>2. The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
>The statement has been terminated.
I presume the value is in ParameterArray[i] contains some "datetime" representation... but in a format that cannot be implicitly converted. you will need to handle that somehow:
http://www.experts-exchange.com/articles/Database/Miscellaneous/DATE-and-TIME-don't-be-scared-and-do-it-right-the-first-time.html

>3. Cannot update identity column 'UniqueID'
the issue is not in the "where" part.
presuming the field that is the identity is ALWAYS the first field, you shall need:
for (i = 1; i < Table.Columns.Count; i++)






0
 

Author Comment

by:homeshopper
ID: 26063857
Thanks.
I made the alterations as per your suggestions.
It's brilliant, works well.
Your suggested article was very informative.
I now understand the cause of the problem.
Regards, Ian.
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question