Posts Tagged Sql Server

String or binary data would be truncated error from an Integer Field in SQL Server Database

Introduction

In this article I am going to explain an interesting problem in SQL Server.

Usually we will see the exception “String or binary data would be truncated” with string datatype columns.

For ex: If you have a column with Datatype Varchar (50) in the table and if you try to insert an entry with more than 50 characters you will get this exception.

To my surprise I faced the same error with an integer column and I tried multiple options to find out from where the exception is thrown. All my changes went in vain and I couldn’t fix with the changes I made in an integer column.

Solution:

After some time I started looking at the Triggers in the table and Bingo, that’s where the problem was and I was able to fix the issue.

In my case when I do an insert to Integer column, I was creating a larger string in trigger and for a particular combination it crossed the allowed 50 character limit which resulted in Error.

Note:

If you face exception during an insert to integer data type column, then First Check the Trigger for your table, the root cause of exception might be in trigger.

Advertisements

, , ,

Leave a comment

how to create Cascading Dropdown list in Asp.Net

Introduction

In this article I am going to explain how to create Cascading Dropdown list in Asp.net from database. The Cascading Dropdown list loads data based on the value selected in parent dropdown list.

For ex: if you have Dropdownlist1 and Dropdownlist2 then Dropdownlist2 will be populated based on the value selected in Dropdownlist1.

Complete Code:

To load data based on the value selected in dropdown list, we will be using the SelectedIndexChanged event in dropdown list and then load the data in dropdown list.

HTML:

In Dropdownlist1 we will be using selectedindexchanged event to fill the dropdownlist2.

asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
            <asp:UpdatePanel ID="UpdatePanel1" runat="server">
                <ContentTemplate>
                    <asp:DropDownList ID="DropDownList1" runat="server" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"
                        AutoPostBack="true">
                    </asp:DropDownList>
                    

                    <asp:DropDownList ID="DropDownList2" runat="server"></asp:DropDownList>
                </ContentTemplate>
            </asp:UpdatePanel>

C#:

First dropdownlist we will populate as we normally populate the dropdownlist from database like given below

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                //Load the first dropdownlist
                LoadDropDown();
            }
        }
        //This method is used to load the first dropdownlist
        public void LoadDropDown()
        {
            //Get your connection string
            string connstring = ConfigurationManager.ConnectionStrings["Adventure WorksConnectionString"].ToString();
            //Create connection here
            using (SqlConnection conn = new SqlConnection(connstring))
            {
                //Sample Query here; You need to update the query as per your need
                string sqlquery = "SELECT TOP 100 a.city as City FROM Person.Address a";
                //Create SQLCommand object
                using (SqlCommand cmd = new SqlCommand(sqlquery, conn))
                {
                    //Open the SQLConnection
                    conn.Open();
                    //Execute the query
                    SqlDataReader rdr = cmd.ExecuteReader();

                    //Assign the values to dropdownlist
                    DropDownList1.DataSource = rdr;
                    //Set the DataValueField of dropdownlist
                    DropDownList1.DataValueField = "City";
                    //Set the DataTextField of dropdownlist
                    DropDownList1.DataTextField = "City";
                    //Bind the dropdownlist
                    DropDownList1.DataBind();
                }
            }
        }

Populating the second Dropdown list:

Here basically we will get the dropdown list values from database based on the value selected in dropdownlist like given below

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Get your connection string
            string connstring = ConfigurationManager.ConnectionStrings["Adventure WorksConnectionString"].ToString();
            //Create connection here
            using (SqlConnection conn = new SqlConnection(connstring))
            {
                //Sample Query here; You need to update the query as per your need
                string sqlquery = "SELECT TOP 100 a.AddressLine1 as AddressLine FROM Person.Address a where a.City =@City";
                //Create SQLCommand object
                using (SqlCommand cmd = new SqlCommand(sqlquery, conn))
                {
                    //Open the SQLConnection
                    conn.Open();

                    //Pass the value selected in previous dropdownlist as parameter to this query
                    cmd.Parameters.AddWithValue("@City", DropDownList1.SelectedItem.Text);
                    //Execute the query
                    SqlDataReader rdr = cmd.ExecuteReader();

                    //Assign the values to dropdownlist
                    DropDownList2.DataSource = rdr;
                    //Set the DataValueField of dropdownlist
                    DropDownList2.DataValueField = "AddressLine";
                    //Set the DataTextField of dropdownlist
                    DropDownList2.DataTextField = "AddressLine";
                    //Bind the dropdownlist
                    DropDownList2.DataBind();
                }
            }
        }

Demo

, , , , ,

1 Comment