Solved

Converting VBScript to VB.NET

Posted on 2014-01-15
9
672 Views
Last Modified: 2014-01-15
I need to rewrite the  VBScript below in VB.NET so that I can use it in  SSIS script task.    So How can I rewrite it in VB.NET? Thank you for your help.


'**  Script Name..: xCheckForRecordsDTS.vbs 
'**                                           
'*********************************************************************** 

Function Main() 
  Dim oConn, oRs, oCmd

  '** Create ADO objects 
  Set oConn = CreateObject("ADODB.Connection") 
  Set oRs = CreateObject("ADODB.Recordset") 
  Set oCmd = CreateObject("ADODB.Command") 

  '** Set DAO connection properties 
  oConn.Open "Driver={SQL Server};Server=myserver;Database=OCDC;Uid=User;Pwd=password;"
    
  '** Set the active connection to the target database ADO Object 
  oCmd.ActiveConnection = oConn 
'  oCmd.CommandType = adCmdText 

  '** Prepare the SQL statement for retrieving a unique task id 
  sSQL = "SELECT Count(*) as NumRecords FROM ActiveDirectoryUsers" 

  '** Execute the SQL statement and retrieve the data 
  Set oRs = oConn.Execute(sSQL)

  '** Move the data set cursor to the first record 
  If Not(oRs.EOF and oRs.BOF) Then 
    oRs.MoveFirst 
  End If 

  If oRs.Fields("NumRecords") = 0 Then
    Main = DTSTaskExecResult_Failure 
  Else
    Main = DTSTaskExecResult_Success 
  End If

Open in new window

0
Comment
Question by:ocdc
  • 5
  • 4
9 Comments
 

Author Comment

by:ocdc
Comment Utility
OK. so far This is what I converted. But still getting some error. Please see the attached file.

P.S.  I turned off Option Expilicit to fix the Option expilicit error in the attached file but turning it off doesnt make any difference still getting the same error.

Thank you.

Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
    Public Sub Main()
        Dim oConn As Object
        Dim oRs As Object
        Dim oCmd As Object
        '** Create ADO objects 
        oConn = CreateObject("ADODB.Connection")
        oRs = CreateObject("ADODB.Recordset")
        oCmd = CreateObject("ADODB.Command")
        '** Set DAO connection properties 
        oConn.open("Driver={SQL Server};Server=myserver;Database=OCDC;Uid=User;Pwd=password;")
        '** Set the active connection to the target database ADO Object 
        oCmd.ActiveConnection = oConn
        '** Prepare the SQL statement for retrieving a unique task id 
        sSQL = "SELECT Count(*) as NumRecords FROM ActiveDirectoryUsers"
        '** Execute the SQL statement and retrieve the data 
        oRs = oConn.Execute(sSQL)
        '** Move the data set cursor to the first record 
        If Not (oRs.EOF And oRs.BOF) Then
            oRs.MoveFirst()
        End If
        If oRs.Fields("NumRecords") = 0 Then
            Main = DTSTaskExecResult_Failure
        Else
            Main = DTSTaskExecResult_Success
        End If

    End Sub
End Class

Open in new window

VBNET-SCRIPT-Task-error.PNG
0
 
LVL 3

Expert Comment

by:13Shadow
Comment Utility
Try this:
Function xCheckForRecordsDTS(FailureSuccess As String)
        Try
            Dim oConn As New ADODB.Connection()
        oConn.ConnectionString = "Driver={SQL Server};Server=myserver;Database=OCDC;Uid=User;Pwd=password;"
        If oConn.State = ConnectionState.Closed Then
            oConn.Open()
        End If

            Dim oRs As New ADODB.Recordset()
            oRs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
            oRs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
            oRs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
            oRs.Open("SELECT Count(*) as NumRecords FROM ActiveDirectoryUsers", oConn)
         

            Return "Success"
            If oRs("NumRecords").Value = 0 OrElse oRs.RecordCount = 0 Then
                Return "Failure"
            End If
            oRs.ActiveConnection = Nothing
            If oConn.State = ConnectionState.Open Then
                oConn.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Function
The function returns a string of Success or Failure. If you are returning a Boolean instead change it accordingly.

** You need to add a reference:  On the COM tab, select Microsoft ActiveX Data Objects 2.5 Library.
0
 

Author Comment

by:ocdc
Comment Utility
This is what I got so far but getting an error "Statement is not valid in a name space" Thank you.


Imports System
Imports System.Data
Imports System.Math
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Dts.Runtime

    Function xCheckForRecordsDTS(ByVal FailureSuccess As String)
        Try
            Dim oConn As New ADODB.Connection()
            oConn.ConnectionString = "Driver={SQL Server};Server=myserver;Database=OCDC;Uid=User;Pwd=password;"
            If oConn.State = ConnectionState.Closed Then
                oConn.Open()
            End If

            Dim oRs As New ADODB.Recordset()
            oRs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
            oRs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
            oRs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
            oRs.Open("SELECT Count(*) as NumRecords FROM ActiveDirectoryUsers", oConn)


            Return "Success"
            If oRs("NumRecords").Value = 0 OrElse oRs.RecordCount = 0 Then
                Return "Failure"
            End If
            oRs.ActiveConnection = Nothing
            If oConn.State = ConnectionState.Open Then
                oConn.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try
    End Function

Open in new window

0
 
LVL 3

Expert Comment

by:13Shadow
Comment Utility
Did you place the function within the Form1 or your ScriptMain class?
The function should go between
Public Class Form1 or ScriptMain

Place Function here

end class
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:ocdc
Comment Utility
OK. made a lot of progress except  I am getting an error " ADOB is not declared" on ADOB.Recordset, ADODB.CursorLocationEnum.adUseClient, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic  and
on MessageBox.show  "Messagebox is not declared" error.

Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
    Function xCheckForRecordsDTS(ByVal FailureSuccess As String)
        Try
            Dim oConn As New Odbc.OdbcConnection()
            oConn.ConnectionString = "Driver={SQL Server};Server=myserver;Database=OCDC;Uid=User;Pwd=password;"
            If oConn.State = ConnectionState.Closed Then
                oConn.Open()
            End If

            Dim oRs As New ADODB.Recordset()
            oRs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
            oRs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
            oRs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
            oRs.Open("SELECT Count(*) as NumRecords FROM ActiveDirectoryUsers", oConn)


            Return "Success"
            If oRs("NumRecords").Value = 0 OrElse oRs.RecordCount = 0 Then
                Return "Failure"
            End If
            oRs.ActiveConnection = Nothing
            If oConn.State = ConnectionState.Open Then
                oConn.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try
    End Function

	Public Sub Main()
		'
		' Add your code here
		'
		Dts.TaskResult = Dts.Results.Success
	End Sub

End Class

Open in new window

0
 
LVL 3

Expert Comment

by:13Shadow
Comment Utility
You must be using a console application.
You can put the code directly in the sub main:
Sub Main()
        Try
            Dim oConn As New Odbc.OdbcConnection()
            oConn.ConnectionString = "Driver={SQL Server};Server=myserver;Database=OCDC;Uid=User;Pwd=password;"
            If oConn.State = ConnectionState.Closed Then
                oConn.Open()
            End If

            Dim oRs As New ADODB.Recordset()
            oRs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
            oRs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
            oRs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
            oRs.Open("SELECT Count(*) as NumRecords FROM ActiveDirectoryUsers", oConn)



            If oRs("NumRecords").Value = 0 OrElse oRs.RecordCount = 0 Then
                Console.Write("Failure")
            Else
                Console.Write("Success")

            End If
            oRs.ActiveConnection = Nothing
            If oConn.State = ConnectionState.Open Then
                oConn.Close()
            End If
        Catch ex As Exception
            Console.Write(ex.Message)
        End Try
    End Sub
0
 
LVL 3

Expert Comment

by:13Shadow
Comment Utility
And you did do this right?
Open the project properties:
** You need to add a reference:  On the COM tab, select Microsoft ActiveX Data Objects 2.5 Library.
0
 
LVL 3

Accepted Solution

by:
13Shadow earned 500 total points
Comment Utility
Try this instead:
Imports System
Imports System.Data
Imports System.Math
Imports System.Data.SqlClient

Module Module1

    Sub Main()

               Using connection As New SqlConnection("Server=myserver;Database=OCDC;Trusted_Connection=True; ")
            Using scommand As New SqlCommand()
                scommand.Connection = connection

                If scommand.Connection.State = ConnectionState.Closed Then
                    scommand.Connection.Open()
                End If '  If sComm
                 scommand.CommandText = "SELECT Count(*) as NumRecords FROM ActiveDirectoryUsers  "
                scommand.CommandType = CommandType.Text
  Dim dr As SqlDataReader = scommand.ExecuteReader(CommandBehavior.SingleRow)
                            dr.Read()
                            If Not IsNothing(dr) Then
                                If dr.HasRows Then
                                    Console.Write("Success")
                                Else
                                  Console.Write("Success")
                                End If      '  If Not rec.EOF Then
                            End If   ' if not isnothing(dr) then
                            dr.Close()
                            dr = Nothing                
            End Using
        End Using
    End Sub


End Module
0
 

Author Comment

by:ocdc
Comment Utility
Thanks it is getting closer.  Now I am getting this error after executing the task

Execution Result window:


 Error: Could not create an instance of entrypoint class Main
             Task Check for Records in ADusers failed
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now