CODEWITHSUNDEEP
vb.netdatatabledatagridsqlcommanddataadapter
PeterF
PeterF

Reputation: 47

In VB.Net, how to repopulate a datagrid from scratch?

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.

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.

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

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    Dim con As New SqlConnection("<Enter Connection string here>")
    Dim rcd As ADODB.Recordset = ReturnRecordset("Select * From ExcludeWords")
    Call DefineDataGrid("Select * From ExcludeWords")
End Sub

Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click
    Call DefineDataGrid("Select * From FindWords")
End Sub

Sub DefineDataGrid(ByVal strQuery As String)
    Dim con As New SqlConnection("<Enter Connection String Here>")
    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 = "<Enter Connection string here>"

    ReturnRecordset = New ADODB.Recordset
    ReturnRecordset.Open(strQuery, con)
End Function

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.
Sincerely, ~ Peter Ferber

Upvotes: 0

Views: 750

Answers (1)

Hursey
Hursey

Reputation: 551

So, made a couple modifications to the DefineDataGrid code you've posted. Seems to be working for me. I suspect might be something to do with the life cycle of either your cmd or con objects causing your problem. Parsing a number of different number of queries through to it and it rebuilds the datagridview correctly

Sub DefineDataGrid(ByVal strQuery As String)
    Dim dt As New DataTable
    FindWordGrid.DataSource = Nothing
    Using con As New SqlConnection("Your Connection String Here")
        Using cmd As New SqlCommand(strQuery, con)
            Dim da = New SqlDataAdapter(cmd)
            da.Fill(dt)
            FindWordGrid.DataSource = dt
        End Using
    End Using
End Sub

Changed the obvious module level implementations of con and cmd to local variables, and since both types implement IDisposable, wrapped them in a Using pattern

Upvotes: 2

Related Questions