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 :
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@somew here.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
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;
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@somew
Please help.
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(G estellname )+'
but in essence, this is just another performance problem
a text, is just a text ...
and this forces the database to do implicit conversions
for nvarchar ...
Gestellname=N'+QuotedStr(G
but in essence, this is just another performance problem
ASKER
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!!!!