471,355 Members | 1,658 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,355 software developers and data experts.

Adding columns to a database table programmatically...

Hi,

I've been tasked to write a windows app that allows people to enter
transactions. For each transaction, there can be an unknown number of items,
and as a person enters an item, the program asks if there are more items. If
yes, a new set of input boxes are created dynamically. What I was wondering
is this - not knowing how many items are going to be entered, is there a way
that I can change the layout of a database table depending on the number of
items entered? After the initial creation of the database, with a set number
of item columns, is there a way for me, (ie. the program), to alter/change
the database layout?

Any help is deeply appreciated...

CDB
Sep 7 '07 #1
5 3288
On Sep 7, 3:22 pm, cdbiggs <cdbi...@discussions.microsoft.comwrote:
Hi,

I've been tasked to write a windows app that allows people to enter
transactions. For each transaction, there can be an unknown number of items,
and as a person enters an item, the program asks if there are more items. If
yes, a new set of input boxes are created dynamically. What I was wondering
is this - not knowing how many items are going to be entered, is there a way
that I can change the layout of a database table depending on the number of
items entered? After the initial creation of the database, with a set number
of item columns, is there a way for me, (ie. the program), to alter/change
the database layout?

Any help is deeply appreciated...

CDB
Nicholas made a good comment, but if you still want to programmically
alter a table's definition, it depends on the dabase providor. If
Microsoft SQL Server, all you need to do is issue an ALTER TABLE SQL
command.

Sep 7 '07 #2
za***@construction-imaging.com wrote:
On Sep 7, 3:22 pm, cdbiggs <cdbi...@discussions.microsoft.comwrote:
>Hi,

I've been tasked to write a windows app that allows people to enter
transactions. For each transaction, there can be an unknown number of items,
and as a person enters an item, the program asks if there are more items. If
yes, a new set of input boxes are created dynamically. What I was wondering
is this - not knowing how many items are going to be entered, is there a way
that I can change the layout of a database table depending on the number of
items entered? After the initial creation of the database, with a set number
of item columns, is there a way for me, (ie. the program), to alter/change
the database layout?

Any help is deeply appreciated...

CDB

Nicholas made a good comment, but if you still want to programmically
alter a table's definition, it depends on the dabase providor. If
Microsoft SQL Server, all you need to do is issue an ALTER TABLE SQL
command.
That *would* be the way to do it, but frankly it would be a disastrous
way to do things from a DBA side of things.. Really..

Basic table with ID column, client/transaction column and item column.
The ID is invisible, and you can get any/all items per transaction nice
and simply.. It also allows for basic querying based on transaction *or*
item, if you're thinking long term.

but yah.. having a table that grows/shrinks columns based on arbitrary
item count is going to make your DBA cry.

P.
Sep 7 '07 #3
CDB,

Here are some other options to consider so (like Nicholas said) you can keep
the DBA happy (maybe :)).

1. You could possibly store all items in a large varchar column as a
delimitted string (i.e. comma, etc.).
Of course this assumes that all potentially entered items would fit.

2. Some databases allow for the creation of multi-valued columns in a table.
I don't believe Sql Server
supports this yet. Multi-valued columns allow for 0-n items in a single
column where n is unknown at
design time. These have actually been around for quite a while in
databases like universe and pick.
Some relational vendors have recently added support for them to their
products (i.e. Informix, DB2).

3. Some databases allow for the creation of XML valued columns. Sql Server
2005 supports this column
type. If you wanted to go this route you can stuff whatever XML you
want in the column.

There are other ways of going about it (like Nicholas fine suggestion of
using one row per item in a
separate related table).

And of course you can always just alter the table by adding more columns.

HTH
--
Gregg Walker

Sep 7 '07 #4
For some reason, I didn't get Nicholas' reply, but, what you wrote really
helps. I don't know why I didn't think of setting up another table to accept
each item in its own row. Also, I like the idea of possibly making the items
comma seperated.

Thank you all for the help. If you could post what Nicholas wrote so I
could see that would be most helpful.

Thanks

CDB

"Gregg Walker" wrote:
CDB,

Here are some other options to consider so (like Nicholas said) you can keep
the DBA happy (maybe :)).

1. You could possibly store all items in a large varchar column as a
delimitted string (i.e. comma, etc.).
Of course this assumes that all potentially entered items would fit.

2. Some databases allow for the creation of multi-valued columns in a table.
I don't believe Sql Server
supports this yet. Multi-valued columns allow for 0-n items in a single
column where n is unknown at
design time. These have actually been around for quite a while in
databases like universe and pick.
Some relational vendors have recently added support for them to their
products (i.e. Informix, DB2).

3. Some databases allow for the creation of XML valued columns. Sql Server
2005 supports this column
type. If you wanted to go this route you can stuff whatever XML you
want in the column.

There are other ways of going about it (like Nicholas fine suggestion of
using one row per item in a
separate related table).

And of course you can always just alter the table by adding more columns.

HTH
--
Gregg Walker

Sep 8 '07 #5
CDB,

Here is the link to my original response:

http://groups.google.com/group/micro...47dd7c7b28fe39
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"cdbiggs" <cd*****@discussions.microsoft.comwrote in message
news:4B**********************************@microsof t.com...
For some reason, I didn't get Nicholas' reply, but, what you wrote really
helps. I don't know why I didn't think of setting up another table to
accept
each item in its own row. Also, I like the idea of possibly making the
items
comma seperated.

Thank you all for the help. If you could post what Nicholas wrote so I
could see that would be most helpful.

Thanks

CDB

"Gregg Walker" wrote:
>CDB,

Here are some other options to consider so (like Nicholas said) you can
keep
the DBA happy (maybe :)).

1. You could possibly store all items in a large varchar column as a
delimitted string (i.e. comma, etc.).
Of course this assumes that all potentially entered items would fit.

2. Some databases allow for the creation of multi-valued columns in a
table.
I don't believe Sql Server
supports this yet. Multi-valued columns allow for 0-n items in a
single
column where n is unknown at
design time. These have actually been around for quite a while in
databases like universe and pick.
Some relational vendors have recently added support for them to their
products (i.e. Informix, DB2).

3. Some databases allow for the creation of XML valued columns. Sql
Server
2005 supports this column
type. If you wanted to go this route you can stuff whatever XML you
want in the column.

There are other ways of going about it (like Nicholas fine suggestion of
using one row per item in a
separate related table).

And of course you can always just alter the table by adding more columns.

HTH
--
Gregg Walker

Sep 8 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Gail Zacharias | last post: by
2 posts views Thread by Viorel | last post: by
3 posts views Thread by Jim Heavey | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.