|
Hello,
I have two tables in a DataSet (Customers and Orders) and they are displayed in a DataGrid. A relation is set up between the two tables.
Now, I have set up all of the necessary commands to update the tables. The user edits the data grid however he/she wishes and then hits a button to update. In the button click code I have
<br />
daCust.Update(ds,"Customers");<br />
daOrders.Update(ds,"Orders");<br />
If the user inserts a new Customer and some new Orders for that customer and then hits update, everything is fine. However, if he/she then tries to delete that new customer, I get a DELETE statement conflicted with COLUMN REFERENCE constraint error because it deletes the Customer and then tries to delete the Orders which reference that customer.
Can anyone suggest a simple solution where I can display two tables in a data grid and have the user edit them in any possible way or order and be able to update the database without getting a constraint error? The way I have it done it doesnt work unless you follow a certain order.
Thanx for the help,
-Flack
|
|
|
|
|
hi all
im not sure if you can do this or not, but i want to pass into a stored procedure
the name of the table as a string and do a query on that
i tried doing it below, but that way doesnt work
any ideas ?????
si
declare @table_name varchar(20)
set @table_name = 'table1'
select *
from @table_name
|
|
|
|
|
try exec ('select * from ' + @table_name)
It will be slower than just executing the SQL directly, and it bypasses all of the advantages of stored procedures (security, strongly typed parameters).
|
|
|
|
|
You can certainly do that.
But, instead of using "SELECT" as a T-SQL, construct it dynamically. Here is how that can be done within your SP.
DELCARE @sql_stmt varchar2(100)
SET @sql_stmt = "SELECT * FROM " + @table_name
EXEC ( @sql_stmt )
Bhaskara
|
|
|
|
|
I want to know the exact difference between a data provider(e.g. oledb provider) and driver(e.g. odbc driver). Is there any difference between them??
How r they related??
|
|
|
|
|
ODBC is Open Data Base Connectivity, which is a connection method to data sources and other things. It requires that you set up a data source, or what's called a DSN using an SQL driver or other driver if connecting to other database types. Most database systems support ODBC.
OLE is Object Linking and Embedding. OLEDB is partly distinguished from OLE itself, now called "automation".
OLEDB is the successor to ODBC, a set of software components that allow a "front end" such as GUI based on VB, C++, Access or whatever to connect with a back end such as SQL Server, Oracle, DB2, mySQL etal.
OLEDB is a different type of data provider that came about with MS's Universal Data Access in 1996 and does not require that you set up a DSN. It is commonly used when building VB apps and is closely tied to ADO. It works with COM, and DCOM as of SQL 7.0.
|
|
|
|
|
OLEDB is a specification for a driver, as is ODBC. So you have OLEDB drivers and ODBC drivers. There are also native drivers, which each have their own specification.
Providers are merely .NET interfaces to the drivers. If you did not have providers, then you would have to code directly against the OLEDB or ODBC api yourself, or use the old (non .NET) ADO.
Since OLEDB is more recent than ODBC, it is more full featured, and often faster. Given a choice, you should use it. Try and stay away from native drivers unless you want to be tied to a particular database. Performance gains from native drivers are usually marginal.
|
|
|
|
|
Hi,
pls help me to find a way.
1.i am able to store a jpg file in SQL server using C#
2.when displaying that image ,that data in binary format.pls help me o display
note : i am using this code in webparts to display in sharepoint.
pls give me the procedure to diaply image data from SQL server to Webpart or browser.
thx
sree
|
|
|
|
|
|
Hello. I need to store custom objects in a SQL Server 2000 table and then having them available to make some queries. I don't need to convert them into objects again. Objects are written in Visual C#
What is the easieset way to achieve it? Do I have to write a method to map each attribute into a table field?
Regards,
Diego F.
|
|
|
|
|
Diego F. wrote:
Do I have to write a method to map each attribute into a table field?
this is one way.
also you can persist it as XML and save it as string.
or wait till Yukon (MS SQL 2005) comes .. It can store .NET objects as any data type
|
|
|
|
|
If I decide to use XML, can then later make SQL queries directly to the data base? I don't need to convert the fields back to objets anymore.
What do you think is easier: mapping or using XML?
I can't wait to Yukon
|
|
|
|
|
Diego F. wrote:
What do you think is easier: mapping or using XML?
depends on the object model you have ..
if simple (array of objects that hold value members) it's better to store them as fields not XML.
If you store them as XML..you'll have to make some parsing after retrieving them from DB (which is bad IMO)
|
|
|
|
|
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!
|
|
|
|