Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102

    Unanswered: retrieve ID, possible for script to be too fast?

    I am creating a new row and attempting to retrieve the unique ID inside the same process.
    This script isn't displaying the ID; is it wrong, or is it possible the request for the ID is happening too soon (before the DB has created it)?
    Bemused...
    Code:
    Set oConn = Server.CreateObject("ADODB.Connection")
     
    oConn.open("DRIVER=SQL Server;SERVER=tcp:blah-de-blah;")
    
    Set RS = Server.CreateObject("ADODB.RecordSet")
    
    ' Open the table
    RS.Open "participants", oConn, 3,3
    ' Add a new record
    RS.AddNew
    RS("fullName") = request.form("name")
    RS("email") = request.form("email")
    
    ' Update the record
    RS.update
    
    ' Retrive the ID
    unique_ID="Step2010_" & RS("id")
    ' Close the RecordSet
    RS.Close
    Set RS = Nothing
    
    ---- later, in the HTML -----
    <%=unique_ID%>  -----results in just the hard-coded 'Step2010_' but no ID
    Last edited by darkmunk; 07-30-10 at 05:44.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If so then it is an ADO issue, not a SQL Server one. I think we would need to see the code you snipped too.

    If I were writing this the VB code would not access the table. It would call a proc that inserted the row and returned the ID (assuming this is an IDENTITY column). The front end and backend are decoupled and you don't have to debug errors of this nature (i.e. where the error might lie with the provider).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I love the OUTPUT clause...
    Code:
    INSERT INTO your_table (col1, col2)
      OUTPUT inserted.col1
      VALUES ('a', 'b')
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes agreed - that is one way of getting the data back to the client (my preferred method too).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    Thanks guys, although I don't really understand, as you are being a bit cryptic for my little brain
    I'm trying to write an SP but atm the DB is refusing to accept it, this is what I am trying:
    Code:
    CREATE PROCEDURE  [dbo].[SP_InsertParticipant]
        @fullName VARCHAR(32), 
        @email VARCHAR(64),
        @location VARCHAR(32),
        @isColleague VARCHAR(4),
        @whichUnit VARCHAR(32),
       
    AS
    BEGIN 
        SET NOCOUNT ON 
        DECLARE @newID INT 
        INSERT INTO participants (fullName,email,location,isColleague,whichUnit) 
        VALUES   (@fullName,@email,@location,@isColleague,@whichUnit) 
        SELECT @newID = SCOPE_IDENTITY() 
        SELECT newID = @newID 
    END 
    GO 
     
    GRANT EXEC ON dbo.SP_InsertParticipant to myusername 
    GO
    Error -2147217900
    Incorrect syntax near the keyword 'AS'.
    CREATE P*****
    Last edited by darkmunk; 07-30-10 at 08:45. Reason: clarification

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    CREATE PROCEDURE  [dbo].[SP_InsertParticipant]
    (
        @fullName VARCHAR(32), 
        @email VARCHAR(64),
        @location VARCHAR(32),
        @isColleague VARCHAR(4),
        @whichUnit VARCHAR(32)
    )
    AS
    Cryptic? Us?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    Ha ha,
    Getting there, thanks,
    now I'm getting:
    Incorrect syntax near 'GO'.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Execute these two statements seperately.
    Code:
    CREATE PROCEDURE  [dbo].[SP_InsertParticipant]
        (
    @fullName VARCHAR(32), 
        @email VARCHAR(64),
        @location VARCHAR(32),
        @isColleague VARCHAR(4),
        @whichUnit VARCHAR(32)
       )
    AS
    BEGIN 
        SET NOCOUNT ON 
    
        INSERT INTO participants (fullName,email,location,isColleague,whichUnit) 
        OUTPUT inserted.IDColumnName AS newID
        VALUES   (@fullName,@email,@location,@isColleague,@whichUnit) 
    
    END 
    GO
    Code:
    GRANT EXEC ON dbo.SP_InsertParticipant to myusername 
    GO
    Success?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    hmmm.
    still getting syntax error at 'GO'
    I'm still at the stage of getting the DB to store the stored predure. That error shows when I try to load your first block of code.
    Do I even need the second username bit?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    CREATE PROCEDURE  [dbo].[SP_InsertParticipant]
        (
    @fullName VARCHAR(32), 
        @email VARCHAR(64),
        @location VARCHAR(32),
        @isColleague VARCHAR(4),
        @whichUnit VARCHAR(32)
       )
    AS
    
    SELECT 'Hello World'
    GO
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    P.S. don't name your objects with the sp_ prefix
    George
    Home | Blog

  12. #12
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    I've got the DB to accept the stored procedure by leaving out 'GO' (why shouldn't I use SP_ prefix BTW)
    It inserts successfully but my ASP isn't getting the ID.
    Item cannot be found in the collection corresponding to the requested name or ordinal
    I've tried
    Code:
    unique_ID="Step2010_" & RS(0)
    and
    Code:
    unique_ID="Step2010_" & RS("newID")

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I have never liked the Recordset AddNew method.

    What version of SQL Server are you using? Output did not exist in SQL 2000.

    Leaving out one of the GO's may lead to an odd bit of behavior, where the grant statement becomes part of the procedure code (which is bad). Running the create procedure and grant as separate batches as Pootle showed in post 8 would be best just to be sure.

    Lastly for the return value, since you are just inserting one record, it may be better to use an output parameter, rather than the output clause (which would require a recordset to capture in the front end). This would make the flow something along the lines of

    create command object for the stored procedure
    create parameter objects for the values to be passed in and out
    populate the parameter objects
    execute command object

    If I have a bit of time today, I could try to gin up a sample for you, but it is not looking good (already procrastinating here ;-)

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Google searching for "don't use sp_ prefix sql server" returns this result which might be worth your reading:
    Stored Procedure performance using ?sp_? prefix ? Myth or fact? | SQLServerPedia
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Heck. It is better to do a demonstration. Run the following:
    Code:
    create procedure sp_help
    as
    select "hello world"
    Now, try to get the words "Hello world" to appear by running that procedure. The short of it is in SQL 2008 a naming conflict between a user procedure and a system procedure is not possible, because the system procedure will take precedence. The user procedure is simply ignored.

    As for the code sample I promised earlier, this is more pseudo code, but except for egregious syntax errors, missing variables, and other assorted logical problems, it should run without problem ;-)
    Code:
    conn = CreateObject("ADODB.Connection")
    com = CreateObject("ADODB.Command")
    
    conn.open  	'  Must come before com.ActiveConnection
    
    com.ActiveConnection = conn
    com.CommandText = "StoredProcName"
    com.CommandType = adcmdstoredproc
    
    com.parameters.append (com.CreateParameter("Name",  adVarChar, adParamInput, 50))  '  Parameter name, datatype, in/out, Size
    com.parameters.append (com.CreateParameter("email", adVarChar, adParamInput, 50))
    com.parameters.append (com.CreateParameter("ID",    adInteger, adParamOutput))
    
    com.parameters(1).value = name
    com.parameters(2).value = email
    
    com.execute
    
    uniqueID = "Step2010_" & com.parameters(3).value

Posting Permissions

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