Question DataGridView and Nested Connections

[STP_Captain_Slow]

Hi

This is my first post and i hope i'm not asking too much.....

I am new(ish) to Visual Basic and i have learned from looking at old VB6 code for programs that have been made for us in the past, this includes using the old ADODB connections and FlexGrids which both have been changed in VB.Net to OLEDB and DataGridView.

I have started to understand the way the connections and datasets etc are created but i am struggling to get to where i was with the old code (I am trying to completely upgrade the code from FlexGrid).

This is the part of the VB6 Code i am trying to upgrade from:

        Dim SQLConn As New ADODB.Connection
        Dim SQLConn2 As New ADODB.Connection
        Dim SQLCommand As New ADODB.Command
        Dim SQLCommand2 As New ADODB.Command
        Dim SQLRecSet As New ADODB.Recordset
        Dim SQLRecSet2 As New ADODB.Recordset
        Dim varSQLString As String
        Dim varSQLString2 As String
        Dim varFlxRow As Short
        Dim varDescTemp As String
        Dim varFirstHash As Integer
        Dim varExistBol As Boolean
        'Dim varExist As Integer

        Me.flxProduct.Clear()

        SQLConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                   "Persist Security Info=False;" & _
                                   "Data Source = DATAPATH

        SQLConn2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                   "Persist Security Info=False;" & _
                                   "Data Source = DATAPATH


        varSQLString = "SELECT DISTINCT LEFT(ProductBarcode,13) AS Code FROM tblProduct "

        varSQLString = varSQLString & " WHERE ProductBarcode LIKE '%" & frmWebUpdate.txtCode.Text & "%' OR Description LIKE '%" & frmWebUpdate.txtCode.Text & "%'"

        varSQLString = varSQLString & " ORDER BY LEFT(ProductBarcode,13) "

        'Open the database.
        SQLConn.Open()

        SQLCommand.CommandText = "tblProduct"
        SQLCommand.CommandType = ADODB.CommandTypeEnum.adCmdTable
        SQLRecSet.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        SQLRecSet.CursorType = ADODB.CursorTypeEnum.adOpenStatic

        SQLConn2.Open()

        SQLCommand2.CommandText = "tblProduct"
        SQLCommand2.CommandType = ADODB.CommandTypeEnum.adCmdTable
        SQLRecSet2.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        SQLRecSet2.CursorType = ADODB.CursorTypeEnum.adOpenStatic

        SQLRecSet.Open(varSQLString, SQLConn, ADODB.CursorTypeEnum.adOpenStatic)
        If SQLRecSet.RecordCount <> 0 Then
            
            varFlxRow = 0
            SQLRecSet.MoveFirst()
            Do While Not SQLRecSet.EOF
                'frmWebUpdate.lblInfo.Text = "Retrieving Data"
                System.Windows.Forms.Application.DoEvents()
                'Run a Second Query to retrieve a single description

                varSQLString2 = "SELECT Description FROM tblProduct WHERE ProductBarcode LIKE '" & SQLRecSet.Fields("Code").Value & "%'"

                                SQLRecSet2.Open(varSQLString2, SQLConn2, ADODB.CursorTypeEnum.adOpenStatic)
                SQLRecSet2.MoveFirst()
                varFlxRow = varFlxRow + 1
                Me.flxProduct.Row = varFlxRow
                Me.flxProduct.Col = 0
                Me.flxProduct.Text = SQLRecSet.Fields("Code").Value
                Me.flxProduct.Col = 1

                varDescTemp = SQLRecSet2.Fields("Description").Value

                varExistBol = varDescTemp Like "*10-11*" Or varDescTemp Like "*09-10*" Or varDescTemp Like "*08-09*" Or varDescTemp Like "*09-11*"
                txtExistBol.Text = varExistBol

                varFirstHash = varDescTemp.IndexOf("-")
                txtHash.Text = varFirstHash

                If varExistBol = True Then
                    Me.flxProduct.Text = " " + varDescTemp.Substring(0, varFirstHash + 4)
                Else
                    Me.flxProduct.Text = " " + varDescTemp.Substring(0, varFirstHash)
                End If

                SQLRecSet2.Close()
                SQLRecSet.MoveNext()
                frmWebUpdate.lblTest.Text = ""
                System.Windows.Forms.Application.DoEvents()
            Loop
        End If
        'Close Connection
        SQLConn.Close()

       
        Exit Sub

A quick summary of the code is that I create two Connection strings / commands and record sets. The first opens the database preforms the SELECT command and populates the RecordSet then I use the Second connection to pull the description that matches the results of the first statement and then populate the FlexGrid (a little bit of trimming is done on the Description before the FlexGrid is populated) I understand how this works as i just use the information from RecSet1 to populate Col1 then data from RecSet2 to populate Col2.

As with my VB.NET version I have managed to create the DataGridView and populate it with the first select statement which populates the Grid correctly according to the SELECT DISTINCT Statement. I now need to understand how i create the second connection and populate it with the column contents based on the results of the first statement, then populate the DataGridView with the information, as i can't see how it happens the same way as the VB6 version with the RecSet's.

VB.NET Code below:

Dim varConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= DATAPATH; Persist Security Info=False;"
        Dim oledbConn As New OleDbConnection(varConnString)

        Dim varSQLString As String

        'oledbConn.Open()

        varSQLString = "SELECT DISTINCT LEFT(ProductBarcode,13) AS ProductBarcode FROM tblProduct "

        varSQLString = varSQLString & " WHERE ProductBarcode LIKE '%" & frmWebUpdate.txtCode.Text & "%' OR Description LIKE '%" & frmWebUpdate.txtCode.Text & "%'"

        varSQLString = varSQLString & " ORDER BY LEFT(ProductBarcode,13) "

        Dim varCmd As OleDbCommand = New OleDbCommand(varSQLString, oledbConn)

        oledbConn.Open()

        varCmd.CommandType = CommandType.Text

        Dim varNewDA As OleDbDataAdapter = New OleDbDataAdapter
        varNewDA.SelectCommand = varCmd

        Dim varProductDT As DataTable = New DataTable()
        varNewDA.Fill(varProductDT)
        dgvProduct.DataSource = varProductDT

So I need some help to create the second nested connection and command as it is done in the VB6 version and then how I would populate the DataGridView correctly.


I was also wondering how I can populate a predefined DataGridView where I have added the Columns and set the Column Properties? This is because when I did that and then ran the above VB.NET code it showed my columns but populated new columns added to the end. Or if I can set the column properties after the DatGridView is populated as I need the columns to be read only.

        Dim dgvPB As New DataGridViewTextBoxColumn
        Dim dgvDesc As New DataGridViewTextBoxColumn

        'Col 0
        dgvPB = New DataGridViewTextBoxColumn
        dgvPB.Name = "Product Barcode"
        dgvProduct.Columns.Add(dgvPB)
        dgvProduct.Columns(0).Width = 130
        dgvProduct.Columns(0).FillWeight = 10
        dgvProduct.Columns(0).HeaderText = "Product Barcode"
        dgvPB.ReadOnly = True

        'Col 1
        dgvDesc = New DataGridViewTextBoxColumn
        dgvDesc.Name = "Description"
        dgvProduct.Columns.Add(dgvDesc)
        dgvProduct.Columns(1).Width = 250
        dgvProduct.Columns(1).FillWeight = 10
        dgvProduct.Columns(1).HeaderText = "Description"
        dgvDesc.ReadOnly = True

Thanks for any help or suggestions in advance!!!