Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

How do I grab MS SQL Server autoincrement value after INSERT?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How do I grab MS SQL Server autoincrement value after INSERT?

    I need to grab the auto-generated PK value from my SQL table as soon as an INSERT fires.

    The darnest thing is that while I can do this in the Xbasic Interactive window, running the exact same sequence in Xbasic code will not return the PK value, no matter what I try. Here's the code that works interactively but not as running code:

    Code:
    				qry = "INSERT INTO ITEM (ITRACKINGNUMBER) VALUES ('ABC')"
    				if conn.Execute(qry)
    					'Get the new IITEMID
    					qry="select id = Scope_Identity()"
    					if conn.Execute(qry)
    						cItemID = conn.resultset.data("id")
    					else
    					end if
    				else
    				end if
    Interactive execution returns the autoincremented ID (IITEMID), running code not so much. Any ideas?

  • #2
    Re: How do I grab MS SQL Server autoincrement value after INSERT?

    Originally posted by nlights View Post
    I need to grab the auto-generated PK value from my SQL table as soon as an INSERT fires.

    The darnest thing is that while I can do this in the Xbasic Interactive window, running the exact same sequence in Xbasic code will not return the PK value, no matter what I try. Here's the code that works interactively but not as running code:

    Code:
                    qry = "INSERT INTO ITEM (ITRACKINGNUMBER) VALUES ('ABC')"
                    if conn.Execute(qry)
                        'Get the new IITEMID
                        qry="select id = Scope_Identity()"
                        if conn.Execute(qry)
                            cItemID = conn.resultset.data("id")
                        else
                        end if
                    else
                    end if
    Interactive execution returns the autoincremented ID (IITEMID), running code not so much. Any ideas?
    No need to use another query as there is an xbasic function LastInsertedIdentity so use

    cItemID = conn.LastInsertedIdentity()
    Glen Schild



    My Blog

    Comment


    • #3
      Re: How do I grab MS SQL Server autoincrement value after INSERT?

      Yeah, I noticed that after posting my question. I tried it, and still keep getting the same result. I even tried it with the optional <tableinfo> parameter, but no go. Trying to think of other approaches here... GUID? :-(

      Comment


      • #4
        Re: How do I grab MS SQL Server autoincrement value after INSERT?

        you need to re-query the server
        qry="select Scope_Identity()"
        result = cn.execute(qry)
        if result
        dim rs as sql::resultset
        rs = cn.resultset
        dim id as n
        id = rs.data(1)
        .
        .
        .
        end if

        in these lines
        I have posted similar code in another post in this area

        http://msgboard.alphasoftware.com/al...226#post672226 post #8

        ps: normally lastInsertedId works, I do not know why it did not in your case.
        thanks for reading

        gandhi

        version 11 3381 - 4096
        mysql backend
        http://www.alphawebprogramming.blogspot.com
        [email protected]
        Skype:[email protected]
        1 914 924 5171

        Comment


        • #5
          Re: How do I grab MS SQL Server autoincrement value after INSERT?

          Thanks, Gandhi --

          That's exactly what I did (re-query.) My code is slightly different than yours, but the principal is the same.

          I'm thinking this has probably something to do with my DB back-end (full MS SQL Server 2008.) Like I said, the code works just fine when entered interactively, but fails (returns blank) when running through the code. I re-engineered my data model, re-wrote a bunch of code, and regenerated my tables in order to use GUIDs (luckily I use a DB CASE tool which makes the data part of the changes very easy/) GUIDs are more work, but at least I know what the primary key value is going to be before and after the row is inserted. This is critical in my case, because I need to make link table entries after the two many-to-many tables are updated, and the link tables need to know what the PKs are on both sides.

          If AA supported data binding on unrelated tables, another workaround would be to look at e.newdata (or whatever it is) after the insert. I think it would give me the new ID, no problem. But since I can't do unrelated table data binding in AA, I'm running into dead-ends all over the place trying to get this pretty simple job done. And it is rather aggravating.

          Furthermore, the documentation is hideous enough to drive me looking for answers through Google search. The videos are great, but good luck trying to find an answer to a specific question in the 1000+ video library...

          Comment


          • #6
            Re: How do I grab MS SQL Server autoincrement value after INSERT?

            take a look at this screencast:

            http://screencast.com/t/KLLXtVC76tdD

            I am able to get it in grid and dialog.
            see if this helps you

            ps: I added one more record before taping so the number is 8 rather than 7.
            Last edited by GGandhi; 05-09-2014, 09:41 PM.
            thanks for reading

            gandhi

            version 11 3381 - 4096
            mysql backend
            http://www.alphawebprogramming.blogspot.com
            [email protected]
            Skype:[email protected]
            1 914 924 5171

            Comment


            • #7
              Re: How do I grab MS SQL Server autoincrement value after INSERT?

              In case someone comes looking for same using MySQL, here is syntax for that database, minus error checking:

              dim cn as sql::Connection
              cn.open("::name::conn")
              vsql = "INSERT INTO table_a (field1) VALUES 'ABC' "

              cn.execute(vsql)
              vsql = "SELECT LAST_INSERT_ID() as mypk" ' this gets the auto-increment value
              cn.execute(vsql)
              mypk = cn.resultset.data("mypk")

              ...go on to use mypk as needed.
              Steve Wood
              Join the ALPHA DEVELOPERS NETWORK
              There is no Cloud. It's just someone else's computer.
              Web - Mobile - Hosting - Products - Frameworks - Developer Resources
              AlphaToGo | IADN (100% Alpha Anywhere Websites)

              Comment


              • #8
                Re: How do I grab MS SQL Server autoincrement value after INSERT?

                Gandhi --

                Thank you for the screencast! Unfortunately, however, I have to roll my own data management in Xbasic because of the inherent limitations in AA Data Binding capabilities (no unrelated data, no one-to-many without repeating sections in UX dialog.) So, I have to write the code that AA really should take care of, and executing that code I get absolutely nowhere with the lastInsertedId() function. Again, works when I type it in the interactive window, but if I copy the exact same lines that worked into the code window and run the code, I can see through the debugger that lastInsertedID() (or any other workarounds) simply do not work. Period. Maybe it's because I'm running full SQL Server 2008 rather than the express version. Who knows? But it is not working here (when the code runs.)

                Comment


                • #9
                  Re: How do I grab MS SQL Server autoincrement value after INSERT?

                  Steve --

                  Thanks for the response. Looks like you are using MySQL. SQL Server uses the SELECT SCOPE_IDENTITY() -command, whereas MySQL users SELECT LAST_INSERT_ID(). SQL Server 2008 does not work with this part of AA.

                  Comment


                  • #10
                    Re: How do I grab MS SQL Server autoincrement value after INSERT?

                    I agree with Glenn,

                    cItemID = conn.LastInsertedIdentity()

                    has always worked for me with SQL Server 2008.
                    I do not like the second query as how can you be sure that another user has not inserted in the meantime? Be careful when looking at such things in the debugger.
                    Immediately after conn.Execute("insert statement here"), conn.LastInsertedIdentity holds the correct value in my experience but doing something else with conn then later querying for value of conn.LastInsertedIdentity will not necessarily hold the value you expect. In short, I recommend you look very closely at your code.
                    If others have different experience I would like to know as this is an important area for me in a number of areas where I do further processing when a record is inserted or updated.

                    Comment


                    • #11
                      Re: How do I grab MS SQL Server autoincrement value after INSERT?

                      You could always use a stored procedure to do the insert. That would also require that your insert be parameterized which is good practice to avoid SQL injection. The stored query would return the Identity of the insert. Here's an example using the Northwind database that allows you to add a new category and return the Identity assigned:

                      Code:
                      CREATE PROCEDURE [dbo].[AddCategory] 
                          @CatName            NVARCHAR(15),
                          @new_identity       INT    OUTPUT
                      AS
                      BEGIN
                          SET NOCOUNT ON
                      
                          INSERT INTO CATEGORIES (CategoryName) VALUES (@CatName)
                      
                          SELECT @new_identity = SCOPE_IDENTITY()
                      
                          RETURN
                      
                      END
                      You would need to call this with an input parameter for the new category name and an output parameter that will contain the Identity value after the insert.

                      The Xbasic would look something like:

                      Code:
                      dim cn as sql::Connection
                      cn.open("::name::Nwind")
                      
                      dim argz as sql::Arguments
                      dim result as n
                      dim catname as c
                      
                      catname = "New Category"
                      
                      argz.set("NewCat",catname)
                      argz.set("result",result,SQL::ArgumentUsage::InputOutputArgument) 
                      
                      vcall="{Call AddCategory(:NewCat, :result)} "
                      
                      
                      OK = cn.execute(vCall,argz)
                      if ok
                        ' Get the IDENTITY value
                         result=argz.find("result").data
                         msgbox("Result","The identity is "+Str(result))
                      else
                         msgbox("Warning:","Insert failed")
                      end if
                      Last edited by compuaid; 05-10-2014, 08:14 AM.
                      Brad Weaver, President
                      ComputerAid International
                      Ottawa ON Canada
                      Versailles KY USA
                      www.compuaid.com

                      Comment


                      • #12
                        Re: How do I grab MS SQL Server autoincrement value after INSERT?

                        Hi Garry --

                        It is strange that this seems to work for some people, not so much for others. I noticed that others have run into this same problem (e.g., http://msgboard.alphasoftware.com/al...sertedIdentity) If it didn't work in interactive mode, I would question my code. But since the EXACT same code works interactively and not in the code, I suspected that it had to do with timing issues. So I paused the debugger for a while before executing LastInsertedIdentity and examining the result, but still nothing.

                        Here's the entire Xbasic function where this happens (or, rather, does not happen.) Maybe I'm missing something...

                        Code:
                        function SaveScanData as c (e as p)
                        
                        DIM vItemTable AS P
                        DIM vActionTable AS P
                        DIM conn AS SQL::Connection
                        DIM rs AS P
                        DIM c AS P
                        DIM cScanList AS C
                        dim ti as SQL::TableInfo
                        
                        DEBUG(1)
                        
                        cScanList = comma_to_crlf(e._var1)
                        
                        
                        if conn.open("::Name::PSShip")
                        	'Iterate tracking numbers: if found in ITEMS table already, keep going.  If not, insert.
                        	for each cTrackingNumber in cScanList    	
                        		'1: Add a row to Item -table if necessary (do not duplicate tracking numbers in the ITEM table)
                        
                        			qry = "SELECT ITRACKINGNUMBER FROM ITEM WHERE ITRACKINGNUMBER = '"+ALLTRIM(cTrackingNumber.Value)+"'"
                        			if conn.Execute(qry)
                        				if EVAL_VALID("conn.Resultset.data(\"ITRACKINGNUMBER\")")      'This is one way to tell if query returned anything
                        					'found, keep going
                        				else
                        					'insert new row to ITEM
                        					rs = conn.ResultSet 
                        					qry = "INSERT INTO ITEM (ITRACKINGNUMBER) VALUES ('"+ALLTRIM(cTrackingNumber.Value)+"')"
                        					if conn.Execute(qry)
                        						'Get the new IITEMID
                        						cItemID = conn.LastInsertedIdentity()  
                                                                        
                                                                        'NOTE: cItemID comes up blank here
                        
                        					else
                        						'error inserting tracking number to ITEM table
                        					end if
                        				end if
                        			else
                        			end if
                        		
                        		'2. Add row to action table
                                                'TODO
                        	next 
                        end if
                        
                        SaveScanData = "//alert('Return from SaveScanData');"
                        Last edited by nlights; 05-10-2014, 01:51 PM.

                        Comment


                        • #13
                          Re: How do I grab MS SQL Server autoincrement value after INSERT?

                          Hi Brad --

                          Excellent, thank you for the "lightning tutorial" on stored procedures and Xbasic . This really is a great approach, because it lets the database do the work, returns the generated key reliably, AND protects against SQL injection.


                          Pertti

                          Originally posted by compuaid View Post
                          You could always use a stored procedure to do the insert. That would also require that your insert be parameterized which is good practice to avoid SQL injection. The stored query would return the Identity of the insert. Here's an example using the Northwind database that allows you to add a new category and return the Identity assigned:

                          Code:
                          CREATE PROCEDURE [dbo].[AddCategory] 
                              @CatName            NVARCHAR(15),
                              @new_identity       INT    OUTPUT
                          AS
                          BEGIN
                              SET NOCOUNT ON
                          
                              INSERT INTO CATEGORIES (CategoryName) VALUES (@CatName)
                          
                              SELECT @new_identity = SCOPE_IDENTITY()
                          
                              RETURN
                          
                          END
                          You would need to call this with an input parameter for the new category name and an output parameter that will contain the Identity value after the insert.

                          The Xbasic would look something like:

                          Code:
                          dim cn as sql::Connection
                          cn.open("::name::Nwind")
                          
                          dim argz as sql::Arguments
                          dim result as n
                          dim catname as c
                          
                          catname = "New Category"
                          
                          argz.set("NewCat",catname)
                          argz.set("result",result,SQL::ArgumentUsage::InputOutputArgument) 
                          
                          vcall="{Call AddCategory(:NewCat, :result)} "
                          
                          
                          OK = cn.execute(vCall,argz)
                          if ok
                            ' Get the IDENTITY value
                             result=argz.find("result").data
                             msgbox("Result","The identity is "+Str(result))
                          else
                             msgbox("Warning:","Insert failed")
                          end if

                          Comment

                          Working...
                          X