Re: Look ups with multiple parameters

Yes, the query is defined with two parameters, that's why if i send one
parameter, the parser says it is fine, but during the running it says
there are too few parameters.
However, if i send two parameters, the parser finds an error as i said.
I've found similar dicsussions about people using functions with two
parameters, they get the same error, so they should either use CALL or
not use parenthesis and it relates to general syntax of VBscript
Here is my whole script in case the problem is somewhere else:

Function Main()
DTSDestination("PartLocationID") =
DTSLookups("GetPartLocation").Execute(DTSSource("LOCATION_CODE"))
DTSDestination("Quantity") = (DTSSource("QTY"))
partGuid = DTSLookups("GetPart").Execute( DTSSource("PART_NUMBER"))
DTSDestination("PartGuid") = CheckNullKey(partGuid)
DTSDestination("TaskGuid") =
DTSLookups("GetTask").Execute(DTSSource("TASK_CODE"))
DTSDestination("PMGuid") =
DTSLookups("GetPM").Execute(DTSSource("PM_NUM"))
Main = DTSTransformStat_OK
End Function

Function CheckNullKey(SourceColumn)

IF (not IsNull(SourceColumn) or not IsEmpty(SourceColumn)) then
NCType = DTSGlobalVariables("NCPartType").Value
partName = DTSSource("PART_NUMBER").Value
DTSLookups("Addpart").Execute(partName, NCType) ''THIS IS THE
ERROR LINE
CheckNullKey = GetLookUp.Execute(partName)
else
CheckNullKey = SourceColumn
END IF
End Function

And the query definition:


INSERT INTO Part
(Guid, Number, Name, Description, TypeID, UnitName, UnitCost, IsTool,
IsTaxable, DefaultQuantityToOrder, IsActive, DateCreated, LastModified)
VALUES (NEWID(), ?, ' ', ' ', ?, ' ', 0, 1, 1, 0, 1, GETDATE(),
GETDATE())

Thanks very much
Irene

Charles Kangai wrote:
Are you sure you defined the lookup with two parameters, i.e. two question
marks? Please check the definition of the lookup again.

Charles Kangai, MCT, MCDBA

"Irene" wrote:

Dear Charles
Thanks for your reply. I tried alll your suggestions, but still I
cannot make it work. If I leave one parameter, it works perfectly fine
(except that query is lacking one parameter). As soon as I put second
parameter, no matter what type is it, the syntax check gives an error:
"cannot use parentheses when calling a sub"
Would you suggest anything else I can try?
Thanks
Irene


Charles Kangai wrote:
Dear Irene,

The lookup name is case sensitive. Otherwise there is nothing wrong with
your syntax. Your problem may be with the values you are passing. If they are
expressions such as DTSSource("MyColumn"), then do not forget the .VALUE at
the end. You may also consider using explicit casting of data types to e.g. a
numerical data type using functions like CInt, for example:

DTSDestination("Column2") =
DTSLookups("AddPart").Execute(DTSSource("Col1").Value,
DTSSource("Col2").Value)

or

DTSDestination("Column2") =
DTSLookups("AddPart").Execute(CInt(DTSSource("Col1").Value),
CInt(DTSSource("Col2").Value))

Do not use Call or anything like that. The above should work. I have similar
examples working.

Charles Kangai, MCT, MCDBA

"Irene" wrote:

Hi,
Do you know how to run a lookup query with multiple parameters? I am
passing them, using a comma:
DTSLookups("AddPart").Execute(Value1, Value2)
But every time get an error "cannot use parentheses when calling a sub"
As suggested in some groups, i tried
Call DTSLookups("Addpart").Execute(Value1, Value2) and
DTSLookups("Addpart").Execute Value1, Value2
But they do not work either, probably, because it's not really a
function
Thanks
Irene





.

Relevant Pages

  • Re: Look ups with multiple parameters
    ... Charles Kangai wrote: ... your lookup to anything. ... (except that query is lacking one parameter). ... You may also consider using explicit casting of data types to e.g. a ...
    (microsoft.public.sqlserver.dts)
  • Re: Look ups with multiple parameters
    ... I think that your error is the fact that you are not assigning the result of ... your lookup to anything. ... (except that query is lacking one parameter). ... You may also consider using explicit casting of data types to e.g. a ...
    (microsoft.public.sqlserver.dts)
  • Re: Look ups with multiple parameters
    ... the query explicitely to a variable. ... Please check the definition of the lookup again. ... Your problem may be with the values you are passing. ... You may also consider using explicit casting of data types to e.g. a ...
    (microsoft.public.sqlserver.dts)
  • Re: question about SQL Parser
    ... query I send it. ... The parser currently won't break on foo.bar syntax but can't handle foo.bar.baz, that wouldn't be too hard to add. ... For Data Definition Language (DDL), you should check out SQL::Translator, it is much more complete for DDL. ... At the moment your best option may be wrapping the parsing in an eval and creating two output files - one of successfully parsed statements and one of statements that couldn't be parsed. ...
    (perl.dbi.users)
  • Re: "Order by" clause
    ... While there is no actual ambiguity, the parser is not so sure. ... explicitly as the first column and once again in the *. ... parser does not try to figure out the meaning of the query. ...
    (microsoft.public.sqlserver.mseq)