Solved

Converting VBScript to VB.NET

Posted on 2014-01-15
9
698 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
Industry Leaders: 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 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

685 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