Re: Look ups with multiple parameters

Right, thank you very much

Charles Kangai wrote:
I think that your error is the fact that you are not assigning the result of
your lookup to anything. You should assign it. The lookup returns a value and
your value is just standing on the line without doing anything. It is like
having a line of code which simply reads "Smith". It will fail, because the
"Smith" needs to be assigned to something. it is thinking that you are trying
to run a procedure called "Smith" and that is why you are getting the error.
You should have something like:

XYZ = DTSLookups("Addpart").Execute(partName, NCType)

At the moment you just have the right hand side!

Charles Kangai, MCT, MCDBA

"Irene" wrote:

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
    ... Yes, the query is defined with two parameters, that's why if i send one ... However, if i send two parameters, the parser finds an error as i said. ... Please check the definition of the lookup again. ... 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: Basic Question for Lookups.
    ... However, especially in the early phases of setting up your Tables, you will probably need at times to look at their contents, and if so, you probably will want to define lookup properties on the foreign keys. ... The Query that I would suggest is similar to what the Lookup Wizard defines, except that I give mine a name, and I limit it to 2 fields instead of several. ... 1801550689 Michael Zachary Jackson ... I'm properly storing Prospect ID pk then FirstName then Middle then Lastname fields.etc. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Tip: Table design and query for bracketed lookup
    ... WHERE [Enter Bracket:] ... No rows would be returned by the query! ... If the "lookup" value is in a column of your query called Lookup, ... FROM RateTable RT1 ...
    (microsoft.public.access.queries)
Loading