Variable/Query problem

BITS

Hello,

I am using Fast Report 4 version 4.9.64
My issue is when I try to preview my the result of a query, I get an error.

DECLARE @serial varchar(30)
set @serial = :SERIALPARAM
select * from ServiceAccent_Temp where serial_no = @serial;

Variable is set to a STRING datatype and has <serialparam> as the VALUE

When I preview the report I get an error on the page which contains the databand with the above Query.

The following error(s) have occurred:
AdoqUERY2: adoqUERY2: Error in expression ???8500078Y???: ???;??? expected
ADOQuery2: Error in expression ???8500078Y???; ???;??? expected

The data (Serial numbers) I am pulling from the table contain 8 figures, 90% are all numbers but 10% have a letter at the end.

When I examine my report, I see that it gives me an error when it gets to a serial number with a letter at the end, in this case, ???8500078Y???. Up until this point, the report has preview???d perfectly!!

When I change the above query to

DECLARE @serial varchar(30)
set @serial = ???8500078Y
select * from ServiceAccent_Temp where serial_no = @serial;

My report works without error but obviously, all my results contain the same serial number??¦

What is wrong? I just cannot seem to get around this issue!?

Many thanks

Mark

Anu de Deus

Very simple: your query should treat the serial_no as string, not a number.
No ideas yet?
How about some quotation marks around @serial?

<!--fonto:Courier New--><span style="font-family:Courier New"><!--/fonto-->select * from ServiceAccent_Temp where serial_no = '@serial'<!--fontc--></span><!--/fontc-->


I'm not used to have parameters in my queries, so if that doesn't work (because of syntax), try something else to convince it is a string, like:


<!--fonto:Courier New--><span style="font-family:Courier New"><!--/fonto-->select * from ServiceAccent_Temp where serial_no LIKE @serial<;!--fontc--></span><!--/fontc-->

or ultimately, do a typecast in your Where clause itself

BITS

Hi,

Thanks for your help.

I tried the quotation marks around serial and your other suggestions but to no avail!

I get the same error or no error or reseult at all.

I've contacted FastReport incase it is a bug but after 3 emails, they still haven't got back to me so I'm pretty lost and overdue on a deadline with my report! Nightmare!

Anu de Deus

Did you try hardcoding like this, just to see if it runs?

select * from ServiceAccent_Temp where serial_no = '098098Y'
or
select * from ServiceAccent_Temp where serial_no like '098098Y'

BITS

Hi, yeah, I have tried hardcoding as you've put above and I use the serial number which appears in the error and a known working one and it does work (Obviosuly all results showing the hardcorded serial number). So now I'm just lost as to what is the cause of my issue.. The variable is down as a STRING so i don't know why it falls over when it gets to the first serial number with a letter in..

technisoft

BITS wrote: »

Hi, yeah, I have tried hardcoding as you've put above and I use the serial number which appears in the error and a known working one and it does work (Obviosuly all results showing the hardcorded serial number). So now I'm just lost as to what is the cause of my issue.. The variable is down as a STRING so i don't know why it falls over when it gets to the first serial number with a letter in..

In my experience, D2006, Firebird, FIBplus, you should NOT use quotation marks when assigning a string to a query parameter.
A possibility might be that some kind of conversion takes place, pure numbers padded while the ones with the letter are not?

To solve such problems in my past I have writen some code that writes the actual SQL text and its parameters to a text file, which in the case of a parametrized query must be done in the AfterOpen() event. Then I use a third party utility like IB_SQL, DBworkbench, to load the SQL text and play around with it.
The greatest advantage of it is that I am working with what the query actually gets and uses and not with what I think it gets and uses.
Using the third party utility also identifies whether the query itself or another program problem is the real problem.