NVARCHAR The size (8000) given to the parameter exceeds the maximum allowed (4000)

When building dynamic TSQL query string with nvarchar type in MS SQL Server, it is possible to exceed the limit  of 4000 characters, this will causes query string to be truncated and fail to run.

I have no luck with ntext type : The text, ntext, and image data types are invalid for local variables.

varchar is the correct type to use — it has a  limit of 8000 characters

you can define your query string as below:

declare @@querystring varchar(max)


declare @@querystring varchar(8000)


MVC2 LoadingElementID not displaying in Safari

I have a MVC2 project that I am using Ajax form, ajax indicator div (LoadingElementId=”indicator”) displays perfectly in IE, Firefox and Chrome, but it doesn’t display in Safari.

Solution:  change “display:none;” to “visibility:hidden;” in ajax indicator’s css class. Now it works for all browsers above.

MVC2 Ajax not working in Firefox – Error “a is undefined”

I was using MVC2 Ajax function with “this.form.onsubmit()”, it works on IE but not firefox, it causes an error “a is undefined” in /scripts/MicrosoftAjax.js    Line 6

if I use /scripts/MicrosoftAjax.debug.js , it says “Sys.ArgumentUndefinedException: Value cannot be undefined. Parameter name: eventObject”

The solution is here http://forums.asp.net/t/1515105.aspx/2/10  solved by tpeczek.

1. add a hidden submit button in the form, for example:

<inputtype=submit id=”AJAXSubmitButton”name=”AJAXSubmitButton”value=”Update”style=”display:none;/>

2. change ajax call function to


so you are forcing the submit button to be clicked, to submit the ajax form.



use Colobox with ASP.NET Ajax

 Colorbox is built on Jquery library,  by default, the colorbox link only works when the first time it loads or postback etc.  However ASP.NET Ajax is partial postback,  so after ajax update you may find colobox are not working any more.

Below post describes the problem on Jquery with Ajax update panel and provide a good solution


It can be used on colorbox as well, open jquery.colorbox.js  and add a function “pageLoad” ,and copy the content from “$(document).ready(function(){” to “pageLoad”

     $(“.example7″).colorbox({width:”80%”, height:”80%”, iframe:true});
function pageLoad(sender,args){

if (args.get_isPartialLoad())

       $(“.example7″).colorbox({width:”80%”, height:”80%”, iframe:true});





Null Value in first row causes data lost in whole dataset – Provider=Microsoft.Jet.OLEDB.4.0

I have a Comma delimited text file wit no header, the first row is the first data record. I am using OleDB to receive a dataset

conn_csv_str = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + filepath + “;Extended Properties=’text;FMT=Delimited;HDR=No’;


I found if I have null value in the first row, for example in addressline column, the rest of the records will be null in addressline column as well!

After some reseach I found the answer on MS website


The Oledb driver is trying to determine the data type of each column, it must get confused by null value in the first row, so it ignore values for the rest of the rows.

The solution is to create a schema.ini file in the same directory with your text file, and before you retrive dataset, add configuration lines into schema.ini file. 

for example, if my file is called helloworld.txt, there are 4 columns in my file: firstname, lastname, adressline,age

I will call below method:




publicvoid AddTXTFileSchema(string filepath, string schemafilename, string

FileStream fs = newFileStream(filepath + “/” + schemafilename, FileMode.Append, FileAccess

StreamWriter sw = newStreamWriter
sw.WriteLine(“[“ + filename + “]”
sw.WriteLine(“Col1=c_firstname Text”
sw.WriteLine(“Col2=lastname Text”
sw.WriteLine(“Col3=addressline Text”
sw.WriteLine(“Col4=age Short”);






notice you can also specify the delimiter (tab, csv, customer etc), after [filename] line, it is very handy if you are using customer delimiter for example  pipe “|”.

The content of schema.ini will keep grouping as you handle more files, it is also a good idea to do housekeeping to clear schema.ini content once a while.

After you add entries to schema.ini file, you can use oledb to retrieve dataset .





publicDataSet ParseTXTFileWithNoHeader(string filepath, string


DataSet ds = newDataSet



conn_csv_str;conn_csv_str = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + filepath + “;Extended Properties=’text;FMT=Delimited;HDR=No'”;






obj_oledb_da;obj_oledb_con = newOleDbConnection
sql_select = “select * from [“ + filename + “]”


obj_oledb_da =

newOleDbDataAdapter (sql_select, obj_oledb_con); 



return ds;



// Parse Text File with No Header

.NET convert hyperlink to url string

try converting html code to text version? below code will remove all html tags

Dim objRegEx As System.Text.RegularExpressions.Regex
               mystring= objRegEx.Replace(mystring, "<[^>]*>", "")

But what if I also need to extract and keep all the urls from <a>tags.
I write a recursion function to convert all <a></a> to simple url string, it is in VB because the original project was developed in VB, yes I miss C# syntax 😦

Public Function ConvertHyperlinksToText(ByVal source As String, ByVal startIndex As Integer) As String

        Dim result As String   ' Result to return
        result = source

        ' wrap the extracted URL string , for example you can use (),
        ' so "<a href="http://www.google.com">Google</a>" will become "(http://www.google.com)"
        ' in this example I use space before and after the url

        Dim urlLeftWrapperString As String = " "
        Dim urlRightWrapperString As String = " "

        ' Tag Start & End
        Dim tagStart As String = "<a "
        Dim tagEnd As String = "</a>"

        ' URL link Start & End, surround by double quote: href="http://www.google.com"
        Dim doubleQuoteLinkStart As String = " href="""
        Dim doubleQuoteLinkEnd As String = """"

        ' URL link Start & End, surround by single quote: href='http://www.google.com'
        Dim singleQuoteLinkStart As String = " href='"
        Dim singleQuoteLinkEnd As String = "'"

        Dim pos_tagStart As Integer
        Dim pos_tagEnd As Integer

        ' find position of tagStart and tagend

        pos_tagStart = result.ToLower().IndexOf(tagStart, startIndex)
        pos_tagEnd = result.ToLower().IndexOf(tagEnd, startIndex)

        ' Find the next  tag block

        If pos_tagStart > -1 And pos_tagEnd > -1 And pos_tagEnd > pos_tagStart Then
            Dim stringLeft As String
            Dim stringTagBlock As String
            Dim stringRight As String

            stringLeft = result.Substring(0, pos_tagStart)
            stringTagBlock = result.Substring(pos_tagStart, pos_tagEnd + 4 - pos_tagStart)   ' content in 
            stringRight = result.Substring(pos_tagEnd + 4, result.Length - (pos_tagEnd + 4))    '  is 4 characters

            If stringTagBlock <> "" Then

                Dim findHref As Boolean = False

                ' try searching for Double Quote surrounded href attribute:  href="...." start

                Dim pos_doubleQuoteLinkStart As Integer
                Dim pos_doubleQuoteLinkEnd As Integer

                pos_doubleQuoteLinkStart = stringTagBlock.ToLower().IndexOf(doubleQuoteLinkStart)
                pos_doubleQuoteLinkEnd = stringTagBlock.ToLower().IndexOf(doubleQuoteLinkEnd, pos_doubleQuoteLinkStart + 7)

                If pos_doubleQuoteLinkStart > -1 And pos_doubleQuoteLinkEnd > -1 And pos_doubleQuoteLinkEnd > pos_doubleQuoteLinkStart Then

                    Dim URL As String

                    URL = stringTagBlock.Substring(pos_doubleQuoteLinkStart + 7, pos_doubleQuoteLinkEnd - (pos_doubleQuoteLinkStart + 7)) ' " href=""  -  7 characters

                    ' concatenation result String :   stringLeft +  + URL +  + stringRight

                    result = stringLeft & urlLeftWrapperString & URL & urlRightWrapperString & stringRight
                    startIndex = ((stringLeft & urlLeftWrapperString & URL & urlRightWrapperString).Length) 'just set startIndex to the end of the tagEnd

                    findHref = True

                End If

                'if we haven't find double quote surrounded href attribute
                ' try searching for Single Quote surrounded href attribute:  href='....' start

                If findHref = False Then

                    Dim pos_singleQuoteLinkStart As Integer
                    Dim pos_singleQuoteLinkEnd As Integer

                    pos_singleQuoteLinkStart = stringTagBlock.ToLower().IndexOf(singleQuoteLinkStart)
                    pos_singleQuoteLinkEnd = stringTagBlock.ToLower().IndexOf(singleQuoteLinkEnd, pos_singleQuoteLinkStart + 7)

                    If pos_singleQuoteLinkStart > -1 And pos_singleQuoteLinkEnd > -1 And pos_singleQuoteLinkEnd > pos_singleQuoteLinkStart Then

                        Dim URL As String

                        URL = stringTagBlock.Substring(pos_singleQuoteLinkStart + 7, pos_singleQuoteLinkEnd - (pos_singleQuoteLinkStart + 7)) ' " href='"  -  7 characters

                        ' concatenation result String :   stringLeft +  + URL +  + stringRight

                        result = stringLeft & urlLeftWrapperString & URL & urlRightWrapperString & stringRight

                        startIndex = ((stringLeft & urlLeftWrapperString & URL & urlRightWrapperString).Length) 'just set startIndex to the end of the tagEnd

                        findHref = True

                    End If

                End If

                If findHref = False Then ' didn't find herf , just set startIndex to the end of the tagEnd

                    startIndex = (pos_tagEnd + 4)

                End If


                ' if stringTagBlock is empty, just set startIndex to the end of the tagEnd
                startIndex = (pos_tagEnd + 4)

            End If

            ' if we still can't find href attribute or block content is empty, that is because  block is probably not well formatted
            ' so we just set startIndex from the end of this block, our recursion function is not dead-locked
            ' call this recursion function again
            result = ConvertHyperlinksToText(result, startIndex)


            ' no  block found

            Return result

        End If

        Return result

    End Function