Link to home
Start Free TrialLog in
Avatar of Robert Becskei
Robert Becskei

asked on

Dynamically Created Query

Hello Everyone ,

I have dynamically Created a Query at runtime ( I need it because I am using it with Indy , in a multithreaded way )
All was working fine till now . The Field (Gestellname) is nvarchar(50) in MSSQL , for some unknown reason to me it seems to be defaulting to Integer somehow . I don't know if it is because the first few hundred entries are numeric only ?

Anyway the code part :

            CoInitialize(nil);

            myConnect := TADOConnection.Create(nil);

            try
              myConnect.Provider := 'SQLOLEDB.1';
              myConnect.LoginPrompt:=false;
              myConnect.ConnectionString:= 'FILE NAME='+ExtractFilePath(Application.ExeName)+'\teutodb.udl';
              myConnect.Connected:=true;

              Qry := TADOQuery.Create(nil);
                try
                  Qry.Connection:=myConnect;
                  Qry.CursorLocation:= clUseServer;
                  Qry.LockType := ltReadOnly;

                  if Qry.Active then Qry.Close;
                  Qry.SQL.Clear;

                  Qry.SQL.Add('select Gestellname,Typ,Kuid,Kunde,Adresse,Ort,PLZ,Land,Werk,Glaeser,Lieferschein,LKW,OR_TOUR,');
                  Qry.SQL.Add('convert(varchar,cast(Liefertag as Date),104) as Liefertag,c2.Modifier,c2.ModifyDate,c2.Computer,');
                  Qry.SQL.Add('DATEDIFF(day,Liefertag,getdate()) as Tage,Planned,PlannedLieferschein,Plannedby,convert(varchar,cast(PlannedDate as Date),104) as PlannedDate,PlannedFailed,');
                  Qry.SQL.Add('Extern,c3.Name,c1.OwnerID,c1.Construction,c4.Gestell as Email');
                  Qry.SQL.Add('from Ladelisten_GestellStammKopf c1');
                  Qry.SQL.Add('inner join LadeListen_GestellStammPos c2 on c1.ID=c2.HeadID');
                  Qry.SQL.Add('left join LadeListen_GestellEigentuemer c3 on c1.OwnerID=c3.ID');
                  Qry.SQL.Add('left join LadeListen_GestellKunden c4 on c2.Kuid=c4.HeadID');
                  Qry.SQL.Add('where MIX=1 and OutSide=1 and Planned=0 and Free=0');
                  Qry.SQL.Add('and Gestellname='+Gestellname+' and Kuid='+kuid+' and ltrim(rtrim(Gestell))='+quotedstr(email));
                  Qry.SQL.Add('order by DATEDIFF(day,Liefertag,getdate()),Gestellname asc');

                  Qry.Open;

Open in new window


Obviously the Free is missing , I thought I post only the relevant part . I have no idea how can I force Qry field Gestellname to default to WideString , nvarchar(50) , I even tried to convert it in the query itself . No change .
What even stranger is you see that I select only the Gestellname , kuid, gestell . And on the first try I am doing 2002,10001,something@somewhere.net . And for some unknown reason I still get the error converting F0001 to integer , this Rack F0001 is not even selected , it is there in the table though ....

Please help.

Thank you
ASKER CERTIFIED SOLUTION
Avatar of Geert G
Geert G
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Robert Becskei
Robert Becskei

ASKER

I must confess I do not understand why is it working now ...

I though it was the same thing when I've built the query by sql add ...

or was that the reason for the integer error ? it thought somehow that because I gave it a number
that the Gestellname is integer?

Thanky you very much!!!!
when you use a parameter, it does implicit conversion to the correct type

a text, is just a text ...
and this forces the database to do implicit conversions

for nvarchar ...
 Gestellname=N'+QuotedStr(Gestellname)+'

but in essence, this is just another performance problem