Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Is there way to do global changes to SQL in ASP pages

Reply
Thread Tools

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

 
 
johnston
Guest
Posts: n/a
 
      07-16-2003
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
 
Reply With Quote
 
 
 
 
Randy R
Guest
Posts: n/a
 
      07-16-2003
> 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.


 
Reply With Quote
 
 
 
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      07-16-2003
This might be a good time to switch to using stored procedures.



"johnston" <(E-Mail Removed)> wrote in message
news:3f154fcc$0$68854$(E-Mail Removed)...
> 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



 
Reply With Quote
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      07-16-2003
http://www.aspfaq.com/2201
http://www.aspfaq.com/2423




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

<(E-Mail Removed)> wrote:
> >This might be a good time to switch to using stored procedures.
> >
> >
> >
> >"johnston" <(E-Mail Removed)> wrote in message
> >news:3f154fcc$0$68854$(E-Mail Removed)...
> >> 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



 
Reply With Quote
 
Ray at
Guest
Posts: n/a
 
      07-16-2003
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > This might be a good time to switch to using stored procedures.
> >
> >
> >
> > "johnston" <(E-Mail Removed)> wrote in message
> > news:3f154fcc$0$68854$(E-Mail Removed)...
> > > 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

> >
> >

>
>



 
Reply With Quote
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      07-16-2003
> 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*.


 
Reply With Quote
 
jason kennedy
Guest
Posts: n/a
 
      07-16-2003
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%>" <(E-Mail Removed)> wrote in message
news:u9ep$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > This might be a good time to switch to using stored procedures.
> > >
> > >
> > >
> > > "johnston" <(E-Mail Removed)> wrote in message
> > > news:3f154fcc$0$68854$(E-Mail Removed)...
> > > > 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
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
Jeff Cochran
Guest
Posts: n/a
 
      07-16-2003
On 16 Jul 2003 08:17:11 -0500, http://www.velocityreviews.com/forums/(E-Mail Removed) (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)
(E-Mail Removed) - 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/
====================================
 
Reply With Quote
 
johnston
Guest
Posts: n/a
 
      07-16-2003
In article <u9ep$(E-Mail Removed)>, "Ray at <%=sLocation%>" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > This might be a good time to switch to using stored procedures.
>> >
>> >
>> >
>> > "johnston" <(E-Mail Removed)> wrote in message
>> > news:3f154fcc$0$68854$(E-Mail Removed)...
>> > > 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
>> >
>> >

>>
>>

>
>

 
Reply With Quote
 
Chris Hohmann
Guest
Posts: n/a
 
      07-16-2003
"johnston" <(E-Mail Removed)> wrote in message
news:3f154fcc$0$68854$(E-Mail Removed)...
> 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?


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
FWSM/PIX and Dynamic PAT using global IP range vs. global interface vs. global IP Hoffa Cisco 1 10-25-2006 06:50 PM
FWSM/PIX and Dynamic PAT using global IP range vs. global interface vs. global IP Hoffa Cisco 0 10-25-2006 01:04 PM
is there a way thru which i can make changes in the security option so that browser accepts activex? zEE ASP .Net 1 07-31-2006 02:40 PM
global filter to redirect asp (old asp!) pages on asp.net site Daves ASP .Net 2 05-31-2006 08:33 AM
Is there way to do global changes to SQL in ASP pages johnston ASP .Net 3 07-17-2003 12:19 PM



Advertisments