Is there way to do global changes to SQL in ASP pages

Discussion in 'ASP General' started by johnston, Jul 16, 2003.

  1. johnston

    johnston Guest

    We have over 100 asp pages that reference a table (distribution_components).
    We added a new column and primary key to that table, so now we have to modify
    the where clause to include that column (we have to make the new column = to a
    session variable in the page)

    The table appears 531 times in our code. Is there some util or method that
    allows us to change it globally, other than going to each occurrance, and
    adding the new column to the where clause
     
    johnston, Jul 16, 2003
    #1
    1. Advertising

  2. johnston

    Randy R Guest

    > The table appears 531 times in our code. Is there some util or method
    that
    > allows us to change it globally, other than going to each occurrance, and
    > adding the new column to the where clause


    If the sql statements are exactly the same, you could do a global search and
    replace using Visual Studio, or something similar. You could probably at
    least knock a few off if you do a search and replace on various instances of
    the sql statements.

    Other than that, I'm not sure of any other way... I think you're out of
    luck.
     
    Randy R, Jul 16, 2003
    #2
    1. Advertising

  3. This might be a good time to switch to using stored procedures.



    "johnston" <> wrote in message
    news:3f154fcc$0$68854$...
    > We have over 100 asp pages that reference a table

    (distribution_components).
    > We added a new column and primary key to that table, so now we have to

    modify
    > the where clause to include that column (we have to make the new column =

    to a
    > session variable in the page)
    >
    > The table appears 531 times in our code. Is there some util or method

    that
    > allows us to change it globally, other than going to each occurrance, and
    > adding the new column to the where clause
     
    Aaron Bertrand - MVP, Jul 16, 2003
    #3
  4. http://www.aspfaq.com/2201
    http://www.aspfaq.com/2423




    "johnston" <> wrote in message
    news:3f155973$0$68854$...
    > In article <>, "Aaron Bertrand - MVP"

    <> wrote:
    > >This might be a good time to switch to using stored procedures.
    > >
    > >
    > >
    > >"johnston" <> wrote in message
    > >news:3f154fcc$0$68854$...
    > >> We have over 100 asp pages that reference a table

    > >(distribution_components).
    > >> We added a new column and primary key to that table, so now we have to

    > >modify
    > >> the where clause to include that column (we have to make the new column

    =
    > >to a
    > >> session variable in the page)
    > >>
    > >> The table appears 531 times in our code. Is there some util or method

    > >that
    > >> allows us to change it globally, other than going to each occurrance,

    and
    > >> adding the new column to the where clause

    > >
    > >

    > do you have a good web page or article on doing that? Lots of people

    sugguest
    > it but i have yet to see a good expalnation with example
     
    Aaron Bertrand - MVP, Jul 16, 2003
    #4
  5. johnston

    Ray at Guest

    Or at least use a function to return an array of your data every place you
    need it 531 times. Good lord. I don't mean this to sound rude johnston,
    really, but let this be a lesson to you.

    Ray at work

    "TomB" <> wrote in message
    news:...
    > I disagree.
    >
    > A good time to switch to using stored procedures is before you've written
    > 531 sql statements in your code.
    >
    >
    > "Aaron Bertrand - MVP" <> wrote in message
    > news:...
    > > This might be a good time to switch to using stored procedures.
    > >
    > >
    > >
    > > "johnston" <> wrote in message
    > > news:3f154fcc$0$68854$...
    > > > We have over 100 asp pages that reference a table

    > > (distribution_components).
    > > > We added a new column and primary key to that table, so now we have to

    > > modify
    > > > the where clause to include that column (we have to make the new

    column
    > =
    > > to a
    > > > session variable in the page)
    > > >
    > > > The table appears 531 times in our code. Is there some util or

    method
    > > that
    > > > allows us to change it globally, other than going to each occurrance,

    > and
    > > > adding the new column to the where clause

    > >
    > >

    >
    >
     
    Ray at, Jul 16, 2003
    #5
  6. > I disagree.
    >
    > A good time to switch to using stored procedures is before you've written
    > 531 sql statements in your code.


    Well, given the lack of a time machine, this is probably the best time
    *possible*. :)
     
    Aaron Bertrand - MVP, Jul 16, 2003
    #6
  7. you can do a search and replace in dreamweaver through the current doc, a
    particular folder, or your whole site.

    or you could read in your ASP docs using fso and use a regular expression to
    append to your SQL. you don't mention whether the 531 statements are exactly
    the same

    i'm not knocking you, but why are there 100 pages featuring an instance of
    the same table? are you not passing values to it to return the required
    results? if the table is identical, you could just supply that table as a
    SSI, and then you'd only have had to update the SQL once.

    for the future, maybe you should just stick with a SELECT * in your SQL till
    you get your database design stabilised

    good luck

    Jason




    "Ray at <%=sLocation%>" <> wrote in message
    news:u9ep$...
    > Or at least use a function to return an array of your data every place you
    > need it 531 times. Good lord. I don't mean this to sound rude johnston,
    > really, but let this be a lesson to you.
    >
    > Ray at work
    >
    > "TomB" <> wrote in message
    > news:...
    > > I disagree.
    > >
    > > A good time to switch to using stored procedures is before you've

    written
    > > 531 sql statements in your code.
    > >
    > >
    > > "Aaron Bertrand - MVP" <> wrote in message
    > > news:...
    > > > This might be a good time to switch to using stored procedures.
    > > >
    > > >
    > > >
    > > > "johnston" <> wrote in message
    > > > news:3f154fcc$0$68854$...
    > > > > We have over 100 asp pages that reference a table
    > > > (distribution_components).
    > > > > We added a new column and primary key to that table, so now we have

    to
    > > > modify
    > > > > the where clause to include that column (we have to make the new

    > column
    > > =
    > > > to a
    > > > > session variable in the page)
    > > > >
    > > > > The table appears 531 times in our code. Is there some util or

    > method
    > > > that
    > > > > allows us to change it globally, other than going to each

    occurrance,
    > > and
    > > > > adding the new column to the where clause
    > > >
    > > >

    > >
    > >

    >
    >
     
    jason kennedy, Jul 16, 2003
    #7
  8. johnston

    Jeff Cochran Guest

    On 16 Jul 2003 08:17:11 -0500, (johnston) wrote:

    >We have over 100 asp pages that reference a table (distribution_components).
    >We added a new column and primary key to that table, so now we have to modify
    >the where clause to include that column (we have to make the new column = to a
    >session variable in the page)
    >
    >The table appears 531 times in our code. Is there some util or method that
    >allows us to change it globally, other than going to each occurrance, and
    >adding the new column to the where clause


    Global search and replace in your favorite code editor...

    Jeff
    ===================================
    Jeff Cochran (IIS MVP)
    - Munged of Course

    I don't get much time to respond to direct email,
    so posts here will have a better chance of getting
    an answer. Besides, everyone benefits here.

    Suggested resources:
    http://www.iisfaq.com/
    http://www.iisanswers.com/
    http://www.iistoolshed.com/
    http://securityadmin.info/
    http://www.aspfaq.com/
    http://support.microsoft.com/
    ====================================
     
    Jeff Cochran, Jul 16, 2003
    #8
  9. johnston

    johnston Guest

    In article <u9ep$>, "Ray at <%=sLocation%>" <> wrote:
    >Or at least use a function to return an array of your data every place you
    >need it 531 times. Good lord. I don't mean this to sound rude johnston,
    >really, but let this be a lesson to you.
    >
    >Ray at work
    >
    >"TomB" <> wrote in message
    >news:...
    >> I disagree.
    >>
    >> A good time to switch to using stored procedures is before you've written
    >> 531 sql statements in your code.
    >>
    >>
    >> "Aaron Bertrand - MVP" <> wrote in message
    >> news:...
    >> > This might be a good time to switch to using stored procedures.
    >> >
    >> >
    >> >
    >> > "johnston" <> wrote in message
    >> > news:3f154fcc$0$68854$...
    >> > > We have over 100 asp pages that reference a table
    >> > (distribution_components).
    >> > > We added a new column and primary key to that table, so now we have to
    >> > modify
    >> > > the where clause to include that column (we have to make the new

    >column
    >> =
    >> > to a


    i have an oracle background and came to the group that does asp programming.
    I have been telling anybody that will listen that we should not use naked sql
    in our asp pages, it should be done via a stored procedure.

    It is either
    stored procedures are for backend stuff not frontend
    or
    great idea, but this project is too small or deadline is too close, too much
    time to develop them, we will surely use it next project, which of course is
    also too small or deadline is too close



    >> > > session variable in the page)
    >> > >
    >> > > The table appears 531 times in our code. Is there some util or

    >method
    >> > that
    >> > > allows us to change it globally, other than going to each occurrance,

    >> and
    >> > > adding the new column to the where clause
    >> >
    >> >

    >>
    >>

    >
    >
     
    johnston, Jul 16, 2003
    #9
  10. "johnston" <> wrote in message
    news:3f154fcc$0$68854$...
    > We have over 100 asp pages that reference a table

    (distribution_components).
    > We added a new column and primary key to that table, so now we have to

    modify
    > the where clause to include that column (we have to make the new

    column = to a
    > session variable in the page)
    >
    > The table appears 531 times in our code. Is there some util or

    method that
    > allows us to change it globally, other than going to each occurrance,

    and
    > adding the new column to the where clause


    WARNING: The following is only suggested for this particular
    circumstance. As you have heard from others, a stored procedure would
    have been the preferred way to handle this.

    That being said, another option available to you is to the following:

    1. Call the new table distribution_components_new
    2. Create a view/saved query called distribution_table based on the new
    table which reproduces the old table with the appropriate where clause
    applied.

    I don't know what the session variable is but I suspect it's something
    available from the database. Could you provide some more details?
     
    Chris Hohmann, Jul 16, 2003
    #10
  11. johnston

    johnston Guest

    In article <eu9Ive#>, "Chris Hohmann" <hohmannATyahooDOTcom> wrote:
    >
    >That being said, another option available to you is to the following:
    >
    >1. Call the new table distribution_components_new
    >2. Create a view/saved query called distribution_table based on the new
    >table which reproduces the old table with the appropriate where clause
    >applied.
    >
    >I don't know what the session variable is but I suspect it's something
    >available from the database. Could you provide some more details?
    >
    >

    the session variable is from the web page, its not in the database.
     
    johnston, Jul 17, 2003
    #11
  12. "johnston" <> wrote in message
    news:3f168fa3$0$68854$...
    > In article <eu9Ive#>, "Chris Hohmann"

    <hohmannATyahooDOTcom> wrote:
    > >
    > >That being said, another option available to you is to the following:
    > >
    > >1. Call the new table distribution_components_new
    > >2. Create a view/saved query called distribution_table based on the new
    > >table which reproduces the old table with the appropriate where clause
    > >applied.
    > >
    > >I don't know what the session variable is but I suspect it's something
    > >available from the database. Could you provide some more details?
    > >
    > >

    > the session variable is from the web page, its not in the database.

    What is it? When is it created? Can it be stored in the database?
     
    Chris Hohmann, Jul 18, 2003
    #12
    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.

Share This Page