How to solve the maximum lenght error( it is too long ) in sql query c#

Question

I have a method which includes sql statement . it is

    public Boolean addRSS(string RSS_title, string Description, DateTime datetime, string RSS_Link, string user_name, float rate)
    {


        // Console.WriteLine(MyString.Remove(5, 10));
       // string a = date.ToString().Replace('.', '-');
        Boolean res = false;
        string sql = "INSERT INTO My_RSS ( RSS_Title,RSS_Description,RSS_Date,RSS_Link,RSS_Rate,UserName) values('" 
            +
            RSS_title + "','" +
            "FFFFFFFFFFFFFFFFFFFFFFFFFAAASDASDASDASD" +
            "', SYSUTCDATETIME(),'" +
            RSS_Link + "'," +
            rate + ",'"+
            user_name +  
            "')";
        try


        {


            // hasan = hasan.Insert(c, hasan);

            SqlCommand cmd = new SqlCommand(sql, Connect());
            cmd.ExecuteNonQuery();
            res = true;
        }
        catch (Exception)
        {
            res = false;
        }

        return res;

    }

It gives the error when I try to enter this input http://rss.feedsportal.com/c/32727/f/510887/s/1da50441/l/0Lekonomi0Bmilliyet0N0Btr0Cenflasyon0Eyuzde0E50Ee0Einene0Ekadar0Esikacak0E0Cekonomi0Cekonomidetay0C210B0A30B20A120C15181930Cdefault0Bhtm/story01.htm to "link column" and it gives error which is Incorrect syntax near 'e'. The identifier that starts with 'Lekonomi0Bmilliyet0N0Btr0Cenflasyon0Eyuzde0E50Ee0Einene0Ekadar0Esikacak0E0Cekonomi0Cekonomidetay0C210B0A30B20A120C15181930Cdefau' is too long. Maximum length is 128. Unclosed quotation mark after the character string ')'.

Also,In the sql side this colum is varchar(455)

Solution 1

The error is saying that the identifier name is too long; this combined with the unclosed quotation mark error means you probably missed an opening quote. That is, you have this:

INSERT INTO Foo ( A ) VALUES ( AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')

instead of

INSERT INTO Foo ( A ) VALUES ( 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')

You shouldn't be building your queries via string concatentation; this is one of the reasons. Parameterized queries will get the quoting right for you. (Note: you don't need to be using stored procs to use parameterized queries.)

var sql = "INSERT INTO My_RSS ( Title, Description, Date, Link, Rate, Name )
           VALUES ( @Title, @Desc, @PostDate, @Link, @Rate, @Name )";

SqlCommand cmd = new SqlCommand(sql, Connect());
cmd.Parameters.Add("@Title", SqlDbType.VarChar, 100).Value = RSS_title;
cmd.Parameters.Add("@Desc", SqlDbType.VarChar, 8192).Value = RSS_description;
cmd.Parameters.Add("@PostDate", SqlDbType.SmallDateTime).Value = DateTime.Now;
cmd.Parameters.Add("@Rate", SqlDbType.Int).Value = rate;

etc.

Solution 2

You Can also add SET QUOTED_IDENTIFIER OFF before 'sql' string and SET QUOTED_IDENTIFIER On after 'sql'

QUOTED IDENTIFIER ON/OFF: This specifies the setting for usage of double quotation. IF this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.

sql = "SET QUOTED_IDENTIFIER OFF " + sql + " SET QUOTED_IDENTIFIER OFF ";

SqlCommand cmd = new SqlCommand(sql, Connect());
cmd.ExecuteNonQuery();
res = true;

You should use this in this case.

Comments

[Michael Edenfield]

The third parameter is only needed for types that have variable length, like varchar, and its only a hint to the query parser how big your strings can be (e.g. the parameter can be smaller and it will be fine; it the parameter is bigger you might get truncation errors). Typically you make them match the field definitions. For int, date, etc. you leave the third parameter out; I'll put a date param in to show you.