Best way to populate controls ona windows form

[bodylojohn]

Hello everybody,

My question isn't so much that I am having a problem but rather a check to see if I am using the best ways to populate controls on a windows form.

Right now I retrieve a single record using a oledbdatareader (or sqldatareader depending on the database I use).

An example code is shown below. And it works. But I was wondering if this is the best and fastest way.

 Private Sub vuldetails()
    Dim strvuldetails_sql As String = String.Format("SELECT BTW_ID,Code,Percentage,IsActief " & _
                                                    "FROM tblBTW " & _
                                                    "WHERE BTW_ID = {0}", intGeselecteerdeBTW)

    Dim cmdvuldetails As New OleDbCommand(strvuldetails_sql, connection)
    connection.Open()
    Dim reader As OleDbDataReader = cmdvuldetails.ExecuteReader

    While reader.Read()
      Me.txtID.Text = reader("BTW_ID").ToString
      Me.txtBtwCode.Text = reader("Code").ToString
      Me.txtPercentage.Text = reader("Percentage").ToString
      chkIsActief.Checked = CBool(reader("IsActief"))
      blnIsActief = CBool(reader("IsActief"))
    End While

    reader.Close()
    connection.Close()
    cmdvuldetails.Dispose()
    cmdvuldetails = Nothing
  End Sub

Thanks in advance for every input.

[jmcilhinney]

If you only want one record at a time then that's basically it. I would tend to use a Using statement to create and dispose all your objects though. It's also useless to set a local variable to Nothing like that, when the variable ceases to exist the very next line. Also, in many cases it might make more sense to get all the data up front into a DataTable and then bind that to your controls instead.

One point to note though: while it's not a big deal in this case because your value is a number, it's a bad habit to get into to insert variables into SQL code using string concatenation or the like. You should pretty much always use parameters. To learn why and how, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.

[bodylojohn]

JMC...

I read your blog and I changed the code to this:

 Private Sub vuldetails()
    Dim strvuldetails_sql As String = String.Format("SELECT BTW_ID,Code,Percentage,IsActief " & _
                                                    "FROM tblBTW " & _
                                                    "WHERE BTW_ID = @GeselecteerdeBTW")

    Using cmdvuldetails As New OleDbCommand(strvuldetails_sql, connection)
      connection.Open()

      With cmdvuldetails.Parameters
        .AddWithValue("@GeselecteerdeBTW", CInt(intGeselecteerdeBTW))
      End With

      Dim reader As OleDbDataReader = cmdvuldetails.ExecuteReader

      While reader.Read()
        Me.txtID.Text = reader("BTW_ID").ToString
        Me.txtBtwCode.Text = reader("Code").ToString
        Me.txtPercentage.Text = reader("Percentage").ToString
        chkIsActief.Checked = CBool(reader("IsActief"))
        blnIsActief = CBool(reader("IsActief"))
      End While

      reader.Close()
      connection.Close()
    End Using
  End Sub

The only thing I do not totally understand is how I can use the datatable here to load the data upfront...

[Shaggy Hiker]

There is a flaw with the code. Right now, if the query returns more than one record, the While loop will load the first row into the controls, then immediately load the next row into the controls, thereby replacing the work you just did. The end result is that you will only show the final record returned by the datareader. If there will only be one row, then don't bother with the While loop. Instead, just call reader.Read one time and load the controls.

If you are returning mutlipe records, then the datatable would make sense, as you would be able to navigate forwards and backwards through it. If you are only loading a single row, as it appears you may be doing, then the datatable is probably not a good idea. The datatable won't be quite as fast as the datareader (though you won't see the difference). You still might want to use the datatable, though, if you will be changing any of the values and want to push those changes back to the DB, since a datatable can be used with a dataadapter to update the database quite easily.

Therefore, it kind of depends on what you eventual goal is for this data.

[jmcilhinney]

There is a flaw with the code. Right now, if the query returns more than one record, the While loop will load the first row into the controls, then immediately load the next row into the controls, thereby replacing the work you just did. The end result is that you will only show the final record returned by the datareader. If there will only be one row, then don't bother with the While loop. Instead, just call reader.Read one time and load the controls.

There may be a bit of a flaw but I don't see it quite that way. I do agree that the While loop has pretty much no place here because the query is never going to return more than one row. The WHERE clause is filtering by primary key so there will never be more than one row. I guess the question is whether it might return no match at all. If there is guaranteed to be a match then the code should just call Read with no condition. If it might not return a match then the code should check HasRows and notify the user if it's False, probably clearing the controls as well.

The only thing I do not totally understand is how I can use the datatable here to load the data upfront...

To get all records you would omit the WHERE clause from the query. If you want to edit and save the data then I would use a data adapter and call Fill to populate the DataTable. If you just want to display the data then I would use a data reader and call Load on the DataTable to populate it. You can then bind the DataTable to a BindingSource and bind that to your controls. To select a record by ID you would call Find on the BindingSource and assign the result to its Position property. There are other members for navigation too.

[bodylojohn]

Hello,

I changed my code to this:

Private Sub vuldetails()
    Dim strvuldetails_sql As String = String.Format("SELECT BTW_ID,Code,Percentage,IsActief " & _
                                                    "FROM tblBTW " & _
                                                    "WHERE BTW_ID = @GeselecteerdeBTW")

    Using cmdvuldetails As New OleDbCommand(strvuldetails_sql, connection)
      connection.Open()

      With cmdvuldetails.Parameters
        .AddWithValue("@GeselecteerdeBTW", CInt(intGeselecteerdeBTW))
      End With

      Dim reader As OleDbDataReader = cmdvuldetails.ExecuteReader

      If reader.HasRows Then
        reader.Read()
        Me.txtID.Text = reader("BTW_ID").ToString
        Me.txtBtwCode.Text = reader("Code").ToString
        Me.txtPercentage.Text = reader("Percentage").ToString
        chkIsActief.Checked = CBool(reader("IsActief"))
        blnIsActief = CBool(reader("IsActief"))
      Else
        MsgBox("Geen gegevens beschikbaar voor id: " & intGeselecteerdeBTW)
      End If

      reader.Close()
      connection.Close()
    End Using

JMC... I also experienced with the datatable bindingsource information you provided me with.

I don't see how I could use it.
Because I use 4 different subs for update,deleting,saving and retrieving data.
I once learned how to do this according to a link in your signature.
Because I got very familiar with it i am scared to let this approach go and figure things out all over again.

However...the code looks cleaned up now...? does it?

But are there other strong arguments for me to use datatables and bindingsource.
Or is my method also usefull for what I do (it hasnt failed me yet).

Thanks again for all of your help and usefull information.