DataTable, DataAdapter, and Queries

[Lerroy_Jenkins]

I posted some of my Programs code up here and long story short it wasn't quiet as good as I hoped. So I was shown a thread that might be able to help me. Link. This is a post by jmcilhinney, very smart and reasonably easy to understand. But I am having problems applying some of the ideas to my program.

What I was doing.

To retrieve my members list I have 3 text boxes and 1 date time picker as search options. The end user would type or pick information they wanted, and any relating members would become displayed inside of a data grid view.

Example code

Private Sub PopulateCustomerData(ByVal QueryString As String)
 
        ' This Private Sub is used to pull the needed information out from the database.
        ' The Sub is expecting a QueryString from any source.
        ' Once the Sub has been given the QueryString it then uses this _
        ' information to query the database and to populate the needed fields inside of the customer data section.
 
Dim Connstring As String = "Provider=microsoft.ACE.oledb.12.0;data source =  " & GetAppPath() & "GymMemberData.accdb"
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(QueryString, Connection)
        Dim ds As DataSet = New DataSet
        da.Fill(ds, "MemberInfo")
 
        tboFName2.Text = ds.Tables("MemberInfo").Rows(0).Item("FirstName").ToString
        tboLName2.Text = ds.Tables("MemberInfo").Rows(0).Item("LastName").ToString
        lblMemberID.Text = ds.Tables("MemberInfo").Rows(0).Item("MemberID").ToString
        tboPhoneNumber.Text = ds.Tables("MemberInfo").Rows(0).Item("PhoneNumber").ToString
        tboAddressLine1.Text = ds.Tables("MemberInfo").Rows(0).Item("AddressLine1").ToString
        tboAddressLine2.Text = ds.Tables("MemberInfo").Rows(0).Item("AddressLine2").ToString
        tboAddressLine3.Text = ds.Tables("MemberInfo").Rows(0).Item("AddressLine3").ToString
        tboPostCode.Text = ds.Tables("MemberInfo").Rows(0).Item("AddressPostCode").ToString
        cboCardIssued.Text = ds.Tables("MemberInfo").Rows(0).Item("CardIssued").ToString
 
        Connection.Close()
 
 
    End Sub

Query string is set by this

Private Sub txtLName_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tboLName.TextChanged
 
        ' This Sub sends the Private Sub "PopulateGridVIew" a QueryString, _
        ' that is then used to query the database against characters in the persons last name.
 
        PopulateGridView("SELECT FirstName, LastName, MemberID, startdate, " & _
                         "CardIssued FROM MembersTable WHERE Lastname Like '" & _
                         tboLName.Text & "%' AND MarkedforDeletion = False")
 
    End Sub

So I go to the database every time the user types anything, and I retrieve the needed information. Later on in the program the user then has the option to update and delete and create. Which is similar, it connects to the database and then updates the records.

My original problem was that I was then trying to repopulate the datagridview within the same update,delete and create button sub. But for some reason and I still have no idea why, it wasn't working.

jmcilhinney pointed out that I should be retrieving the data, storing it localy, editing it localy and then updating the database at the end. Fine, but can I get my head around how to do that? No.

His examples show you using a fixed Query, where as you can see in mine, it all depends on what the user types in. He also sets the DataAdapter and DataTable as open to the whole form, fine.=

Sorry I am waffeling on. What I am trying to say is that, I understand the idea behind all of his examples, but I am really struggeling to implement the examples into my code. And I am here asking for some help please guys .

I try and not ask for help but I am really stuck and confused here.

[Hack]

What kind of return do you get when you run that query?

[jmcilhinney]

You're using a fixed query just as much as I was, or at least you should be. You should be creating the DataAdapter with its SelectCommand and adding the appropriate parameters. All you do to retrieve the data is then set the parameter Values and call Fill. E.g.

Imports System.Data.SqlClient
 
Public Class Form1
 
    Private table As New DataTable
    Private connection As New SqlConnection("connection string here")
    Private adapter As New SqlDataAdapter("SELECT * FROM Person WHERE FirstName LIKE '%' + @FirstName + '%'", connection)
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.adapter.SelectCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 50)
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.adapter.SelectCommand.Parameters("@FirstName").Value = Me.TextBox1.Text
        Me.adapter.Fill(Me.table)
        Me.DataGridView1.DataSource = Me.table
    End Sub
 
End Class

You can then use the same data adapter to save the data exactly as I showed in the CodeBank.