none
set binding navigator to last row added by binding source,addnew RRS feed

  • Question

  • I want the binding navigator to be positioned to the last record added after update (access database).

    I cannot see how.


    RONATMOODYLAKE

    Thursday, September 26, 2013 11:03 PM

Answers

All replies

  • Not be sure what you want.

    Could please restate your problem more clearly?

    Thanks.

    Friday, September 27, 2013 1:36 AM
  • Hello,

    Welcome to this forum.

    Sorry for that I can hardly guess that what you want.

    Since you posted your question to this forum, so I guess that you want to know how to build the relation in the DataSet.

    If it is, please have a look at the link below:

    http://msdn.microsoft.com/en-us/library/system.data.datatable.childrelations.aspx

    It has a complete sample regarding to the relation.

    Above is just my guess, so could please show something that let us know more clearly about what you want.

    Thanks &Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Friday, September 27, 2013 9:13 AM
    Moderator
  • A form containing many contact fields has just been added.  This code updates the table.  The form contains the record just added.  Here is the code:

            If Me.ContactsDataSet.HasChanges Then
                Try
                    Me.Validate()
                    Me.ContactBindingSource.EndEdit()
                    Me.ContactTableAdapter.Update(Me.ContactsDataSet.contact)
                Catch ex As Exception
                    FindErrors()
                End Try
            End If
            Me.ContactTableAdapter.Fill(Me.ContactsDataSet.contact)

    This positions the binding navigator at record zero.  I want it to be positioned at the record just added.  Make sense?


    RONATMOODYLAKE

    Friday, September 27, 2013 9:44 PM
  • This should do the trick:

    Me.ContactBindingSource.Position = Me.ContactsDataSet.contact.Count - 1
    The above code is assuming that the you're adding a record, which you said you were. As long as you're sorting the data you're retrieving, then the last record should be the one you just added. 



    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com




    • Edited by BonnieBMVP Saturday, September 28, 2013 1:20 AM
    Saturday, September 28, 2013 1:14 AM
  • I might also add that you don't have to refill your DataSet (unless you want to be sure it's up to date in case other users have been updating the database at the same time). Just doing the .Update() should be all you need to do. I believe that will then not reset the Position of your BindingNavigator/BindingSource (not 100% sure, haven't tested it).

    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, September 28, 2013 1:24 AM
  • Nope - doesn't work.  After the statement:

            Me.ContactTableAdapter.Fill(Me.ContactsDataSet.contact)

    I added:

            Me.ContactBindingSource.Position = Me.ContactsDataSet.contact.Count - 1

    The display went to the last record in the table, not the one I just added.


    RONATMOODYLAKE

    Saturday, September 28, 2013 1:27 AM
  • Have you tried not refilling the DataTable and therefore not messing with the Position at all?

    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, September 28, 2013 1:32 AM
  • Yes - the display goes to the first record, not the one I just added.  This was so easy in VB6, just did a findfirst and it was there!

    RONATMOODYLAKE

    Saturday, September 28, 2013 1:39 AM
  • Sorry - I re-read your suggestion.  Ignore the last response.  The result of not refilling the DataTable is that the display shows the record just added as the last record.  It is not sorted alphabetically by last name, first name, etc.

    RONATMOODYLAKE

    Saturday, September 28, 2013 1:46 AM
  • Try using the .IndexOf() method. You'll have to have the instance of the row you just added. Something like this:

    ' correct syntax?
    ' MyRow is the row you just added, you have to at least know that
    Dim Pos as int = Me.ContactsDataSet.contact.Rows.IndexOf(MyRow)
    
    Me.ContactBindingSource.Position = Pos
    Sorry I'm not up on VB syntax and I always forget the correct way to specify the Dim ... but you should get the idea anyway.


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, September 28, 2013 1:48 AM
  • OK, then you can ignore my last response too!!  ;0)   Glad that worked for you ... =0)

    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, September 28, 2013 1:49 AM
  • Sorry again - It didn't work out.  I want the last record added to appear in the proper sequence when scrolling through the binding navigator.  It is not generally the last record, but should appear in the proper alphabetical sequence according to last name, first name, etc.

    The fill select is:

    SELECT        ID, lastname, firstname, middle, suffix, salutation, address1, address2, city, country, state, zip, alt_address1, alt_address2, alt_city, alt_country, alt_state, alt_zip, 
                             spouse, picture_name, document_folder, home_phone, fax, email, website, birthdate, died, causeofdeath, work_phone, cell_phone, alt_phone1, alt_phone2, 
                             additional_email, gps_coordinates, company, Aux_Info, date_created, Flag1, Flag2, Flag3, Flag4, Flag5, Flag6, Flag7, Flag8, Flag9, Flag10, Flag11, Flag12, Flag13, 
                             Flag14, Flag15, Flag16, Flag17, Flag18, Flag19, Flag20
    FROM            contact
    ORDER BY lastname, firstname, middle, suffix


    RONATMOODYLAKE

    Saturday, September 28, 2013 2:03 AM
  • You said earlier that it wasn't sorted. 

    Did you try my 2nd suggestion using the .IndexOf() method & setting the .Position property?


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, September 28, 2013 4:37 AM
  • I didn't try it because I do not know the value of MyRow.  This code:

            If Me.ContactsDataSet.HasChanges Then
                Try
                    Me.Validate()
                    Me.ContactBindingSource.EndEdit()
                    Me.ContactTableAdapter.Update(Me.ContactsDataSet.contact)
                Catch ex As Exception
                    FindErrors()
                End Try
            End If
            Debug.Print(ContactBindingSource.Current("ID"))

    shows an ID of -1.  The ID is a datatype autonumber.  It is also the primary key.  I want to identify this record at this point in the code so I can reposition the ContactBindingSource to it after the new record is sorted into the ContactBinding Navigator.

    The next statement is:

    Me.ContactTableAdapter.Fill(Me.ContactsDataSet.contact)

    This sorts the new record appropriately.  At this point I want to set the ContactBindingSource to the record just added.  I really need to get some identifier for the record before the Fill happens.  How??


    RONATMOODYLAKE

    Saturday, September 28, 2013 2:04 PM
  • The Update will put the new PK in the DataTable. Also, the BindingSource will be updated, so BindingSource.Current should now have the new PK. I'm not sure what's wrong with your code (showing the ID is still -1), but it should be working. I've just finished testing this myself and it all looks good.

    I know that doesn't help you at all, but at least you know that it's supposed to work. I guess you've got some more digging to do to see why PK is not being updated.


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, September 28, 2013 5:06 PM
  • It seems that the autonumber process doesn't execute until the Fill statement happens, therefore the erroneous PK value.  Would it help to show more of my code?

    I have to admit that I am losing confidence in Visual Studio process for VB 2012.  Buggy?  I do not know where to go for answers now, but I really appreciate your attempts to help.  This problem is mostly cosmetic, but I really do not like seeing things fail that are supposed to work.  It erodes my confidence in the Visual Studio system and makes me wonder if I should look at other development tools.  Maybe I found a bug?  If so, someone should want to address it.


    RONATMOODYLAKE

    Saturday, September 28, 2013 10:23 PM
  • A couple of things:

    I'm using VS 2010, not VS 2012 ... but I doubt if that has anything to do with it.

    I didn't notice that you said it was an Access database (sorry, I missed that). I don't have Access anyway, so I couldn't test this even if I wanted to. But, this all works just fine with SQL Server, as I said. The buggy part of yours then is most likely the OleDb Provider for the Access database can't handle updating the PK in the DataTable after the .Update(). I don't know why though ... but I'm glad that SQL Server stuff gets handled correctly ;0) Which, of course, doesn't help you ... unless you switch to SQL Server.  ;0)

    I had started to write an idea for a workaround earlier this morning, until I was testing and saw that it worked fine for SQL Server. Unfortunately, I wiped out what I had started to write. Let's see if I can remember ... you could keep a List<int> of all the PK's in your DataTable (assuming your PK is int, use whatever datatype is appropriate). Don't add the PK from the newly added row (because it's -1) just yet. After the .Update() and after the .Fill(), compare all the PK's in your newly filled DataTable against the List. The new PK to your newly added row won't be in your List, so that's how you'll find the new Row (along with it's new PK). Then, use the IndexOf that row and set the BindingSource.Position accordingly, as previously mentioned. That should work.


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, September 28, 2013 10:57 PM
  • Excellent idea - doesn't work.  Here is the code:

            set_PK_list()
            ContactBindingSource.EndEdit()
            If Me.ContactsDataSet.HasChanges Then
                Try
                    Me.Validate()
                    Me.ContactBindingSource.EndEdit()
                    Me.ContactTableAdapter.Update(Me.ContactsDataSet.contact)
                Catch ex As Exception
                    FindErrors()
                End Try
            End If
            target_ID = find_new_id()
        Private Sub set_PK_list()
            Dim sqlString As String
            Dim sqlConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & data_path & ";"
            Dim sqlConn As New OleDb.OleDbConnection(sqlConnString)
            Dim sqlCmnd As New OleDb.OleDbCommand
            Dim Reader As OleDb.OleDbDataReader
            sqlString = "select * from contact"
            sqlCmnd.Parameters.Clear()
            sqlCmnd.Connection = sqlConn
            sqlCmnd.CommandText = sqlString
            sqlConn.Open()
            Reader = sqlCmnd.ExecuteReader()
            Me.ComboBoxBindingNavigator.Items.Clear()
            ReDim primary_keys(0)
            primary_key_count = 0
            If Reader.HasRows Then
                While Reader.Read()
                    ReDim Preserve primary_keys(primary_key_count)
                    primary_keys(primary_key_count) = Reader("ID")
                    primary_key_count = primary_key_count + 1
                End While
            End If
            Reader.Close()
            sqlConn.Close()
        End Sub

        Private Function find_new_id() As Long
            Dim sqlString As String, i As Long, test_id As Long
            Dim sqlConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & data_path & ";"
            Dim sqlConn As New OleDb.OleDbConnection(sqlConnString)
            Dim sqlCmnd As New OleDb.OleDbCommand
            Dim Reader As OleDb.OleDbDataReader
            sqlString = "select * from contact"
            sqlCmnd.Parameters.Clear()
            sqlCmnd.Connection = sqlConn
            sqlCmnd.CommandText = sqlString
            sqlConn.Open()
            Reader = sqlCmnd.ExecuteReader()
            primary_key_count = 0
            If Reader.HasRows Then
                While Reader.Read()
                    test_id = Reader("ID")
                    For i = 0 To primary_key_count - 1
                        If test_id <> primary_keys(i) Then
                            Reader.Close()
                            sqlConn.Close()
                            Return test_id
                        End If
                    Next i
                End While
            End If
            Reader.Close()
            sqlConn.Close()
            Return -1
        End Function

    Returns -1

    bad news.  Good idea though.  Any more ideas?


    RONATMOODYLAKE

    Saturday, September 28, 2013 11:46 PM
  • My code is buggy.  I will test and respond when I think it works.  Sorry.

    RONATMOODYLAKE

    Sunday, September 29, 2013 12:09 AM
  • Here is an update:

            set_PK_list()
            ContactBindingSource.EndEdit()
            If Me.ContactsDataSet.HasChanges Then
                Try
                    Me.Validate()
                    Me.ContactBindingSource.EndEdit()
                    Me.ContactTableAdapter.Update(Me.ContactsDataSet.contact)
                Catch ex As Exception
                    FindErrors()
                End Try
            End If
            target_ID = find_new_id()
            Me.ContactTableAdapter.Fill(Me.ContactsDataSet.contact)
            Me.ContactBindingSource.Position = target_ID

    This shows the last record in the table, not the one I just added.

    The  find_new_id() code is:

        Private Function find_new_id() As Long
            Dim sqlString As String, i As Long, test_id As Long, found As Boolean
            Dim sqlConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & data_path & ";"
            Dim sqlConn As New OleDb.OleDbConnection(sqlConnString)
            Dim sqlCmnd As New OleDb.OleDbCommand
            Dim Reader As OleDb.OleDbDataReader
            sqlString = "select * from contact"
            sqlCmnd.Parameters.Clear()
            sqlCmnd.Connection = sqlConn
            sqlCmnd.CommandText = sqlString
            sqlConn.Open()
            Reader = sqlCmnd.ExecuteReader()
            If Reader.HasRows Then
                While Reader.Read()
                    test_id = Reader("ID")
                    found = False
                    For i = 0 To primary_key_count - 1
                        If test_id = primary_keys(i) Then
                            found = True
                            Exit For
                        End If
                    Next i
                    If found = False Then
                        Return test_id
                        Exit Function
                    End If
                End While
            End If
            Reader.Close()
            sqlConn.Close()
            Return -1
        End Function

     It returned the last record.  Not correct.


    RONATMOODYLAKE

    Sunday, September 29, 2013 12:19 AM
  • Sorry, Ron. I should have made what I had been thinking about a bit more obvious. 

    I mentioned List<int>, which is the C# syntax. I didn't realize that maybe you didn't recognize that because it has a slightly different in syntax in VB, which is List(Of int). But, that probably doesn't matter at all, I just like to use generic Lists rather than Arrays (which you're using). Lists have methods like .Clear() and .Add() which make them much easier to use than Arrays, in my opinion.

    You should fill the List after filling the DataTable. You've created a whole new method to access the database again, just for the purpose of filling the List. Seems redundant.

    Here's the gist of how it should work:

    1) Your function that you've called set_PK_list should use the Me.ContactsDataSet.contact DataTable to fill the List, rather than getting it from the database. You've already accessed the database to .Fill() your DataTable, make use of that! Basically, every time you .Fill(), call the set_PK_list function immediately after.

    2) Same with your function find_new_id(). You need to be comparing what's in the List vs what's in the DataTable. Again, you do NOT need to access the database. The data is already in the DataTable.

    The workflow should be like this:

    Code to .Update(Me.ContactsDataSet.contact) 
    Code to .Fill(Me.ContactsDataSet.contact) 
    set_PK_list()
    target_ID = find_new_id()
    Add target_ID to the List
    Use target_ID to find the .IndexOf the row in Me.ContactsDataSet.contact.
    Set the BindingSource.Position to the Index.

    Does this make sense?


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, September 29, 2013 12:45 AM
  • I will try it.  Update later.

    Thanks.


    RONATMOODYLAKE

    Sunday, September 29, 2013 12:48 AM
  • I would post code instead of just saying do steps 1, 2 and 3 ... but I'd just mangle the VB anyway. I suppose I could have written something in C# and used a C#-to-VB translator, but they're not always accurate either. Hopefully you followed my step-by-step thinking. If not, ask more questions and I'll see what I can do about conjuring up some code and converting C#-to-VB.

    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, September 29, 2013 12:54 AM
  • You can post whatever, and thanks again.

    I have written code since 1965 and in almost every language.  This new junk is un-necessarily convoluted and intentionally confusing, but I am determined to persist.

    I see a separation between people who want to produce something useful and those who want to maintain lofty arrogance.  I have always said that the justification for any particular methodology is simply that "it works".


    RONATMOODYLAKE

    Sunday, September 29, 2013 1:13 AM
  • I've been writing code since 1975 (or a few years earlier if you count my college years), so you've got me beat, old man! ;0)

    When I first started with .NET in early 2002, 1.0 was particularly buggy. 1.1 was a great improvement, but still I wrote a lot of my own classes to do things that weren't done natively by the .NET Framework. In essence, I wrote my own "Framework". As new versions of .NET came along, I modified some of my old "Framework" classes to take advantage of some of the new stuff. And some of the new stuff was crap, as far as I was concerned and I didn't want to use it at all (those damn TableAdapters are a case in point ... to this day, I still prefer my own "Framework" DataAccess classes).

    Anyway, I don't foresee being able to write up any helpful code for you tonite. It's almost dinner time and then there's always a movie during/after dinner. By then, it's almost bedtime (or at least "read a book time") ... so I'm pretty much done for the day, computer-wise. Looks like you're on your own for tonite to try and figure out what the heck I was talking about a few posts back. ;0)

    I'll have more time tomorrow. Good luck!


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, September 29, 2013 1:29 AM
  • I am 70 years old, a physicist.  My first experience with computers was with a DEC PDP 5, 4K 12 bit words, no disk, no tape, just a teletype with a paper tape reader, punch.  I made working programs using a switch register with 12 switches.  I could directly address 128 12-bit words without using indirect addressing, but managed to create some useful code that acquired data in real-time from a gun system at the Remington Arms Company in Bridgeport, Conn.  I was duly impressed when I discovered that someone actually wrote an assembler that could translate my machine code using symbols!

    I have been writing software ever since - for weapons systems, medical informatics, and GPS guided missile applications.  Now just a hobby.  I need to learn new stuff.

    I am now camping in Minnesota and preparing to leave home for the winter.  My wife and I are fortunate enough to be able to avoid the harsh Minnesota winters by hiding out in Africa.  It's a wonderful and affordable place.


    RONATMOODYLAKE

    Sunday, September 29, 2013 2:10 AM
  • I found something that seems to work, although probably not bullet-proof:

            set_PK_list()   ' builds a list of primary keys
            ContactBindingSource.EndEdit()
            If Me.ContactsDataSet.HasChanges Then
                Try
                    Me.Validate()
                    Me.ContactBindingSource.EndEdit()
                    Me.ContactTableAdapter.Update(Me.ContactsDataSet.contact)
                Catch ex As Exception
                    FindErrors()
                End Try
            End If
            target_ID = find_new_id()   ' finds the id of the new record.  It is not in the list of primary keys.
            Me.ContactTableAdapter.Fill(Me.ContactsDataSet.contact) ' fills all records sorted by last name, first name, etc
            Me.ContactBindingSource.Position = get_position_for(target_ID)  ' replicates sort and positions binding source at the index for the new record

    The key was to get the count of a list on contact records sorted in the same was as the .Fill statement.  This is the code:

        Private Function get_position_for(ByVal contact_id As Long) As Long
            Dim count As Long
            Dim sqlString As String
            Dim sqlConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & data_path & ";"
            Dim sqlConn As New OleDb.OleDbConnection(sqlConnString)
            Dim sqlCmnd As New OleDb.OleDbCommand
            Dim Reader As OleDb.OleDbDataReader
            sqlString = "select * from contact order by lastname, firstname, middle, suffix"
            sqlCmnd.Parameters.Clear()
            sqlCmnd.Connection = sqlConn
            sqlCmnd.CommandText = sqlString
            sqlConn.Open()
            Reader = sqlCmnd.ExecuteReader()
            count = 0
            If Reader.HasRows Then
                While Reader.Read()
                    If Reader("ID") = contact_id Then
                        Return count
                        Exit Function
                    End If
                    count = count + 1
                End While
            End If
            Reader.Close()
            sqlConn.Close()
            Return -1
        End Function

    preposterously long trip just to get the binding navigator pointing to the new record!!!


    RONATMOODYLAKE


    Sunday, September 29, 2013 1:47 PM
  • Hi Ron,

    I'm working on some better code for you (as I mentioned earlier, using the already filled DataTable to fill the PK list rather than hitting up the database again with the DataReader) ... but I'm not done with it yet. I probably won't be posting it here until after lunch, Pacific Time.

    However, as I was coding, the thought occurred to me that perhaps a different DataProvider might not have this problem (I don't know that for sure, it could just be that the SqlClient classes work better than the OleDb classes). But, at any rate, I see that you're using the Jet.OleDb and perhaps the Ace.OleDb would be better? I guess some of that depends on what version of Access you're using.

    Check out the Connection strings here: http://www.connectionstrings.com/access

    Sorry I didn't think of this sooner ... no guarantee it will work any better, but it's worth a try. Let me know how/if it works out.


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, September 29, 2013 4:29 PM
  • OK, Ron. I've got some better code for you. 

    You haven't replied back as to whether or not using a different DataProvider fixed the issue of the .Update() method not updating the newly inserted row's PK in the DataTable. So, I may have done this all for naught, but what the heck. Also, this is all in C#, sorry. You can use one of the online converters to convert it to VB. A good one is http://www.developerfusion.com/tools/convert/csharp-to-vb/

    So, in your Form Load event (or wherever you first get your data and load up your DataSet), you're going to want to run the set_PK_list() function after you load up your DataSet. Note that my version is quite different than yours, because I'm using the data in the existing DataTable instead of needlessly accessing the database again.

    private void set_PK_list()
    {
        foreach (ContactsDataSet.contactRow row in this.ContactsDataSet.contact.Rows)
        {
            if (this.primary_keys.Contains(row.ID) == false)
                this.primary_keys.Add(row.ID);
        }
        // you could also clear the list, this.primary_keys.Clear()
        // and add every PK without bothering with the if ...Contains
    }
    

    Do your .Update() and then the .Fill() the same as you always did. 

    But, then use the following code to find the PK of the newly added row, because it will not be in the PK List, find it's index in the DataTable and change the BindingSource.Position. Lastly, add the PK to the List.

    long target_ID = this.find_new_id();
    if (target_ID > -1)
    {
        this.ContactBindingSource.Position = this.get_position_for(target_ID);
        this.primary_keys.Add(target_ID);
    }
    
    
    // And here are the other two functions you need, again note how different it is from yours
    private long find_new_id()
    {
        var hit = this.ContactsDataSet.contact.Where(row => this.primary_keys.Contains(row.ID) == false)
                               .Select(row => row.ID);
    
        // Of course, the following only works if you've inserted only one row
        // but, it's a starting point and you can add additional functionality if desired
        if (hit != null)
            return hit.Select(pk => pk).FirstOrDefault();
        else
            return -1;
    }
    private int get_position_for(long target_ID)
    {
        for (int i = 0; i < this.ContactsDataSet.contact.Count; i++)
        {
            if (this.ContactsDataSet.contact[i].ID == target_ID)
                return i;
        }
    
        return -1;
    }
    

    Note that in the above code, I'm using LINQ ... it can be quite handy and if you haven't already learned it, you should read up on it. Hopefully the C#-to-VB converter will convert the LINQ to VB with the proper syntax ... I know some basic VB syntax, but I have no idea what it should be for LINQ.

    Hope this helps ...


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, September 29, 2013 10:40 PM
  • Oh, BTW ... I forgot to mention this. Sounds like you're originally from Connecticut ... so am I!!  I grew up in the eastern part of the state though, Coventry (birthplace of Nathan Hale, as we all like to brag about). It's about 20 miles east of Hartford (10 miles east of Manchester).  Your career sounds more interesting than mine ... my first programming job was in 1975 at Traveler's Insurance Co. writing Cobol. Although I *did* learn machine code on a DEC PDP in college at UConn (can't remember now if it was 8 or 11 ... used paper tape) and also learned assembler for IBM 360.

    I live in Washington state now.

    You winter in Africa?!?!!!!  Wow, I've never been there.


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, September 29, 2013 10:54 PM
  • You have time to see the world.

    I lived in Bridgeport (Trumbull, actually) while working as a research physicist at the Remington Arms Company.  I was born in Minnesota and attended Hamline University in St. Paul.  I have worked in St. Paul for Univac (now Unisys), in Denver for Honeywell, and then in Minneapolis for Honeywell.

    Africa is delightful, very affordable, and the people are great!  Don't consider it unless you like perfect weather (stay near the southern coast of the Indian Ocean), you like good food (who doesn't?), you want to see lots of animals, and you are not afraid of black people.

    My wife is from Pullman, Washington and Jackson Hole, Wyoming.


    RONATMOODYLAKE

    Monday, September 30, 2013 12:34 AM
  • I've seen some of the world, just not all of it. ;0)  I've been to Europe and South America (the Galapagos Islands are wonderful!).

    Did you try either either of my latest suggestions ... (using a different Data Provider or the latest code I posted)?


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, September 30, 2013 4:32 AM
  • Haven't tried as yet.  I plan to focus on making my code better.  Your suggestion has provided a workable solution, however.

    I am grateful.


    RONATMOODYLAKE

    Monday, September 30, 2013 12:38 PM
  • I'm glad I've helped you reach a workable solution!  I hope you can get a chance to try out my code (after converting it to VB) ... it's much better than needlessly hitting the database just to mess with the PK List.

    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, September 30, 2013 1:46 PM
  • I will give it a try.  Visit me on facebook.  Ronald Chandler - hobbitdome@aol.com.


    RONATMOODYLAKE

    Monday, September 30, 2013 4:47 PM
  • I am starting to implement your suggestions.  I want to use the methods we discussed for several tables in my dataset, so I am starting with the set_PK_list() subroutine.

    The call passes a dataTable to it because I want it to work for various datatables.  The call for the contact table is as follows:

    set_PK_list(ContactsDataSet.contact)

    I am stuck on the implementation of the subroutine as follows:

        Public Sub set_PK_list(ByVal dataset_table As Data.DataTable)
            Dim i As Long
            ReDim primary_keys(0)
            primary_key_count = 0
            For i = 0 To dataset_table.Rows.Count - 1
                ReDim Preserve primary_keys(primary_key_count)
                primary_keys(primary_key_count) = dataset_table.???????????????
                primary_key_count = primary_key_count + 1
            Next
        End Sub

    Somewhere hidden inside my dataset_table is the primary key (row.ID).  Do you know where it is hidden?


    RONATMOODYLAKE

    Monday, September 30, 2013 7:18 PM
  • Yep, that's as easy one, Ron. And, to make it even more "all-purpose", in case all your PK columns are not called "ID", pass that as a parameter too.

    Call it like this:

    set_PK_list(ContactsDataSet.contact, "ID")

    And then, your function gets changed to this:

    Public Sub set_PK_list(ByVal dataset_table As Data.DataTable, string PKName As String)
        Dim i As Long
        ReDim primary_keys(0)
        primary_key_count = 0
        For i = 0 To dataset_table.Rows.Count - 1
            ReDim Preserve primary_keys(primary_key_count)
            
            ' I think you use either Item or Items. It's slightly different than C#
            ' You'll also have to cast that PKName column to a long type, since accessing the
            ' data this way is not strongly typed. I don't remember the casting syntax for VB
            ' CType or something like that?
            primary_keys(primary_key_count) = dataset_table.Rows(i).Item(PKName) 
    
            primary_key_count = primary_key_count + 1
        Next
    End Sub
    


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, September 30, 2013 7:55 PM
  • I think I got all of your ideas to work.  Here are the calls:

            set_PK_list(ContactsDataSet.contact, "ID")   ' builds a list of primary keys
            ContactBindingSource.EndEdit()
            If Me.ContactsDataSet.HasChanges Then
                Try
                    Me.Validate()
                    Me.ContactBindingSource.EndEdit()
                    Me.ContactTableAdapter.Update(Me.ContactsDataSet.contact)
                Catch ex As Exception
                    FindErrors()
                End Try
            End If
            Me.ContactTableAdapter.Fill(Me.ContactsDataSet.contact) ' fills all records sorted by last name, first name, etc
            target_ID = find_new_id(ContactsDataSet.contact, "ID")   ' finds the id of the new record.  It is not in the list of primary keys.
            If target_ID > -1 Then
                Me.ContactBindingSource.Position = get_position_for(ContactsDataSet.contact, target_ID, "ID")  ' replicates sort and positions binding source at the index for the new record
            End If

    Here are the global routines:

        Public Sub set_PK_list(ByVal dataset_table As Data.DataTable, PK_name As String)
            Dim i As Long
            ReDim primary_keys(0)
            primary_key_count = 0
            For i = 0 To dataset_table.Rows.Count - 1
                ReDim Preserve primary_keys(primary_key_count)
                primary_keys(primary_key_count) = dataset_table.Rows(i).Item(PK_name)
                primary_key_count = primary_key_count + 1
            Next
        End Sub

        Public Function find_new_id(ByVal dataset_table As Data.DataTable, PK_name As String) As Long
            Dim i As Long, j As Integer, test_id As Long, found As Boolean
            For i = 0 To dataset_table.Rows.Count - 1
                test_id = dataset_table.Rows(i).Item(PK_name)
                found = False
                For j = 0 To primary_key_count - 1
                    If test_id = primary_keys(j) Then
                        found = True
                        Exit For
                    End If
                Next j
                If found = False Then
                    Return test_id
                    Exit Function
                End If
            Next i
            Return -1
        End Function

        Public Function get_position_for(ByVal dataset_table As Data.DataTable, ByVal contact_id As Long, PK_name As String) As Long
            Dim i As Long
            For i = 0 To dataset_table.Rows.Count - 1
                If dataset_table.Rows(i).Item(PK_name) = contact_id Then
                    Return i
                    Exit Function
                End If
            Next
            Return -1
        End Function

    It took a while (and a lot of work on your part), but I think this should work for other tables in the project also.

    Thanks again!!


    RONATMOODYLAKE

    Monday, September 30, 2013 10:22 PM
  • You're quite welcome, Ron! I'm glad all of our hard work paid off!  ;0)

    Good luck with the rest of your project!


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by RONATMOODYLAKE Tuesday, October 1, 2013 11:52 AM
    Tuesday, October 1, 2013 4:57 AM