Operation is not allowed when obejct is closed

zazo · July 24th, 2008, 12:31 PM

of course not i can't ignore it your suggestion , i have asked a collegue to debug it for me as i don't have visual basic on my machine , plus i have tried to use Dim xxx as New ADODB.Connection but i got a compliation error , so i did try to change it to ADODB.Connection and ADODB.RecordSet

joefawcett · July 24th, 2008, 12:41 PM

Aaarrgggh! You DON'T NEED Visual Basic because it's not Visual Basic, it's VBScript, MSXML doesn't support any compiled languages, only VBScript or JScript.

--

Joe (Microsoft MVP - XML)

zazo · July 24th, 2008, 01:00 PM

no offense please , am supposed to be here to find a guide from an expert like you , any way what do you want me exactly to do

joefawcett · July 24th, 2008, 01:08 PM

Sorry but it's very difficult. I gave instructions on how to isolate the code and run it in the seventh post in this topic.
Let's just check one thing, which processor are you using to run the transform? You've said MSXML but then you say you're using Visual Basic code which wouldn't work.

--

Joe (Microsoft MVP - XML)

zazo · July 24th, 2008, 01:23 PM

that what i know MSXML 4.0 and 6.0 i found both in the program files
the Visual basic code i tried cause i thought it can accept visual basic but it seems as you said it is vbscript

joefawcett · July 24th, 2008, 01:34 PM

I've tidied up the code a bit and added some debugging MsgBox-es. You need to run this as a script as call the function with some realistic values.

Code:

Option Explicit

Function UpdateCOA(AccDimCode,Name,manager,closed,category,Rep1,Rep2,Rep3,Rep4,Rep5,RepLevel,AllocNum,AutoAcc,AccrualAcc,AccrualYear,AccrualPer,UsrGrp,cc,db)

    'Function to get EAN code from Stock Item and replace that stock code with the EAN

    ' Function to get EAN code from Stock Item and replace that stock code with the EAN
    Dim dsn 'As String
    Dim cn 'As ADODB.Connection
    Dim sqlStmt 'As String
    Dim recData 'As ADODB.Recordset
    Dim accountExists 'As Integer 'not sure what this is for
    Dim TmpSupplier 'As String
    dsn = "Provider=SQLOLEDB;Trusted_Connection=yes; Data Source=" &  db & "; Initial Catalog=ScaSystemDB;" &_
    " user id='user'; password='pwd'"
    set cn = CreateObject("ADODB.connection")
    cn.open dsn
    'Check if connection is open
    MsgBox cn.State, vbOkOnly, "cn 1"   
    sqlStmt = "select DBName from ScaCompanies where CompanyCode = '" & cc & "'"
    'Check sqlStmt
    MsgBox sqlStmt, vbOkOnly, "sqlStmt 1"
    Set recData = CreateObject("ADODB.Recordset") 
    recData.open sqlStmt, cn
    if not recData.EOF  then databaseName = recData(0)
    recData.close
    cn.close

    dsn = "Provider=SQLOLEDB;Trusted_Connection=yes; Data Source=" & db & "; Initial Catalog=" &_
    databaseName & "; user id='user'; password='pwd'"
    set cn = createObject("ADODB.connection")
    cn.open dsn
    'Check if connection is open  
    MsgBox cn.State, vbOkOnly, "cn 1" 
    'Check for OVT code
    accountexists = 0
    'Set recData = CreateObject("ADODB.Recordset") 
    sqlStmt = "Update  GL03" & cc & "06 Set GL03003 = '" & Name & "' , GL03006 = '" & manager & "' , GL03007 = '" & closed & "', GL03008 = '" & category &_
          "', GL03009 = '" & Rep1 & "' , GL03010 = '" & Rep2 & "', GL03011 = '" & Rep3 & "', GL03012 = '" & Rep4 & "', GL03013 = '" & Rep5 &_
         "' , GL03014 = '" & RepLevel & "', GL03015 = '" & AllocNum & "' , GL03016 = '" & AutoCc  &_
         " ', GL03017 = '" & AccrualAcc & "', GL03018 = '" & AccrualYear & "', GL03019 = '" & AccrualPer & "',GL03020 = '" & UsrGrp & "' WHERE GL03001 = '" & AccDimCode & "'"
    'Check sqlStmt
    MsgBox sqlStmt, vbOkOnly, "sqlStmt 2"
    recData.open sqlStmt, cn
    if not recData.EOF then
        if Len(recData(0)) > 0 then 
            TmpSupplier = recData(0)
        else
            TmpSupplier = supplier
        end if
    end if
    recData.Close
    cn.close
    UpdateCOA = sqlStmt 
End Function

Call UpdateCOA( <insert values here>)

Just make sure that the code lines haven't wrapped.

--

Joe (Microsoft MVP - XML)

zazo · July 24th, 2008, 03:12 PM

joe ,
sorry for the delay in response i was out
please bare with me where can i run this code , do you recommend any program to download as i don't have a program to debug this code in my vbscript function is called inline inside a xslt file and the product am using will not fire this message box i guess
and thanks alot for your cooperation so far

samjudson · July 24th, 2008, 03:30 PM

If you save the code in a text file and call it something like "test.vbs" you can then run it by either doubble clickingon the file, or running the following from the command prompt: "cscript test.vbs"

/- Sam Judson : Wrox Technical Editor -/

zazo · July 24th, 2008, 03:36 PM

joe , sorry for the delay ,

can you address a free vbscript debugger cause i don't have one on my machine, plus i don't know if the prodcut am using is going to support the msgbox as it is a vbscript function called inside the xslt document , thanks alot for you patiency and effort for supproting my problem , i do really appreciate it

zazo · July 25th, 2008, 01:26 PM

At last i managed to solve the problem , first i downloaded the onscript http://www.computerperformance.co.uk...e/onscript.htm

and i removed those last three lines

Option Explicit

Function UpdateCOA(AccDimCode,Name,manager,closed,category, Rep1,Rep2,Rep3,Rep4,Rep5,RepLevel,AllocNum,AutoAcc ,AccrualAcc,AccrualYear,AccrualPer,UsrGrp,cc,db)

    'Function to get EAN code from Stock Item and replace that stock code with the EAN

    ' Function to get EAN code from Stock Item and replace that stock code with the EAN
    Dim dsn 'As String
    Dim cn 'As ADODB.Connection
    Dim sqlStmt 'As String
    Dim recData 'As ADODB.Recordset
    Dim accountExists 'As Integer 'not sure what this is for
    Dim TmpSupplier 'As String
    dsn = "Provider=SQLOLEDB;Trusted_Connection=yes; Data Source=" & db & "; Initial Catalog=ScaSystemDB;" &_
    " user id='user'; password='pwd'"
    set cn = CreateObject("ADODB.connection")
    cn.open dsn
    'Check if connection is open
    MsgBox cn.State, vbOkOnly, "cn 1"
    sqlStmt = "select DBName from ScaCompanies where CompanyCode = '" & cc & "'"
    'Check sqlStmt
    MsgBox sqlStmt, vbOkOnly, "sqlStmt 1"
    Set recData = CreateObject("ADODB.Recordset")
    recData.open sqlStmt, cn
    if not recData.EOF then databaseName = recData(0)
    recData.close
    cn.close

    dsn = "Provider=SQLOLEDB;Trusted_Connection=yes; Data Source=" & db & "; Initial Catalog=" &_
    databaseName & "; user id='user'; password='pwd'"
    set cn = createObject("ADODB.connection")
    cn.open dsn
    'Check if connection is open
    MsgBox cn.State, vbOkOnly, "cn 1"
    'Check for OVT code
    accountexists = 0
    'Set recData = CreateObject("ADODB.Recordset")
    sqlStmt = "Update GL03" & cc & "06 Set GL03003 = '" & Name & "' , GL03006 = '" & manager & "' , GL03007 = '" & closed & "', GL03008 = '" & category &_
          "', GL03009 = '" & Rep1 & "' , GL03010 = '" & Rep2 & "', GL03011 = '" & Rep3 & "', GL03012 = '" & Rep4 & "', GL03013 = '" & Rep5 &_
         "' , GL03014 = '" & RepLevel & "', GL03015 = '" & AllocNum & "' , GL03016 = '" & AutoCc &_
         " ', GL03017 = '" & AccrualAcc & "', GL03018 = '" & AccrualYear & "', GL03019 = '" & AccrualPer & "',GL03020 = '" & UsrGrp & "' WHERE GL03001 = '" & AccDimCode & "'"
    'Check sqlStmt
    MsgBox sqlStmt, vbOkOnly, "sqlStmt 2"
    recData.open sqlStmt, cn
' if not recData.EOF then
' if Len(recData(0)) > 0 then
' TmpSupplier = recData(0)
' else
' TmpSupplier = supplier
' end if
' end if
    recData.Close
    cn.close
    UpdateCOA = sqlStmt
End Function

Call UpdateCOA( <insert values here>)

Thanks alot for your effort and pateince Joe , Thanks sam , thanks to every one who gave me support , i do really appreciate it