[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Converting VBScript to VB.NET

Posted on 2014-01-15
9
Medium Priority
?
765 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
ID: 39783268
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
ID: 39783475
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
ID: 39783650
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:13Shadow
ID: 39783699
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
 

Author Comment

by:ocdc
ID: 39783837
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
ID: 39783873
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
ID: 39783941
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 2000 total points
ID: 39784020
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
ID: 39784149
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

872 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