ADO & Stored Procedure blues

[Guest.Visitor]

Sky, Shouldn't the statement be: ". . . and LWHS = '" & WhseText & "'" You are sending the &WhseText as part of the statement, which would be incorrect. You want to send the entered value to search for. Thus you put an apostrophe right before the search value, close the statement with a quote, substitute the search value entered (this is outside the statement), then close the statement value with another apostrophe which would be entered within quotes. Thus, if the user enters E4, this would send the following up: and LWhs = 'E4' . Make sense? Bill "skywalker" wrote in message news:4e68137b.3@WebX.WawyahGHajS... > I have a sql statement that reads: > With rs > .Open "Select LWHS, LPROD, LLOT, LLOC, LOPB, LRCT, LISSU, LADJU from ILIL01 where LPROD Like('NF%') and LWHS = &WhseText", _ > Con1, adOpenStatic, adLockOptimistic, adCmdText > > This statement works if I deleted the last selection LWHS=&WhseText. > WhseText is a TextBox object, users typed this value and I want to limit selection into this parameter. I just cant figured out if this will work on this statement. Will Somebody help me with the correct syntax. Thanks a lot > Sky

[Guest.Visitor]

Hi, Could somebody please set me straight on how to call a stored procedure using ADO in VB 6.0? Everytime I run my program I get the following error: Run-time : 3704 Operation is not allowed when the object is closed. This error is issued in Procedure LoadListView in statemen Do Until rstInfo.EOF... Here is my code:[*]*********************************************** Private Sub Form_Load() Const strPROCEDURE_NAME As String = "Form_Load" On Error GoTo ErrorHandler 'Initialize ListView Columns Call BldLvwCols 'Build default query based on gblViewTyp If gblViewTyp = 1 Then Call InitRecSet2 ElseIf gblViewTyp = 2 Then strQuery = "Select * from MYLIB.DIRTEXMB" 'Get recordset Call InitRecSet ElseIf gblViewTyp = 3 Then strQuery = "Select * from MYLIB.DIRTEX75" 'Get recordset Call InitRecSet End If 'Load ListView Call LoadListView ExitPoint: Exit Sub ErrorHandler: Call HandleError(mstrMODULE_NAME, strPROCEDURE_NAME, "Error loading form.") Resume ExitPoint End Sub[*]******************************************** Private Sub InitRecSet2() Const strPROCEDURE_NAME As String = "InitRecSet2" Dim cmdStoredPrc As New ADODB.Command On Error GoTo ErrorHandler cmdStoredPrc.CommandType = adCmdStoredProc cmdStoredPrc.CommandTimeout = 60 cmdStoredPrc.CommandText = "{CALL MYLIB.DIRTEXPROC}" cmdStoredPrc.ActiveConnection = cn400 cmdStoredPrc.Prepared = True Set rstInfo = cmdStoredPrc.Execute ExitPoint: Exit Sub ErrorHandler: Call HandleError(mstrMODULE_NAME, strPROCEDURE_NAME, "Error encountered in executing stored procedure.") Resume ExitPoint End Sub[*]************************************************** Private Sub LoadListView() Const strPROCEDURE_NAME As String = "LoadListView" On Error GoTo ErrorHandler 'Load each item in the recordset into the listview lvwApproach65.ListItems.Clear Do Until rstInfo.EOF Call UpdLvwApproach rstInfo.MoveNext Loop 'Re-size specific columns to fit data If lvwApproach65.ListItems.Count <> 0 Then LV_AutoSizeColumn lvwApproach65, lvwApproach65.ColumnHeaders.Item(3) LV_AutoSizeColumn lvwApproach65, lvwApproach65.ColumnHeaders.Item(4) End If ExitPoint: Exit Sub ErrorHandler: Call HandleError(mstrMODULE_NAME, strPROCEDURE_NAME, "Error loading recordset into listview.") Resume ExitPoint End Sub Any ideas why this could be happening? Please let me know if need to post more info. Thanks in advance! Erick

[Guest.Visitor]

Bill, Yes, you're absolutely correct, and i got it work right now. But the second error i have encounter was to summarize field into a variable which gives me an error SQL0122, eg. sum(OPB + RCT + ADJ - ISSUE) as onhand from File1. In os/400 is should be read like this: sum(fld1 + fld2 + fld3) into :var from file1 where var was define as packed or zoned dec. Am I missing something here? Thanks a lot Sky Thanks Sky

[Guest.Visitor]

Sky, I haven't used SQL like that, so I can't tell if you are doing it right or wrong. When I have something like that to do in Visual FoxPro, I just download the info into a temp table (or view), then read that table loading the appropriate values into memory variables at that time. Bill > Yes, you're absolutely correct, and i got it work right now. But the second error i have encounter was to summarize field into a variable which gives me an error SQL0122, eg. sum(OPB + RCT + ADJ - ISSUE) as onhand from File1. > In os/400 is should be read like this: > sum(fld1 + fld2 + fld3) into :var from file1 where var was define as > packed or zoned dec. Am I missing something here?