CODEWITHSUNDEEP
c#.netsql-server
jjctw1969
jjctw1969

Reputation: 59

C# SQL query run time error using Nuget System.Data.SqlClient?

This is my code:

if (!(string.IsNullOrEmpty(findIt)))
{
    string query = @"select top (@Num) * from dbo.main where (Data = @FindIt) ";         

    // Data-> ntext
    DataTable table = new DataTable();
    SqlDataReader myReader;

    using (SqlConnection myCon = new SqlConnection(sqlDataSource))
    {
        myCon.Open();

        using (SqlCommand myCommand = new SqlCommand(query, myCon))
        {
            myCommand.Parameters.AddWithValue("@Num",limit);
            myCommand.Parameters.Add(new SqlParameter("@FindIt", SqlDbType.NText)).Value = findIt;

            myReader = myCommand.ExecuteReader(); // Run time error

            myReader.Close();
            myCon.Close();
        }
    }
}

Error message:

System.Data.SqlClient.SqlException: The data types ntext and ntext are incompatible in the equal to operator.

I don't know what it means and how to fix it

Upvotes: 1

Views: 175

Answers (1)

user18387401
user18387401

Reputation: 2562

The ntext data type does not support equality comparisons. That data type has been deprecated for a long time and should not be used in a new database and should be changed in an existing database. Use nvarchar instead if you possibly can. If you really must use ntext in the database, you have three options in your query:

  • Convert the database value to nvarchar and use nvarchar for your parameter, then perform an equality comparison.
  • Perform a LIKE comparison.
  • Use the PATINDEX function to effectively do a LIKE comparison and get the index of the match, which should be 1 for equality.

Note that a LIKE comparison with no wildcards is effectively an equality comparison.

Upvotes: 2

Related Questions