Looking for alternative method to OpenDialog to populate DataGridView

[akomarek]

I am working with VB.NET in VS2013 and need an alternative to the way I am using a Main form (frmMain) search of a SQL Server db to populate a DataGridView that is populated from this search on another form (frmSearch). I am currently using a ShowDialog() Function, which is the root of my problem. It works beautifully to populate the DataGridView on frmSearch, which I then am able click in the first cell of any row, and open a third form (frmInfo) that contains more detailed information for the user. If anyone has an alternative method to use rather than the ShowDialog method, that I’m using, I would be very grateful. Here is my code:

frmMain

Public Class frmMain
    Dim cnStr As String = "Data Source=**********;Initial Catalog=TrackRFA;Persist Security Info=True;User ID=*******;Password=*******"
    Dim cn As New SqlConnection(cnStr)
    Dim com As SqlCommand
    Dim SeQL As String = ""
    Dim results As New frmSearch
    Dim hasResults As Boolean = False
    Dim dr As SqlDataReader
    Dim cmd As New SqlCommand()
    
Private Sub btnFind_Click(ByVal sender As System.Object, e As System.EventArgs) Handles btnFindR.Click
    
SeQL = "select TrackNo,ProjectNo,VendorName,ProjectDescription from " & cboArea.SelectedItem & " where TrackNo LIKE '%" & txtFindR.Text & "%'"

        'Create a New Command
        With cmd
            .CommandText = SeQL
            .Connection = cn
        End With
        cn.Open()
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        hasResults = dr.Read()
        cn.Close()

        'Check to see if a record exists
        If hasResults = False Then
            MsgBox("No Results Found for that entry")
            ReDo()
        Else

            com = New SqlCommand(SeQL, cn)

            If results.ShowDialog(com) = Nothing Then
                End
            Else
'This shows form and waits for form to close before allowing execution to advance.
                results.ShowDialog(com)
            End If
        End If

        Me.txtFindR.Text = ""
        Me.SendToBack()
    End Sub

frmSearch

Public Class frmSearch
    Public Shared RFA As String
    Public Shared Tabl As String
    Public Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles RFAGrid.CellContentClick

        If e.ColumnIndex <> 0 Then
            Exit Sub
        End If

        RFA = RTrim(CStr(RFAGrid.Rows(e.RowIndex).Cells(0).Value))
        'This alows the user to open the selected record or cancel their action
        If MsgBox("Do you want to open RFA Record ID: " & RTrim(RFA) & " ?", CType(MessageBoxButtons.YesNo, MsgBoxStyle), "View this Record?") = MsgBoxResult.Yes Then

            Tabl = Trim(CStr(frmMain.cboArea.SelectedItem))

            If Tabl = "Engineering" Or Tabl = "AssetManagement" Then
                uxOpenForm_Load(RFA, Nothing)
                frmInfo.FillForm()
            ElseIf Tabl = "StreetStormwater" Or Tabl = "UtilityBoard" Or Tabl = "WaterSewer" Then
                uxOpenForm2_Load(RFA, Nothing)
                frmInfo2.FillForm()
            ElseIf Tabl = "RFA" Then
                uxOpenForm3_Load(RFA, Nothing)
                frmInfo3.FillForm()
            ElseIf Tabl = "Vision2025" Then
                uxOpenForm4_Load(RFA, Nothing)
                frmInfo4.FillForm()
            End If
        Else
            Me.Show()
        End If

    End Sub

    Public Overloads Function ShowDialog(ByVal com As SqlCommand) As System.Windows.Forms.DialogResult
        Dim daSearch As SqlDataAdapter
        Dim showForm As Boolean = True
        Try
            'Create a new instance of a SqlDataAdapter
            daSearch = New SqlDataAdapter(com)

            'Create a command builder to generate SQL update to update the data base.
            Dim cb As New SqlCommandBuilder(daSearch)

            'Populate a New DataTable and bind it to the binding source.
            Dim table As New DataTable()

            daSearch.Fill(table)
            Me.bsData.DataSource = table

            If table.Rows.Count = 0 Then
                MsgBox("The value you have selected does match any Record!")
                End
            End If

            'Call Sub to resize the DataGridView to fit the new content
            SizeAllColumns(Nothing, Nothing)

 Catch ex As Exception
            MessageBox.Show(ex.Message)
            'If there is an exception getting the data from the database don't show the form
             showForm = False
        End Try

        If showForm Then
            'Show the form
            Return MyBase.ShowDialog()
        Else
            'Return as if the user clicked cancel
            Return DialogResult.Cancel
        End If

    End Function

    Private Sub frmSearch_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Bind the DataGridView to the BindingSource
        Me.RFAGrid.DataSource = Me.bsData
        SizeAllColumns(Nothing, Nothing)
    End Sub

    Private Sub SizeAllColumns(ByVal sender As Object, ByVal e As System.EventArgs)
        'resize the DataGridView to fit the new content
        RFAGrid.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
    End Sub

    Private Sub uxOpenForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
        frmInfo.TracNo.Text = RFA
        frmInfo.txtDept.Text = Tabl
        frmInfo.Show()
    End Sub

frmInfo

Public Class frmInfo
    Public Shared r As Integer
    Public LookAt As String
    Public TheDept As String
    Public Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17, Col18, Col19, Col20 As String

    Sub frmInfo_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        LookAt = Me.TracNo.Text
        TheDept = Me.txtDept.Text
        FillForm()
    End Sub

    Public Sub FillForm()
        Dim conStr As String = "Data Source=*******;Initial Catalog=TrackRFA;Persist Security Info=True;User ID=*******;Password=*******"
        Dim linkToDB As New SqlConnection(conStr)
        Dim LookAt As String
        Dim TheDept As String
        Dim results As New frmSearch
        Dim output As New List(Of String)()
        linkToDB.Open()

        LookAt = Me.TracNo.Text
        TheDept = Me.txtDept.Text
        
        Dim sqlText As String = "select * from " & TheDept & " where TrackNo LIKE '%" & LookAt & "%'"
        Dim dataAction As New SqlCommand(sqlText, linkToDB)

        Dim scanCustomer As SqlDataReader = dataAction.ExecuteReader()

        If (scanCustomer.HasRows = True) Then
            Do While scanCustomer.Read()
                Console.WriteLine(" Your name is: " & Convert.ToString(scanCustomer(0)))
                Col1 = Convert.ToString(scanCustomer(0))
                Col2 = Convert.ToString(scanCustomer(1))
                Col3 = Convert.ToString(scanCustomer(2))
                Col4 = Convert.ToString(scanCustomer(3))
                Col5 = Convert.ToString(scanCustomer(4))
                Col6 = Convert.ToString(scanCustomer(5))
                Col7 = Convert.ToString(scanCustomer(6))
                Col8 = Convert.ToString(scanCustomer(7))
                Col9 = Convert.ToString(scanCustomer(8))
                Col10 = Convert.ToString(scanCustomer(9))
                Col11 = Convert.ToString(scanCustomer(10))
                Col12 = Convert.ToString(scanCustomer(11))
                Col13 = Convert.ToString(scanCustomer(12))
                Col14 = Convert.ToString(scanCustomer(13))
                Col15 = Convert.ToString(scanCustomer(14))
                Col16 = Convert.ToString(scanCustomer(15))
                Col17 = Convert.ToString(scanCustomer(16))
                Col18 = Convert.ToString(scanCustomer(17))
                Col19 = Convert.ToString(scanCustomer(18))
                Col20 = Convert.ToString(scanCustomer(19))

            Loop
        End If

        Me.TracNo.Text = LookAt
        Me.ProjNo.Text = Col2
        Me.ContNo.Text = Col3
        Me.EaseNo.Text = Col4
        Me.ssNo.Text = Col5
        Me.OrdNo.Text = Col6
        Me.baNo.Text = Col7
        Me.FunNo.Text = Col8
        Me.ResNo.Text = Col9
        Me.DeeNo.Text = Col10
        Me.VenNam.Text = Col11
        Me.txtAmt.Text = Col12
        Me.ProDesc.Text = Col13
        Me.ProBeDat.Text = Col14
        Me.FilNam.Text = Col15
        Me.txtRMUA.Text = Col16
        Me.txtTMUA.Text = Col17
        Me.txtTARE.Text = Col18
        Me.txtMemo.Text = Col19
        Me.MayApp.Text = Col20

        scanCustomer.Close()
        linkToDB.Close()
        frmSearch.Owner.Close()
    End Sub

I have tried other methods and failed until I used the OpenDialog, which is a real pain but it works to a certain extent. The problem I am having with the ShowDialog is closing forms behind me, and trying use the print feature to open other forms to print a CrystalReportViewer. It keeps going back to the “If showForm Then” in the ShowDialog Function on the frmSearch form, and errors on the Return statement like it’s in an infinite loop or recursion. I would really like to find an alternative to the ShowDialog and populate the DataGridView differently on ‘frmSearch’. Thank you

[.paul.]

Public Overloads Function ShowDialog(ByVal com As SqlCommand) As System.Windows.Forms.DialogResult???

Public Sub New(ByVal com As SqlCommand)

[akomarek]

Public Overloads Function ShowDialog(ByVal com As SqlCommand) As System.Windows.Forms.DialogResult???

Public Sub New(ByVal com As SqlCommand)

I tried the change you suggested and got a number of errors on frmMain now "Runtime errors might occur when converting 'System.Data.SqlClient.SqlCommand' to 'System.Windows.Forms.IWin32Window'":

            If results.ShowDialog(com) = Nothing Then
                End
            Else
                'This shows the form and waits for the form to close before allowing execution to advance further.
                results.ShowDialog(com)
            End If

[techgnome]

I'm going to try and decipher the situation here...

You have a form... let's call it "Form1" ... on this form you have a button that shows another form - we'll call it "Form2" ... on this form2, there is a grid that shows search results. There is then code from there that then shows another form, let's call that one Form3 ... ... and in each case you used FormX.ShowDialog so now you have all these forms open andcan't get back to where you started, right?


You have the wrong setup. You should use OpenDialog for just that DIALOG... pausing execution while you wait for user interaction, then continue on.

At the moment you've daisy-chained the forms.
Instead, rather you should have is form1, shows an instance of Form2 and waits for the user's response. IF the user then clicks "OK", have Form2 return the selection (typically through a property). From Form1 it looks like this:

Dim F2 as New Form2
If F2.ShowDialog = DialogResult.OK Then
  MyData = F2.GetData
Else
'User clicked cancel, do what you need to do here
End If

Once you then have the data, you can continue on. OK, so the next step is to show Form3, right? Same deal...

Dim F2 as New Form2
If F2.ShowDialog = DialogResult.OK Then
  'Get the data via a property in Form2
  MyData = F2.SomeData
  Dim F3 as New Form3
  'PAss data to F3
  F3.SomeData = myData 'Pass it through a property
  'Show Form3
  F3.ShowDialog 'If the OK/Cancel or response from the user is important, wrap this with an If statement as well... if not, this is sufficient to show the form
Else
'User clicked cancel, do what you need to do here
End If

Now it's the main form controlling the flow, as it should be.

You can also create a constructor that takes the data:

Dim F2 as New Form2
If F2.ShowDialog = DialogResult.OK Then
  'Get the data via a property in Form2
  MyData = F2.SomeData
  'PAss data to F3
  Dim F3 as New Form3(MyData) 'PAss the data via constructor
  'Show Form3
  F3.ShowDialog 'If the OK/Cancel or response from the user is important, wrap this with an If statement as well... if not, this is sufficient to show the form
Else
'User clicked cancel, do what you need to do here
End If

Note: this is a very simplified version as I didn't feel like having to untangle your code, which I think has over complicated the situation, and is partly why I'm not 100% sure I actually understand the issue

-tg

[akomarek]

I'm going to try and decipher the situation here...

You have a form... let's call it "Form1" ... on this form you have a button that shows another form - we'll call it "Form2" ... on this form2, there is a grid that shows search results. There is then code from there that then shows another form, let's call that one Form3 ... ... and in each case you used FormX.ShowDialog so now you have all these forms open andcan't get back to where you started, right?


You have the wrong setup. You should use OpenDialog for just that DIALOG... pausing execution while you wait for user interaction, then continue on.

At the moment you've daisy-chained the forms.
Instead, rather you should have is form1, shows an instance of Form2 and waits for the user's response. IF the user then clicks "OK", have Form2 return the selection (typically through a property). From Form1 it looks like this:

Dim F2 as New Form2
If F2.ShowDialog = DialogResult.OK Then
  MyData = F2.GetData
Else
'User clicked cancel, do what you need to do here
End If

Once you then have the data, you can continue on. OK, so the next step is to show Form3, right? Same deal...

Dim F2 as New Form2
If F2.ShowDialog = DialogResult.OK Then
  'Get the data via a property in Form2
  MyData = F2.SomeData
  Dim F3 as New Form3
  'PAss data to F3
  F3.SomeData = myData 'Pass it through a property
  'Show Form3
  F3.ShowDialog 'If the OK/Cancel or response from the user is important, wrap this with an If statement as well... if not, this is sufficient to show the form
Else
'User clicked cancel, do what you need to do here
End If

Now it's the main form controlling the flow, as it should be.

You can also create a constructor that takes the data:

Dim F2 as New Form2
If F2.ShowDialog = DialogResult.OK Then
  'Get the data via a property in Form2
  MyData = F2.SomeData
  'PAss data to F3
  Dim F3 as New Form3(MyData) 'PAss the data via constructor
  'Show Form3
  F3.ShowDialog 'If the OK/Cancel or response from the user is important, wrap this with an If statement as well... if not, this is sufficient to show the form
Else
'User clicked cancel, do what you need to do here
End If

Note: this is a very simplified version as I didn't feel like having to untangle your code, which I think has over complicated the situation, and is partly why I'm not 100% sure I actually understand the issue

-tg

Thank you for your assessment of my issue but I am able to "get back to where I started". Actually, the entire process works quite well the way it is until I try to add a Report form to it now. Everything runs fine and I can move between the forms ok, but it took me a while to figure out how to get around OpenDialog to have the final form come out on top, frmInfo (there are 4 of these that handle the output from the 7 different tables the user has to choose from, some have similar layouts.) there are frmInfo2, frmInfo3, and frmInfo4. frmMain provides a combobox for them to select the table they want to search and then they have 3 different options to search by; ID, Project, or Vendor, with a button next to each option (textbox) that begins the search process.
The issue I am running into was just the last straw for me with the OpenDialog and I wanted to see if I could find a completely different way to handle the search from the main form to the DataGridView on the search form. It's beginning to look like this is the best way to do it and I just need to work around these issues.
When I try to run the report from the frmInfo form, it errors on the frmSearch form at:

        If showForm Then
            'Show the form
            Return MyBase.ShowDialog()
            Application.OpenForms(DialogResult).SuspendLayout()
        Else
            'Return as if the user clicked cancel
            Return DialogResult.Cancel
        End If

With the following error:


I believe it is trying to Return because I am trying to open another form named 'frmRep1E' that contains a Crystal Report viewer. Reports are new to me and this looked like a good way to handle the reporting I needed to do. But I could be wrong. I don't get to code in VB very often, only as it is needed.