Data Relationship & SQL data adapter

Hi

I was trying to fill SqlDataAdapter from a " View" with inner join of two tables. When I call the update method of the data adapter it is failing as it cannot generate automatic SQL for multiple tables in my view. Writing own SQL script for update is one way out but it is tedious process.

Is there any other work around to do this?

Can I create a view of two joined data sets on some condition (Just like creating a view in database) in the memory.

Cheers,

Sudath Attanayake.One option you might look into which is extremely common among database programmers, is to only do updates through stored procedures. One of the main reason is security. If you disalow any updates to hapen to the tables or views, then you can control who can update what by simply controlling the permission to execute the individual stored procs, or special case what data can be updated.

Data Model for Application Inventory

Can someone help me provide a logical E-R model of the different entities
with relationship for collecting Application Inventory ?
The goal is to collect all the Applications along with dependencies.
On the top of my head, I can think of a Server/Hardware Table, an
ApplicationName Table that would appear to be many to many i.e One Server
could host multiple apps and one App could be hosted on multiple servers.
Anyways, I am sure you know what Im talking about. But can someone help me
with all the different tables,etc. that could also include location of
servers, type of servers, type of apps, application dependencies, database
dependencies,etc.
ThanksPublic forums are not the right avenue to get this information since your
conceptual model & business rules are mostly transparent to others here.
If you are ill-equipped to develop and E-R model, the right approach is to
hire a qualified professional who can have direct access to your business
model.
Anith|||Anith,
This is just for my own collection of application inventory and not to be
consumed by the entire company. I was just looking for a very conceptual
model of the different entities with some common attributes. I dont think I
would need to hire an external consultant for this. Just want to get a start
on where to begin..
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23wKTNzIVHHA.2256@.TK2MSFTNGP02.phx.gbl...
> Public forums are not the right avenue to get this information since your
> conceptual model & business rules are mostly transparent to others here.
> If you are ill-equipped to develop and E-R model, the right approach is to
> hire a qualified professional who can have direct access to your business
> model.
> --
> Anith
>|||Not sure if the point is missed, but hardly anyone here is familiar with
your conceptual model other than you. A general advice that you receive here
may be based on what others perceive as the problem and may not be the
actual design
problem, not to mention the chances of misunderstanding and
misinterpretation being very high. That is why newsgroup responses cannot
substantively contribute to your requirement.
Different organizations categorize and document their applications
differently. Some consider cataloging all aspects of the existing
applications while some just documents their names with perhaps say, usable
acronyms. Some companies introduce a temporal element to document the
current or historical status of applications used by them.
Even if you are doing it for personal use, I have no idea of your
expectations are. A google search returned a few shareware downloads that do
this sort of thing. I don't know what your application inventory would be
like. Perhaps someone who is familiar with developing it might be able to
help you with this.
Anith

Data Model for Application Inventory

Can someone help me provide a logical E-R model of the different entities
with relationship for collecting Application Inventory ?
The goal is to collect all the Applications along with dependencies.
On the top of my head, I can think of a Server/Hardware Table, an
ApplicationName Table that would appear to be many to many i.e One Server
could host multiple apps and one App could be hosted on multiple servers.
Anyways, I am sure you know what Im talking about. But can someone help me
with all the different tables,etc. that could also include location of
servers, type of servers, type of apps, application dependencies, database
dependencies,etc.
ThanksPublic forums are not the right avenue to get this information since your
conceptual model & business rules are mostly transparent to others here.
If you are ill-equipped to develop and E-R model, the right approach is to
hire a qualified professional who can have direct access to your business
model.
--
Anith|||Anith,
This is just for my own collection of application inventory and not to be
consumed by the entire company. I was just looking for a very conceptual
model of the different entities with some common attributes. I dont think I
would need to hire an external consultant for this. Just want to get a start
on where to begin..
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23wKTNzIVHHA.2256@.TK2MSFTNGP02.phx.gbl...
> Public forums are not the right avenue to get this information since your
> conceptual model & business rules are mostly transparent to others here.
> If you are ill-equipped to develop and E-R model, the right approach is to
> hire a qualified professional who can have direct access to your business
> model.
> --
> Anith
>|||Not sure if the point is missed, but hardly anyone here is familiar with
your conceptual model other than you. A general advice that you receive here
may be based on what others perceive as the problem and may not be the
actual design
problem, not to mention the chances of misunderstanding and
misinterpretation being very high. That is why newsgroup responses cannot
substantively contribute to your requirement.
Different organizations categorize and document their applications
differently. Some consider cataloging all aspects of the existing
applications while some just documents their names with perhaps say, usable
acronyms. Some companies introduce a temporal element to document the
current or historical status of applications used by them.
Even if you are doing it for personal use, I have no idea of your
expectations are. A google search returned a few shareware downloads that do
this sort of thing. I don't know what your application inventory would be
like. Perhaps someone who is familiar with developing it might be able to
help you with this.
--
Anith

Data Migration

Hi friends,
In my project I have redesigned my database structure. In the existing
structure there is no Primary key and no relationship b/w data.
In the new structure Primary key and the relationship is added.
Now I want to migrate the existing data into the new structure.
There is a chance for duplicate records and also records that does not
satisfy referential integrity.
How to migrate the data? I want to have a copy of the duplicate records and
also the records which does not satisfy referential integrity.
Its a huge database, so i can't query table by table to find the mismatch
records.
How to proceed?
thanks
vanithaHi,
Let me know can you consider the following:
I assume that you have two database one is normalized and other one is not
normialized.
1. Backup the Both Databases.
2. Add err_text column to all tables in non-normolized DB.
3. Write a store procedure with the following logic:
a. Select a record and try inserting to Normolized DBs table ,
if no error occurs delete the same from Non-Normolized table,
otherwise
updated err_text column of respective table in non-normolized db
with the
error
b. perform step (a) for each record in every table in non-normlized DB.
4. After the step3 what ever record exists in table of non-normolized db
either they
are passed Primary or referential integrity.|||Vanitha
There are many approaches to this. some are very trickey too.
1)One thing that you can easily do is to update new tables and compare with
old one find unmatched rows using pimary key.( u can use dynamic query to
supply table names while comparing)
2) You can use sp_MSforeachtble: You have to write something dynamically to
abtain all duplicate rows
3) write a procedure (using cursor perhaps) to take columns from
information.schema.columns where for each table and supply those columns to
find out duplicat rows using group by and count(*) >1 as you usually do.
4) Use a sproc to insert row by row to another table and insert into
errortable whereever there is an error using @.@.error <>. This approach gives
coplete rows for which inserts failed.
but 2 and 3 only gives duplicate rows and does not check other constriants
Hope This will give you a lead. though does not solve
Regards
R.D
"Vanitha" wrote:

> Hi friends,
> In my project I have redesigned my database structure. In the existing
> structure there is no Primary key and no relationship b/w data.
> In the new structure Primary key and the relationship is added.
> Now I want to migrate the existing data into the new structure.
> There is a chance for duplicate records and also records that does not
> satisfy referential integrity.
> How to migrate the data? I want to have a copy of the duplicate records an
d
> also the records which does not satisfy referential integrity.
> Its a huge database, so i can't query table by table to find the mismatch
> records.
> How to proceed?
> thanks
> vanitha
>|||I can't add a column to the existing table.
Can I add a new table into the existing database called "errorTable" and the
n
insert the table_name, column data and the error description.
pls advice on this.
thanks
"SlowLearner" wrote:

> Hi,
> Let me know can you consider the following:
> I assume that you have two database one is normalized and other one is not
> normialized.
> 1. Backup the Both Databases.
> 2. Add err_text column to all tables in non-normolized DB.
> 3. Write a store procedure with the following logic:
> a. Select a record and try inserting to Normolized DBs table ,
> if no error occurs delete the same from Non-Normolized table,
> otherwise
> updated err_text column of respective table in non-normolized db
> with the
> error
> b. perform step (a) for each record in every table in non-normlized DB
.
> 4. After the step3 what ever record exists in table of non-normolized db
> either they
> are passed Primary or referential integrity.
>|||there are millions of records, so I can't select each and every record and
check the errors.
I want to do this in bulk.
pls help me to solve this.
thanks
vanitha
"SlowLearner" wrote:

> Hi,
> Let me know can you consider the following:
> I assume that you have two database one is normalized and other one is not
> normialized.
> 1. Backup the Both Databases.
> 2. Add err_text column to all tables in non-normolized DB.
> 3. Write a store procedure with the following logic:
> a. Select a record and try inserting to Normolized DBs table ,
> if no error occurs delete the same from Non-Normolized table,
> otherwise
> updated err_text column of respective table in non-normolized db
> with the
> error
> b. perform step (a) for each record in every table in non-normlized DB
.
> 4. After the step3 what ever record exists in table of non-normolized db
> either they
> are passed Primary or referential integrity.
>|||why dont you create a new table or temp table with the same syntax and add
all uninserted columns data to new error table
Regards
R.D
"R.D" wrote:
> Vanitha
> There are many approaches to this. some are very trickey too.
> 1)One thing that you can easily do is to update new tables and compare wi
th
> old one find unmatched rows using pimary key.( u can use dynamic query to
> supply table names while comparing)
> 2) You can use sp_MSforeachtble: You have to write something dynamically t
o
> abtain all duplicate rows
> 3) write a procedure (using cursor perhaps) to take columns from
> information.schema.columns where for each table and supply those columns t
o
> find out duplicat rows using group by and count(*) >1 as you usually do.
> 4) Use a sproc to insert row by row to another table and insert into
> errortable whereever there is an error using @.@.error <>. This approach giv
es
> coplete rows for which inserts failed.
> but 2 and 3 only gives duplicate rows and does not check other constriants
> Hope This will give you a lead. though does not solve
> Regards
> R.D
>
> "Vanitha" wrote:
>|||There are n number of records... so i can't chk record by record.
is there any way to do in bulk. and if any error during the operation, the
migration shd not be cancelled, it shd log in another table or something lik
e
that.
thanks
vanitha
"R.D" wrote:
> Vanitha
> There are many approaches to this. some are very trickey too.
> 1)One thing that you can easily do is to update new tables and compare wi
th
> old one find unmatched rows using pimary key.( u can use dynamic query to
> supply table names while comparing)
> 2) You can use sp_MSforeachtble: You have to write something dynamically t
o
> abtain all duplicate rows
> 3) write a procedure (using cursor perhaps) to take columns from
> information.schema.columns where for each table and supply those columns t
o
> find out duplicat rows using group by and count(*) >1 as you usually do.
> 4) Use a sproc to insert row by row to another table and insert into
> errortable whereever there is an error using @.@.error <>. This approach giv
es
> coplete rows for which inserts failed.
> but 2 and 3 only gives duplicate rows and does not check other constriants
> Hope This will give you a lead. though does not solve
> Regards
> R.D
>
> "Vanitha" wrote:
>|||TRY THIS
CAN YOU JUST INSERT INTO NEW TABLE AND COMPARE WITH OLD TABLE SO THAT YOU
KNOW WHAT RECORDS ARE NOT INSERTED
this is like this
1) add indentity column in the old table and new table
2) insert into new table2( normalised)
3) insert rows in an order
4) find out which rows are not available in new table using somethinglike
SELECT * FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE.IDENTITY =
TABLE2.IDENTITY WHERE TABLE2.IDENTITY IS NULL
I think this is simple and possible
Regards
R.D
REGARDS
R.D
"Vanitha" wrote:
> There are n number of records... so i can't chk record by record.
> is there any way to do in bulk. and if any error during the operation, the
> migration shd not be cancelled, it shd log in another table or something l
ike
> that.
> thanks
> vanitha
> "R.D" wrote:
>|||OOPS
IN THE SECOND TABLE IDENTIY COLUMN SHOULD ALSO BE INSERTED EXLICITLY OR MAKE
IT A NUMERIC.
sorry there is caps lock pressed on my key board
R.D
"Vanitha" wrote:
> There are n number of records... so i can't chk record by record.
> is there any way to do in bulk. and if any error during the operation, the
> migration shd not be cancelled, it shd log in another table or something l
ike
> that.
> thanks
> vanitha
> "R.D" wrote:
>|||thanks a lot
if any error exits during migration, then it stops the migration process.
how to continue the migration even if error occurs.
thanks
vanitha
"R.D" wrote:
> OOPS
> IN THE SECOND TABLE IDENTIY COLUMN SHOULD ALSO BE INSERTED EXLICITLY OR MA
KE
> IT A NUMERIC.
> sorry there is caps lock pressed on my key board
> R.D
> "Vanitha" wrote:
>

Data manipulation with One-to-many-to-one relationship

Is it possible to INSERT, UPDATE, DELETE data in this type of relationship? If so, how in VS2005? Not having issues with SELECT, even without joins.

Thanks

Hi,

Try providing more information about what you are trying to do. Do you have tables set up in a DB already? What columns do you have in those tables, and what are the references that make a given row of data in each table unique (primary keys)?

If you don't have a schema yet, provide (in your own words), what facts you are trying to convey - such as 'A customer places an order. Order has line items. Each product has a product number.' etc... This will give viewers here a better understanding of what you need.

Good luck, in any event. BRN..

|||

Let's say for example:

Table 1 has Table1ID (PK) and Table1Data

Table 2 has Table2ID (PK), Table1ID (FK for Table1.Table1ID), Table3ID (FK for Table3.Table3ID)

Table 3 has Table3ID (PK), and Table3Data

Relationship is one-to-many for Table1.Table1ID to Table2.Table1ID and one-to-many for Table3.Tat.ble3ID to Table2.Table3ID, creating a many to many relationship for Table1 to Table 3 using Table 2 as an intersection table. I do not have any cascading.

I am able to query the database and gather the information, but need to be able to add, edit, and delete in a C# VS2005 project. Starting off small with something like this would enable me to conquer a bigger project that I would like to create. This is the first time I have had to work with data that required this type of relationship.

I'd prefer to see the SQL.

Is this enough information?

Many thanks

|||OK, let take these 3 tables for example:

create table Employee (EmplNumber int not null primary key,EmplName nvarchar(20) not null)

create table Department (DeptNumber int not null primary key,DeptName nvarchar(50) not null)

-- relationship between Employee and Department

create table DeptEmpl (DeptNumber int not null foreign key references Department(DeptNumber)
ON DELETE CASCADE ON UPDATE CASCADE,
EmplNumber int not null foreign key references Employee(EmplNumber)
ON DELETE CASCADE ON UPDATE CASCADE)

insert into Employee values (1, 'John')
insert into Employee values (2, 'Terry')
insert into Employee values (3, 'Andrew')

insert into Department values (1, 'Marketing')
insert into Department values (2, 'Sales')
insert into Department values (3, 'Accounting')

insert into DeptEmpl values (1, 1)
insert into DeptEmpl values (1, 3)
insert into DeptEmpl values (2, 1)
insert into DeptEmpl values (2, 2)
insert into DeptEmpl values (2, 3)
insert into DeptEmpl values (3, 1)

?
There should be no problem when you try to insert data-- the PK/FK contraint will maintain the reference integrity for you. The key point here is theON DELETE/UPDATE CASCADE, they will help to maintain reference integrity during DELETE/UPDATE, so no extra work need to be done manually. If you have other concerns, feel free to post them|||

Thanks Iori_Jay

It is completely clear to me what you are doing in this example. How does one handle user input variables from a web application?

Let's say the user wants to insert a new Employee 'Edgar' that belongs to Department 'Sales'.

|||OK, typical steps for such INSERT should be:

1. Insert the information for the new employee 'Edgar' toEmployee table, and get theEmplNumber for the new inserted employee
2. Check theDepartment table to see whether there is a 'Sales' department, if not then add the 'Sales' department.
3. Insert a row toDeptEmpl table to represent the relationship between 'Edgar' and 'Sales'.

So a sample stored procedure looks like:

CREATE PROCEDURE usp_InsertNewEmployee @.EmpName nvarchar(20), @.DeptName nvarchar(50)
AS
IF((LEN(@.EmpName)=0) OR (LEN(@.DeptName)=0))
RAISERROR('Please input both Employee name and the department name which he(she) belongs to',16,1)
DECLARE @.newEmpNumber INT,@.DeptNumber INT
SELECT @.newEmpNumber=max(EmplNumber)+1 FROM Employee
SELECT @.DeptNumber=DeptNumber FROM Department WHERE DeptName=@.DeptName

INSERT INTO Employee SELECT @.newEmpNumber, @.EmpName
IF (@.DeptNumber IS NULL)
BEGIN
SELECT @.DeptNumber=max(DeptNumber)+1 FROM Department
INSERT INTO Department SELECT @.DeptNumber,@.DeptName
END
INSERT INTO DeptEmpl SELECT @.newEmpNumber,@.DeptNumber
go

EXEC usp_InsertNewEmployee 'Edgar','Sales'|||

Iori_jay,

Thanks so much for your help. I've learned many things from this post. I appreciate you sharing your talents.

ICE|8

|||It's my pleasure to help with this issue Wish you a happy festival!