VB.Net Code Error

[Shyam_Pragash]

Hi Team,

Trying to insert to values to sql server as per the columns and datatype

am get this error

Code :

' Replace with your connection string
Dim connectionString As String = DBConnection.ToString

' Replace with your DataTable
Dim dataTable As DataTable = Dt_Excel

' Replace with your target SQL Server table name
Dim tableName As String = Table_Name

' Call the function to insert data into SQL Server table
 Try
      InsertDataIntoSQLServer(connectionString, tableName, dataTable)
      Console.WriteLine("Data inserted successfully.")
      Catch ex As Exception
         Console.WriteLine("Error: " & ex.Message)
End Try

Private Sub InsertDataIntoSQLServer(connectionString As String, tableName As String, dataTable As DataTable)
    Using connection As New SqlConnection(connectionString)
        connection.Open()

        ' Get column information from the target SQL Server table
        Dim targetColumns As List(Of String) = GetTargetTableColumns(connection, tableName)

        ' Create a mapping between DataTable columns and SQL Server table columns
        Dim columnMapping As Dictionary(Of String, String) = MapColumns(dataTable, targetColumns)

        ' Generate SQL command dynamically
        Dim sqlColumns As String = String.Join(", ", columnMapping.Values)
        Dim sqlValues As String = String.Join(", ", columnMapping.Keys.Select(Function(col) "@" & col))
        Dim sqlCommandText As String = $"INSERT INTO {tableName} ({sqlColumns}) VALUES ({sqlValues})"

        Using command As New SqlCommand(sqlCommandText, connection)

            ' Add parameters to the SqlCommand based on DataTable columns
            For Each kvp As KeyValuePair(Of String, String) In columnMapping
                command.Parameters.AddWithValue("@" & kvp.Key, dataTable.Rows(0)(kvp.Value))
            Next

            ' Execute the SQL command
            command.ExecuteNonQuery()
        End Using
    End Using
End Sub


Private Function GetTargetTableColumns(connection As SqlConnection, tableName As String) As List(Of String)
    Dim columns As New List(Of String)()

    Using command As New SqlCommand($"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableName}'", connection)
        Using reader As SqlDataReader = command.ExecuteReader()
            While reader.Read()
                columns.Add(reader.GetString(0))
            End While
        End Using
    End Using

    Return columns
End Function

Private Function MapColumns(dataTable As DataTable, targetColumns As List(Of String)) As Dictionary(Of String, String)
    Dim columnMapping As New Dictionary(Of String, String)()

    ' Perform column mapping based on name matching
    For Each targetColumn As String In targetColumns
        For Each dataColumn As DataColumn In dataTable.Columns
            If String.Compare(targetColumn, dataColumn.ColumnName, StringComparison.OrdinalIgnoreCase) = 0 Then
                ' Map the DataTable column to the corresponding SQL Server table column
                columnMapping.Add(dataColumn.ColumnName, targetColumn)
                Exit For
            End If
        Next
    Next

    Return columnMapping
End Function

Thanks
Shyam

[arjunshenoy]

Hi @Shyam_Pragash

There might be an issue with the structural syntax of the code. Please try the following & let us know if it works.

' Replace with your connection string
Dim connectionString As String = DBConnection.ToString

' Replace with your DataTable
Dim dataTable As DataTable = Dt_Excel

' Replace with your target SQL Server table name
Dim tableName As String = Table_Name

' Call the function to insert data into SQL Server table
Try
    InsertDataIntoSQLServer(connectionString, tableName, dataTable)
    Console.WriteLine("Data inserted successfully.")
Catch ex As Exception
    Console.WriteLine("Error: " & ex.Message)
End Try

Private Sub InsertDataIntoSQLServer(connectionString As String, tableName As String, dataTable As DataTable)
    Using connection As New SqlConnection(connectionString)
        connection.Open()

        ' Get column information from the target SQL Server table
        Dim targetColumns As List(Of String) = GetTargetTableColumns(connection, tableName)

        ' Create a mapping between DataTable columns and SQL Server table columns
        Dim columnMapping As Dictionary(Of String, String) = MapColumns(dataTable, targetColumns)

        ' Generate SQL command dynamically
        Dim sqlColumns As String = String.Join(", ", columnMapping.Values)
        Dim sqlValues As String = String.Join(", ", columnMapping.Keys.Select(Function(col) "@" & col))
        Dim sqlCommandText As String = $"INSERT INTO {tableName} ({sqlColumns}) VALUES ({sqlValues})"

        Using command As New SqlCommand(sqlCommandText, connection)

            ' Add parameters to the SqlCommand based on DataTable columns
            For Each kvp As KeyValuePair(Of String, String) In columnMapping
                command.Parameters.AddWithValue("@" & kvp.Key, dataTable.Rows(0)(kvp.Value))
            Next

            ' Execute the SQL command
            command.ExecuteNonQuery()
        End Using
    End Using
End Sub

Private Function GetTargetTableColumns(connection As SqlConnection, tableName As String) As List(Of String)
    Dim columns As New List(Of String)()

    Using command As New SqlCommand($"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableName}'", connection)
        Using reader As SqlDataReader = command.ExecuteReader()
            While reader.Read()
                columns.Add(reader.GetString(0))
            End While
        End Using
    End Using

    Return columns
End Function

Private Function MapColumns(dataTable As DataTable, targetColumns As List(Of String)) As Dictionary(Of String, String)
    Dim columnMapping As New Dictionary(Of String, String)()

    ' Perform column mapping based on name matching
    For Each targetColumn As String In targetColumns
        For Each dataColumn As DataColumn In dataTable.Columns
            If String.Compare(targetColumn, dataColumn.ColumnName, StringComparison.OrdinalIgnoreCase) = 0 Then
                ' Map the DataTable column to the corresponding SQL Server table column
                columnMapping.Add(dataColumn.ColumnName, targetColumn)
                Exit For
            End If
        Next
    Next

    Return columnMapping
End Function

Hope this helps,
Best Regards.

[Shyam_Pragash]

Hi @arjunshenoy

Thanks

Same issue

Can you check and share the xaml file…

Thanks
Shyam

[gRao]

Hi @Shyam_Pragash ,

In Invoke code activity …Imports or the function/sub line are not needed in invoke code
You can break your requirements and try out the below steps:

• Use UiPath.Database.activities

• Create Connection Object

• Open Connection

• Use ExecuteQuery Activity and pass the query to fetch the columns

• Map the columns /Rearrange Datatable columns comparing the SQL Table columns

• Use ExecuteNonQuery and pass the insert query and datatable to insert the data to SQL Server table

• Close the connection

• Sorround the activities in a try catch Block and close the DB connection on success/exception at both places.

Hope this helps.

Regards,
Geetishree Rao

[Shyam_Pragash]

Hi @gRao

Already created the follow are per your guide

I am getting error on Invoke Vb.net Code activtiy. Error details already share.

Thanks
Shyam

[Shyam_Pragash]

Hi Team,

Any idea about this ???

Can you ping me…

Thanks
Shyam

[Yoichi]

Hi,

Basically we cannot use definition of sub or function such as Private Sub etc. in InvokeCode activity.

So it’s necessary to modify the code without them.

Regards,

[rajneesh94 (Rajneesh Khare)]

Hi @Shyam_Pragash,

The Invoke Code Activity does not support writing of Functions and Sub. You would have to try doing this using UiPath Activites. Alternatively you could try this modified code that does not have Function structure.

' Replace with your connection string
Dim connectionString As String = "Your_Connection_String_Here"

' Replace with your DataTable
Dim dataTable As DataTable = Dt_Excel

' Replace with your target SQL Server table name
Dim tableName As String = "Your_Table_Name_Here"

Try
    ' Insert data into SQL Server table
    Using connection As New SqlConnection(connectionString)
        connection.Open()

        ' Get column information from the target SQL Server table
        Dim targetColumns As New List(Of String)()
        Using command As New SqlCommand($"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableName}'", connection)
            Using reader As SqlDataReader = command.ExecuteReader()
                While reader.Read()
                    targetColumns.Add(reader.GetString(0))
                End While
            End Using
        End Using

        ' Create a mapping between DataTable columns and SQL Server table columns
        Dim columnMapping As New Dictionary(Of String, String)()
        For Each targetColumn As String In targetColumns
            For Each dataColumn As DataColumn In dataTable.Columns
                If String.Compare(targetColumn, dataColumn.ColumnName, StringComparison.OrdinalIgnoreCase) = 0 Then
                    ' Map the DataTable column to the corresponding SQL Server table column
                    columnMapping.Add(dataColumn.ColumnName, targetColumn)
                    Exit For
                End If
            Next
        Next

        ' Generate SQL command dynamically
        Dim sqlColumns As String = String.Join(", ", columnMapping.Values)
        Dim sqlValues As String = String.Join(", ", columnMapping.Keys.Select(Function(col) "@" & col))
        Dim sqlCommandText As String = $"INSERT INTO {tableName} ({sqlColumns}) VALUES ({sqlValues})"

        Using command As New SqlCommand(sqlCommandText, connection)
            ' Add parameters to the SqlCommand based on DataTable columns
            For Each kvp As KeyValuePair(Of String, String) In columnMapping
                command.Parameters.AddWithValue("@" & kvp.Key, dataTable.Rows(0)(kvp.Value))
            Next

            ' Execute the SQL command
            command.ExecuteNonQuery()
        End Using
    End Using

    Console.WriteLine("Data inserted successfully.")
Catch ex As Exception
    Console.WriteLine("Error: " & ex.Message)
End Try

Cheers

[Shyam_Pragash]

Hi @rajneesh94

Thanks you for reply.

Last one week i stuck this code…

as per your code i am getting an error…

Sharing the xaml file for your verification purpose…

Main.xaml (12.5 KB)

Thanks
Shyam

[rajneesh94 (Rajneesh Khare)]

In the Import Panel add these imports:

System.Data.SqlClient
System.Data

Alternatively in the code replace ‘SqlConnection’ with ‘System.Data.SqlClient.SqlConnection’. This will surely resolve the error. If it helps mark this as a solution.

Cheers

[Shyam_Pragash]

Hi @rajneesh94

As per your guide import the namespace and replace it Compile error is fixed…

but running the code this error thrown

My Final Goal :

i am trying to convert and insert values from Datable Columns datatype on based sql server columns, datatype matches. if not matches convert as per sql server columns datatype. in above sql server columns name are same in datatable columns. finally datable values to be insert into sql server columns.

Thanks
Shyam

[rajneesh94 (Rajneesh Khare)]

@Shyam_Pragash, now you can find multiple approaches, debug them and choose which one suits your requirement.

Kindly mark the above post as solution.

Thanks