Database display/manipulation

I have a web page called homepage.aspx created in Visual Studio 2005 using VB. It contains some hyperlinks on the left side. I have a database created in SQL Server 2005. I need to add, delete, edit, update data in the database directly from this same page. How can I achieve this?. i.e is it possible to, say, have a form on a section of this same web page that would allow me to directly manipulate/modify and update the database? (If you may give code, you may use abstract names for the database, tables and column and/or field names-I will understand). Many thanks in advance.

Yes, what you want to do is possible - and is one of the main points behind ASP.NET. You need to read some of the many free tutorials that are available. It's not the sort of thing anyone can describe in useful detail in a reply to a post in this forum. Here are a couple:

http://msconline.maconstate.edu/tutorials/ASPNET20/default.htm
http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/default.aspx

|||

The links above are useful in this regard. The question I have is a bit different. I have arleady written the code to manipulate the database using GridView control. This ofcourse works in conjunction with the SqlDataSource control. The SqlDataSource control permits selecting a single database table at a time for actions of delete, add, edit etc. This means that if I need to do the above actions on many tables, I will need to reconfigure the SqlDataSource for each table. This also means that I will be able to delete, add, update data in all tables by using a number of web pages as each each table manipulation will have to be done via a seperate web page. This is ok but is very usable. I want to put,say, a form on just one web page and then this form will allow me to perform actions of add, delete, update etc on all tables using just this page. In brief: How can I fit a form on the existing web page and use this form to add, delete, udate the entire database data?.

|||The SqlDataSource will take any valid SQL or the name of a stored procedure for its CommandText property. You can assign this value at runtime in the code-behind. Most applications of the type you are describing tend to not show one "table" at a time - they show data from related tables using joins. Each data maintenance task usually has its own page/form, so adding/editing/deleting a story, for example would all be managed from one page, whereas adding/editing/deleting a contact would be done on a different one. You could do it all in one page, but I would never do so. Maintenance of code would be a mightmare.

Database design question

Hi there

I'm in the process of creating an order database that contains tables for products and an order table that contains information for orders. Should the order table have a xref to the products table to show what products are associated or should there be a separate table to show what products are associated for the order (ie a orderProduct table) that would also contain pricing. The reason for my question is for tracking order history. If the price of a product changes and the order table is associated with the product id then old orders will have the new pricing which is incorrect. Would it be better to when an order is place copy the values of price and the product info to an other table?

Thanks

Good thinking. Separate table. OrderProducts. OrderID FK, ProductID FK, OrderPrice. OrderPrice is the price at the time of the order.

Incidentally, can anyone delete a product if your customer discontinues it? If so, you might want to think about a YesNo/bit column to flag a discontinued product for suppression rather than wiping it from the database.

|||

I would recommend that you do that, for exactly the reason you state. I have worked with MS Commerce Server 2000 and it had a similar database design. Here is a simplified example from my vague memory:

Order
OrderID (primary key)
OrderNumber (human readable order number)
CustomerID (foreign key to customer)
Date
SubTotal
Tax
Shipping
GrandTotal

Product
ProductID (primary key)
Active
SKU
Name
Description
Price

OrderItem
SKU
Name
Price
Quantity
Total

The idea is that any field that could change throughout the lifetime of a product should be stored with the order in order to record exactly what they ordered. You may even think about moving the Price for Products out into a ProductPrice table like the following.

Product
ProductID
SKU
Name
Description

ProductPrice
ProductID (foreign key to product)
EffectiveDate (date this price becomes effective)
Price

This design allows you to change prices ahead of time and specify when they become effective. To get the current price you would select the ProductPrice with the highest EffectiveDate that is greater than or equal to today's date. This may be totally unnecessary though, it's just an idea.

Mark

|||thanks for the replies guys that does help clarify things. I'm currently having the same type of issues with a room booking database design. I want to have tenants book certain rooms for specified time intervals. The tenants can book rooms (rooms are stored in a table) and select things like room setup (configurations are stored in a table). All the tables have an active flag on then. I also have a bookings table but unsure as to how to store the rooms and configurations that are selected. Should i be using ids in the booking table? What happens if the rooms become inactive down the road and the client was to view past bookings? Do i leave out the check on the active flags and show the rooms anyway? I believe that i need the room ids in order to validate new bookings against what is already booked. If the admins want to edit bookings and say a room or configuration that was previously active is now inactive would i force the admin to select a new configuration? tables such as room and room configuration do not have an interface to change values or inactivate them, i'm just wondering for the future if they say they want to add a room and remove a room then past bookings will get affected.

Database design problem

We have to design a database for UserInfo. We already have a user
table, and contains userid. There are three user type(ex. student,
teacher,admin), they have their own tables. i don't know how to add the
personal infomation field. One way is putting some common field into
the user table, the other is laying the infomation of subtype into
their own tables.Which is better?Is personal info the same for all types? is it present in all types? If yes,
feel free to add it to the user table.
MC
"Readon Shaw" <xydarcher@.163.com> wrote in message
news:1132886720.966817.144210@.o13g2000cwo.googlegroups.com...
> We have to design a database for UserInfo. We already have a user
> table, and contains userid. There are three user type(ex. student,
> teacher,admin), they have their own tables. i don't know how to add the
> personal infomation field. One way is putting some common field into
> the user table, the other is laying the infomation of subtype into
> their own tables.Which is better?
>|||yes, but what is the advantages of that?
MC wrote:
> Is personal info the same for all types? is it present in all types? If ye
s,
> feel free to add it to the user table.
> MC
> "Readon Shaw" <xydarcher@.163.com> wrote in message
> news:1132886720.966817.144210@.o13g2000cwo.googlegroups.com...|||Readon Shaw wrote:
> yes, but what is the advantages of that?
The same advantage as that of having a user table at all - to help you
to apply constraints against it and therefore ensure the integrity of
your data. For example, if you have a name or email address that's
common to all types of user you'll perhaps want to add a constraint
that the address or name be unique across all users. That's much easier
to do if they are all in one table.
In general, the fewer places in which one attribute can appear, the
fewer the opportunities there are for anomalies and incorrect data.
David Portas
SQL Server MVP
--|||Phew, thanks David, I was trying to come up with a short answer :).
In addition let me point out that the entity in this design is User, and
that 'additional' tables might be called 'derivations'. Since the
information you're adding to design is based on entity and not on
derivation, you should apply it there. And only there.
Am I making sense here?
MC
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1132916473.316709.85460@.f14g2000cwb.googlegroups.com...
> Readon Shaw wrote:
> The same advantage as that of having a user table at all - to help you
> to apply constraints against it and therefore ensure the integrity of
> your data. For example, if you have a name or email address that's
> common to all types of user you'll perhaps want to add a constraint
> that the address or name be unique across all users. That's much easier
> to do if they are all in one table.
> In general, the fewer places in which one attribute can appear, the
> fewer the opportunities there are for anomalies and incorrect data.
> --
> David Portas
> SQL Server MVP
> --
>|||MC wrote:
> Phew, thanks David, I was trying to come up with a short answer :).
> In addition let me point out that the entity in this design is User, and
> that 'additional' tables might be called 'derivations'. Since the
> information you're adding to design is based on entity and not on
> derivation, you should apply it there. And only there.
> Am I making sense here?
>
> MC
>
Intuitively that makes sense, yes. More formally, this problem is the
subject of a design rule that Chris Date calls the Principle of
Orthogonal Design, which is used to prevent certain types of redundancy
and anomaly. Roughly this says that integrity constraints should
prevent data being recorded in more than one table if there is some
non-loss decomposition of the same data into fewer tables. That doesn't
absolutely preclude the user name (for example) appearing in both the
Teacher and Student tables but it does require that there should be
some constraint to prevent the same person appearing in both. For
practical reasons it is therefore more efficient to implement one
column and one constraint in one table rather than some more complex
constraints across several tables.
See the following article for more discussion of this.
http://www.dbdebunk.com/page/page/622331.htm
David Portas
SQL Server MVP
--|||Just to be certain, i didnt really try to explain the logic to you David,
but to the original poster :).
MC
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1132923609.030176.20790@.z14g2000cwz.googlegroups.com...
> MC wrote:
> Intuitively that makes sense, yes. More formally, this problem is the
> subject of a design rule that Chris Date calls the Principle of
> Orthogonal Design, which is used to prevent certain types of redundancy
> and anomaly. Roughly this says that integrity constraints should
> prevent data being recorded in more than one table if there is some
> non-loss decomposition of the same data into fewer tables. That doesn't
> absolutely preclude the user name (for example) appearing in both the
> Teacher and Student tables but it does require that there should be
> some constraint to prevent the same person appearing in both. For
> practical reasons it is therefore more efficient to implement one
> column and one constraint in one table rather than some more complex
> constraints across several tables.
> See the following article for more discussion of this.
> http://www.dbdebunk.com/page/page/622331.htm
> --
> David Portas
> SQL Server MVP
> --
>