Results 1 to 7 of 7

Thread: Ugh I am still not undersranding the tableadaper and updating

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Ugh I am still not undersranding the tableadaper and updating

    I have a tableadapter set up and it fills the right data into a dataset (no longer a dataAdapter since I ran CommandBuiler on it). I thought I was about to crack that nut but then it complains about everything needing a specific type and I've no idea what it is talking about, I just know it means I am doing it wrong.

    Here is the code for initializing and filling the datatable. The data stuff us all declared public and I don't dispose of them so they are still available and configured in my datagrid form
    Code:
       Public Function QueryDB(query As String, Optional param1 As String = "", Optional param2 As String = "", Optional param3 As String = "", Optional param4 As String = "", Optional param5 As String = "")
            Dim dt As New DataTable
            connDB = New OleDbConnection(connStr)
            connDB.Open()
            cmdDB = New OleDbCommand(query, connDB)
            With cmdDB
                .Parameters.AddWithValue("@param1", param1)
                .Parameters.AddWithValue("@param2", param2)
                .Parameters.AddWithValue("@param2", param3)
                .Parameters.AddWithValue("@param4", param4)
                .Parameters.AddWithValue("@param5", param5)
            End With
            usersAdapter = New OleDbDataAdapter(cmdDB)
            builderDB = New OleDbCommandBuilder(usersAdapter)
            usersAdapter.Fill(dt)
            connDB.Close()
            Return dt
        End Function
    And here is where I am trying to call for an update (probably completely wrong)
    Code:
           If isdataDirty = True Then
                connDB.Open()
                usersAdapter.Update(dtUsers)
                connDB.Close()
            End If

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Ugh I am still not undersranding the tableadaper and updating

    Firstly, there's no table adapters there. Table adapters are generated by the system when you create a typed DataSet. No typed DataSet means no table adapters. You're using a data adapter. The clue is in the name:
    Code:
    usersAdapter = New OleDbDataAdapter(cmdDB)
    Secondly, this makes no sense:
    no longer a dataAdapter since I ran CommandBuiler on it
    A data adapter is always a data adapter. You can create the action commands yourself or let a command builder do it for you but it's still a data adapter.

    As for the code, there's no point manually opening and closing the connection. The Fill and Update methods of the adapter will automatically open the connection if it's not open and automatically close it again if it opened it. You should let that happen when making a single call. You should only open and close the connection yourself if you're making multiple calls, so that the connection is not closed and reopened in between.

    Next, there's really no point creating the command object yourself. You can pass the query text to the data adapter constructor and it will create the command for you. If you need to use it, e.g. to add parameters, you can get it from the SelectCommand property. In fact, there's no point creating the connection either, because you can pass the connection string to the data adapter constructor too. Also, the whole point of using a With block is to avoid writing the same expression over and over, so why are you writing '.Parameters' over and over?
    vb.net Code:
    1. Dim table As New DataTable
    2.  
    3. adapter As New OleDbDataAdapter(query, connectionString)
    4.  
    5. With adapter.SelectCommand.Parameters
    6.     .AddWithValue("@param1", param1)
    7.     .AddWithValue("@param2", param2)
    8. End With
    9.  
    10. adapter.Fill(table)
    Now, as for the issue, what's the actual issue? Vague descriptions are of limited use. Error messages are specific. The system gives them to you for diagnostic purposes. If you want us to diagnose the issue then you provide them to us. Specify EXACTLY where the error occurs (presumably on the Update call but we shouldn't need to assume) and EXACTLY what the error message(s) is.

    Rereading your question and your code, there are other issues there too, which might have something to do with your issue. Why are you declaring the optional parameters as type String? How do you know that you'll only ever want String parameters in your query? That method looks like it's supposed to support an arbitrary query you're assigning to the same 'usersAdapter` field each time, so what's up with that? If that method is specific to user records then it should be written that way and if it's generic then it should be written that way, not half and half.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: Ugh I am still not undersranding the tableadaper and updating

    Ah that is a lot of information that I haven't seen before. Well, I heard about the adapter handling the connection itself but I ran into "not open" when I tried it so I gave up.

    I couldn't find any mention of adding parameters to an Adapter but knowing it is through the select method let me find information that I will research tomorrow (It's almost bedtime now in the Philippines). The great thing with the Internet is it is full of information but the bad thing is that it is full of information OTHER than what you are looking for.

    Thanks a lot! I'll redo it tomorrow and give it another try.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Ugh I am still not undersranding the tableadaper and updating

    Did you check out my ADO.NET example thread? I thought I'd recommended it but, if not, it's here.

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: Ugh I am still not undersranding the tableadaper and updating

    No I hadn't seen those posts before, they help clear up a lot of things thanks!

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: Ugh I am still not undersranding the tableadaper and updating

    Well I had thought I could simplify my database handling by using Dataadapters but not only is it a lot more complicated than I'd hoped it is also not able to handle tables from multiple other tables (l like to display a name instead of the number in a row so I left join the table with the name). I think I've convinced myself that it is much less work for me to simply use sql I write and do the updating/inserting/deleting manually.

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Ugh I am still not undersranding the tableadaper and updating

    You're wrong. Firstly, there's nothing about data adapters that is not using SQL that you write and there's nothing about them that is not saving changes manually. I suspect that what you're actually talking about is command builders. Command builders are handy in specific simple situations but generally no one uses them seriously. That doesn't mean that you should use a data adapter though. If you have read my CodeBank thread properly then you have seen an example of saving data with or without a command builder but using a data adapter in both cases.

    If you want to display data from multiple tables then go ahead and use a join. There's nothing about using a data adapter that should stop you. If you're actually editing data though, you probably shouldn't be using a join anyway. Rather, you should be populating multiple DataTables and then using data-binding to allow logical selection of foreign keys. For example, lets say that you have a Role table and a User table and User contains a foreign key RoleId. If you want to edit the user data then you should populate one DataTable with the User data and one DataTable with the Role data and then use a ComboBox so that the user can select a RoleName value from a drop-down list to insert a RoleId value in a User record. That might look like this:
    vb.net Code:
    1. Private data As New DataSet
    2.  
    3. Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    4.     Using connection As New SqlConnection("connection string here"),
    5.           roleAdapter As New SqlDataAdapter("SELECT RoleId, RoleName FROM Role", connection) With {.MissingSchemaAction = MissingSchemaAction.AddWithKey},
    6.           userAdapter As New SqlDataAdapter("SELECT UserId, RoleId, UserName FROM User", connection) With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
    7.         connection.Open()
    8.  
    9.         roleAdapter.Fill(data, "Role")
    10.         userAdapter.Fill(data, "User")
    11.     End Using
    12.  
    13.     Dim roleTable = data.Tables("Role")
    14.     Dim userTable = data.Tables("User")
    15.  
    16.     data.Relations.Add("UserRole",
    17.                        roleTable.Columns("RoleId"),
    18.                        userTable.Columns("RoleId"))
    19.  
    20.     roleBindingSource.DataSource = roleTable
    21.     userBindingSource.DataSource = userTable
    22.  
    23.     With roleComboBox
    24.         .DisplayMember = "RoleName"
    25.         .ValueMember = "RoleId"
    26.         .DataSource = roleBindingSource
    27.  
    28.         .DataBindings.Add("SelectedValue", userBindingSource, "RoleId")
    29.     End With
    30.  
    31.     userNameTextBox.DataBindings.Add("Text", userBindingSource, "UserName")
    32. End Sub
    Now, when you select a User record, you will see the RoleName in the ComboBox that corresponds to the RoleId in the User record. When you add or edit a user record, you will select a RoleName value from the list and the corresponding RoleId is pushed into the User record. When you save the changes to the User table, whether you use a command builder or not, the correct data will be saved.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width