Well the message seems to be saying that you closed the rstinfo record set. Since you don't show the CallInitRecSet2 I can't tell what you might have done wrong. Also you don't show the rstinfo field declaration - is it set up as a global variable?
Unconfigured Ad Widget
Collapse
Announcement
Collapse
No announcement yet.
ADO & Stored Procedure blues
Collapse
X
-
ADO & Stored Procedure blues
Is rstInfo a form level variable? If you defined it in the InitRecSet2 routine that would explain the problem since it would be local to that routine. I guess I would just step add a break in the program immediately after you open the record set and then single step thru the program periodically displaying rstInfo(0) until you get an error message indicating the record set was closed when you try to display it.
Comment
-
ADO & Stored Procedure blues
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
Comment
-
ADO & Stored Procedure blues
Joe, 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 .MoveLast .MoveFirst End With I tried that in my sql statement bu when I debugged the program, " & whseText & " was blank. whseText was defined in Form1 as TextBox and I tried to use it on Form2 for my record selection to display record on datagrid. Do I need to assign it to another variable? Thanks a lot Sky
Comment
-
ADO & Stored Procedure blues
I had tried that and still doesnt work. The message is runtime error 424: "object required". I think when I am trying to display the next form, the value of whseText.Text becomes empty. I dont know how I am going to declare a this field to be static or global so that it will be available in the entire application, its only available on the first module (form1). Thanks Mark
Comment
-
ADO & Stored Procedure blues
Joe, This doesnt work also, what i did is I created a module and define a new field which is public and used it for my sql, the latter doesnt work because the object whseText (Textbox) is only accessible on first module (Form1) and not on the second Form2. The second problem i have encounter so far was the result of a sum() expression specified on SELECT not valid SQL0122. heres the sql statement: "SELECT WHS, PROD, LOT, LOC, sum(OPB + RCT + ADJ - ISSUE) as onhand from LOCPF where PROD Like('NF%') and WHS = '" & WhseCode & "' ", _ Con1, adOpenStatic, adLockOptimistic, adCmdText Thanks Rod
Comment
-
ADO & Stored Procedure blues
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
Comment
-
ADO & Stored Procedure blues
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
Comment
-
ADO & Stored Procedure blues
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
Comment
-
ADO & Stored Procedure blues
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?
Comment
Comment