RE: Stored Procedures, Visual Basic 6 and LIKE clause

Remove the quotes.

        BEGIN 
        declare tempvar varchar(50);
        set tempvar = '%' + criteria + '%';
        DECLARE C1 CURSOR WITH RETURN FOR SELECT MAX ( BOX ) AS RETURNED FROM
CLSDFILES . CLOSEDFILES WHERE BOX LIKE tempvar ; OPEN C1 ;
        END  ; 

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Adam Lang
> Sent: Thursday, June 09, 2005 1:26 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: Stored Procedures, Visual Basic 6 and LIKE clause
> 
> 
> Ok, I think I understand what you are saying, but this leads to a dumb
> question.  How do I do it?
> 
> I have been reading through the stored procedures Redbook and can not
> seem to figure out how to go about this.
> 
> Thsi si what I came up with, but it won't generate.
> 
>       BEGIN 
>       declare tempvar varchar(50);
>       set tempvar = criteria;
>       DECLARE C1 CURSOR WITH RETURN FOR SELECT MAX ( BOX ) AS 
> RETURNED FROM
> CLSDFILES . CLOSEDFILES WHERE BOX LIKE '%tempvar%' ; OPEN C1 ;
>       END  ; 
> 
> It actually errors on the declare C1 cursor part.  I assume it is
> because the declares have to be together, btu then I wouldn't know how
> to set the variable after the declare of the statement.
> 
> On 6/7/05, Walden H. Leverich <WaldenL@xxxxxxxxxxxxxxx> wrote:
> > Add a row to your dataset with a value of 'check criteria 
> match' for the
> > box field and try it again, I'll bet you get a row back.
> > 
> > I'd say the problem is that your LIKE clause is looking for 
> something
> > that contains the value 'CRITERIA' somewhere in it, that is 
> the letters
> > C-R-I-T-E-R-I-A and not the value of the variable called CRITERIA.
> > You'll need to do the concat into a temp variable and then loose the
> > single quotes. Right now SQL doesn't see that usage of CRITERIA as a
> > parm, it sees it as a constant.
> > 
> > -Walden
> > 
> > ------------
> > Walden H Leverich III
> > Tech Software
> > (516) 627-3800 x11
> > WaldenL@xxxxxxxxxxxxxxx
> > http://www.TechSoftInc.com
> > 
> > Quiquid latine dictum sit altum viditur.
> > (Whatever is said in Latin seems profound.)
> > 
> > 
> > --
> > This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> > 
> > 
> 
> 
> -- 
> "Your Majesty has slain the mother; how can one kill the 
> child as well?"
> - Ts'ao Jui, Romance of the Three Kingdoms
> 
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>