Improve company productivity with a Business Account.Sign Up

x
?
Solved

Converting VBScript to VB.NET

Posted on 2014-01-15
9
Medium Priority
?
808 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
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…
When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…
If you are looking for an automated tool which can generate reports for Outlook emails and other items from PST file, then you can go for Kernel PST Reporter tool. The reports which are created by this tool are helpful to analyze and understand PST …

595 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