VB: How to Transfer Data from a DataGrid to Online SQL Table dynamically?

Question

I'm very new to VB, I was able to get data from a proprietary database into a DataGrid view, however now, I need to export that data to an online SQL table, similar to what is happening with the DataGrid

This is the code that works for the Datagrid, I have tried a couple of options, but currently I have NO idea on how to generate the same data dynamically to an online SQl table (of which I now virtually nothing)

---

Private Sub Search(cabID As String, docTypeID() As String)
    Dim lSearch As New Generic.List(Of SDKClientAccess.HitlistSearchItem)
    For Each ctrl As Control In Me.panFields.Controls
        If TypeOf ctrl Is TextBox AndAlso Not ctrl.Text Is Nothing AndAlso ctrl.Text.Length > 0 Then
            Dim hsItem As New SDKClientAccess.HitlistSearchItem
            hsItem.MetaField = SDKClientAccess.SearchMetaFields.State
            hsItem.Mode = SDKClientAccess.SearchModes.AND
            hsItem.FieldID = ctrl.Tag.ToString
            hsItem.Operator = SDKClientAccess.SearchOperators.Equal
            hsItem.StartValue = ctrl.Text
            'hsItem.EndValue = "" use when Operator is set to FDClientAccess.SearchOperators.Between
            lSearch.Add(hsItem)
        End If
    Next
    Dim resHitlist As SDKClientAccess.HitlistResults = _cAccess.GetHitlist(cabID, docTypeID, lSearch.ToArray)
    If resHitlist Is Nothing Then
        MessageBox.Show("No matches found!", "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        dgHitlist.DataSource = Nothing
        txtDocGUID.Text = Nothing
    Else
        Dim dt As New DataTable
        dt.Columns.Add("ActRevision")
        dt.Columns.Add("Deleted")
        dt.Columns.Add("Guid")
        dt.Columns.Add("Pages")
        dt.Columns.Add("RefGuid")
        dt.Columns.Add("Size")
        dt.Columns.Add("State")
        For Each Val As SDKClientAccess.HitlistCell In resHitlist.Tables(0).Rows(0).Values
            dt.Columns.Add(Val.ID)
        Next

        For Each hRow As SDKClientAccess.HitlistRow In resHitlist.Tables(0).Rows
            Dim dr As DataRow = dt.NewRow()
            dr("ActRevision") = hRow.ActRevision
            dr("Deleted") = hRow.Deleted
            dr("Guid") = hRow.Guid
            dr("Pages") = hRow.Pages
            dr("RefGuid") = hRow.RefGuid
            dr("Size") = hRow.Size
            dr("State") = hRow.State
            For Each Val As SDKClientAccess.HitlistCell In hRow.Values
                dr(Val.ID) = Val.Value
            Next
            dt.Rows.Add(dr)
        Next

        dgHitlist.DataSource = dt
        txtDocGUID.Text = DirectCast(dgHitlist.DataSource, DataTable).Rows(dgHitlist.CurrentRowIndex)("GUID").ToString
    End If
End Sub

---

I'd really appreciate help in solving this!

Thanks to everyone!

Answer 1

Ok, I was able to solve my problem. However, if you know of a better or more efficient way, please let me know.

---

Private Sub Search(cabID As String, docTypeID() As String)


    Dim conStr As String = "Server=xxxxxxx.database.windows.net;Database=ECM_Project;User Id=xxxxx;Password=xxxxxx"

    Dim lSearch As New Generic.List(Of SDKClientAccess.HitlistSearchItem)
    For Each ctrl As Control In Me.panFields.Controls
        If TypeOf ctrl Is TextBox AndAlso Not ctrl.Text Is Nothing AndAlso ctrl.Text.Length > 0 Then
            Dim hsItem As New SDKClientAccess.HitlistSearchItem
            hsItem.MetaField = SDKClientAccess.SearchMetaFields.State

            hsItem.Mode = SDKClientAccess.SearchModes.AND
            hsItem.FieldID = ctrl.Tag.ToString
            hsItem.Operator = SDKClientAccess.SearchOperators.Equal
            hsItem.StartValue = ctrl.Text
            lSearch.Add(hsItem)
        End If
    Next
    Dim resHitlist As SDKClientAccess.HitlistResults = _cAccess.GetHitlist(cabID, docTypeID, lSearch.ToArray)

    If resHitlist Is Nothing Then
        MessageBox.Show("No matches found!", "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        dgHitlist.DataSource = Nothing
        txtDocGUID.Text = Nothing
    Else
        Dim dt As New DataTable
        dt.Columns.Add("ActRevision")
        dt.Columns.Add("Deleted")
        dt.Columns.Add("Guid")
        dt.Columns.Add("Pages")
        dt.Columns.Add("RefGuid")
        dt.Columns.Add("Size")
        dt.Columns.Add("State")
        For Each Val As SDKClientAccess.HitlistCell In resHitlist.Tables(0).Rows(0).Values
            dt.Columns.Add(Val.ID)
        Next

        For Each hRow As SDKClientAccess.HitlistRow In resHitlist.Tables(0).Rows
            Dim dr As DataRow = dt.NewRow()
            dr("ActRevision") = hRow.ActRevision
            dr("Deleted") = hRow.Deleted
            dr("Guid") = hRow.Guid
            dr("Pages") = hRow.Pages
            dr("RefGuid") = hRow.RefGuid
            dr("Size") = hRow.Size
            dr("State") = hRow.State
            For Each Val As SDKClientAccess.HitlistCell In hRow.Values
                dr(Val.ID) = Val.Value

                Dim Filename As String = ""
                Dim MainCategoryID As String = ""
                Dim SubCategoryID As String = ""
                Dim MainClassificationID As String = ""
                Dim SubClassificatioID As String = ""
                Dim PropertyCode As String = ""
                Dim PropertyName As String = ""
                Dim Region As String = ""
                Dim Sector As String = ""


                If (Val.ID = "3511DC38") Then
                    Filename = CStr(Val.Value)
                End If

                If (Val.ID = "935FDDCF") Then
                    MainCategoryID = CStr(Val.Value)
                End If

                If (Val.ID = "3A668646") Then
                    SubCategoryID = CStr(Val.Value)
                End If

                If (Val.ID = "824C0FD3") Then
                    MainClassificationID = CStr(Val.Value)
                End If

                If (Val.ID = "3A317B82") Then
                    SubClassificatioID = CStr(Val.Value)
                End If

                If (Val.ID = "C4078214") Then
                    PropertyCode = CStr(Val.Value)
                End If

                If (Val.ID = "36AC71D6") Then
                    PropertyName = CStr(Val.Value)
                End If

                If (Val.ID = "3A317B82") Then
                    Region = CStr(Val.Value)
                End If

                If (Val.ID = "3A317B82") Then
                    Sector = CStr(Val.Value)
                End If

                Dim LVRow As String = ""

                Dim objCon As New SqlConnection(conStr)
                Dim obj As SqlCommand
                Dim strSQL As String = ""


                If objCon.State = ConnectionState.Closed Then
                    Try
                        objCon.Open()
                        obj = objCon.CreateCommand()

                        strSQL = "INSERT INTO [dbo].[ECM_GridItems_v2]([ID],[FileName],[Length],[MainCategoryID],[SubCategoryID],[MainClassificationID],[SubClassificatioID],[PropertyCode],[PropertyName],[Region],[Sector]) VALUES('" + hRow.Guid + "','" + Filename + "','" + hRow.Size.ToString() + "','" + MainCategoryID + "','" + SubCategoryID + "','" + MainClassificationID + "','" + SubClassificatioID + "' ,'" + PropertyCode + "','" + PropertyName + "','" + Region + "' ,'" + Sector + "')"

                        obj.CommandText = strSQL
                        obj.ExecuteNonQuery()
                        objCon.Close()
                        objCon = Nothing

                    Catch ex As Exception
                        objCon.Close()
                        objCon = Nothing

                        MessageBox.Show(ex.Message)
                    End Try
                End If

            Next
            dt.Rows.Add(dr)
        Next
        dgHitlist.DataSource = dt
        txtDocGUID.Text = DirectCast(dgHitlist.DataSource, DataTable).Rows(dgHitlist.CurrentRowIndex)("GUID").ToString
    End If
End Sub

---