Re: Stored Procedures, Visual Basic 6 and LIKE clause

Same error as other statement.  Invalid use of +

On 6/9/05, Wilt, Charles <CWilt@xxxxxxxxxxxx> wrote:
> Sorry,
> 
> Actually it needs to be like this:
> 
>         BEGIN
>         declare tempvar varchar(50);
>         DECLARE C1 CURSOR WITH RETURN FOR SELECT MAX ( BOX ) AS RETURNED FROM
>                 CLSDFILES . CLOSEDFILES WHERE BOX LIKE tempvar;
>         set tempvar = '%' + criteria + '%';
>         OPEN C1 ;
>         END  ;
> 
> 
> 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: Tuesday, June 07, 2005 11:10 AM
> > To: Midrange Systems Technical Discussion
> > Subject: Stored Procedures, Visual Basic 6 and LIKE clause
> >
> >
> > Hello,
> >
> > I have a Visual Basic 6 program using ADO and the iSeries ODBC driver
> > for getting data off the iSeries (V5R2.
> >
> > I use a variety of stored procedures currently with no problem, until
> > I tried doing a LIKE clause.
> >
> > The stored procedure runs, but it returns a NULL value.  When I run
> > the SQL in the "run scripts" window of NAvigator, it returns data,
> > like it should.  I went over code a lot and I can see nothign I am
> > doing different from the other stored procedures I am calling.  I am
> > curious if anyone has any ideas.
> >
> > Here is the definituion of the stored procedure I am calling:
> >
> > CREATE PROCEDURE CLSDFILES.LAST_BOX (
> >       IN CRITERIA VARCHAR(50) )
> >       DYNAMIC RESULT SETS 1
> >       LANGUAGE SQL
> >       SPECIFIC CLSDFILES.LAST_BOX
> >       NOT DETERMINISTIC
> >       READS SQL DATA
> >       CALLED ON NULL INPUT
> >       BEGIN DECLARE C1 CURSOR WITH RETURN FOR SELECT MAX ( BOX ) AS
> > RETURNED FROM CLSDFILES . CLOSEDFILES WHERE BOX LIKE '%CRITERIA%' ;
> > OPEN C1 ; END  ;
> >
> > And here is the Visual Basic code I am calling it with:
> >
> > Set MyCommand = New ADODB.Command
> >
> > With MyCommand
> >     .ActiveConnection = MyConnection
> >     .CommandText = "CLSDFILES.LAST_BOX"
> >     .CommandType = adCmdStoredProc
> >     .Parameters("criteria") = criteria
> > End With
> >
> > Set rcdset = New ADODB.Recordset
> > With rcdset
> >     .CursorType = adOpenStatic
> >     .LockType = adLockReadOnly
> >     Set .Source = MyCommand
> >     .Open
> > End With
> >
> > If IsNull(rcdset.Fields.Item(0).Value) Then
> >     MsgBox "No boxes with that search criteria."
> > Else
> >     MsgBox rcdset.Fields.Item(0).Value
> > End If
> >
> > Also of note.  When I call the SQL directly from VB, it also works
> > that it is supposed to:
> > 'sSQL = "SELECT max(BOX) FROM CLSDFILES . CLOSEDFILES WHERE BOX LIKE
> > '%" & criteria & "%'"
> > '
> > 'Set rcdset = MyConnection.Execute(sSQL)
> > '
> > 'If IsNull(rcdset.Fields.Item(0).Value) Then
> > '    MsgBox "No boxes with that search criteria."
> > 'Else
> > '    MsgBox rcdset.Fields.Item(0).Value
> > 'End If
> > --
> > "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.
> >
> >
> 
> --
> 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.
> 
>