troubleshooting Question

convert string to integer in ADOquery SQL

Avatar of Richard Teasdale
Richard TeasdaleFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerDelphiSQL
5 Comments1 Solution1159 ViewsLast Modified:
Hi: I have the following query on a form. It reads an MSSQL database over which I have no control. Has worked fine for a year until  a user has decided to add "(1)"  to an invoice number at which point the WHERE clause  "AND Dbo_goorders.InvNo<1" falls over with an error.
Because it is SQL does anybody know how to get a conversion function into the code, in order that it can read the invoice number? Incidentally it may be better to say "InvNo is BLANK" ; OR "InvNo is NULL" but I don't know how to do that, either!

ADOQuery1.SQL.Add('SELECT Dbo_goorders.OrderNo, Dbo_goorders.InvNo, Dbo_goorders.Ordered,Dbo_goorders.Delivered, Dbo_goorders.Invoiced, Dbo_goorders.AccNo, ');
 ADOQuery1.SQL.Add('Dbo_goorders.Customer, Dbo_goorders.Goods, Dbo_goorders.Nett, Dbo_goorders.VAT, Dbo_goorders.SurchargeRate, Dbo_goorders.SurchargeVal, Vvdates."DATE", Vvdates."MONTH", Vvdates."YEAR" ');
      ADOQuery1.SQL.Add('FROM vvdates Vvdates INNER JOIN GOOrders Dbo_goorders ');
  ADOQuery1.SQL.Add(' ON  (Dbo_goorders.Delivered = Vvdates."DATE") ');
     ADOQuery1.SQL.Add('WHERE  Dbo_goorders.DocNo > 0  AND Dbo_goorders.Nett >0  AND Dbo_goorders.state = :SELINV AND Vvdates.Month = :SELMONTH AND Vvdates.Year = :SELYEAR AND Dbo_goorders.InvNo<1 order by Dbo_goorders.orderno');
ADOQuery1.Parameters.ParamByName('SELMONTH').Value := StrToInt(Edit1.Text);
ADOQuery1.Parameters.ParamByName('SELYEAR').Value := StrToInt(Edit2.Text);
ADOQuery1.Parameters.ParamByName('SELINV').Value := (Edit3.Text);
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros