Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

ADO & Stored Procedure blues

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • ADO & Stored Procedure blues

    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?

  • #2
    ADO & Stored Procedure blues

    The record set rstInfo is defined as Dim rstInfo As New ADODB.Recordset . The call to InitRecSet2 is located in Form_Load(). I can't find any reference to closing the record set in the program. Thanks for the response Bill!

    Comment


    • #3
      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


      • #4
        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


        • #5
          ADO & Stored Procedure blues

          It looks as though WhseText was being literally placed in the select statement......add the variable into the mix like this (maybe) : " & WhseTexT & " HTH, Joe

          Comment


          • #6
            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


            • #7
              ADO & Stored Procedure blues

              You might try: LWHS = '" & whseText.Text & "' ", If LWHS is a non-numeric field, you need to put quotes around the comparison value.

              Comment


              • #8
                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


                • #9
                  ADO & Stored Procedure blues

                  Maybe it needs to be form1.whseText.Text ?

                  Comment


                  • #10
                    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


                    • #11
                      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


                      • #12
                        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


                        • #13
                          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


                          • #14
                            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

                            Working...
                            X