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)

I'm willing to bet money `RSS_title` contains a `'` character, make sure you escape them (`RSS_title.Replace("'","''")`). You *should* be using parametrized queries of course, but that'll get you fixed in 10sec for now. — Blindy, Mar 21 '12 at 14:01
@Blindy Why recommend a 10sec fix instead of the correct one? — dmck, Mar 21 '12 at 14:03
Fix first, make pretty when you don't have 6 projects swimming around you at the same time? On that note I need another coffee... — Blindy, Mar 21 '12 at 14:06

Michael Edenfield · Accepted Answer · 2012-03-21T16:53:49.453

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.

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. — Michael Edenfield, Mar 21 '12 at 16:51

score 8 · Answer 2 · answered May 14 '13 at 06:47

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.

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

2 Answers2

Linked