DataTable, DataAdapter, and Queries
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
vb.net 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
vb.net Code:
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.
Re: DataTable, DataAdapter, and Queries
What kind of return do you get when you run that query?
Re: DataTable, DataAdapter, and Queries
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.
vb.net Code:
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.