Return value from Stored Procedure

Discussion in 'ASP General' started by Dooza, Sep 16, 2008.

  1. Dooza

    Dooza Guest

    I have a stored procedure that takes a number of inputs, does a bulk
    insert, and then outputs a recordset. When I run the stored procedure in
    Server Management Studio I also get a return value from the stored
    procedure which is an INT.

    I want to access this return value on my ASP/VBScript page, but do not
    know how to access it.

    Here is my code so far:

    <%
    Dim rsImport
    Dim rsImport_cmd
    Dim rsImport_numRows

    Set rsImport_cmd = Server.CreateObject ("ADODB.Command")
    rsImport_cmd.ActiveConnection = MM_aclv4test_STRING
    rsImport_cmd.CommandText = "{call dbo.PriceUpdateImport(?,?,?,?,?)}"
    rsImport_cmd.Prepared = true
    rsImport_cmd.Parameters.Append
    rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param1",
    200, 1, 255, rsImport__vendor)
    rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param2",
    200, 1, 255, rsImport__name)
    rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param3",
    200, 1, 255, rsImport__filename)
    rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param4",
    200, 1, 255, rsImport__validfrom)
    rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param5",
    200, 1, 255, rsImport__user)

    Set rsImport = rsImport_cmd.Execute
    rsImport_numRows = 0
    %>

    There is more code above that sets the variable for each parameter.

    I have tried adding this: rsImport_cmd.CreateParameter("@RETURN_VALUE",
    3, 4) but I got this error:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

    Multiple-step OLE DB operation generated errors. Check each OLE DB
    status value, if available. No work was done.

    My stored procedure doesn't declare an output return value, which may be
    the problem, but when I execute the stored procedure in server
    management studio I am given a return value.

    Any ideas or pointers?

    Cheers,

    Steve
     
    Dooza, Sep 16, 2008
    #1
    1. Advertising

  2. Dooza wrote:
    > I have a stored procedure that takes a number of inputs, does a bulk
    > insert, and then outputs a recordset. When I run the stored procedure
    > in Server Management Studio I also get a return value from the stored
    > procedure which is an INT.
    >
    > I want to access this return value on my ASP/VBScript page, but do not
    > know how to access it.
    >
    > Here is my code so far:
    >
    > <%
    > Dim rsImport
    > Dim rsImport_cmd
    > Dim rsImport_numRows
    >
    > Set rsImport_cmd = Server.CreateObject ("ADODB.Command")
    > rsImport_cmd.ActiveConnection = MM_aclv4test_STRING
    > rsImport_cmd.CommandText = "{call dbo.PriceUpdateImport(?,?,?,?,?)}"
    > rsImport_cmd.Prepared = true
    > rsImport_cmd.Parameters.Append
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param1",
    > 200, 1, 255, rsImport__vendor)
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param2",
    > 200, 1, 255, rsImport__name)
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param3",
    > 200, 1, 255, rsImport__filename)
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param4",
    > 200, 1, 255, rsImport__validfrom)
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param5",
    > 200, 1, 255, rsImport__user)
    >
    > Set rsImport = rsImport_cmd.Execute
    > rsImport_numRows = 0
    > %>
    >
    > There is more code above that sets the variable for each parameter.
    >
    > I have tried adding this:
    > rsImport_cmd.CreateParameter("@RETURN_VALUE", 3, 4) but I got this
    > error:
    >

    The return parameter should be the first one you append. You will
    probably appreciate my free Command object code generator available
    here: http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator.zip

    > My stored procedure doesn't declare an output return value, which may
    > be the problem,

    No. A peculiarity of SQL Server is that it will not send output or
    return parameters until all resultsets generated by the procedure have
    been consumed by the caller. This is done for you behind the scenes in
    Query Manager. In ADO code, you have to do this yourself. You have to
    either navigate to the last record in the recordset, or close the
    recordset, before attempting to read the output or return parameter
    values. Switching to a client-side cursor guarantees that the current
    resultset is consumed.

    Moreover, you need to make sure the procedure is not generating any
    unexpected resultsets. Another peculiarity of SQL Server is that it
    sends those "x rows were affected" messages to the caller as closed
    resultsets. If your ADO code does not handle them, you will never see
    the output or return parameters. So, your standard practice should be to
    suppress those messages by including the line "SET NOCOUNT ON" at the
    beginning of your procedure.

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Sep 16, 2008
    #2
    1. Advertising

  3. The parameter @RETURN_VALUE must be the *first* parameter to be
    created/appended to the Paramaters collection. In the same way, all the
    other parameters must be created/appended in the same order as their
    declaration in the SP.

    You also have a line with a call to .Append with no parameter:

    rsImport_cmd.Parameters.Append

    I don't know what this instruction is doing but you should remove it.

    You're also using the old canonical syntax for calling a SP: "{call
    dbo.PriceUpdateImport(?,?,?,?,?)}". I really don't know what this syntax is
    doing here but as you are using ADO to call your Sql-Server, you should
    forget about using ODBC. I won't be surprised if the error message that
    you're getting is coming from that. However, as we don't know anything the
    connection string/provider that you're using, I cannot tell you anything
    more on this one.

    You will also have other problems if you don't add the SET NOCOUNT ON
    instruction at the beginning of your SP or if this SP returns the result of
    multiples queries.

    Finally, don't forget the other recommandations from Bob Barrows.

    --
    Sylvain Lafontaine, ing.
    MVP - Technologies Virtual-PC
    E-mail: sylvain aei ca (fill the blanks, no spam please)


    "Dooza" <> wrote in message
    news:...
    >I have a stored procedure that takes a number of inputs, does a bulk
    >insert, and then outputs a recordset. When I run the stored procedure in
    >Server Management Studio I also get a return value from the stored
    >procedure which is an INT.
    >
    > I want to access this return value on my ASP/VBScript page, but do not
    > know how to access it.
    >
    > Here is my code so far:
    >
    > <%
    > Dim rsImport
    > Dim rsImport_cmd
    > Dim rsImport_numRows
    >
    > Set rsImport_cmd = Server.CreateObject ("ADODB.Command")
    > rsImport_cmd.ActiveConnection = MM_aclv4test_STRING
    > rsImport_cmd.CommandText = "{call dbo.PriceUpdateImport(?,?,?,?,?)}"
    > rsImport_cmd.Prepared = true
    > rsImport_cmd.Parameters.Append
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param1", 200,
    > 1, 255, rsImport__vendor)
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param2", 200,
    > 1, 255, rsImport__name)
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param3", 200,
    > 1, 255, rsImport__filename)
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param4", 200,
    > 1, 255, rsImport__validfrom)
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param5", 200,
    > 1, 255, rsImport__user)
    >
    > Set rsImport = rsImport_cmd.Execute
    > rsImport_numRows = 0
    > %>
    >
    > There is more code above that sets the variable for each parameter.
    >
    > I have tried adding this: rsImport_cmd.CreateParameter("@RETURN_VALUE", 3,
    > 4) but I got this error:
    >
    > Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
    >
    > Multiple-step OLE DB operation generated errors. Check each OLE DB status
    > value, if available. No work was done.
    >
    > My stored procedure doesn't declare an output return value, which may be
    > the problem, but when I execute the stored procedure in server management
    > studio I am given a return value.
    >
    > Any ideas or pointers?
    >
    > Cheers,
    >
    > Steve
     
    Sylvain Lafontaine, Sep 17, 2008
    #3
  4. Taking a look with the SQL-Server Profiler could also be a good idea.

    --
    Sylvain Lafontaine, ing.
    MVP - Technologies Virtual-PC
    E-mail: sylvain aei ca (fill the blanks, no spam please)


    "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
    wrote in message news:...
    > The parameter @RETURN_VALUE must be the *first* parameter to be
    > created/appended to the Paramaters collection. In the same way, all the
    > other parameters must be created/appended in the same order as their
    > declaration in the SP.
    >
    > You also have a line with a call to .Append with no parameter:
    >
    > rsImport_cmd.Parameters.Append
    >
    > I don't know what this instruction is doing but you should remove it.
    >
    > You're also using the old canonical syntax for calling a SP: "{call
    > dbo.PriceUpdateImport(?,?,?,?,?)}". I really don't know what this syntax
    > is doing here but as you are using ADO to call your Sql-Server, you should
    > forget about using ODBC. I won't be surprised if the error message that
    > you're getting is coming from that. However, as we don't know anything
    > the connection string/provider that you're using, I cannot tell you
    > anything more on this one.
    >
    > You will also have other problems if you don't add the SET NOCOUNT ON
    > instruction at the beginning of your SP or if this SP returns the result
    > of multiples queries.
    >
    > Finally, don't forget the other recommandations from Bob Barrows.
    >
    > --
    > Sylvain Lafontaine, ing.
    > MVP - Technologies Virtual-PC
    > E-mail: sylvain aei ca (fill the blanks, no spam please)
    >
    >
    > "Dooza" <> wrote in message
    > news:...
    >>I have a stored procedure that takes a number of inputs, does a bulk
    >>insert, and then outputs a recordset. When I run the stored procedure in
    >>Server Management Studio I also get a return value from the stored
    >>procedure which is an INT.
    >>
    >> I want to access this return value on my ASP/VBScript page, but do not
    >> know how to access it.
    >>
    >> Here is my code so far:
    >>
    >> <%
    >> Dim rsImport
    >> Dim rsImport_cmd
    >> Dim rsImport_numRows
    >>
    >> Set rsImport_cmd = Server.CreateObject ("ADODB.Command")
    >> rsImport_cmd.ActiveConnection = MM_aclv4test_STRING
    >> rsImport_cmd.CommandText = "{call dbo.PriceUpdateImport(?,?,?,?,?)}"
    >> rsImport_cmd.Prepared = true
    >> rsImport_cmd.Parameters.Append
    >> rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param1",
    >> 200, 1, 255, rsImport__vendor)
    >> rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param2",
    >> 200, 1, 255, rsImport__name)
    >> rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param3",
    >> 200, 1, 255, rsImport__filename)
    >> rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param4",
    >> 200, 1, 255, rsImport__validfrom)
    >> rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param5",
    >> 200, 1, 255, rsImport__user)
    >>
    >> Set rsImport = rsImport_cmd.Execute
    >> rsImport_numRows = 0
    >> %>
    >>
    >> There is more code above that sets the variable for each parameter.
    >>
    >> I have tried adding this: rsImport_cmd.CreateParameter("@RETURN_VALUE",
    >> 3, 4) but I got this error:
    >>
    >> Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
    >>
    >> Multiple-step OLE DB operation generated errors. Check each OLE DB status
    >> value, if available. No work was done.
    >>
    >> My stored procedure doesn't declare an output return value, which may be
    >> the problem, but when I execute the stored procedure in server management
    >> studio I am given a return value.
    >>
    >> Any ideas or pointers?
    >>
    >> Cheers,
    >>
    >> Steve

    >
    >
     
    Sylvain Lafontaine, Sep 17, 2008
    #4
  5. Dooza

    Dooza Guest

    Bob Barrows [MVP] wrote:
    > The return parameter should be the first one you append. You will
    > probably appreciate my free Command object code generator available
    > here: http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator.zip


    Thanks Bob, I will give it a go.

    >> My stored procedure doesn't declare an output return value, which may
    >> be the problem,

    > No. A peculiarity of SQL Server is that it will not send output or
    > return parameters until all resultsets generated by the procedure have
    > been consumed by the caller. This is done for you behind the scenes in
    > Query Manager. In ADO code, you have to do this yourself. You have to
    > either navigate to the last record in the recordset, or close the
    > recordset, before attempting to read the output or return parameter
    > values. Switching to a client-side cursor guarantees that the current
    > resultset is consumed.


    Aha, the missing piece of the pie, that will explain it.

    > Moreover, you need to make sure the procedure is not generating any
    > unexpected resultsets. Another peculiarity of SQL Server is that it
    > sends those "x rows were affected" messages to the caller as closed
    > resultsets. If your ADO code does not handle them, you will never see
    > the output or return parameters. So, your standard practice should be to
    > suppress those messages by including the line "SET NOCOUNT ON" at the
    > beginning of your procedure.


    Thats something I have always let Server Studio Manager do for me each
    time I create an SP... I didn't know what it was to begin with, so
    Googled it and realised how important it was.

    Thanks for the advice, I will let you know how I get on.

    Steve
     
    Dooza, Sep 17, 2008
    #5
  6. Dooza

    Dooza Guest

    Bob Barrows [MVP] wrote:
    > The return parameter should be the first one you append. You will
    > probably appreciate my free Command object code generator available
    > here: http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator.zip


    Bob, just to let you know this doesn't do anything in Firefox, but it
    does work in Internet Explorer.

    Steve
     
    Dooza, Sep 17, 2008
    #6
  7. Dooza wrote:
    > Bob Barrows [MVP] wrote:
    >> The return parameter should be the first one you append. You will
    >> probably appreciate my free Command object code generator available
    >> here:
    >> http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator.zip

    >
    > Bob, just to let you know this doesn't do anything in Firefox, but it
    > does work in Internet Explorer.
    >

    I knew that and was willing to live with it. After all, I created it for my
    own use and I use IE at work. You are certainly free to rewrite it to make
    it browser-independent.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Sep 17, 2008
    #7
  8. Dooza

    Dooza Guest

    Bob Barrows [MVP] wrote:
    > Dooza wrote:
    >> Bob Barrows [MVP] wrote:
    >>> The return parameter should be the first one you append. You will
    >>> probably appreciate my free Command object code generator available
    >>> here:
    >>> http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator.zip

    >> Bob, just to let you know this doesn't do anything in Firefox, but it
    >> does work in Internet Explorer.
    >>

    > I knew that and was willing to live with it. After all, I created it for my
    > own use and I use IE at work. You are certainly free to rewrite it to make
    > it browser-independent.


    No thanks, I can live with using IE now and then :)

    Steve
     
    Dooza, Sep 17, 2008
    #8
  9. Dooza

    Dooza Guest

    Bob Barrows [MVP] wrote:
    > No. A peculiarity of SQL Server is that it will not send output or
    > return parameters until all resultsets generated by the procedure have
    > been consumed by the caller. This is done for you behind the scenes in
    > Query Manager. In ADO code, you have to do this yourself. You have to
    > either navigate to the last record in the recordset, or close the
    > recordset, before attempting to read the output or return parameter
    > values. Switching to a client-side cursor guarantees that the current
    > resultset is consumed.


    I have decided to change my tactics, as I needed the return parameter
    before the recordset.

    The stored procedure does a bulk insert into a temporary table, does
    some checks on the data. If there are duplicates it returns an error
    code and a recordset with the duplicate data.

    If there are no duplicates but other errors it will output different
    error numbers.

    If there are no errors, a success number is returned and the data is
    inserted into a permanent table and its details inserted into a job
    table, with its ID returned.

    My ASP needs to check the error number and display a message. If the
    duplicates are found it needs to list them. I need the error message
    before the recordset.

    I need to change my stored procedure to return 3 recordsets:

    1. Error number
    2. ID of import job if it was successful
    3. Recordset of duplicates

    Am I thinking this the right way?

    Steve
     
    Dooza, Sep 17, 2008
    #9
  10. Dooza wrote:
    > Bob Barrows [MVP] wrote:
    >> No. A peculiarity of SQL Server is that it will not send output or
    >> return parameters until all resultsets generated by the procedure
    >> have been consumed by the caller. This is done for you behind the
    >> scenes in Query Manager. In ADO code, you have to do this yourself.
    >> You have to either navigate to the last record in the recordset, or
    >> close the recordset, before attempting to read the output or return
    >> parameter values. Switching to a client-side cursor guarantees that
    >> the current resultset is consumed.

    >
    > I have decided to change my tactics, as I needed the return parameter
    > before the recordset.


    Actually, from reading the next paragraphs, you don't. You need either a
    recordset and error number, or a returned ID
    >
    > The stored procedure does a bulk insert into a temporary table, does
    > some checks on the data. If there are duplicates it returns an error
    > code and a recordset with the duplicate data.
    >
    > If there are no duplicates but other errors it will output different
    > error numbers.
    >
    > If there are no errors, a success number is returned and the data is
    > inserted into a permanent table and its details inserted into a job
    > table, with its ID returned.


    I'm not clear here: if there are multiple records involved, why would there
    necessarily be a single ID returned?

    >
    > My ASP needs to check the error number and display a message. If the
    > duplicates are found it needs to list them. I need the error message
    > before the recordset.


    ? no, you don't. Why does it matter which order you retrieve the data?
    >
    > I need to change my stored procedure to return 3 recordsets:
    >
    > 1. Error number
    > 2. ID of import job if it was successful
    > 3. Recordset of duplicates
    >
    > Am I thinking this the right way?
    >

    I don't think so. Why does everyone want to return data in the form of
    resultsets? :)

    My thinking would be on the lines:
    A return parameter to pass an error number
    An output parameter to pass an ID
    A recordset to return any duplicates (this recordset could be empty)

    In the procedure:
    do the bulk insert
    check the data
    if no dupes,
    -insert the details
    -set the output parameter value to the id(?)
    -truncate the temp table
    -select the contents of the empty temp table
    - set the return value to 0 - RETURN(0)
    if dupes exist
    -insert the non-dupes
    -set the output parm value to the id(?)
    -delete the non-dupes from the temp table
    -select the contents of the temp table
    set the return value to your error code

    In vbscript:
    Set conn = CreateObject ("ADODB.Connection")
    conn.open MM_aclv4test_STRING
    Set rsImport_cmd = CreateObject ("ADODB.Command")
    Set rsImport_cmd.ActiveConnection = conn
    rsImport_cmd.CommandText = "PriceUpdateImport"
    rsImport_cmd.CommandType = 4 'adCmdStoredProc
    'parameter creation here

    set rsImport= rsImport_cmd.Execute
    'forget about that num_rows variable - it's useless
    if not rsImport,eof then ardata=rsImport.GetRows
    rsImport.close
    errcode=rsImport_cmd(0)
    outpos=<index of output parameter in Parameters collection>
    id=rsImport_cmd(outpos)
    conn.close





    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Sep 17, 2008
    #10
  11. Dooza

    Dooza Guest

    Bob Barrows [MVP] wrote:
    > Dooza wrote:
    >> Bob Barrows [MVP] wrote:
    >>> No. A peculiarity of SQL Server is that it will not send output or
    >>> return parameters until all resultsets generated by the procedure
    >>> have been consumed by the caller. This is done for you behind the
    >>> scenes in Query Manager. In ADO code, you have to do this yourself.
    >>> You have to either navigate to the last record in the recordset, or
    >>> close the recordset, before attempting to read the output or return
    >>> parameter values. Switching to a client-side cursor guarantees that
    >>> the current resultset is consumed.

    >> I have decided to change my tactics, as I needed the return parameter
    >> before the recordset.

    >
    > Actually, from reading the next paragraphs, you don't. You need either a
    > recordset and error number, or a returned ID
    >> The stored procedure does a bulk insert into a temporary table, does
    >> some checks on the data. If there are duplicates it returns an error
    >> code and a recordset with the duplicate data.
    >>
    >> If there are no duplicates but other errors it will output different
    >> error numbers.
    >>
    >> If there are no errors, a success number is returned and the data is
    >> inserted into a permanent table and its details inserted into a job
    >> table, with its ID returned.

    >
    > I'm not clear here: if there are multiple records involved, why would there
    > necessarily be a single ID returned?
    >
    >> My ASP needs to check the error number and display a message. If the
    >> duplicates are found it needs to list them. I need the error message
    >> before the recordset.

    >
    > ? no, you don't. Why does it matter which order you retrieve the data?
    >> I need to change my stored procedure to return 3 recordsets:
    >>
    >> 1. Error number
    >> 2. ID of import job if it was successful
    >> 3. Recordset of duplicates
    >>
    >> Am I thinking this the right way?
    >>

    > I don't think so. Why does everyone want to return data in the form of
    > resultsets? :)
    >
    > My thinking would be on the lines:
    > A return parameter to pass an error number
    > An output parameter to pass an ID
    > A recordset to return any duplicates (this recordset could be empty)
    >
    > In the procedure:
    > do the bulk insert
    > check the data
    > if no dupes,
    > -insert the details
    > -set the output parameter value to the id(?)
    > -truncate the temp table
    > -select the contents of the empty temp table
    > - set the return value to 0 - RETURN(0)
    > if dupes exist
    > -insert the non-dupes
    > -set the output parm value to the id(?)
    > -delete the non-dupes from the temp table
    > -select the contents of the temp table
    > set the return value to your error code
    >
    > In vbscript:
    > Set conn = CreateObject ("ADODB.Connection")
    > conn.open MM_aclv4test_STRING
    > Set rsImport_cmd = CreateObject ("ADODB.Command")
    > Set rsImport_cmd.ActiveConnection = conn
    > rsImport_cmd.CommandText = "PriceUpdateImport"
    > rsImport_cmd.CommandType = 4 'adCmdStoredProc
    > 'parameter creation here
    >
    > set rsImport= rsImport_cmd.Execute
    > 'forget about that num_rows variable - it's useless
    > if not rsImport,eof then ardata=rsImport.GetRows
    > rsImport.close
    > errcode=rsImport_cmd(0)
    > outpos=<index of output parameter in Parameters collection>
    > id=rsImport_cmd(outpos)
    > conn.close


    Hi Bob,
    Thank you very much for doing this, I really appreciate you taking time
    out of your day to help me.

    My stored procedure rolls back when it finds the duplicates, showing the
    user the duplicates so that they can try again, but with cleaner data.

    You see once I have imported the clean data, I return the job ID (which
    is used when inserting the data as one of the primary keys) so that it
    can be passed onto the next stage of the import process. There are
    multiple stages to the import process, each updating different things.
    This is just the first part, there are all sorts of different checks
    that need to be done before the final updates take place.

    I think I can use your code to get where I want to.

    Thanks again,

    Steve
     
    Dooza, Sep 17, 2008
    #11
  12. Dooza

    Dooza Guest

    Bob Barrows [MVP] wrote:
    > Dooza wrote:
    >> Bob Barrows [MVP] wrote:

    > Actually, from reading the next paragraphs, you don't. You need either a
    > recordset and error number, or a returned ID
    >> The stored procedure does a bulk insert into a temporary table, does
    >> some checks on the data. If there are duplicates it returns an error
    >> code and a recordset with the duplicate data.
    >>
    >> If there are no duplicates but other errors it will output different
    >> error numbers.
    >>
    >> If there are no errors, a success number is returned and the data is
    >> inserted into a permanent table and its details inserted into a job
    >> table, with its ID returned.

    >
    > I'm not clear here: if there are multiple records involved, why would there
    > necessarily be a single ID returned?


    Just re-read this...

    There are 3 tables
    #Import - for the initial bulk insert from a tab separated file
    PriceUpdateJob - the main details of the import, with an auto-id, name
    of job, date of job, active from date, username, and various other bit
    checks to indicate progress throughout the import process
    PriceUpdateImportData - for the actual data from the initial file, with
    the job id and item id being primary keys.

    Not sure if that makes much difference :)

    Steve
     
    Dooza, Sep 17, 2008
    #12
  13. Dooza

    Dooza Guest

    Hi Bob,
    I have changed my SP so that it doesn't specify an output parameter, I
    am going to rely on the return value instead, if thats possible.

    Throughout my SP I have RETURN 50001 to stop the SP and give the error
    number. Right at the end, if everything has been successful I use RETURN
    @import (which is the I job ID)

    In SSMS I always get a recordset, even if its empty, and always get a
    return value.

    The return value is either 50000, 50001, 50002 or the ID of the job. I
    may have to change the error numbers to something bigger, or do as you
    suggest and have an output parameter.

    I have also changed my code as per your instructions:

    <%
    Dim rsImport
    Dim rsImport_cmd
    Dim rsImport_numRows

    Set conn = CreateObject ("ADODB.Connection")
    conn.open MM_aclv4test_STRING
    Set rsImport_cmd = CreateObject ("ADODB.Command")
    Set rsImport_cmd.ActiveConnection = conn
    rsImport_cmd.CommandText = "PriceUpdateImport"
    rsImport_cmd.CommandType = 4 'adCmdStoredProc
    rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param1",
    200, 1, 255, rsImport__vendor) ' adVarChar
    rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param2",
    200, 1, 255, rsImport__name) ' adVarChar
    rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param3",
    200, 1, 255, rsImport__filename) ' adVarChar
    rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param4",
    200, 1, 255, rsImport__validfrom) ' adVarChar
    rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param5",
    200, 1, 255, rsImport__user) ' adVarChar

    Set rsImport = rsImport_cmd.Execute

    If NOT rsImport.EOF Then
    ardata = rsImport.GetRows
    End if
    rsImport.Close
    errcode = rsImport_cmd(0)

    conn.close
    Set conn = Nothing
    %>

    When there are duplicates I can loop through the array of data and see
    the duplicates, but I never get the correct errcode. I always get the
    value of the first parameter. I have tried adding the @RETURN_VALUE
    parameter but always get an error about there being too many arguments.

    Am I making an obvious mistake?

    Cheers,

    Steve
     
    Dooza, Sep 17, 2008
    #13
  14. Dooza wrote:
    > Hi Bob,
    > I have changed my SP so that it doesn't specify an output parameter, I
    > am going to rely on the return value instead, if thats possible.


    It's possible, but not advisable.
    >
    > Throughout my SP I have RETURN 50001 to stop the SP and give the error
    > number. Right at the end, if everything has been successful I use
    > RETURN @import (which is the I job ID)
    >
    > In SSMS I always get a recordset, even if its empty, and always get a
    > return value.
    >
    > The return value is either 50000, 50001, 50002 or the ID of the job. I
    > may have to change the error numbers to something bigger, or do as you
    > suggest and have an output parameter.


    There should be no problem returning the
    >
    > I have also changed my code as per your instructions:
    >
    > <%
    > Dim rsImport
    > Dim rsImport_cmd
    > Dim rsImport_numRows
    >
    > Set conn = CreateObject ("ADODB.Connection")
    > conn.open MM_aclv4test_STRING
    > Set rsImport_cmd = CreateObject ("ADODB.Command")
    > Set rsImport_cmd.ActiveConnection = conn
    > rsImport_cmd.CommandText = "PriceUpdateImport"
    > rsImport_cmd.CommandType = 4 'adCmdStoredProc


    The Return parameter should have been appended here - this is very
    important!

    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param1",
    > 200, 1, 255, rsImport__vendor) ' adVarChar
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param2",
    > 200, 1, 255, rsImport__name) ' adVarChar
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param3",
    > 200, 1, 255, rsImport__filename) ' adVarChar
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param4",
    > 200, 1, 255, rsImport__validfrom) ' adVarChar
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param5",
    > 200, 1, 255, rsImport__user) ' adVarChar
    >
    > Set rsImport = rsImport_cmd.Execute
    >
    > If NOT rsImport.EOF Then
    > ardata = rsImport.GetRows
    > End if
    > rsImport.Close
    > errcode = rsImport_cmd(0)

    The 0 refers to the first item in the Parameters collection. Right now,
    that is the rsImport__vendor parameter. You need to append the
    RETURN_VALUE parameter first!

    >
    > conn.close
    > Set conn = Nothing
    > %>
    >
    > When there are duplicates I can loop through the array of data and see
    > the duplicates, but I never get the correct errcode. I always get the
    > value of the first parameter. I have tried adding the @RETURN_VALUE
    > parameter but always get an error about there being too many
    > arguments.
    >
    > Am I making an obvious mistake?
    >


    See above

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Sep 17, 2008
    #14
  15. Dooza

    Dooza Guest

    I was missing the 2 from the end

    rsImport_cmd.Parameters.Append
    rsImport_cmd.CreateParameter("@RETURN_VALUE", 3, 4,2)

    It now does return the correct error number AND the successfully
    inserted ID.

    Steve
     
    Dooza, Sep 17, 2008
    #15
  16. Use the code generator! Just substitute your variable names for the
    names I used in the generator.

    Dooza wrote:
    > Hi Bob,
    > I have changed my SP so that it doesn't specify an output parameter, I
    > am going to rely on the return value instead, if thats possible.
    >
    > Throughout my SP I have RETURN 50001 to stop the SP and give the error
    > number. Right at the end, if everything has been successful I use
    > RETURN @import (which is the I job ID)
    >
    > In SSMS I always get a recordset, even if its empty, and always get a
    > return value.
    >
    > The return value is either 50000, 50001, 50002 or the ID of the job. I
    > may have to change the error numbers to something bigger, or do as you
    > suggest and have an output parameter.
    >
    > I have also changed my code as per your instructions:
    >
    > <%
    > Dim rsImport
    > Dim rsImport_cmd
    > Dim rsImport_numRows
    >
    > Set conn = CreateObject ("ADODB.Connection")
    > conn.open MM_aclv4test_STRING
    > Set rsImport_cmd = CreateObject ("ADODB.Command")
    > Set rsImport_cmd.ActiveConnection = conn
    > rsImport_cmd.CommandText = "PriceUpdateImport"
    > rsImport_cmd.CommandType = 4 'adCmdStoredProc
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param1",
    > 200, 1, 255, rsImport__vendor) ' adVarChar
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param2",
    > 200, 1, 255, rsImport__name) ' adVarChar
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param3",
    > 200, 1, 255, rsImport__filename) ' adVarChar
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param4",
    > 200, 1, 255, rsImport__validfrom) ' adVarChar
    > rsImport_cmd.Parameters.Append rsImport_cmd.CreateParameter("param5",
    > 200, 1, 255, rsImport__user) ' adVarChar
    >
    > Set rsImport = rsImport_cmd.Execute
    >
    > If NOT rsImport.EOF Then
    > ardata = rsImport.GetRows
    > End if
    > rsImport.Close
    > errcode = rsImport_cmd(0)
    >
    > conn.close
    > Set conn = Nothing
    > %>
    >
    > When there are duplicates I can loop through the array of data and see
    > the duplicates, but I never get the correct errcode. I always get the
    > value of the first parameter. I have tried adding the @RETURN_VALUE
    > parameter but always get an error about there being too many
    > arguments.
    >
    > Am I making an obvious mistake?
    >
    > Cheers,
    >
    > Steve


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Sep 17, 2008
    #16
  17. Dooza

    Dooza Guest

    Bob Barrows [MVP] wrote:
    > Dooza wrote:
    >> Hi Bob,
    >> I have changed my SP so that it doesn't specify an output parameter, I
    >> am going to rely on the return value instead, if thats possible.

    >
    > It's possible, but not advisable.


    I have taken your advice and used an output parameter, and its working!

    >> Throughout my SP I have RETURN 50001 to stop the SP and give the error
    >> number. Right at the end, if everything has been successful I use
    >> RETURN @import (which is the I job ID)
    >>
    >> In SSMS I always get a recordset, even if its empty, and always get a
    >> return value.
    >>
    >> The return value is either 50000, 50001, 50002 or the ID of the job. I
    >> may have to change the error numbers to something bigger, or do as you
    >> suggest and have an output parameter.

    >
    > There should be no problem returning the
    >> I have also changed my code as per your instructions:
    >>
    >> <%
    >> Dim rsImport
    >> Dim rsImport_cmd
    >> Dim rsImport_numRows
    >>
    >> Set conn = CreateObject ("ADODB.Connection")
    >> conn.open MM_aclv4test_STRING
    >> Set rsImport_cmd = CreateObject ("ADODB.Command")
    >> Set rsImport_cmd.ActiveConnection = conn
    >> rsImport_cmd.CommandText = "PriceUpdateImport"
    >> rsImport_cmd.CommandType = 4 'adCmdStoredProc

    >
    > The Return parameter should have been appended here - this is very
    > important!


    I am there now, thanks to you Bob. Its all starting to make sense and
    work, my days of relying on Dreamweaver are almost over :)

    Thanks again!

    Steve
     
    Dooza, Sep 17, 2008
    #17
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Neo Chou
    Replies:
    1
    Views:
    787
  2. Paul
    Replies:
    1
    Views:
    383
    =?Utf-8?B?UmFtaXJvIENhbGRlcm9u?=
    Jan 19, 2004
  3. Nick
    Replies:
    8
    Views:
    3,277
  4. philip
    Replies:
    2
    Views:
    11,580
    bruce barker \(sqlwork.com\)
    Aug 10, 2006
  5. =?Utf-8?B?QmlzaG95?=
    Replies:
    1
    Views:
    313
    Mark Fitzpatrick
    Dec 24, 2006
Loading...

Share This Page