ASP.Net DataColumn Error: Cannot set Column 'DataList'. The value violates the MaxLength limit of this column

Last Reply 6 months ago By dharmendr

Posted 6 months ago

Hi sir...

this is regarding the query which you have provided me. i export the data to excel. Before that i got the mesage as nvarchar max for datalist limit reached and then again messagebox appears as exporting completed. 

i exported lakhs of data into excel. From the row 13776,we can see that i am getting the ids of the exhibitorlist instaead of name of the exhibitorlist.

this is the solution which you have provided me in my previous question.

                if (ckb_countrycount.Checked == true)//added by chetan
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string coun = dt.Rows[i]["Datalist"].ToString();
                        if (coun == "" || coun == null)
                        {
                            dt.Rows[i]["Datalist"] = 0;
                        }
                        else
                        {
                            conn.Open();
                            SqlCommand command2;
                            //string qury = "SELECT exhibitiorlist from exibitions where EID in(" + coun + ")";
                            //  string qury = "SELECT STUFF((SELECT N',' + [exhibitiorlist] FROM exibitions WHERE EID in(" + coun + ") FOR XML PATH(''),TYPE).value('text()[1]','NVARCHAR(MAX)'),1,1,N'') Exhibitorlist";
                            string qury = "SELECT STUFF((SELECT N',[' + exhibitiorlist + ']' FROM exibitions WHERE EID in(" + coun + ") FOR XML PATH(''),TYPE).value('text()[1]','NVARCHAR(MAX)'),1,1,N'') Exhibitorlist";
                            command2 = new SqlCommand(qury, conn);
                            string final_ex_count = command2.ExecuteScalar().ToString();
                            dt.Rows[i]["Datalist"] = final_ex_count;
                            conn.Close();
                        }

                    }
                }

 

string qury = "SELECT STUFF((SELECT N',[' + exhibitiorlist + ']' FROM exibitions WHERE EID in(" + coun + ") FOR XML PATH(''),TYPE).value('text()[1]','NVARCHAR(MAX)'),1,1,N'') Exhibitorlist";

 

Posted 6 months ago

Hi chetan,

Set the MaxLength property for Datalist column after filling the DataTable.

C#

dt.Columns["Datalist"].MaxLength = int.MaxValue;

VB.Net

dt.Columns("Datalist").MaxLength = Integer.MaxValue