|
OK, I decided to map the objects.
The first problem I have encountered is how to design the table that represents a collection. How can I do that?
|
|
|
|
|
Hi
can you send the object model you try to save ?
for example :
if you try to save an array of Quadrilaterals.. each one has 4 points so you can make something like this :
tblQuad:
ID,name,Fillcolor
tblPoint
ID,X,Y
tblQuadPoint
QuadID,PointID,order
and so on.
|
|
|
|
|
I'll try to explain my application. I'm making an application that
models a restaurant. I manage three types of objects:
- Dish: the basic object that has an id, name, description and price. It
represents all available dishes in the restaurant.
- DishCollection: is a collection of Dish objects that represents the dishes
from a single order (all dishes from all customers in a table)
- Order: this object has simple types - date, table, totalPrice- and a
DishCollection. This one represent an order, with the details of number of
table, date, the total price and the dishes that the customer have asked for
(the DishCollection)
I want to store the Order objects in a data base and later make some queries
to know the money collected, the most popular dish,... So I have to create
the tables.
Can you help me with the tables design?
|
|
|
|
|
Try this for a starting point:
CREATE TABLE [dbo].[dish] (
[dish_id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (50) NOT NULL ,
[description] [nvarchar] (256) NULL ,
[price] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[order] (
[order_id] [int] IDENTITY (1, 1) NOT NULL ,
[order_date] [datetime] NOT NULL ,
[table_number] [tinyint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[order_dish_collection] (
[order_id] [int] NOT NULL ,
[dish_id] [int] NOT NULL ,
[quantity] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[dish] ADD
CONSTRAINT [DF_dish_price] DEFAULT (0) FOR [price],
CONSTRAINT [PK_dish] PRIMARY KEY CLUSTERED
(
[dish_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[order] ADD
CONSTRAINT [DF_order_order_date] DEFAULT (getdate()) FOR [order_date],
CONSTRAINT [DF_order_table_number] DEFAULT (0) FOR [table_number],
CONSTRAINT [PK_order] PRIMARY KEY CLUSTERED
(
[order_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[order_dish_collection] ADD
CONSTRAINT [DF_order_dish_collection_quantity] DEFAULT (1) FOR [quantity],
CONSTRAINT [PK_order_dish_collection] PRIMARY KEY CLUSTERED
(
[order_id],
[dish_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[order_dish_collection] ADD
CONSTRAINT [FK_order_dish_collection_dish] FOREIGN KEY
(
[dish_id]
) REFERENCES [dbo].[dish] (
[dish_id]
),
CONSTRAINT [FK_order_dish_collection_order] FOREIGN KEY
(
[order_id]
) REFERENCES [dbo].[order] (
[order_id]
)
GO
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
I want to perform a simple SQL SELECT query, such as the following:
SELECT *
FROM MyTable
WHERE SomeString IN ("red", "green", "blue")
ie. SomeString is a simple string that needs to fall within a range of values (in this example, "red", "green", and "blue). The problem is, the number of values in this range is determined at runtime, so I need to be able to dynamically add parameters. Is it possible to implement this in ADO.NET with data adapters? Because I really like using my typed dataset, but I'm stuck with how to fit this part in.
|
|
|
|
|
See if you can use command builder object to determine how many parameters the procedure accepts. or you may pass delimited text using which individual values can be seperated inside your SP.
Bhaskara
|
|
|
|
|
Just to clarify, I am using Access 2000 (no stored procedures).
|
|
|
|
|
Good day.
I create a product form that contains some textbox and combobox for the user to enter the data into a Product table in the SQL server database. The selection items in the combobox is SupplierName. When the user press add button to add the data to the Product table in SQL server. I want to store the SupplierNo instead of SupplierName into the Product Table in the SQL server. How do I retrieve the SupplierNo from the Supplier table by the selected item in the combobox and store it in the Product table?
Many thanks and best regards,
viv
|
|
|
|
|
Hi
SupplierName
For displaying purpose only You are using means you can bu that in between combo box and put value as SupplierNo. On click of add button if get the value of combo box by select1.value code, then it will automatically gives the SupplierNo..Send this value to SQL Query as variable
Happy Coding!!!
|
|
|
|
|
Hi,
I'm having a problem with an sql select statement from some c# code. The function is meant to count the number of entries in a table where the users dates of birth are between two dates. The two birthday ranges are taken from two integers userAgeFrom and userAgeTo. When I run the code I get the following error:
"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."
The weird thing is if I manually populate the strings userDOBFrom and userDOBTo with dates (eg userDOBTo = "12/09/1971") the select statement works and returns the number of users. When I try to populate the strings from a DateTime object, as below, the function fails.
Any help would be much appreciated!
Kevin
Here is the code:
<br />
int userAgeFrom = 18; <br />
int userAgeTo = 30; <br />
int numUsers; <br />
string selectStatement; <br />
string userDOBFrom; <br />
string userDOBTo; <br />
<br />
DateTime dtUserDOBFrom = DateTime.Now; <br />
DateTime dtUserDOBTo = DateTime.Now; <br />
<br />
userDOBFrom = dtUserDOBFrom.AddYears(-userAgeFrom).ToShortDateString(); <br />
<br />
userDOBTo = dtUserDOBFrom.AddYears(-userAgeTo).Date.ToShortDateString(); <br />
<br />
selectStatement = "select count(*) from USERS where date_of_birth between '" + userDOBFrom + "' and '"+ userDOBTo + "'"; <br />
<br />
sqlDBConnection.Open(); <br />
<br />
sqlDataAdapter.SelectCommand.CommandText = selectStatement; <br />
<br />
numUsers = (int)sqlDataAdapter.SelectCommand.ExecuteScalar(); <br />
<br />
sqlDBConnection.Close(); <br />
<br />
|
|
|
|
|
Hmm, better would be to use command parameters.
Otherwise though, I see nothing wrong with your code, other than the misplaced ".Date." in userDOBTo = dtUserDOBFrom.AddYears(-userAgeTo).Date.ToShortDateString();
|
|
|
|
|
Hi, thanks for the tip, I've tried it with and without the extra .Date and get the same error message.
Can you elaborate on the command parameters?
Cheers!
|
|
|
|
|
Sorry, don't have time for a full explanation. Its in the Help.
Basic idea is that instead of quoting the dates, you would put a placeholder there instead, like "... BETWEEN @P1 and @P2". Then you can create and add parameters to your command. When you do it this way, it has the benefit of strong-typing your parameters, which avoids nasty surprises like the one you are getting, as well as sneaky SQL-Injection attacks.
It sounds more complex than it is...
|
|
|
|
|
Try:
userDOBFrom.ToShortDateString()
userDOBTo.ToShortDateString()
when you build the SQL statement. The default ToString() method may generate the wrong syntax.
Normally I would insert:
MessageBox.Show(selectStatement);
To see what is going on in this case.
|
|
|
|
|
This might be a long shot, but we've had problems like this with different regional settings. The ToShortDateString() "uses formatting information derived from the current culture."
Another thing to try (even longer shot), is to make sure that the collation on your server is expecting your dates in the format that ToShortDateString() is returning.
|
|
|
|
|
I agree with the general consensus. Most likely ToShortDateString() is returning a string representation of a date that SQL Server can't parse.
I would use the formatted ToString() instead:
userDOBFrom = dtUserDOBFrom.AddYears(-userAgeFrom).ToString("yyyy-MM-dd") . This should put it in an ISO standard format that SQL Server should understand, regardless of culture/regional settings.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
I hope you can help me... I store an elapsed time value as a string (varchar) (mm:ss) in my SQL database. When I query the data from Excel, how can I calculate an average from this data? Would it help if I made the SQL datatype some kind of time value?
Thanks in advance for any advice you may provide.
'til next we type...
HAVE FUN!! -- Jesse
|
|
|
|
|
Jesse Evans wrote:
When I query the data from Excel, how can I calculate an average from this data?
Why are you storing it as a string? I would store it as an integer and if you needed in the format mm:ss a simple calculated field could be created for that function.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
My Blog
|
|
|
|
|
check this
avg(cast(field as int))
|
|
|
|
|
Hi. I can't find the right category for my question so I decided to just post this here.
- Is it possible to use MS Access as a front-end to an Access database (as back-end) that is deployed on the Web Server? My initial reading on this topic seems to imply that I can have MDB as a back end to my front-end application (although this is not highly-recommended for huge applications). However, I need some straight answers to my questions so I posted on this forum.
To access an MDB file on the Web Server, I was told that the front-end application must use a middleware (such as ASP / ASP.NET). And also a capability to send HTTP requests. Does Access has this capability?
I really hope someone could help me with my questions. Thanks very much..
|
|
|
|
|
winpiglet wrote:
To access an MDB file on the Web Server, I was told that the front-end application must use a middleware (such as ASP / ASP.NET). And also a capability to send HTTP requests. Does Access has this capability?
this is true..you need ASP or ASP.NET
And access XP/2003 can help you export reports and forms to ASP.(try right click-export)
|
|
|
|
|
There seems to be a lot of headaches that become apparent on this message board when it comes to topics such as "enforcing database constraints" or "enforcing complex business rules in the database."
I work for a company called Dataphor with a new development tool that not only automates the application development process, but also allows for a declarative approach to enforcement of complex business rules, constraint enforcement, etc.
The product I am speaking of is Dataphor - an automated application environment. By combining the power of a businews rules engine, the automation of a meta-data repository, and the flexibility of a RAD environment, Dataphor provides an unprecendented level of prductivity and maintainability.
Dataphor is a product of Alphora. More information may be found at alphora.com.
-Brady
|
|
|
|
|
On my website I have 3 forms where people enter their contact details (name, email, address, phone and country) and some additional fields which are different for each form. Form 1 requires all the details to be entered, form 2 requires only name, email and country and form 3 requires name, email, address and country.
I initially designed my database with seperate tables for each of the forms since they require different contact details however was wondering whether removing the contact details to a seperate table and leaving some of the columns null would be better.
Can anyone advise me on this?
Thanks
SuzyB
If I had a better memory I would remember more.
|
|
|
|
|
If the common fields will have same data on all the forms, I would create one table for contact details which are nullable, and other individual data into seperate tables and enforce the constriants through application.
Bhaskara
|
|
|
|
|
Hi,
I want to know the primary keys of a given table. I s there any query or procedure which gives the primary keys of a given table in MS SQL.
Karteek
|
|
|
|