Re: Build SQL string looping through field names

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: WC Justice (wcjhome_at_cox-internet.com)
Date: 01/24/05 

Date: Mon, 24 Jan 2005 10:51:54 -0600

Thanks for the response. This is an ASP-based website with a Microsoft 2003
Windows Small Business Server SQL Server back end.

I respect your advice regarding duplicating of records, however it is
necessary in this case. Each contract contains these 40 provisions which
need to be editable at the contract level. When the user indicates that he
wants to create a new contract, one of the subroutines attaches the default
provisions to the new contract, which are then edited as necessary or
appropriate. I started hard coding the SQL string but was hoping to avoid
doing it for both the INSERT and UPDATE statements.

A scan of the links you provided makes me feel that it may be a little over
my head, but if you think it will address my question, I will study it until
I get it.

Thanks again.

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23%23HJrDhAFHA.3336@TK2MSFTNGP11.phx.gbl...
> WC Justice wrote:
> > I need to build an UPDATE statement that copies the values of roughly
> > 40 fields from a table
>
> Oops - you forgot to tell us what type and version of database you are
> using. I Suspect Access, but it would be nice to be sure.
>
> > that stores standard or default values into a
> > table of specific contracts.
>
> Simple answer:
>
> Don't
>
> Don't store the same data twice. You are likely to be using a relational
> database here. By storing te same data twice, you are interfering with the
> ability of the rdbms to preserve data integrity.
>
> Instead of copying all these values to a separate table, store a link to
the
> standard values.
>
> The only reason not to do this is if you need to track historicity, i.e.,
> you need to know what the values were at the time the record was created.
> But even this can be done without copying the data by using EffectiveDate
> and DiscontinueDate columns in the standards table.
>
> > There are 8 or so fields in the source
> > table that are not in the destination table. The remaining field
> > names are identical in both tables. How do I create a recordset of
> > the field names I am interested in so that I can loop through it to
> > build my UPDATE statement?
>
> If you're bound and determined, then you have a few options, which you can
> read about here:http://www.aspfaq.com/show.asp?id=2177, although he did
> leave out the OpenSchema option which you can read about here:
> http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthopenschema.asp
>
>
> Bob Barrows
>
> --
> 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"
>
>

Relevant Pages

  • Re: Haulage company liability
    ... Standard Disclaimer - IANAL! ... the contract has whatever terms were agreed at the time ... a price and you gave them an instruction to collect the goods for transport. ... small claims court. ...
    (uk.transport)
  • Corruption probe over Tube deals
    ... Detectives are investigating allegations of corruption in collapsed Tube firm Metronet, the Standard reveals today. ... Police have been called in after a six-figure contract for complex electrical work was given to a firm that specialises in unblocking drains. ... Metronet project manager, Ed Maloney, set up a company with Lanes' director Bruce Crompton, shortly before the Oxford Circus contract was awarded. ...
    (uk.transport.london)
  • Re: Newbie question about formula based values
    ... So I prefer the separate field approach. ... I have 2 levels of contract for customers: Standard and Premium. ...
    (comp.databases.ms-access)
  • Re: I NEED INFORMATION ABOUT ADT AND FEDERAL REGULATIONS
    ... somekind of damages would have had to have had happened....so far it ... looks like the op is just lookin' for a way out of the contract. ... | your door about it it is a standard. ... | case or if a fire or burg system fails to properly sieze a phone line ...
    (alt.security.alarms)
  • Re: Converting CString to Integer
    ... > Can you find the bug in your function? ... > standard functions like strtol when possible. ... no contract, don't we? ...
    (microsoft.public.vc.mfc)