dcsimg
Results 1 to 31 of 31

Thread: [RESOLVED] Working with CheckedListox and database

  1. #1

    Thread Starter
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,118

    Resolved [RESOLVED] Working with CheckedListox and database

    Hi,

    This question will probably involves more than one thread.

    I have in a SQL database tblUsers here I capture fields like FirstName, LastName, Phone, etc.

    I want to see for every user what Items in the checkedlistbox the person have selected. In this example for Mary item 2 and item 4 is selected.



    Name:  Capture.JPG
Views: 305
Size:  12.8 KB



    If I change the dropdown and select another person I want to see that person selection. In the database, do I need to add another field in tblUsers e.g. SelectedItems ?

    Or.. do I add another table that is forreign key to tblUsers? eg. tblSelections

    The items in the checkedlistbox can grow and shrink (meaning the items can be 1 or 100). So I need to save whatever a person selected (regardless of how many items is selected) to the database.

    That is first part of question

    Next I want to know what every user selected. This is just an example:

    If Mary login:

    She can only see form 2 and form 4 (because that is what was checked under her name)

    This example is now only using access to forms.. But it can be used for many scenarios e.g. populate a combobox based on items selected e.g. If Mary login then combobox is populated with Item 2 and Item 4. If Peter login then combobox is populated with Item 1 and Item 5.

    Etc.

    thanks
    Last edited by schoemr; May 29th, 2019 at 03:23 AM.
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  2. #2
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    168

    Re: Working with CheckedListox and database

    I think i would have created 3 table's

    a) Persons
    b) PersonHasRights
    c) Rights

    B would look like Id, PersonId, RightId
    C would look like Id, Right
    Last edited by Goggy; May 29th, 2019 at 03:22 AM. Reason: forgot a d... Not that it mathers, my english is poor anyway ;-)
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  3. #3
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,177

    Re: Working with CheckedListox and database

    I agree with goggy's basic design. PersonHasRights = tblSelections in your OP and of course Rights = Items. PersonHasRights will contain a record for each selection any user could make.

  4. #4

    Thread Starter
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,118

    Re: Working with CheckedListox and database

    Hi guys,

    Thank you very much for your guidance. I have created an example as per your replies and looks like this:

    Name:  Capture1.jpg
Views: 134
Size:  19.4 KB

    In this picture above I can see that Mary have apples and lemons.
    If I change to Peter I can see he have grapes and peaches.

    I have added the ItemID's manually.


    Now.... how do I get from this above to this here below? For every user I must see all options to select from. Then he/her current selections must be show like checkbox.

    Name:  Capture2.JPG
Views: 134
Size:  23.8 KB

    PS. I changed from CheckedListBox to Datagridview because it seems CheckedListBox does not support data binding. (DataGridview is also good for me)
    Last edited by schoemr; Jun 3rd, 2019 at 06:29 AM.
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  5. #5

    Thread Starter
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,118

    Re: Working with CheckedListox and database

    This is the schema:

    Attachment 168967
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  6. #6
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    168

    Re: Working with CheckedListox and database

    I can't seem to open the shema you attached
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  7. #7

    Thread Starter
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,118

    Re: Working with CheckedListox and database

    Hi, oh, let me try again

    Name:  Capture3.JPG
Views: 122
Size:  25.2 KB
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  8. #8
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    168

    Re: Working with CheckedListox and database

    Being rusty in SQL, but i would imagine something like so....

    Code:
    Select 
    	IsNUll(tblUsers.UserName,'') As Name, 
    	IsNUll(tblItems.Item,'') As Selection
    From 
    	tblUsers
    		Left Join tblSelections on tblSelections.UserId = TblUsers.UserId,
    		Left Join tblItems on tblItems.ItemId = tblSelectedions.ItemId
    Where
           tblUsers.UserId = UserIdValue
    Last edited by Goggy; Jun 5th, 2019 at 07:26 AM. Reason: forgot the where clouse
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  9. #9

    Thread Starter
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,118

    Re: Working with CheckedListox and database

    Hi,

    I have implemented that select on the on the tblItems like so:

    Code:
    SELECT        ISNULL(tblUsers.UserName, '') AS Name, ISNULL(tblItems.Item, '') AS Selection
    FROM            tblUsers LEFT JOIN
                             tblSelections ON tblSelections.UserID = tblUsers.UserID LEFT JOIN
                             tblItems ON tblItems.ItemID = tblSelections.ItemID
    WHERE        (tblUsers.UserID = @UserID)
    What I am trying to achieve is this:

    Name:  Capture1.jpg
Views: 116
Size:  26.7 KB

    Firstly the user must be presented with a list of items to choose from. So I have tblUsers and tblItems.

    Here I can see that Mary which is User # 1 and she picked items 1,4,5 (Apples, Lemons, Bananas)

    That selection must now save to tblSelections (in this picture it is already reside but I put it there by manually. Also this table will not be visible to user.)

    So How can I click button and save Mary's selections in tblItems to tblSelections?

    Say now Mary choose different items then tblSelections must obviously reflect new selections. No matter what you select and deselect that must be saved to tblSelections.

    This is my first problem

    For the 2nd problem......

    If the form load then tblItems checkbox columns (which is unbound) must automatically be checked according to what is saved in tblSelections. (this must also happen when different user is selected in tblUsers)

    So every user must see what items is selected already when the form loads (displayed by means of checkbox column) and be able to modify and save that selections....

    Can someone please help me?

    Thanks
    Last edited by schoemr; Jun 7th, 2019 at 05:50 AM.
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  10. #10
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    168

    Re: Working with CheckedListox and database

    On the first problem... one could do something along the lines of so

    Code:
    Dim Query as string 
    
    Query = "Delete From tblSelections Where UserId = @UserId;"
    
    
    For each Row As DatagridViewRow in dgvSelections.ROWS
    	Query &= $"Insert Into tblSelections (UserID,ItemID) value({Row.cells(1).Value.ToString},{Row.cells(2).Value.ToString})" 
    Next
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  11. #11

    Thread Starter
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,118

    Re: Working with CheckedListox and database

    Quote Originally Posted by Goggy View Post
    On the first problem... one could do something along the lines of so

    Code:
    Dim Query as string 
    
    Query = "Delete From tblSelections Where UserId = @UserId;"
    
    
    For each Row As DatagridViewRow in dgvSelections.ROWS
    	Query &= $"Insert Into tblSelections (UserID,ItemID) value({Row.cells(1).Value.ToString},{Row.cells(2).Value.ToString})" 
    Next
    Thank you Goggy, I will try this
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  12. #12
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    168

    Re: Working with CheckedListox and database

    Graag gedaan
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  13. #13
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,051

    Re: Working with CheckedListox and database

    Hi,

    hope Goggy doesn't mind me chipping in, the idea with the Checkedlistbox isn't that bad

    here my go
    the Values get saved for each user .. 1 to 10 items or whatever items you add to the Checkedlistbox

    here the Insert part to tha Database

    Code:
    Imports System.Data.OleDb
    
    Public Class Form6
    
        'I created a Table like this:
        'Field ID = Autoincrement
        'Field someText = Text
        'Field UserID = Integer
        'Field Yes/N0 = Boolean
    
    
        Private Sub Form6_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            CheckedListBox1.CheckOnClick = True
            Dim i As Integer = 0
            'the Field someText is the Text Value in the ChecklistBox
            'so it would save Item0 or Item2 etc....
            For i = 0 To 10
                Me.CheckedListBox1.Items.Add("Item" + i.ToString)
            Next
            TextBox1.Text = CStr(2) 'hardcoded UserID
        End Sub
    
    
    
        Public Sub SaveCheckedItems()
            Dim sDB As String = "E:\Adressen.mdb"
            Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                    "Data Source=" & sDB & ";"
    
            Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
            Cn.Open()
            Try
                Using connection As New OleDbConnection(sCon)
                    connection.Open()
    
                    'add the checked Items 
                    Dim command As New OleDbCommand( _
                      "INSERT INTO tbl_Checked (someText, UserID, IsChecked) VALUES(?,?,?);", _
                      connection)
                    'your Values
                    Dim TextParameter = command.Parameters.Add("someText", OleDbType.VarWChar, 40)
                    Dim UserParameter = command.Parameters.Add("UserID", OleDbType.Integer)
                    Dim IsTrueParameter = command.Parameters.Add("IsChecked", OleDbType.Boolean)
    
    
                   
                    For Each itemChecked As Object In CheckedListBox1.Items
                        TextParameter.Value = itemChecked
                        UserParameter.Value = TextBox1.Text
                        'now save ALL items in the Checklistbox 
                        'but set the IsChecked Value in the Table to True or False
                        IsTrueParameter.Value = CheckedListBox1.GetItemCheckState(CheckedListBox1.Items.IndexOf(itemChecked))
    
                        command.ExecuteNonQuery()
    
                    Next itemChecked
                End Using
            Catch ex As Exception
                MessageBox.Show("Error :" & ex.Message)
            End Try
        End Sub
    
        
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            SaveCheckedItems()
        End Sub
    here a Image of the Database table
    Name:  isChecked.jpg
Views: 111
Size:  67.2 KB


    I would then use a Datareader to fill the Checkedlistbox based on the UserID

    HTH
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  14. #14
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,192

    Re: Working with CheckedListox and database

    You could do something like ChrisE example but there would be lots of wasted records in the selections table and every time you added or delete a record to the Items table you would also have to cleanup the Selections table. If you want that type of display without all the extra records and extra maintenance then I'd say try retrieving your Parent/Child data as normal, then retrieve the Items table and bind it the CheckedListBox. Then manually load the CheckedItems for the current UserId. Each time you move to a different UserId then you reload the CheckedListBox.

    The tricky part is updating the Selections table each time an item is Checked/UnChecked. But I thing you could handle that using the CheckedListBox ItemCheck event.

    There is probably a more elegant way, maybe creating a UserCheckedItems Class would make it neater. Might be a Linq statement, but I don't do Ling anymore.

  15. #15
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,051

    Re: Working with CheckedListox and database

    Quote Originally Posted by wes4dbt View Post
    You could do something like ChrisE example but there would be lots of wasted records in the selections table and every time you added or delete a record to the Items table you would also have to cleanup the Selections table. If you want that type of display without all the extra records and extra maintenance then I'd say try retrieving your Parent/Child data as normal, then retrieve the Items table and bind it the CheckedListBox. Then manually load the CheckedItems for the current UserId. Each time you move to a different UserId then you reload the CheckedListBox.
    Hi wes,
    yes there would be records there that are not checked, but the Idea of the selection table is that there are 10 Items to choose
    from.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  16. #16
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,013

    Re: Working with CheckedListox and database

    If both lists come from SQL type database you could select all user items and union all items where not in user items.

  17. #17
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,051

    Re: Working with CheckedListox and database

    Quote Originally Posted by kpmc View Post
    If both lists come from SQL type database you could select all user items and union all items where not in user items.
    good thought, but then you have 2 Checkedlistboxes
    no matter which way you go, there are 10 items to choose from, so IMO the user should see those 10 items.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  18. #18
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,013

    Re: Working with CheckedListox and database

    2 Checkedlistboxes
    Why is that? A Union would form 1 table with their items from useritems table and from all items in itemtable minus their items. then just databind to that table with 1 clb.

  19. #19
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,192

    Re: Working with CheckedListox and database

    Quote Originally Posted by ChrisE View Post
    Hi wes,
    yes there would be records there that are not checked, but the Idea of the selection table is that there are 10 Items to choose
    from.
    But the OP stated the Items list could increase or decrease, so each time that happens you would have to adjust the SelectedItems list for each user. It can be done but it's not really necessary.

    Chris I have to admit, I've done things this way (hang my head in shame). It's just one of those things that end up being a pain in the butt for the life of the application.
    Last edited by wes4dbt; Jun 8th, 2019 at 03:50 PM.

  20. #20
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,192

    Re: Working with CheckedListox and database

    Quote Originally Posted by kpmc View Post
    If both lists come from SQL type database you could select all user items and union all items where not in user items.
    Yes but how do you get it to display like the OP wants?

  21. #21
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,051

    Re: Working with CheckedListox and database

    Quote Originally Posted by wes4dbt View Post
    But the OP stated the Items list could increase or decrease, so each time that happens you would have to adjust the SelectedItems list for each user. It can be done but it's not really necessary.

    Chris I have to admit, I've done things this way (hang my head in shame). It's just one of those things that end up being a pain in the butt for the life of the application.

    Hi wes,

    take a close look at the Image in my Post, the Table you see there is the n Table
    so I have a Users Table and my Image shows the selected Items

    so I can't see a problem with the Insert Update Delete actions one has with Database Tables for each User


    the other method you prefer would save only the selected Items
    so from 10 Items the User selected 3 Items
    what steps do you have to take in case the User wants to change that selection
    a)reload the original options to select
    b)make the new selection
    c)Update the Table

    so this is also for each User
    Last edited by ChrisE; Jun 9th, 2019 at 07:01 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  22. #22
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,192

    Re: Working with CheckedListox and database

    Quote Originally Posted by ChrisE View Post
    Hi wes,

    take a close look at the Image in my Post, the Table you see there is the n Table
    so I have a Users Table and my Image shows the selected Items

    so I can't see a problem with the Insert Update Delete actions one has with Database Tables for each User


    the other method you prefer would save only the selected Items
    so from 10 Items the User selected 3 Items
    what steps do you have to take in case the User wants to change that selection
    a)reload the original options to select
    b)make the new selection
    c)Update the Table

    so this is also for each User
    The difference is if a new Item choice is Added, then all that is changed is One record is Added to the Items table. Your method would also cause a new record for each User having to be Added to the SelectedItems table. And I don't like the idea of bloating the SelectedItems table with non selected items.

    As for your code, it looks to me that each time you Load/Edit a User and then press the Save button 10 more items are going to be added to the Users SelectedItems choices, going from 10 to 20 to 30...... That seems like a problem. But that really doesn't have anything to do why I wouldn't use your suggested method. The main reason is application maintenance, I just prefer having to adjust the least amount of records as possible and minimizing the table size. But as I said your method is doable and I got no problem if that's how you or the OP want to do it.
    Last edited by wes4dbt; Jun 9th, 2019 at 08:56 PM.

  23. #23
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,051

    Re: Working with CheckedListox and database

    Hi wes

    the Post#13 was the Insert Part

    this could be the Load Part, depending which UserID is in Textbox1
    Code:
     Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            'clear first
            CheckedListBox1.Items.Clear()
    
            Dim sDB As String = "E:\Adressen.mdb"
            Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                    "Data Source=" & sDB & ";"
    
            Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
            Cn.Open()
    
            Dim sSQL As String = "Select ID,someText, UserID, IsChecked From tbl_Checked Where UserID=" & TextBox1.Text
            Dim Cmd As New OleDb.OleDbCommand(sSQL, Cn)
            Dim Dr As OleDb.OleDbDataReader
            Dr = Cmd.ExecuteReader
    
            While Dr.Read()
                Dim checkedstate As Integer = Dr.Item(3)
                'set Item to checked or unchecked
                If checkedstate = -1 Then
                    Dim Index As Integer = CheckedListBox1.Items.Add(Dr.Item(1))
                    CheckedListBox1.SetItemCheckState(Index, CheckState.Checked)
                Else
                    Dim index As Integer = CheckedListBox1.Items.Add(Dr.Item(1))
                End If
            End While
            Dr.Close()
            Cmd.Dispose()
            Cn.Close()
        End Sub
    then Edit and save back
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  24. #24

    Thread Starter
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,118

    Re: Working with CheckedListox and database

    Hi guys, thank you very much for your ideas

    This scenario:

    The guest house have a ordering system where guests can order breakfast (for next morning) There is a cut-off time which is before 2AM. So a guest can order breakfast from this menu:

    1 - 2 Fried Eggs
    2 - 2 Scrambled Eggs
    3 - 2 Poached Eggs
    4 - 2 Cheese grillers
    5 - 2 slices of toast
    6 - 3 Rashers of bacon
    7 - Avocado
    8 - Avocado Toast With Egg
    9 - Basil pesto cherry tomatoes
    10 - Berry and Yogurt Smoothie
    11 - Cherry tomatoes
    12 - Grilled tomato
    13 - Mushrooms
    14 - Nut Butter, Banana, and Chia Seed Toast
    15 - Quinoa and Chia Porridge
    16 - Quinoa Fruit Salad
    17 - Savory Oatmeal With an Egg
    18 - Smoked Salmon
    19 - Tomato Toast With Macadamia Ricotta

    But there are many more items on the menu and also every item is show price.

    Now the guest chose e.g. 1,4,6,9 and that selection is saved. But later the guest wish to change that selection... Then the guest must see what is already selected and must also see what is still available to select.

    In fact the guest can change selection many times (before 2 AM) because after that time, the kitchen staff prepares to serve breakfast.

    But now... this is only one guest, and it is only for one breakfast. A guest can stay for many days and only after settle the bill upon checkout. That is also why history is kept.

    Also from time to time the guest house may wish to add or remove items from the menu.
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  25. #25
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,192

    Re: Working with CheckedListox and database

    That's a nice explanation but do you have a question? Not trying to be sarcastic just not sure what it is you want. Are you having problems with something, if so, what?
    Last edited by wes4dbt; Jun 10th, 2019 at 08:20 PM.

  26. #26

    Thread Starter
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,118

    Re: Working with CheckedListox and database

    Quote Originally Posted by ChrisE View Post
    Hi wes

    the Post#13 was the Insert Part

    this could be the Load Part, depending which UserID is in Textbox1
    Code:
     Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            'clear first
            CheckedListBox1.Items.Clear()
    
            Dim sDB As String = "E:\Adressen.mdb"
            Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                    "Data Source=" & sDB & ";"
    
            Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
            Cn.Open()
    
            Dim sSQL As String = "Select ID,someText, UserID, IsChecked From tbl_Checked Where UserID=" & TextBox1.Text
            Dim Cmd As New OleDb.OleDbCommand(sSQL, Cn)
            Dim Dr As OleDb.OleDbDataReader
            Dr = Cmd.ExecuteReader
    
            While Dr.Read()
                Dim checkedstate As Integer = Dr.Item(3)
                'set Item to checked or unchecked
                If checkedstate = -1 Then
                    Dim Index As Integer = CheckedListBox1.Items.Add(Dr.Item(1))
                    CheckedListBox1.SetItemCheckState(Index, CheckState.Checked)
                Else
                    Dim index As Integer = CheckedListBox1.Items.Add(Dr.Item(1))
                End If
            End While
            Dr.Close()
            Cmd.Dispose()
            Cn.Close()
        End Sub
    then Edit and save back
    Hi Chris,

    I have been having difficulty with this code example for some time...

    Say... I change the checkedListibox to a datagriview, with a checkbox column.. Won't it be a better way? If so, how will this code change?

    Why I am asking... Say I have a DGV with 5 items. I have to save all 5 items (whether it is checked or not) ??

    Meaning something like:

    1 - Checked
    2 - NotChecked
    3 - NotChecked
    4 - NotChecked
    5 - Checked


    This is much more difficult than what it seems

    Perhaps I should start a new thread but this time not asking for a CheckedListox.... Rather a Datagridview
    Last edited by schoemr; Jul 8th, 2019 at 06:08 AM.
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  27. #27
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,453

    Re: Working with CheckedListox and database

    I would only save the checked items. If it's one, then it' one. If it's two, then it's two, if it's 15, then it's 15. Since this is SQL Server, then you have the power of Stored Procs at your disposal. I would create a stored proc that takes the userId, and a CSV list of IDs to save. Split the Ids into a table variable. Then use an UPSERT Statement to do the inserts, updates, and deletes on the table as needed. Although there really isn't much to update, it would mostly be the insert or delete, but either way, that should get the job done.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  28. #28
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,453

    Re: Working with CheckedListox and database

    When it comes to selecting the data... you left join to the selection table from the items table... if there is an id there, then you set the checkmark... if not, then no checkmark. Easy peasy.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  29. #29

    Thread Starter
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,118

    Re: Working with CheckedListox and database

    Easy peasy.
    Yeah, right
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  30. #30

    Thread Starter
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,118

    Re: Working with CheckedListox and database

    Quote Originally Posted by techgnome View Post
    When it comes to selecting the data... you left join to the selection table from the items table... if there is an id there, then you set the checkmark... if not, then no checkmark. Easy peasy.

    -tg
    This part I do actually understand..
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  31. #31
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,471

    Re: Working with CheckedListox and database

    Hello,

    A consideration.

    The following is in SQL-Server but can be done in MS-Access and other databases. The current design may be expanded upon, it's now simply a basic sample. What is not seen are a) cascading rules and default values. Since you want this data for one day the date condition could be removed and a process would remove prior day records at a specified time.

    Name:  F1.png
Views: 39
Size:  12.6 KB

    Sample SQL

    The declare becomes a parameter for the command object.

    Code:
    DECLARE @GuestIdentifier INT = 2
    
    SELECT G.GuestIdentifier,
           G.RoomIdentifier,
           G.FirstName,
           G.LastName,
           BO.OrderDate,
           BOI.Quantity,
           BOI.ItemIdentifier,
           BI.Item,
           BOI.OrderItemIdentifier,
           BO.BreakfastIdentifier
    FROM BreakfastItems AS BI
         INNER JOIN BreakfastOrderItems AS BOI ON BI.ItemIdentifier = BOI.ItemIdentifier
         RIGHT OUTER JOIN Guests AS G
         INNER JOIN BreakfastOrders AS BO ON G.GuestIdentifier = BO.GuestIdentifier ON BOI.BreakfastIdentifier = BO.BreakfastIdentifier
    WHERE CONVERT(VARCHAR(10), BO.OrderDate, 120) = CONVERT(VARCHAR(10), GETDATE(), 120)
          AND G.GuestIdentifier = @GuestIdentifier;

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width