-
Apr 23rd, 2022, 04:24 AM
#1
Thread Starter
Addicted Member
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
-
Apr 23rd, 2022, 05:38 AM
#2
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:
Dim table As New DataTable
adapter As New OleDbDataAdapter(query, connectionString)
With adapter.SelectCommand.Parameters
.AddWithValue("@param1", param1)
.AddWithValue("@param2", param2)
End With
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.
-
Apr 23rd, 2022, 06:18 AM
#3
Thread Starter
Addicted Member
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.
-
Apr 23rd, 2022, 07:05 AM
#4
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.
-
Apr 23rd, 2022, 07:46 PM
#5
Thread Starter
Addicted Member
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!
-
Apr 23rd, 2022, 11:51 PM
#6
Thread Starter
Addicted Member
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.
-
Apr 24th, 2022, 12:37 AM
#7
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:
Private data As New DataSet
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Using connection As New SqlConnection("connection string here"),
roleAdapter As New SqlDataAdapter("SELECT RoleId, RoleName FROM Role", connection) With {.MissingSchemaAction = MissingSchemaAction.AddWithKey},
userAdapter As New SqlDataAdapter("SELECT UserId, RoleId, UserName FROM User", connection) With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
connection.Open()
roleAdapter.Fill(data, "Role")
userAdapter.Fill(data, "User")
End Using
Dim roleTable = data.Tables("Role")
Dim userTable = data.Tables("User")
data.Relations.Add("UserRole",
roleTable.Columns("RoleId"),
userTable.Columns("RoleId"))
roleBindingSource.DataSource = roleTable
userBindingSource.DataSource = userTable
With roleComboBox
.DisplayMember = "RoleName"
.ValueMember = "RoleId"
.DataSource = roleBindingSource
.DataBindings.Add("SelectedValue", userBindingSource, "RoleId")
End With
userNameTextBox.DataBindings.Add("Text", userBindingSource, "UserName")
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|