how to SELECT * except for one field?

Hello,

I want to SELECT * but omit one field (the ID field, the first one)
from the recordset. Any way of doing this? I want to do this because
I have several tables with different fields and I want to use the same
code to list the contents of each, so I don't want to specifically
select column names.

-Mike

Re: how to SELECT * except for one field?

mike wrote:
> Hello,
>
> I want to SELECT * but omit one field (the ID field, the first one)
> from the recordset. Any way of doing this? I want to do this because
> I have several tables with different fields and I want to use the same
> code to list the contents of each, so I don't want to specifically
> select column names.
>
You have to list the column names, which is good programming technique
regardless:
http://www.aspfaq.com/show.asp?id=2096

--
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.

Re: how to SELECT * except for one field?

Hi Bob,

I have many tables, each with different fields. I have one script that
displays all of them. I really don't want to have multiple scripts or
logic. Is there any way of excluding a column for which you know the
name for, and is shared among all the tables?


Bob Barrows [MVP] wrote:
> mike wrote:
> > Hello,
> >
> > I want to SELECT * but omit one field (the ID field, the first one)
> > from the recordset. Any way of doing this? I want to do this because
> > I have several tables with different fields and I want to use the same
> > code to list the contents of each, so I don't want to specifically
> > select column names.
> >
> You have to list the column names, which is good programming technique
> regardless:
> http://www.aspfaq.com/show.asp?id=2096
>
> --
> 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.

Re: how to SELECT * except for one field?

Not without using selstar which as Bob's mentioned, is not a good idea.

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"mike" wrote in message
news:1152722975.022708.172780@p79g2000cwp.googlegroups.com.. .
> Hi Bob,
>
> I have many tables, each with different fields. I have one script that
> displays all of them. I really don't want to have multiple scripts or
> logic. Is there any way of excluding a column for which you know the
> name for, and is shared among all the tables?
>
>
> Bob Barrows [MVP] wrote:
> > mike wrote:
> > > Hello,
> > >
> > > I want to SELECT * but omit one field (the ID field, the first one)
> > > from the recordset. Any way of doing this? I want to do this because
> > > I have several tables with different fields and I want to use the same
> > > code to list the contents of each, so I don't want to specifically
> > > select column names.
> > >
> > You have to list the column names, which is good programming technique
> > regardless:
> > http://www.aspfaq.com/show.asp?id=2096
> >
> > --
> > 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.
>

Re: how to SELECT * except for one field?

Even when using selstar, you cannot select all but one field ...

Steven Burn wrote:
> Not without using selstar which as Bob's mentioned, is not a good
> idea.
>
> --
> Regards
>
> Steven Burn
> Ur I.T. Mate Group
> www.it-mate.co.uk
>
> Keeping it FREE!
>
> "mike" wrote in message
> news:1152722975.022708.172780@p79g2000cwp.googlegroups.com.. .
>> Hi Bob,
>>
>> I have many tables, each with different fields. I have one script
>> that displays all of them. I really don't want to have multiple
>> scripts or logic. Is there any way of excluding a column for which
>> you know the name for, and is shared among all the tables?
>>
>>
>> Bob Barrows [MVP] wrote:
>>> mike wrote:
>>>> Hello,
>>>>
>>>> I want to SELECT * but omit one field (the ID field, the first one)
>>>> from the recordset. Any way of doing this? I want to do this
>>>> because I have several tables with different fields and I want to
>>>> use the same code to list the contents of each, so I don't want to
>>>> specifically select column names.
>>>>
>>> You have to list the column names, which is good programming
>>> technique regardless:
>>> http://www.aspfaq.com/show.asp?id=2096
>>>
>>> --
>>> 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.

--
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.

Re: how to SELECT * except for one field?

"mike" wrote in message
news:1152717596.054663.160280@i42g2000cwa.googlegroups.com.. .
> Hello,
>
> I want to SELECT * but omit one field (the ID field, the first one)
> from the recordset. Any way of doing this? I want to do this because
> I have several tables with different fields and I want to use the same
> code to list the contents of each, so I don't want to specifically
> select column names.

Why don't you change your code so that it doesn't display the first field?

Or, you could use the ADOX.Catalog object, like this...

function OmitOneField(cat, tablename, thisfield)
dim c, s, t

t = lcase(thisfield)
for each c in cat.Tables(tablename).columns
if lcase(c.name) <> t then
s = s & ", " & c.name
end if
next
if len(s) > 0 then s = "select " & mid(s, 2) & " from " & tablename
OmitOneField = s
end function

set cat = server.createobject("adox.catalog")
set conn = server.createobject("adodb.connection")
conn.Open "whatever"
set cat.ActiveConnection = conn

set rst = conn.execute(OmitOneField(cat, "theTable", "theID"))

etc.

OR, you could use a recordset, like this...

set rst = conn.Execute("select * from " & tablename & " where " & thisfield
& " = 0")
for c = 1 to rst.Fields.count - 1
s = s & ", " & rst(c).name
next
rst.close
if len(s) > 0 then set rst = conn.execute("select " & mid(s, 2) & " from " &
tablename)

OR, in sqlserver it appears that you can get the field names like this...

select syscolumns.name
from syscolumns inner join
sysobjects on syscolumns.id = sysobjects.id
where (sysobjects.name = 'tablename') and (syscolumns.colorder > 1)
order by syscolumns.colorder

and loop through them as above. No doubt your database has something
similar.

--
roger

Re: how to SELECT * except for one field?

I know, but it would effectively allow the OP to use specific's without
specifying each one individually (tis what I meant )

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"Bob Barrows [MVP]" wrote in message
news:%239XVypdpGHA.4760@TK2MSFTNGP05.phx.gbl...
> Even when using selstar, you cannot select all but one field ...
>
> Steven Burn wrote:
> > Not without using selstar which as Bob's mentioned, is not a good
> > idea.
> >
> > --
> > Regards
> >
> > Steven Burn
> > Ur I.T. Mate Group
> > www.it-mate.co.uk
> >
> > Keeping it FREE!
> >
> > "mike" wrote in message
> > news:1152722975.022708.172780@p79g2000cwp.googlegroups.com.. .
> >> Hi Bob,
> >>
> >> I have many tables, each with different fields. I have one script
> >> that displays all of them. I really don't want to have multiple
> >> scripts or logic. Is there any way of excluding a column for which
> >> you know the name for, and is shared among all the tables?
> >>
> >>
> >> Bob Barrows [MVP] wrote:
> >>> mike wrote:
> >>>> Hello,
> >>>>
> >>>> I want to SELECT * but omit one field (the ID field, the first one)
> >>>> from the recordset. Any way of doing this? I want to do this
> >>>> because I have several tables with different fields and I want to
> >>>> use the same code to list the contents of each, so I don't want to
> >>>> specifically select column names.
> >>>>
> >>> You have to list the column names, which is good programming
> >>> technique regardless:
> >>> http://www.aspfaq.com/show.asp?id=2096
> >>>
> >>> --
> >>> 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.
>
> --
> 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.
>
>