DGV not showing data from SQL query RRS feed

  • Question

  • I have a DGV that loads just fine on another DGV in my app.

    My question is why doesn't the same code work on a 2nd DGV.

    DataGridView2 is all setup on form.load:

    ' DGV inventory view setup
    		ViewInventory.DataGridView2.Columns.Add(0, "Name")
    		ViewInventory.DataGridView2.Columns.Add(1, "Quantity on Hand")
    		ViewInventory.DataGridView2.Columns.Add(2, "Price")
    		ViewInventory.DataGridView2.Columns(0).Width = 200
    		ViewInventory.DataGridView2.Columns(1).Width = 100
    		ViewInventory.DataGridView2.Columns(2).Width = 50
    		' do not show row header
    		ViewInventory.DataGridView2.RowHeadersVisible = False
    		' do not allow click in the header area
    		For m As Integer = 0 To ViewInventory.DataGridView2.ColumnCount - 1
    			ViewInventory.DataGridView2.Columns(m).SortMode = DataGridViewColumnSortMode.NotSortable
    		ViewInventory.DataGridView2.Columns(0).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
    		ViewInventory.DataGridView2.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
    		ViewInventory.DataGridView2.Columns(1).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
    		ViewInventory.DataGridView2.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
    		ViewInventory.DataGridView2.Columns(2).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
    		ViewInventory.DataGridView2.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
    		ViewInventory.DataGridView2.ReadOnly = True
    		' sort dgv ascending
    		ViewInventory.DataGridView2.Sort(ViewInventory.DataGridView2.Columns(0), System.ComponentModel.ListSortDirection.Ascending)

    I have a button control to click and start the 'ViewInventory' form:

    Private Sub ViewInventory_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    		' get data and put into dgv *****************************************************
    		DataGridView2.Rows(0).Selected = True
    		PictureBox1.Image = Nothing
    		'get selected mold name data from name column and assign to local var
    		inventoryImage = DataGridView2.CurrentRow.Cells(0).Value.ToString
    		Dim imgFileJpg As String = StartForm.GlobalVariables.ImgFolder & inventoryImage & ".jpg"
    		Dim imgFilePng As String = StartForm.GlobalVariables.ImgFolder & inventoryImage & ".png"
    		If System.IO.File.Exists(imgFileJpg) Then
                PictureBox1.Image = Image.FromFile(imgFileJpg)
            ElseIf System.IO.File.Exists(imgFilePng) Then
                PictureBox1.Image = Image.FromFile(imgFilePng)
    			PictureBox1.Image = Image.FromFile("C:\StatuarySalesPlus\images\nopic.png")
    		End If
        End Sub

    And lastly, my function that is called from the 'ViewInventory' form:

    Public Function LoadDgvInventoryView()
    			Using con As OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\StatuarySalesPlus\DB\SSPOS.accdb")
    				Dim ds As DataSet = New DataSet
    				Dim adapter As New OleDb.OleDbDataAdapter
    				Dim sql As String
    				sql = "SELECT ProductName, QtyOnHand, ProductPrice FROM Inventory"
    				adapter.SelectCommand = New OleDb.OleDbCommand(sql, con)
    				'For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
    				'	Dim someVar As String = (ds.Tables(0).Rows(i)(0).ToString())
    				'	MsgBox(someVar)
    				ViewInventory.DataGridView2.DataSource = ds.Tables(0)
    			End Using
    		End Try
    		Return Nothing
    	End Function

    As you see I have inserted a msgbox (temporarily commented out) to test the data contents of the dataset table. It returns the correct values from the DB. But never gets loaded into the DGV as it returns this error on executing the form load for 'InventoryView':

    Here is the data being extracted from the DB for the DGV:

    I would appreciate a little help on this one- could be simple that I am not seeing.

    The msgbox that is in use before the actual variable assignment is to see the value before executing.

    Thanks in advance, Pete

    • Edited by Pete - Friday, February 22, 2019 8:08 PM
    Friday, February 22, 2019 7:55 PM

All replies

  • Sounds to me like the DataGridView (even though I see it) DataSource is not getting set or cleared. The first thing to do (then follow the bullets below if needed) is set a breakpoint in the first line loading data and step through each line of code especially where the DataSource for the DataGridView is being set. 

    I would suggest 

    • Turning Option Strict On (there will be at least one error on the last function which should be a Sub).
    • Transfer all code in form load, create a form shown event, copy code from the load event into the shown event so there is nothing left in form load.

    Why do I think this? Because I can simulate a load as follows where one row value is Nothing/null and zero errors.

    Option Strict On
    Option Infer On
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) _
            Handles Button1.Click
        End Sub
        Private Sub Form1_Load(sender As Object, e As EventArgs) _
            Handles MyBase.Load
            Dim dt As New DataTable
            dt.Columns.Add(New DataColumn() With
                  .ColumnName = "FirstName",
                  .DataType = GetType(String)
            dt.Rows.Add(New Object() {Nothing})
            DataGridView1.DataSource = dt
            DataGridView1.AutoGenerateColumns = False
        End Sub
    End Class

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, February 22, 2019 8:39 PM
  • Thanks for your input Karen. I didn't even notice I had 'option strict on' not included- I generally always have it on.

    I will run through some diags with breakpoints.

    Friday, February 22, 2019 8:50 PM
  • In your current approach, make sure that you display the form using ViewInventory.ShowDialog or ViewInventory.Show. Do not create it with ‘New ViewInventory’.

    Saturday, February 23, 2019 9:37 AM