title: In VB.Net, how to repopulate a datagrid from scratch?
body: <p>I have written a generic VB.net subroutine that populates a datagrid with the results of a query.  The subroutine as one parameter, strQuery, which is a string that can translate to any valid SQL-Server view or stored procedure that returns a recordset.  Other than that, there is no constraint on the SQL code, and sending two queries that have entirely different field profiles is a valid proposition.</p>
  <p>In order to get this to work, I must completely purge the data grid of whatever dataset had been there previously, thus allowing the control to drop its prior identity and start over, allowing, from scratch, the new dateset to redefine the control's contents.</p>
  <p>I have finally solved the problem.  Perhaps I should have mentioned that I am using Visual Studio 2010, and that if Hersey were using a later version, then the code that worked for him may not have worked for me.  The change to my code is one additional line: setting both the name and datapropertyname to the same name. I noticed that when I went to look at the column view, I noticed that the datapropertyname is how the table links to the source, and the name is an effective alias for the field, how it will be presented.  Obviously, for clarity sake, both must be the same!  This now works as advertised.
  Thanks, ~ Peter Ferber</p>
  <pre><code>Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
      Dim con As New SqlConnection(&quot;&lt;Enter Connection string here&gt;&quot;)
      Dim rcd As ADODB.Recordset = ReturnRecordset(&quot;Select * From ExcludeWords&quot;)
      Call DefineDataGrid(&quot;Select * From ExcludeWords&quot;)
  End Sub
  
  Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click
      Call DefineDataGrid(&quot;Select * From FindWords&quot;)
  End Sub
  
  Sub DefineDataGrid(ByVal strQuery As String)
      Dim con As New SqlConnection(&quot;&lt;Enter Connection String Here&gt;&quot;)
      Dim dt As New DataTable
  
      FindWordGrid.Columns.Clear()
      FindWordGrid.DataSource = Nothing
  
      Dim rcd As ADODB.Recordset = ReturnRecordset(strQuery)
  
      Dim MyField As ADODB.Field
  
      Dim iCount As Integer = -1
      FindWordGrid.ColumnCount = rcd.Fields.Count
      For Each MyField In rcd.Fields
          iCount = iCount + 1
          FindWordGrid.Columns(iCount).Name = MyField.Name
          FindWordGrid.Columns(iCount).DataPropertyName = MyField.Name
      Next
  
      Dim cmd As New SqlCommand(strQuery, con)
      Dim da = New SqlDataAdapter(cmd)
      da.Fill(dt)
      FindWordGrid.DataSource = dt
  End Sub
  
  Function ReturnRecordset(strQuery As String) As ADODB.Recordset
      Dim con As ADODB.Connection = &quot;&lt;Enter Connection string here&gt;&quot;
  
      ReturnRecordset = New ADODB.Recordset
      ReturnRecordset.Open(strQuery, con)
  End Function
  </code></pre>
  <p>My setup is easy to reproduce: I am using datasets with only a handful of records in each respective table.  The only constraint is that the respective runs must have a different field profile.  I have been experimenting with different techniques for much of the day, and I now think it best to get some new grey matter on the subject.  Getting this process correctly is the last major hurdle for me, in making a key presentation I wish to make, in lobbying for a job role.  Thanks, in advance, for your comments, thoughts, and ideas.<br />
  Sincerely, ~ Peter Ferber</p>