Professional Web Applications Themes

New to ASP and Stored Procedures - ASP Database

Hi I have some experince with ASP and databases in General, however Stored Procedures are new. I need to call a stored procedure and have bene told I need to to the following: declare gmnv varchar(20) EXEC GMW_NV_Create gmnv OUTPUT EXEC GMW_NV_SetValue gmnv, 'contact', 'CURE' EXEC GMW_NV_SetValue gmnv, 'rectype', 'P' EXEC GMW_WriteContSupp gmnv Any pointer on how to code this in ASP woudl be apprecaited Regards John Berman [email]john_bermanblueyonder.co.uk[/email]...

  1. #1

    Default New to ASP and Stored Procedures

    Hi

    I have some experince with ASP and databases in General, however Stored
    Procedures are new.

    I need to call a stored procedure and have bene told I need to to the
    following:

    declare gmnv varchar(20)

    EXEC GMW_NV_Create gmnv OUTPUT

    EXEC GMW_NV_SetValue gmnv, 'contact', 'CURE'

    EXEC GMW_NV_SetValue gmnv, 'rectype', 'P'

    EXEC GMW_WriteContSupp gmnv

    Any pointer on how to code this in ASP woudl be apprecaited

    Regards

    John Berman

    [email]john_bermanblueyonder.co.uk[/email]


    John Berman Guest

  2. #2

    Default Re: New to ASP and Stored Procedures

    I see you have output parameters, so you will need to use a Command object.
    I've written a stored procedure code generator which is available here:
    [url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url]

    Give it a try. To read the values of the output parameters after executing
    the procedures, simply use, for example:
    outputval = cmd.Parameters("gmnv")

    You need to verify that the stored procedures contain the line

    SET NOCOUNT ON

    so that you do not get any unexpected resultsets.

    Wait - you need these executed in sequence? I would create a 5th stored
    procedure that encapsulates these stored procedure calls and execute that
    single stored procedure. Like this (I don't know if this sequence of
    procedures needs to return any results to the client - I will assume that
    nothing is returned to the client):

    Create Procedure GMW_NV_Do_Stuff AS
    declare gmnv varchar(20)

    EXEC GMW_NV_Create gmnv OUTPUT

    EXEC GMW_NV_SetValue gmnv, 'contact', 'CURE'

    EXEC GMW_NV_SetValue gmnv, 'rectype', 'P'

    EXEC GMW_WriteContSupp gmnv
    go

    To execute this in asp, you can use a simpler technique:

    dim cn
    set cn=createobject("adodb.connection")
    cn.open "Provider=sqloledb;" & _
    "Data Source=server_name;" & _
    "Initial Catalog=database_name;" & _
    "User ID = xx;" & _
    "Password = "

    cn.GMW_NV_Do_Stuff


    HTH,
    Bob Barrows

    John Berman wrote:
    > Hi
    >
    > I have some experince with ASP and databases in General, however
    > Stored Procedures are new.
    >
    > I need to call a stored procedure and have bene told I need to to the
    > following:
    >
    > declare gmnv varchar(20)
    >
    > EXEC GMW_NV_Create gmnv OUTPUT
    >
    > EXEC GMW_NV_SetValue gmnv, 'contact', 'CURE'
    >
    > EXEC GMW_NV_SetValue gmnv, 'rectype', 'P'
    >
    > EXEC GMW_WriteContSupp gmnv
    >
    > Any pointer on how to code this in ASP woudl be apprecaited
    >
    > Regards
    >
    > John Berman
    >
    > [email]john_bermanblueyonder.co.uk[/email]
    --
    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] Guest

  3. #3

    Default Re: New to ASP and Stored Procedures

    Bob

    Thanks for this, I downloaded your app, to generte the code but all I get is
    404 error.
    I will make the 5th Sp but when I run cn.GMW_NV_Do_Stuff I will need to
    define the variables hence I thought to use the generator to do the hard bit
    for me.

    Must I run the code generator on the sql server ?

    Regards

    John Berman


    "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:el0bqVH$DHA.2860tk2msftngp13.phx.gbl...
    > I see you have output parameters, so you will need to use a Command
    object.
    > I've written a stored procedure code generator which is available here:
    >
    [url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url]
    >
    > Give it a try. To read the values of the output parameters after executing
    > the procedures, simply use, for example:
    > outputval = cmd.Parameters("gmnv")
    >
    > You need to verify that the stored procedures contain the line
    >
    > SET NOCOUNT ON
    >
    > so that you do not get any unexpected resultsets.
    >
    > Wait - you need these executed in sequence? I would create a 5th stored
    > procedure that encapsulates these stored procedure calls and execute that
    > single stored procedure. Like this (I don't know if this sequence of
    > procedures needs to return any results to the client - I will assume that
    > nothing is returned to the client):
    >
    > Create Procedure GMW_NV_Do_Stuff AS
    > declare gmnv varchar(20)
    >
    > EXEC GMW_NV_Create gmnv OUTPUT
    >
    > EXEC GMW_NV_SetValue gmnv, 'contact', 'CURE'
    >
    > EXEC GMW_NV_SetValue gmnv, 'rectype', 'P'
    >
    > EXEC GMW_WriteContSupp gmnv
    > go
    >
    > To execute this in asp, you can use a simpler technique:
    >
    > dim cn
    > set cn=createobject("adodb.connection")
    > cn.open "Provider=sqloledb;" & _
    > "Data Source=server_name;" & _
    > "Initial Catalog=database_name;" & _
    > "User ID = xx;" & _
    > "Password = "
    >
    > cn.GMW_NV_Do_Stuff
    >
    >
    > HTH,
    > Bob Barrows
    >
    > John Berman wrote:
    > > Hi
    > >
    > > I have some experince with ASP and databases in General, however
    > > Stored Procedures are new.
    > >
    > > I need to call a stored procedure and have bene told I need to to the
    > > following:
    > >
    > > declare gmnv varchar(20)
    > >
    > > EXEC GMW_NV_Create gmnv OUTPUT
    > >
    > > EXEC GMW_NV_SetValue gmnv, 'contact', 'CURE'
    > >
    > > EXEC GMW_NV_SetValue gmnv, 'rectype', 'P'
    > >
    > > EXEC GMW_WriteContSupp gmnv
    > >
    > > Any pointer on how to code this in ASP woudl be apprecaited
    > >
    > > Regards
    > >
    > > John Berman
    > >
    > > [email]john_bermanblueyonder.co.uk[/email]
    >
    > --
    > 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.
    >
    >

    John Berman Guest

  4. #4

    Default Re: New to ASP and Stored Procedures

    Why do you need to bring all this data back to ASP? It seems like you're
    passin one variable from SP to SP. Couldn't you do all of this work in a
    single SP, and call that from ASP?

    [url]http://www.aspfaq.com/2201[/url]
    [url]http://www.aspfaq.com/params.htm[/url]

    --
    Aaron Bertrand
    SQL Server MVP
    [url]http://www.aspfaq.com/[/url]




    "John Berman" <John_bermanblueyonder.co.uk> wrote in message
    news:U8n%b.8413$IW1.7247news-binary.blueyonder.co.uk...
    > Hi
    >
    > I have some experince with ASP and databases in General, however Stored
    > Procedures are new.
    >
    > I need to call a stored procedure and have bene told I need to to the
    > following:
    >
    > declare gmnv varchar(20)
    >
    > EXEC GMW_NV_Create gmnv OUTPUT
    >
    > EXEC GMW_NV_SetValue gmnv, 'contact', 'CURE'
    >
    > EXEC GMW_NV_SetValue gmnv, 'rectype', 'P'
    >
    > EXEC GMW_WriteContSupp gmnv
    >
    > Any pointer on how to code this in ASP woudl be apprecaited
    >
    > Regards
    >
    > John Berman
    >
    > [email]john_bermanblueyonder.co.uk[/email]
    >
    >

    Aaron Bertrand [MVP] Guest

  5. #5

    Default Re: New to ASP and Stored Procedures

    Sorry about this (its partially due to my ignorance re stored procedures


    The SP's in question are apparently set in stone, they create a record in
    our dbase and were written externally. So Im told to create a new record I
    must do this

    declare gmnv varchar(20)
    EXEC GMW_NV_Create gmnv OUTPUT
    EXEC GMW_NV_SetValue gmnv, 'contact', 'CURE'
    EXEC GMW_NV_SetValue gmnv, 'rectype', 'P'
    EXEC GMW_NV_SetValue gmnv, 'contsupref', '[ip address]'
    EXEC GMW_NV_SetValue gmnv, 'phone', 'xA89Y881$N'
    EXEC GMW_NV_SetValue gmnv, 'accountno', '[accountno from contact1 record]'
    EXEC GMW_NV_SetValue gmnv, 'title', '[todays date mm/dd/ccyy]'
    EXEC GMW_NV_SetValue gmnv, 'country', '[todays date mm/dd/ccyy]'
    EXEC GMW_NV_SetValue gmnv, 'linkacct', '1'
    EXEC GMW_NV_SetValue gmnv, 'notes', '[todays date and time mm/dd/ccyy
    hh:mmam]'
    EXEC GMW_NV_SetValue gmnv, 'user', 'CURE'
    EXEC GMW_WriteContSupp gmnv
    EXEC GMW_NV_Delete gmnv

    As you can see some of the values are standard and some I need to provide at
    the time of creation

    Now I have done this from ASP

    ' Open connection to the database
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open xDb_Conn_Str1
    Set rs = Server.CreateObject("ADODB.Recordset")
    strSql = "'EXEC GMW_NV_SetValue gmnv, 'rectype', 'P'"
    Rs.open strSql, conn

    and it works but I seemed long winded and I was not sure if I was doing this
    the write way ? as I belive I would need to repeat this for each line from
    the orginal SP

    Regards

    John Berman



    "Aaron Bertrand [MVP]" <aaronTRASHaspfaq.com> wrote in message
    news:uXTJvWI$DHA.3772TK2MSFTNGP11.phx.gbl...
    > Why do you need to bring all this data back to ASP? It seems like you're
    > passin one variable from SP to SP. Couldn't you do all of this work in a
    > single SP, and call that from ASP?
    >
    > [url]http://www.aspfaq.com/2201[/url]
    > [url]http://www.aspfaq.com/params.htm[/url]
    >
    > --
    > Aaron Bertrand
    > SQL Server MVP
    > [url]http://www.aspfaq.com/[/url]
    >
    >
    >
    >
    > "John Berman" <John_bermanblueyonder.co.uk> wrote in message
    > news:U8n%b.8413$IW1.7247news-binary.blueyonder.co.uk...
    > > Hi
    > >
    > > I have some experince with ASP and databases in General, however Stored
    > > Procedures are new.
    > >
    > > I need to call a stored procedure and have bene told I need to to the
    > > following:
    > >
    > > declare gmnv varchar(20)
    > >
    > > EXEC GMW_NV_Create gmnv OUTPUT
    > >
    > > EXEC GMW_NV_SetValue gmnv, 'contact', 'CURE'
    > >
    > > EXEC GMW_NV_SetValue gmnv, 'rectype', 'P'
    > >
    > > EXEC GMW_WriteContSupp gmnv
    > >
    > > Any pointer on how to code this in ASP woudl be apprecaited
    > >
    > > Regards
    > >
    > > John Berman
    > >
    > > [email]john_bermanblueyonder.co.uk[/email]
    > >
    > >
    >
    >

    John Berman Guest

  6. #6

    Default Re: New to ASP and Stored Procedures

    John Berman wrote:
    > Bob
    >
    > Thanks for this, I downloaded your app, to generte the code but all I
    > get is 404 error.
    > I will make the 5th Sp but when I run cn.GMW_NV_Do_Stuff I will need
    > to define the variables hence I thought to use the generator to do
    > the hard bit for me.
    You won't need to. You can use this simple technique to pass parameter
    values:

    cn.GMW_NV_Do_Stuff parmval1, ..., parmvalN

    Bob Barrows

    --
    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] Guest

  7. #7

    Default Re: New to ASP and Stored Procedures - Thanks

    Thanks to all, it works fine now
    Its a steep learning curve

    Regards

    John Berman

    "John Berman" <John_bermanblueyonder.co.uk> wrote in message
    news:pvp%b.9600$IW1.3912news-binary.blueyonder.co.uk...
    > Sorry about this (its partially due to my ignorance re stored procedures
    >
    >
    > The SP's in question are apparently set in stone, they create a record in
    > our dbase and were written externally. So Im told to create a new record I
    > must do this
    >
    > declare gmnv varchar(20)
    > EXEC GMW_NV_Create gmnv OUTPUT
    > EXEC GMW_NV_SetValue gmnv, 'contact', 'CURE'
    > EXEC GMW_NV_SetValue gmnv, 'rectype', 'P'
    > EXEC GMW_NV_SetValue gmnv, 'contsupref', '[ip address]'
    > EXEC GMW_NV_SetValue gmnv, 'phone', 'xA89Y881$N'
    > EXEC GMW_NV_SetValue gmnv, 'accountno', '[accountno from contact1
    record]'
    > EXEC GMW_NV_SetValue gmnv, 'title', '[todays date mm/dd/ccyy]'
    > EXEC GMW_NV_SetValue gmnv, 'country', '[todays date mm/dd/ccyy]'
    > EXEC GMW_NV_SetValue gmnv, 'linkacct', '1'
    > EXEC GMW_NV_SetValue gmnv, 'notes', '[todays date and time mm/dd/ccyy
    > hh:mmam]'
    > EXEC GMW_NV_SetValue gmnv, 'user', 'CURE'
    > EXEC GMW_WriteContSupp gmnv
    > EXEC GMW_NV_Delete gmnv
    >
    > As you can see some of the values are standard and some I need to provide
    at
    > the time of creation
    >
    > Now I have done this from ASP
    >
    > ' Open connection to the database
    > Set conn = Server.CreateObject("ADODB.Connection")
    > conn.Open xDb_Conn_Str1
    > Set rs = Server.CreateObject("ADODB.Recordset")
    > strSql = "'EXEC GMW_NV_SetValue gmnv, 'rectype', 'P'"
    > Rs.open strSql, conn
    >
    > and it works but I seemed long winded and I was not sure if I was doing
    this
    > the write way ? as I belive I would need to repeat this for each line from
    > the orginal SP
    >
    > Regards
    >
    > John Berman
    >
    >
    >
    > "Aaron Bertrand [MVP]" <aaronTRASHaspfaq.com> wrote in message
    > news:uXTJvWI$DHA.3772TK2MSFTNGP11.phx.gbl...
    > > Why do you need to bring all this data back to ASP? It seems like
    you're
    > > passin one variable from SP to SP. Couldn't you do all of this work in
    a
    > > single SP, and call that from ASP?
    > >
    > > [url]http://www.aspfaq.com/2201[/url]
    > > [url]http://www.aspfaq.com/params.htm[/url]
    > >
    > > --
    > > Aaron Bertrand
    > > SQL Server MVP
    > > [url]http://www.aspfaq.com/[/url]
    > >
    > >
    > >
    > >
    > > "John Berman" <John_bermanblueyonder.co.uk> wrote in message
    > > news:U8n%b.8413$IW1.7247news-binary.blueyonder.co.uk...
    > > > Hi
    > > >
    > > > I have some experince with ASP and databases in General, however
    Stored
    > > > Procedures are new.
    > > >
    > > > I need to call a stored procedure and have bene told I need to to the
    > > > following:
    > > >
    > > > declare gmnv varchar(20)
    > > >
    > > > EXEC GMW_NV_Create gmnv OUTPUT
    > > >
    > > > EXEC GMW_NV_SetValue gmnv, 'contact', 'CURE'
    > > >
    > > > EXEC GMW_NV_SetValue gmnv, 'rectype', 'P'
    > > >
    > > > EXEC GMW_WriteContSupp gmnv
    > > >
    > > > Any pointer on how to code this in ASP woudl be apprecaited
    > > >
    > > > Regards
    > > >
    > > > John Berman
    > > >
    > > > [email]john_bermanblueyonder.co.uk[/email]
    > > >
    > > >
    > >
    > >
    >
    >

    John Berman Guest

Similar Threads

  1. Stored Procedures
    By Anthony M. Davis in forum Coldfusion - Advanced Techniques
    Replies: 28
    Last Post: February 9th, 03:17 PM
  2. Stored Procedures JDBC
    By mighty wayward in forum Coldfusion - Advanced Techniques
    Replies: 6
    Last Post: June 14th, 11:00 AM
  3. dt_ Stored Procedures
    By chopper in forum ASP Database
    Replies: 2
    Last Post: July 20th, 04:06 PM
  4. Stored Procedures and 4GL
    By Ahmer Sajjad in forum Informix
    Replies: 1
    Last Post: September 9th, 01:23 PM
  5. Your opinion about stored procedures
    By mono in forum ASP.NET General
    Replies: 3
    Last Post: July 26th, 08:23 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139