LINQ to SQL Update (C#)

Question

Hello all,

This is the script of my table:

CREATE TABLE ClientTypes
(
    type_id int PRIMARY KEY IDENTITY,
    type_name varchar(250) not null,
    type_applications_list text,
    dtIntro datetime DEFAULT(getdate())
)

---

And in ASP.net i'm trying to do this:

protected void btnActualizar_Click(object sender, EventArgs e)
    {
        var aplicacao = (from apl in dc.ClientTypes
                         where apl.type_id == tipoCliente
                         select apl).Single();

        aplicacao.type_name = txtAplicações.Text.ToString();

        dc.SubmitChanges();
    }

However, when it runs, it crashes and says:

"The data types text and varchar are incompatible in the equal to operator."

I really don't want to change the SQL Datatype to varchar, i would like it to stay in text. I have made some tests, with other datatype values, like int... and everything went well.

I really don't understand this, has im using a control which returns a String.

Thx in advance

Can anyone help me? Thx in advance.

Answer 1

Try this change:

aplicacao.type_name = txtAplicações.Text;

I assume you are getting the exception at this line? It seems that the Linq query is fine, provided that tipoCliente is an int (even if it was not you would get a different error).

Answer 2

MSDN states that you should not use the text data type as it will be removed in future versions of SQL Server. The text data type cannot be compared or sorted so if you want to compare in your LINQ query, you'll have to change the type.

Answer 3

I wonder if the update statement that LINQ generates is automatically adding in a line for type_applications_list = "Whatever it equals" for you. If so, it may be causing the problem.

Would you try expanding your statement with the line:

aplicacao.type_applications_list = 
    aplicacao.type_applications_list.Substring(0, 2000);

Or some other big number in the substring. Just to see if it works then.

Alternatively, if you are using SQL2005 or greater you could try converting the TEXT data type to a Varchar(MAX). I know you'd prefer not to so please try my first idea first.

Answer 4

It's just a guess, but I wonder if you're running into a problem with Optimistic Concurrency checking on the type_applications_list property. In your DBML file, check to see if type_applications_list property has UpdateCheck set to something besides "Never"; If so, try changing it to "Never".

Here's an explanation of Linq to SQL Optimistic Concurrency