Data Adapter Fill Not Working When Called From Button 1

[Auguy]

Just playing around here and trying to get this to work. I have subroutine (CreateCustomerAdapter) that creates a data adapter with a select command. The Fill works if I call it from the form load but not if I call it from code on a button click (Button2). What am I doing wrong? I get error System.NullReferenceException: Object Reference not set to an instance of an object

Imports MySql.Data.MySqlClient

Public Class Form1
  Public dbconn As New MySqlConnection
  Public sql As String
  Public dbcomm As MySqlCommand
  Public command As MySqlCommand
  Public dbread As MySqlDataReader
  Public ds As DataSet
  Public Phys As DataTable
  Public adapter As MySqlDataAdapter

  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    dbconn = New MySqlConnection("Data Source=localhost ; user id=root ; password=sa7486 ; database=Referrals;")
    Try
      dbconn.Open()
    Catch ex As Exception
      MsgBox("Error in connection, please check Database and connection server.")
    End Try

    Dim adapter As New MySqlDataAdapter
    adapter = CreateCustomerAdapter(dbconn)

    Dim ds As New DataSet
    Dim Phys As New DataTable

    adapter.Fill(ds, "Phys")
    For Each pRow As DataRow In ds.Tables("Phys").Rows
      ListBox1.Items.Add(pRow("LastName") & ", " & pRow("FirstName"))
    Next
  End Sub

  Public Function CreateCustomerAdapter(ByVal connection As MySqlConnection) As MySqlDataAdapter
    Dim adapter As MySqlDataAdapter = New MySqlDataAdapter()

    ' Create the SelectCommand. 
    Dim command As MySqlCommand = New MySqlCommand( _
        "SELECT * FROM Physician", connection)

    adapter.SelectCommand = command

    Return adapter
  End Function

  Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    ListBox1.Items.Clear()

    Dim ds As New DataSet
    Dim Phys As New DataTable
    'ds.Clear()

    Try
      adapter.Fill(ds, "Phys")
    Catch ex As Exception
      MessageBox.Show(ex.ToString)
    End Try

    For Each pRow As DataRow In ds.Tables("Phys").Rows
      ListBox1.Items.Add(pRow("LastName") & ", " & pRow("FirstName"))
    Next
  End Sub

Auguy
Sylvania/Toledo Ohio

 

[Auguy]

OK, is it as simple as adding the following code to the button click before the call to Fill? If so, why do I have to do this?

  Dim adapter As New MySqlDataAdapter
    adapter = CreateCustomerAdapter(dbconn)

Auguy
Sylvania/Toledo Ohio

 

[jebenson]

You have "adapter" defined at the form Declarations level (Public adapter As MySqlDataAdapter). Then in Form_Load, you define another "adapter" variable (Dim adapter As New MySqlDataAdapter). The "adapter" defined in Form_Load obscures the "adapter" defined in Declarations. When you define "adapter" in the button click event, that "adapter" also obscures the one defined in Declarations. If you want to use the "adapter" in Declarations, remove the "Dim adapter As New MySqlDataAdapter" line from the Form_Load event and from the Button_Click event. Then when you call "adapter = CreateCustomerAdapter(dbconn)" it will use the "adapter" from Declarations.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Auguy]

Thanks again for both posts.

Auguy
Sylvania/Toledo Ohio

 

[Auguy]

Worked perfectly, this is why you shouldn't do this stuff when you're tired. Thanks!

Auguy
Sylvania/Toledo Ohio