swilson
Posts: 84
|
| Posted: 01/18/2006, 2:21 PM |
|
I used the Example Pack example of Many to Many via Multi-select ListBox and Checkbox with success.
The code is not efficient for repeat use, however. Even if only one item is changed, the code deletes all records and rewrites all the Many to Many records on each submit.
First, do I care? Is there a limit to how high the ID of the joiner table can be? In my case, each submit rewrites a few hundred records. The unique ID for the joiner table now exceeds 100,000 and in a matter of years, it could be 100 million. If speed of the update is not a concern, should I care?
Otherwise, does anyone have a vb .net example that only updates the changed items and not the whole array?
_________________
It continues to amaze me, how often the solutions to seemingly complex problems are so very simple. |
 |
 |
swilson
Posts: 84
|
| Posted: 01/18/2006, 8:49 PM |
|
Well, I answered my own question.
For those who care, and those who would offer improvement on my code....
It would take two lifetimes for me to exceed the int data type limits for the primary key ID. Nevertheless, here's the code I used to add and delete my many-to-many table from a checkboxlist:
Dim Sql As String
Dim ACID As Integer
Dim NewDao As DataAccessObject = Settings.IDPCessnaDataAccessObject
Dim i As Integer
Try
ACID = Int32.Parse(AircraftID.Value)
if ACID < 0 Then ACID = 0
catch e As Exception
ACID = 0
End Try
For i = 0 To AircraftOptions.Items.Count - 1
If IsNothing(Settings.IDPCessnaDataAccessObject.ExecuteScalar("Select ID From ACID_FactOpID Where FactOpID =" _
& AircraftOptions.Items(i).Value &" and ACID = " & Int32.Parse(AircraftID.Value))) _
and AircraftOptions.Items(i).Selected then
Sql = "INSERT INTO ACID_FactOpID (FactOpID, ACID) VALUES (" & _
NewDao.ToSql(AircraftOptions.Items(i).Value,FieldType._Integer) & "," & _
NewDao.ToSql(ACID.ToString().ToString(),FieldType._Integer) & ")"
NewDao.RunSql(Sql)
End If
If Not IsNothing(Settings.IDPCessnaDataAccessObject.ExecuteScalar("Select ID From ACID_FactOpID Where FactOpID =" _
& AircraftOptions.Items(i).Value &" and ACID = " & Int32.Parse(AircraftID.Value))) _
and Not AircraftOptions.Items(i).Selected then
Sql = "DELETE FROM ACID_FactOpID Where FactOpID =" & AircraftOptions.Items(i).Value & _
" and ACID = " & Int32.Parse(AircraftID.Value)
NewDao.RunSql(Sql)
End If
Next i
I also use the joined table to store other data, so wiping out all rows indiscriminately, like the example pack code, was not good.
_________________
It continues to amaze me, how often the solutions to seemingly complex problems are so very simple. |
|
|
|
peterr
Posts: 5971
|
| Posted: 01/19/2006, 12:02 AM |
|
Hi,
I suspect that maybe you misunderstood the original example code or it doesn't apply to your situation. I just looked at it and I honestly believe that the example code may be more efficient than the above version, depending on the case. At least from what I see the example code deletes several records with one SQL DELETE statement while your code deletes smaller number of records but with multiple DELETE statements in a loop. Since such page cannot show too many records even in practice (how many projects could you assign to one employee , especially on one page?) then it's safe to assume that the example will delete maximum 50 records even in a large corporation with a million employees and thousands of projects.
This is because the WHERE statement in the example deletes only several records out of possible millions. And it deletes only those records that were shown on the page, not all records as you might've interpretted. Here is the relevant part:
ASP: EmpProjectConn.Execute("DELETE FROM projects_employees WHERE emp_id=" & EmpProjectConn.ToSQL(EmpID,ccsInteger))
VB.NET: Sql = "DELETE FROM projects_employees WHERE emp_id= " & NewDao.ToSql(EmpID.ToString(),FieldType._Integer)
So only the records for the selected EmpID are being deleted, and those are the same records that are being displayed on the page. Nothing else is deleted, inserted or updated than the records displayed on that page. Since there can never be too many of such records displayed on a page (at least in the example), the deletion is quick and scalable. I've seen couple other places in the examples that could be improved, but not this one.
So I would recommend using the sample code, however, I'm not sure if it applies to all other situations. For example if somehow you're able to display thousands of checkboxes on a single page, or you are assigning thousands of aircraft parts via a single listbox.
Anyway, if I interpretted your code correctly than I just don't see much difference in the number of records being touched in both solutions. That's just my little assessment 
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
peterr
Posts: 5971
|
| Posted: 01/19/2006, 12:59 PM |
|
I just realized that you are probably using auto-incremented keys for the many-to-many table, while CCS table used in the example doesn't. So if you just don't want to use up too many IDs then the situation is little different in your case indeed.
Also, I found that one of the reasons for deleting all records in the example is data integrity and consistency. It's just an opportunity to cleanup the data in case there are records with empty project_id or a deleted project_id. It's based on an assumption that not all databases enforce referential integrity, like MySQL4.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
swilson
Posts: 84
|
| Posted: 01/20/2006, 7:15 AM |
|
Thanks for the comments Peter. Since I do use an auto-incremented key and have additional fields in the join table, I only want the affected fields deleted and do not want to delete and rewrite all the fields on each submit.
I posted my own answer to my question for the sake of other novice developers like myself. Believe me, we appreciate your posts. THANKS.
_________________
It continues to amaze me, how often the solutions to seemingly complex problems are so very simple. |
|
|
|
peterr
Posts: 5971
|
| Posted: 01/20/2006, 1:01 PM |
|
You are not a novice if you whipped up such code
I would recommend your solution to anyone, just keeping in mind that each approach may have some advantages.
And sorry for throwing my 2 cents everywhere
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
