Post reply

Do an Insert, then back to a Select (Easy way?)

  • July 20, 2005 at 3:01 pm

    #165400

    I'm trying to find a simple way of doing this.

    I'm inserting a UserId and Date and the SpaID is an auto increment number

    rs_SQL = "INSERT INTO Spa (LastMaintID, LastMaintDate) Values ('"& ClockNumber &"', GetDate() ) "

    rs_SQL = "SELECT Max(SpaID) as SpaID FROM Spa"

    SpaID = rs("SpaID")

    rs_SQL = "SELECT AccountInfo, SpaStartDate FROM Spa WHERE SpaID = '"& SpaID &"' "

    SpaStartDate = rs("SpaStartDate")

    AccountInfo = rs("AccountInfo")

     

    Is there a way I can do this all in one step? Instead of 3?

  • July 20, 2005 at 5:14 pm

    #575941

    Sell I would recommend that you do not do embedded sql in your application, but thats just me.

    I'm not exactly sure if you can do this so give it a try.

    When you try to retreive the last record inserted, I dont think this will be reliable, because someone could have inserted a row between your insert, and this lookup.

    rs_SQL = "SELECT Max(SpaID) as SpaID FROM Spa"

    SpaID = rs("SpaID")

    So Try this.

    rs_SQL = "INSERT INTO Spa (LastMaintID, LastMaintDate) Values ('"& ClockNumber &"', GetDate() ) select scope_Identity() as SpaID " which this will return the Key of the record you inserted.

    Then you can do this

    SpaID = rs_SQL("SpaID")

    rs_SQL = "SELECT AccountInfo, SpaStartDate FROM Spa WHERE SpaID = '"& SpaID &"' "

    Give er a try.

    but you can certainly do all of this in a stored procedure, using only 1 call.

  • July 20, 2005 at 7:31 pm

    #575963

    Ray - I had no idea you could do an insert and select as you did....I took it one step further with:

    rs_SQL = 'INSERT INTO Spa (LastMaintID, LastMaintDate) Values ('"& ClockNumber &"', GetDate()) SELECT AccountInfo, SpaStartDate FROM Spa WHERE SpaID = Scope_Identity()'

    and IT WORKED!

    slboytoy: You might have to tweak the quotes some...

    And about not using embedded SQL - it's not just you Ray!







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 25, 2005 at 9:05 am

    #576827

    The Scope_Identity works great (when using Query Analyzer).  When I post in ASP and try to retrieve the rs("SpaId") I get

    Item cannot be found in the collection corresponding to the requested name or ordinal.

    I'm not sure you can do an Insert + Select in the same SQL connection.  rsS.Open rs_SQL, SQLConnStr, 3, 3

     

     

  • July 25, 2005 at 9:14 am

    #576829

    that's because your select only has the "AccountInfo, SpaStartDate " fields - add "SpaId" to it and run again.....







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 25, 2005 at 9:28 am

    #576835

    I'm not sure you can do an Insert + Select in the same SQL connection.

    Yes it is called a batch! And I would suggest you put that logic in a stored procedure for performanc, easier maintenance,  better security, level of indirection, Back end Tables independence and all other nice feature that come along with them

     


    * Noel

  • July 25, 2005 at 9:42 am

    #576839

    I changed the query to reflect that.   I just used  "Insert .......  SELECT SCOPE_IDENTITY() as SpaID "

     

    Since everyone is telling me to use storeprocedures, I might start converting my pages..

  • July 25, 2005 at 12:12 pm

    #576893

    Since everyone is telling me to use storeprocedures, I might start converting my pages..

    Good for you

     


    * Noel

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply