|
You are going to have a lot of problems getting support for vb6 controls, it is a dead language.
You would be better served by scrapping the entire application and rewriting in a current language where you can get support.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
I am using Microsoft SQL server 2008. I have 2 SQL tables Table 'Product1' and Table 'Product2'. In 'Product1' Table I have columns named
1.SLNO,
2.Name,
3.Place
4.State
And 'Product2' have columns named,
1.SLNO,
2.Name,
3.Place
4.State
5.regno
here 'regno' if FK to 'SLNO' (of product1 table ).
My requirement is to update the columns in 'Product2' table when I modify data in 'Product1' table ( 'name' 'Place' and 'state' column).
Is it possible to update 'Product2' table without running individual queries for each rows ?
If yes how to do this ?
Please help.
|
|
|
|
|
Since the columns in Product2 always have to match the columns in Product1, wouldn't it make more sense to remove them from the Product2 table? Otherwise, you're just duplicating data for no reason.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
yes you are right. But for time being I cannot do that. However I got a one method which partially works. Here it goes..
So I have added 'Unique constraint' for 3 columns (Name, Place and State) in table1 table using,
ALTER TABLE table1 ADD
UNIQUE(column)
and created foreign key in table2 table using,
ALTER TABLE table2 ADD
FOREIGN KEY (column) REFERENCES table1(column);
While adding 'ON DELETE CASCADE' to table2 I am getting error, that
"There are no primary or candidate keys in the referenced table"
Is it possible to set ''ON DELETE CASCADE" without adding primary key in the parent table ?
Also how to add "ON DELETE CASCADE" for some foreign keys ? In my case I have 3 foreign keys ( Name, Place and State). But I want to add "ON DELETE CASCADE" only for Place and State.
Please help.
|
|
|
|
|
srikrishnathanthri wrote: Is it possible to set ''ON DELETE CASCADE" without adding primary key in the parent table ?
A foreign key must reference either a primary key, or a column with a unique constraint. To set up a foreign key between the tables, you'll need a unique constraint on the SLNO column in table1.
srikrishnathanthri wrote: But I want to add "ON DELETE CASCADE" only for Place and State.
I don't think you've understood what ON DELETE CASCADE means. When you delete the row from table1, any rows in table2 with the same SLNO will be deleted as well. It doesn't do anything when you change a value in the referenced row, and it doesn't delete the value from specific columns.
Cascading Referential Integrity Constraints[^]
You could do this with a trigger[^] on table1, but it's a pretty nasty workaround, which can lead to hard-to-trace problems. As it's only two tables, it would be simpler to update both at the same time.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Ok. I got your second point, yes now I understood 'ON DELETE CASCADE' correctly. But I have doubt in my first question.
Here I am referring foreign key to a column with a unique constraint. I have created foreign keys refereeing to unique constraint without any error.
The point I am getting error is when I run,
ALTER TABLE [dbo].[Product2] ADD CONSTRAINT
[FK_ProductDetails_Products] FOREIGN KEY([name],[place],[state])
REFERENCES [dbo].[Product1] ([name],[Place],[state])
ON UPDATE CASCADE
ON DELETE CASCADE
Can you guess what might be the error.
|
|
|
|
|
Do you have a unique constraint on Product1 for name, place and state?
ALTER TABLE dbo.Product1
ADD CONSTRAINT UX_Product1_Name_Place_State
UNIQUE NONCLUSTERED (name, Place, state)
If not, you can't create a foreign key pointing to it.
If you have, then you'll probably get an error relating to multiple cascade paths, since you already have a foreign key relationship between the two tables.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Now I have a doubt,
I want to another column named 'regno' and I want to make it primary key of product1. Now is it possible to add FK in prodcut2 for regno, name, state and place. ?
Because I am getting an error..
Introducing FOREIGN KEY constraint 'FK_ProductDetails_Products' on table 'Product2' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
In my case,
regno refers to Prooduct1's regno ( which is primary key)
name, place and state refers to Prooduct1's name, place and state ( which are unique constraints )
Why I am getting this error ?
|
|
|
|
|
Because you have multiple FKs between the tables. It's a bad design, but it's possible - so long as only one of the FKs specifies a "cascade" rule for update or delete.
[A] table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement.
You're trying to use a foreign key for something that it wasn't designed for. If you can't change the table design, switch to issuing multiple queries, or using a trigger to push the changes from Product1 to Product2.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi
I would suggest you spend some time understanding Normalization of tables and reference integrity. If you have a good understanding of these concepts then it will be a lot easier for you designing the tables in the right way and fixing any errors.
|
|
|
|
|
Hi,
In MySQL, I have basket field call item_name in my order_basket table.
I want an SQL statement to take all item_name and INSERT it into a variable call param_details whch a TEXT type with a "\n" between them.
something like this
DECLARE param_details text;
INSERT CONTACT(item_name, "\n" INTO param_details FROM order_basket
How can I do this please?
Thanks,
Jassim[^]
Technology News @ www.JassimRahma.com
|
|
|
|
|
DECLARE @var AS NVARCHAR
SELECT @var = field1 FROM table1 WHERE ...
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
but this is the issue, the item_name is in separate rows and I want to make them in one variable with a line feed, they are something like this:
Vienna Cheesecake
Caramel Crunch
Wedding Package
Graduation Sprinkles Cake
Red Velvet Cupcake
Original Cookies
in separate rows
Technology News @ www.JassimRahma.com
|
|
|
|
|
DECLARE @var NVARCHAR(MAX)
SET @var = ''
SELECT @var = @var + '[LINE-BREAK]' + field1 + field2 + field3 FROM table1
SELECT @var
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
|
I think you're looking for GROUP_CONCAT()[^]. As far as I understand it, you don't need the grouping aspect of it but there doesn't seem to be an equivalent function without it. So you need to provide the GROUP BY clause with a column whose value is equal for all rows whose item_name you want to concatenate. Pay attention to the remark about the maximum string length returned by the function in the linked documentation.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
I'm in my last year in college and creating an application for a company is a major requirement for my diploma.
I will be using C# (MS Visual Studio) to create a transaction processing system with its own database. However, after doing some research, it seems that I'm still unable to decide which would be the better choice for the system I'm planning to create.
Some say MySQL has more advantages, some say SQL Server does a better job at handling data for businesses. I can't seem to find any consensus. I've only had experience with MS Access, and a bit with MySQL for Web programming.
To be honest, as of now I don't have a list yet of the specific requirements of the system since I will still have to meet with my advisers. It will however involve the basic functions of a common TPS: adding/deleting/editing data, viewing data, printing of reports, database management. There will be loads of data to be handled as this will be developed for business use. I am also planning to include an audit trail feature, so there's time-tracking involved.
What would you advise?
What I have tried:
After looking up several articles on the internet I still can't decide which would handle the basic needs of a TPS better. Even some of my instructors have conflicting opinions on this; some advise me to use MySQL, some advise me to use SQL Server.
|
|
|
|
|
kmllev wrote: What would you advise?
Look at the price first.
One may have more features than the other, but that is hardly relevant if you're not using those features.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
But if you can live with the limitations, the price for SQL Express[^] is the same as the price for MySQL. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Yes, as much as possible I would like to stick to something that's free. I have MySQL Workbench, and SQL Server has an Express version.
|
|
|
|
|
Ignoring the features like MARS and platform-dependencies; which of the two do you have more experience with?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I have more experience with MySQL Workbench because I've done Web programming before, and initially I was already planning to go with it for my project since I'm more familiar with it. However, a schoolmate of mine also developed such TPS, an electoral system to be precise, and he used SQL Server for it, and he recommended it to me, and with all these different opinions it's hard for me to decide.
|
|
|
|
|
To turn the question around a bit, which one do you want to have experience with?
|
|
|
|
|
I´m sorry to mess things up for you.
But I have to recommend you have a look at Postgresql with Entity framework.
It´s just awesome, very productive when writing code.
|
|
|
|
|
I will definitely look into it and put it into consideration. Thank you.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
