locked
I need help with Powershell and ADODB Commands with parameters RRS feed

  • Question

  • Good afternoon all,

    After writing a VBScript that works perfectly on my dev box, I am faced with a GoDaddy virtual dedicated server running Windows 2008 64bit and have to transcribe it into Powershell.  I tested and find out that I get an error when trying to assign a parameter to the command object.  Any help would be great appreciated.  I am having trouble not only in creating the parameter but also reusing it in the loop execution

    Try{
        $conn = New-Object -comobject ADODB.Connection
        $Conn.Open("Provider=SQLNCLI10.1;Initial Catalog=legal;Data Source=localhost;User Id =sa;PASSWORD=******;")
        $cmd = New-object -comobject ADODB.Command
        $cmd.ActiveConnection = $Conn
        $cmd.CommandType = 4
        $cmd.CommandText = "lg_sp_get_outstanding"
        $rs=$cmd.Execute()
        $cmd.CommandText = "lg_sp_set_Content"


        $cmd.Parameters.Append.CreateParameter("@FileID",3,1)
        $cmd.Parameters.Append.CreateParameter("@body",201,1,-1)
       
         if ($rs.EOF -eq $false){
             $Word = New-Object -Com Word.Application
             $Word.Visible = $false #set to 'true' for debugging!
             $Word.DisplayAlerts = 0
             
             While(-not $rs.EOF){
                 $document = $rs.Fields(1).Value
                Write-Host $rs.Fields(1).Value
                if (test-path $document) {
                    $existingDoc=$word.Documents.Open($document)
                    $wdFormatTXT = [ref] 7
                    $saveaspath = $document.Replace('.docx','.txt')
                    $saveaspath = $document.Replace('.doc','.txt')
           
                    $existingDoc.SaveAs( [ref] $saveaspath,$wdFormatTXT )
                    $existingDoc.Close()

                    $x = Get-Content $saveaspath
                    $cmd.Parameters("@FileID") = $rs.Fields.Item("FileID").Value
                    $cmd.Parameters("@body") = $x
                    $cmd.execute

                } # does it exist?
               $rs.MoveNext()
             }
             $Word.Quit()
             
        }
        $rs.Close
        $conn.Close()
    }
    catch{
         Write-Host "We had an error" -ForegroundColor red

    }

    Sunday, January 15, 2012 8:10 PM

Answers

  • Here is how to do it in PowerShell without using unsafe code or unmanaged code.

    $cstr='Integrated Security=SSPI;Initial Catalog=DBTest;Data Source=.\sqlexpress'
    $conn= new-object System.Data.SqlClient.SqlConnection($cstr)
    $cmd=$conn.CreateCommand()  
    $cmd.CommandType = 4
    $cmd.CommandText = "lg_sp_set_Content"
    $cmd.Parameters.Add('@FileID',3,1)
    $cmd.Parameters.Add('@body',1,1,-1)
    $reader=$cmd.ExecuteReader() 
    


    Note  that we can directly add the parameter.


    jv
    Sunday, January 15, 2012 9:53 PM

All replies

  • Good afternoon all,

    After writing a VBScript that works perfectly on my dev box, I am faced with a GoDaddy virtual dedicated server running Windows 2008 64bit and have to transcribe it into Powershell.  I tested and find out that I get an error when trying to assign a parameter to the command object.  Any help would be great appreciated.  I am having trouble not only in creating the parameter but also reusing it in the loop execution

    Try{
        $conn = New-Object -comobject ADODB.Connection
        $Conn.Open("Provider=SQLNCLI10.1;Initial Catalog=legal;Data Source=localhost;User Id =sa;PASSWORD=******;")
        $cmd = New-object -comobject ADODB.Command
        $cmd.ActiveConnection = $Conn
        $cmd.CommandType = 4
        $cmd.CommandText = "lg_sp_get_outstanding"
        $rs=$cmd.Execute()
        $cmd.CommandText = "lg_sp_set_Content"


        $cmd.Parameters.Append.CreateParameter("@FileID",3,1)
        $cmd.Parameters.Append.CreateParameter("@body",201,1,-1)
       
         if ($rs.EOF -eq $false){
             $Word = New-Object -Com Word.Application
             $Word.Visible = $false #set to 'true' for debugging!
             $Word.DisplayAlerts = 0
             
             While(-not $rs.EOF){
                 $document = $rs.Fields(1).Value
                Write-Host $rs.Fields(1).Value
                if (test-path $document) {
                    $existingDoc=$word.Documents.Open($document)
                    $wdFormatTXT = [ref] 7
                    $saveaspath = $document.Replace('.docx','.txt')
                    $saveaspath = $document.Replace('.doc','.txt')
           
                    $existingDoc.SaveAs( [ref] $saveaspath,$wdFormatTXT )
                    $existingDoc.Close()

                    $x = Get-Content $saveaspath
                    $cmd.Parameters("@FileID") = $rs.Fields.Item("FileID").Value
                    $cmd.Parameters("@body") = $x
                    $cmd.execute

                } # does it exist?
               $rs.MoveNext()
             }
             $Word.Quit()
             
        }
        $rs.Close
        $conn.Close()
    }
    catch{
         Write-Host "We had an error" -ForegroundColor red

    }


    When you create a command parameter it must match an actual parameter on your SP in both arguments and type.

    It is not possible to know what is wrong unless you post the exact error.  Just saying there is an error is not useful at all.

    You should also post the definition of the SP so we can see the match.

     


    jv
    Sunday, January 15, 2012 9:16 PM
  • A little invewstigation and I see that ther eis a very big mistake;

    $p=$cmd.CreateParameter("@FileID",3,1)
    $cmd.Parameters.Append($p)

    You must create the parameter.  The way you are doing could never have worked in VBScript.

    Looking at the error message would have shown you this.

     


    jv
    Sunday, January 15, 2012 9:27 PM
  • Here is how to do it in PowerShell without using unsafe code or unmanaged code.

    $cstr='Integrated Security=SSPI;Initial Catalog=DBTest;Data Source=.\sqlexpress'
    $conn= new-object System.Data.SqlClient.SqlConnection($cstr)
    $cmd=$conn.CreateCommand()  
    $cmd.CommandType = 4
    $cmd.CommandText = "lg_sp_set_Content"
    $cmd.Parameters.Add('@FileID',3,1)
    $cmd.Parameters.Add('@body',1,1,-1)
    $reader=$cmd.ExecuteReader() 
    


    Note  that we can directly add the parameter.


    jv
    Sunday, January 15, 2012 9:53 PM