?
Solved

Converting VBScript to VB.NET

Posted on 2014-01-15
9
Medium Priority
?
722 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 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