1

Please assist,

I have been getting this error whenever I pass values to a parameter using code similar to:

parameters.paramByName('ParamVal1').value := trim(TextBox1.Text);
parameters.paramByName('ParamVal2').value := trim(TextBox2.Text);

So, if one of the text boxes is blank, then the error:

The data types varchar and text are incompatible in the greater than or equal to operator

appears.

What could be wrong?

After Running the sqlprofiler as suggested below

exec sp_executesql N'Select * from BLOtherChargeDetails 

where Field1 = @P1 and Field2 >= @P2 and Field2 <= @P3 and
Field3 >= @P4 and Field3 <= @P5 and
Field4 >= @P6 and Field4 <= @P7 and 
Field5 = @P8

Order By Field2, Field3
',N'@P1 varchar(50),@P2 text,@P3 varchar(5),@P4 text,@P5 varchar(5),@P6 varchar(8),@P7 varchar(8),@P8 smallint','Value_P1','','ZZZZZ','','ZZZZZ','20130401','20130611',0
go
KE50
  • 546
  • 1
  • 7
  • 17
  • 2
    It would help if you give some more information about what data type the parameters are, but the error is fairly clear: somewhere you are comparing `varchar` and `text` directly, which is not possible. If you aren't sure where this is happening, using SQL Profiler to trace the execution on the server might help. Although since `text` is now [deprecated](http://msdn.microsoft.com/en-us/library/ms187993.aspx) you may wish to consider changing to `(n)varchar(max)` instead. – Pondlife Jun 06 '13 at 18:41

3 Answers3

1

This isn't so much a Delphi limitation as a SQL Server limtation.

What you are doing is something linke this:

select Expression 
from YourTable
where YourTable.TextColum = 'VarCharLiteral'

but using parameters:

select Expression 
from YourTable
where YourTable.TextColum = :VarCharParameter

For both character literals and character parameters, SQL Server will pick VARCHAR or NVARCHAR as the data type.

SQL Server does not allow you to have TEXT or NTEXT parameters, so either character literals or character parameters will get a data type of VARCHAR or NVARCHAR.

There are two workarounds, adapted from SQL SERVER – Fix: Error : 402 The data types ntext and varchar are incompatible in the equal to operator

  • Move away from TEXT colums.

This is the preferred way as TEXT and NTEXT have been deprecated for a while, see nvarchar(max) vs NText

For that yo have to alter your table like this:

alter table YourTable
alter column TextColum NVARCHAR(MAX)
  • Cast your TEXT column to VARCHAR

This is the easiest to get working and is a short-term solution.

select Expression 
from YourTable
where convert(NVARCHAR(MAX), YourTable.TextColum) = :VarCharParameter
Community
  • 1
  • 1
Jeroen Wiert Pluimers
  • 23,965
  • 9
  • 74
  • 154
  • Thanks Jeroen for the insightful answer, but the database fields are varchar(50), after checking using sqlprofiler, it appears only the blank TextBoxes are being parsed as Text. Again, why could this be? – KE50 Jun 11 '13 at 18:42
  • Can you put the following in your question: 1) the DDL for your table (i.e. the `CREATE TABLE` for it). 2) which Delphi components you use for accessing your table. 3) some sample data. 4) the design-time text (just copy-paste your query from your TForm/TDataModule and paste it here) – Jeroen Wiert Pluimers Jun 12 '13 at 15:00
1

Finally got it working;

For some strange reason, some select queries with parameters always give an error, despite the statement being correct.

Testing during design time, the error "Invalid Parameter" appears.

The Fix:

  1. Remove the parameters {during design time} i.e. the section having

    "where field1 = :parameter1 and field2 >= :parameter2"

    then activate the TADOQuery, by changing the active status to active.

  2. Return the parameters {during design time} and then activate the TADOQuery again.

Try running the query during runtime and this time it should work.

KE50
  • 546
  • 1
  • 7
  • 17
  • +1 This reveals the cause to be the opposite of my answer: your fields are `VARCHAR` or `NVARCHAR`, but the design-time Delphi Parameters indicate a field type of `TEXT`. It still fails as they are incompatible, but just the other way around. One scenario that can cause this is that the code was originally made in a Delphi version that still assumed anything text-like in a parameter had to be tagged as type `TEXT` – Jeroen Wiert Pluimers Jun 12 '13 at 15:04
0

The data types varchar and text are incompatible

Just had this problem using ftString. Changed the parameter type to ftFixedChar with a size of 50 (my size requirement), and all was good thereafter.

EDIT

I modified the query to specify a variable for use within the query expression instead of the parameter:

DECLARE @MyVar nvarchar(50)
SET @MyVar = :MyDelphiVal

SELECT ...
WHERE mycol LIKE ISNULL(@MyVar, N'') + '%'

which permitted the ftString parameter type.

BTW, Delphi 2010, SQL2008 R2, ADO

bvj
  • 3,294
  • 31
  • 30