HELP! Synchronize Db And Maintain Foriegn Key Relations?

Jan 30, 2007

I have to synchronize 2 databases hourly but am having difficulty maintaining foreign key relations. These tables use auto-increment columns as primary keys, with child records in other tables related with foreign keys. I can't change the way the local software uses primary or foreign keys as it is hardcoded in the local app. (microsoft retail management system)..(however the web-remote app is easily customized). I am using CDB synchronizer to sync the two databases because the remote one is mysql.

Example tables layout:
Items table has auto-increment primary key 'id'
TransactionEntry table has its own auto-increment primary key 'id' and a foreign key 'item_id'

Example of how remote and local database foreign key relations are incorrect after sync using CDB synchronizer:
8:00am -first installation of database-'item' tables auto-increment 'id' columns match with id last record value of '6'

locally the following products are added:

11001 short sleeve t---gets added with primary key in 'item' table 'id' of '7'

11002 long sleeve t----gets added with primary key in 'item' table 'id' '8'

remotely the following products are added:

21001 hipster jeans- --gets added with primary key in 'item' table 'id' of '7'

31001 overalls---gets added with primary key in 'item' table 'id' '8'

remotely someone orders 21001..so TransactionEntry table records sale of "item_id" of '7', but after synch with our local server,

product with "item_id" of '7' is "short sleeve t".

9:00 -synch takes place...item_id foreign key isn't accurate because of independent auto-increment values..

whenever a product is ordered, the TransactionEntry table will record the product's ID column thats available in it's own local copy... after synch, the 'item_id' field will not match the 'Item' table id field and the data about the transaction's product is lost.

I have read of solutions involving staging/temporary tables to cascade update foreign keys before synching into main database, but hopefully there is a more elegant solution for this. If this is only way, will it be reliable? foreign key mix-match seems like could cause havoc.

View 2 Replies


ADVERTISEMENT

Foriegn Key

Jan 17, 2006

please tell me how to make a foreign key in Ms SQL7

View 3 Replies View Related

Foriegn Key Restraint

Jan 25, 2000

Here is a table structure, with 2 contrainst applied to the same columns. Can this possible cause a problem.

CREATE TABLE dbo.app_person
(
appid int NOT NULL,
source char(1) NOT NULL,
first char(16) NULL,
mi char(1) NULL,
last char(24) NULL,
suffix char(4) NULL,
ssn char(9) NULL,
dob datetime NULL,
marital_status decimal(1,0) NULL,
housing char(1) NULL,
mortgage_holder char(20) NULL,
mortgage_payment decimal(9,2) NULL,
auto_holder char(20) NULL,
auto_account char(16) NULL,
auto_balance decimal(9,2) NULL,
auto_payment decimal(9,2) NULL,
de_datetime datetime NULL,
de_clerk char(10) NULL,
excl_other_income bit DEFAULT 0 NOT NULL,
pre_housing char(1) NULL,
same_area bit DEFAULT 0 NOT NULL,
income_type decimal(2,0) NULL,
other_income_type decimal(2,0) NULL,
relationship decimal(2,0) NULL,
line_of_work bit DEFAULT 0 NOT NULL,
selected_bureau_id decimal(1,0) NULL,
excl_income bit DEFAULT 0 NOT NULL,
CONSTRAINT PK_app_person PRIMARY KEY NONCLUSTERED (appid,source),
CONSTRAINT IX_lnapp100 UNIQUE NONCLUSTERED (appid,source),
CONSTRAINT FK_lnapp100_lnapp000 FOREIGN KEY (appid) REFERENCES dbo.app_main (appid)
)

View 1 Replies View Related

Primary Key And Foriegn Key?

Dec 25, 2006

What is a Primary Key and Foriegn Key and how do they relate? Also, if I have 18 tables...how do I know the tables relate to another. Thanks.

View 4 Replies View Related

Primary Key + Foriegn Key

Nov 12, 2007

help needed,

sorry for such a basic querry please help me.

i have created 2 tables
both of them has different primary keys
now in first table there is also a refernece for primary key of second table

1) how do i make these both primary keys autoincrement
2) how do i make refernce to primary key of second table

like
table1
itemid itemrate itemcode itemdesscriotion

table2
productid itemid purchaseinvoice invoice amount

also kindly direct me to some quick and easy tutorials on SQL


thanks all for your help.

View 1 Replies View Related

Error While Trying To Save A Foriegn Key Relationship

Feb 6, 2008

Hi
  I have a table called ClientPlan and in which PlanId is the Primary key. another table called ClientSources1 and i have a column called planId.. and i am trying to set that as Foriegn key so that if i delete a record in the ClientPlan table i want the record to be deleted in Clientsources too.. i have tried settting the Foreign in the clientsources table for ClientPlan to Cascade update and delete.. but that doesnt work...and i get the error below... 
ClientPlan' table saved successfully'ClientSources1' table- Unable to create relationship 'FK_ClientSources1_ClientPlan'.  Introducing FOREIGN KEY constraint 'FK_ClientSources1_ClientPlan' on table 'ClientSources1' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.Could not create constraint. See previous errors.
 
any help will be appreciated.
Regards
Karen

View 2 Replies View Related

Loading Tables With Foriegn Key Contraints

Aug 31, 2006

Hi,

I am having trouble loading tables (within the same data flow) that have a foriegn key relationship defined between them. For instance:

Table A is a parent (one side of the relationship) to Table B (many side of the relationship).

I am trying to load Table A first within the data flow and then Table B after, but I get the following error:

[OCMD EntityRole Insert [2666]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_EntityRole_Entity". The conflict occurred in database "ODS", table "dbo.Entity", column 'EntityGuid'.".

I am currently using OLE DB commands to perform the inserts, I load table A and move on to then load Table B, I can see the records in Table A before trying to load Table B but for some reason Table B load still fails.

I was thinking maybe this has something to do with the transaction setting or Isolation level but have played with this to no avail (currently everything is the default - supported/serializable). Also, I am thinking maybe because the OLE DB commands are creating two seperate connections (they are using the same connection manager) the second one is unable to see the transactions from the other (first) connection (Table A)?

Is there a way around this without dropping (disabling) forigen keys before the load and adding them back in after? Would like to avoid this?

I would also like to avoid reading the data source multiple times. Everything I need is in the one source so I would like to populate multiple tables from the one source data stream instead of reading the same data 2,3 or 4 times etc.

Seems to me there must be a simple explanation/solution for this but I'm stuck at this point?

P.S.

I was intially using OLE DB destinations (because they are much faster) and was having the same issue, which made sense because the OLE DB destinations do not let you pass the data stream on so I had to multi cast to the destinations so they were loading at the same time. I would rather use the OLE DB destinations so if you have any ideas around how I could do this using those components that would be appreciated too!

Thanks!

View 3 Replies View Related

WHY DO FORIEGN KEYS HAVE TO BE UNIQUE OR HAVE A CONSTRAINT?

Oct 9, 2007

How do I go about protecting rows from deletion in this scenerio?
Rule 1 The Administrator Users Account may not be deleted
Rule 2 All Groups have Administrator as a member, and the Administrator cannot be removed.
Rule 3 All Groups have the Administrators Group as a member, and the Administrators Group cannot be removed.



Four tables:

Users Table (
UID bigint Identity seeded with 1234 Primary key
UserID varchar(30) NOT NULL UNIQUE

)
INSERT FIRST RECORD (this record needs to be protected from deletion)
UID = 1234
UserID='Admininstrator'

INSERT FIRST RECORD (this record and others can be deleted)
UID = 1235
UserID='Test User 1'

Groups Table (
GID bigint Identity seeded with 1234 Primary key
GroupName varchar(30) NOT NULL UNIQUE
)

INSERT FIRST RECORD (this record needs to be protected from deletion)
GID = 1234
UserID='Admininstrators'

INSERT SECOND RECORD (this record and others can be deleted)
GID = 1235
UserID='Test Group 1'

Group_Members Table (
GID bigint NOT NULL //points to the group's ID and can't be unique
UID bigint NOT NULL //points to the members UserID and can't be unique
)
INSERT FIRST RECORD (this record needs to be protected from deletion because UID points to the Administrator)
GID = 1234
UID = 1234

INSERT SECOND RECORD (this record and others can be deleted because UID does not point to the Administrator.)
GID = 1234
UID = 1235


Group_Group_Members Table (
GID bigint NOT NULL //points to the group's ID and can't be unique
GGID bigint NOT NULL //points to the group members GID and can't be unique
)
INSERT FIRST RECORD (this record needs to be protected from deletion because GGID points to the Administrators Group.)
GID = 1234
GGID = 1234

INSERT SECOND RECORD (this record and others can be deleted because GGID does not point to the Administrators Group.)
GID = 1234
GGID = 1235


I have tried using foriegn keys, constraints an every thing else, but I hit a brick wall because FK requires the ke to be primary (btw is UNIQUE).
Any help would be appreciated.

View 2 Replies View Related

Data Table Foriegn Key Problem

Aug 26, 2007

I am just learning how to program in SQL, so please be patient with me


ok, here it is, i am getting the following errors with my script when i try to execute, and although i realize that this is not the best style to write my script in, i am trying to learn why this is erroring. the errors are:


Msg 1769, Level 16, State 1, Line 9

Foreign key 'Employee2Job_Title' references invalid column 'Title' in referencing table 'Employee'.

Msg 1750, Level 16, State 0, Line 9

Could not create constraint. See previous errors.



now i realize that the second error is because of the first, and that if i can fix the first, the second will go away. Thanks for the help.

Here is my script



use inventory

go

CREATE TABLE Job_Title

(Job_Title_Title char(25) NOT NULL ,

Job_Title_EEO1_Classification char(25) ,

Job_Title_Job_Description Varchar(45) ,

Job_Title_Exempt_Status Varchar(15)

, PRIMARY KEY (Job_Title_Title)

);

CREATE TABLE Employee

(Employee_Emp_ID integer NOT NULL ,

Employee_last_name varchar(15) ,

Employee_first_name varchar(15) ,

Employee_address varchar(30) ,

Employee_city varchar(15) ,

Employee_state char(2) ,

Employee_Telephone_area_code char(3) ,

Employee_Telephone_number char(8) ,

Employee_EEO1_Classification char(25) ,

Employee_Hire_Date char(8) ,

Employee_Salary char(6) ,

Employee_Gender Varchar(1) ,

Employee_Age char(2) ,

Employee_Title char(25)

, PRIMARY KEY (Employee_Emp_ID)

, constraint Employee2Job_Title FOREIGN KEY (Title

) REFERENCES Job_Title

);



Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(1, 'Edelman', 'Glenn', '175 Bishop Lane', 'La Jolla', 'CA', 619, '555-0199', 'Sales Workers', 10/7/2003, 21500.00, 'M', 64, 'Cashier')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(2, 'McMullen', 'Eric', '762 Church Street', 'Lemon Grove', 'CA', 619, '555-0133', 'Sales Workers', 11/1/2002, 13500.00, 'M', 20, 'Bagger')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(3, 'Slenj', 'Raj', '123 Torrey Drive', 'North Clairmont', 'CA', 619, '555-0123', 'Officials & Managers', 6/1/2000, 48000.00, 'M', 34, 'Assistant Manager')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(4, 'Broun', 'Erin', '2045 Parkway Apt 2b', 'Encinitas', 'CA', 760, '555-0100', 'Sales Workers', 3/12/2003, 10530.00, 'F', 24, 'Bagger - 30 hours/wk')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(5, 'Carpenter', 'Donald', '927 Second Street', 'Encinitas', 'CA', 619, '555-0154', 'Office/Clerical', 11/1/2003, 15000.00, 'M', 18, 'Stocker')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(6, 'Esquivez', 'David', '10983 North Coast Highway Apt 902', 'Encinitas', 'CA', 760, '555-0108', 'Operatives (Semi skilled)', 7/25/2003, 18500.00, 'M',25, 'Asst. - Butchers & Seafood Specialists')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(7, 'Sharp', 'Nancy', '10793 Montecino Road', 'Ramona', 'CA', 858, '555-0135', 'Sales Workers', 7/12/2003, 21000.00, 'F', 24, 'Cashier')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Accounting Clerk', 'Office/Clerical', 'Computes, Classifies, records, and verifies numerical data for use in maintaining accounting records.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Assistant store manager', 'Officials & Mangers', 'Supervises and coordintes activities of workers in department of food store. Assist store manager in daily operations of store.', 'Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Bagger', 'Sales Worker', 'Places customer orders in bags. Performs carry out duties for customers.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Cashier', 'Sales Worker', 'Operates Cash register to itemize and total customers purchases in grocercy store', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Computer Support Specialist', 'Technician', 'Installs, Modifies, and makes minor repairs to personal computer hardware and software systems and provides technical assistance and training to system users.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Director of Finance & Accounting', 'Officials & Mangers', 'Plans and directs finance and accounting activites for Kudlser Fine Foods.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Retail Asst. Bakery & Pastry', 'Craft Workers (Skilled)', 'Obtains or prepares Bakery and Pastry items requested by customers in retail food store.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Retail Asst. Butchers and Seafood Specialists', 'Operatives (Semi skilled)', 'Obtains or prepares Meat and Seafood items requested by customers in retail food store.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Stocker', 'Office/Clerical', 'Stores, prices, and restocks merchandise displays in store.', 'Non-Exempt')

View 3 Replies View Related

Can I Add A Foriegn Key On A Indexed View To Reference Other Table

Nov 8, 2006

Is there anybody familiar with indexed view here?

Can I add a foriegn key on a indexed view to reference other table?

Thanks!

View 1 Replies View Related

SS 2005 Express- Allowing Null Values And Foriegn Key Constraints

Dec 28, 2006

Hello,I have a foreign key constraint between two tables (Appointments andMissedAppointmentReasons) and I'd like to allow null values in Appointmentstable for the field containing the MissedAppointmentReason, but currently, Iget a Foreign Key Constraint Error when I try to add a record to theAppointments table.Any ideas how I can have the null values but still maintain the Foreign KeyConstraint?Thanks!Rick

View 3 Replies View Related

Msdb Maintain?!

Nov 15, 2006

hi expertsss..
my msdb database is like 2gb big.. to me is really big.. so is there a way to maintain that? and how. ..
also.. my disk level fragment are bad on one of my drive (some datafiles in there and msdb is there too). is there any 3rd party tool i can use to do the defragment and set schedule ? please help thanks~

View 7 Replies View Related

How To Maintain Transaction Between Two Databases?

Apr 9, 2007

Hi All,
Pandon me for asking such question, I am still a beginner to ASP.NET.
I have a project that require me to do single operation that is suppose to update two databases, wonder how do I maintain transaction between these two databases? Please advise, thank you!

View 3 Replies View Related

How To Maintain Database Concurrence?

Apr 17, 2004

My application uses sql for performing operation. something like conncetion.execute(query). so there is only conncetion object no recordset object or something like that .

i want to run multiple instence of my application so i want to maintain integrety of data. and i am looking for solution through sql for locking mechanism. so concurrent data access dont currept data.

I hope you have uderstand my requirement.

View 1 Replies View Related

Maintain A Sql Data Base

Aug 15, 2005

Sebastian Garibaldi writes "Hi I'm Sebastian from Argentina, and i have a problem with a SQL data base. I receive error from data base of broken index and consistency errors. I set the fill factor with the information from the books online, i put 70 in tables that have a lot of INSERT/UPDATE/DELETE but it works for two o tree days.

I now that i must make some maintain in the database but which tools i shuld use?

here i paste an error from dbcc checktable:


Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 981108969. The text, ntext, or image node at page (1:949979), slot 52, text ID 57535781339136 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 981108969. The text, ntext, or image node at page (1:949979), slot 53, text ID 57535782191104 is not referenced.
DBCC results for 'FCRMVI'.
There are 108460 rows in 17430 pages for object 'FCRMVI'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'FCRMVI' (object ID 981108969).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (ArleiProd.dbo.FCRMVI ).



thanks, and sorry my english."

View 1 Replies View Related

PK Relations

Feb 22, 2008

Is there a fast way to see, which relations a table has for his PK?

View 2 Replies View Related

Help With Relations

Aug 21, 2004

I am using MSDE Manager for my database, and I was wondering, if data is entered on one table, IE UserID, shouldnt that same userID pop up in the related column on the foreign table as well?

View 1 Replies View Related

Relations

Jun 14, 2007

Maybe a stupid question but...



is it posible to add relations in sql compact databases (.sdf) ?



I tried in server explorer (in orcas) and there is no option for relations. Also .sdf file cannot be opened in SQL Server Express Management Studio SP2 because is version 3.5.

View 1 Replies View Related

Using Code To Maintain Relationships Across Tables.

May 17, 2002

Can anyone give me an idea like, what percentage of organizations use 'code' to maintain the parent-child relations on their tables than
having FK constraints thru the db model? Because,all the companies that I worked with used 'code' to control the relationships across the tables(not the PK/FKs.!!)
Thanks.
Neil.

View 2 Replies View Related

Maintain Jobs, View Logs

Mar 13, 2006

I have a user who needs to check whether 2 jobs have run on a SQL Server 2000 instance, and be able to view logs.

Is there a way of doing this, and not allowing them access to anything else?

View 2 Replies View Related

SQL 2012 :: Use TFS To Maintain Versions Of Objects

Oct 23, 2015

I would like to maintain version control of the all the sql objects (sp, view , tables ) and maintain source code versions control. Any way to use TFS to maintain versions of sql objects. Also the folder structure when using TFS.

View 1 Replies View Related

2 Tables And Relations

Aug 28, 2007

 Hi There,Im trying to display a list of results and have extra information pulled from another table. Please read on as this isnt what you think, just hard to explain but I will try.In Table 1 I have a set of Orders, example data below.Order 1, Line 1Order 1, Line 2Order 1, Line 3Now I have another table to record what lines a user would like to monitor, for this example the user will monitor these lines.Line 1Line 2but not Line 3I need to display a single grid view using the least amount of queries possible to diplay the following resultOrder 1, Line 1, MonitoredOrder 1, Line 2, MonitoredOrder 1, Line 3, NOT MonitoredI have tried viewed, but with the relationships it only displayes what is avialable in both tables, I have also thought about using a DataSet relationship with two queries through the code, but am really wondering if anybody knows of a simple and low overhead way of producing this result? Mayby stored procedures?Kind Regards,Gareth 

View 2 Replies View Related

Table Relations

Mar 26, 2008

I'm looking for some input about how I should setup my database tables. Here's the setup - There are several Report Forms that users can fill out and each one has its own table (I'll use Report1 and Report2 for examples). There are several other tables that relate to these Report tables, for example, a table named Pictures. If a user is filling out Report1 or 2, they could add a picture to it. The picture information is stored in the Picture table, along with the ReportID of the report it belongs to.
Report1 (table)intReportID (pk)vcFirstNameetc...Report2 (table)intReportID (pk)vcFirstNameetc...Pictures (table)intPictureID (pk)intReportID (fk - to match ReportID in appropriate Report table - not being allowed...)vcFilePathetc...
Right now, I've two relationships setup for the pictures table - one with the picture table ReportID as a FK to the Report1 table's ReportID PK, and another setup the same way, but for Report2.It's not letting me do this because it's looking for a matching ReportID in both the Report1 and Report2 tables for each entry in the Pictures table. A match will only ever exist for one or the other, never both.
In addition to Pictures, I have about 9 other tables that work the same way. I also have about 7 Report tables total. I'd really like to set something up so if I delete a record from a report table, it will delete all matching entries from the other tables. Does anyone know how I can go about setting this up?
Thanks very much!

View 5 Replies View Related

SQL-server : Why Relations???

Aug 9, 2000

Hayyy

I have just made a database with 3 tables, they are now connectet with relationships.
My problem is when I write my select statement:
select * from table1 a, table2 b where a.ID=2.

I will get all the ´records from both the tables, I just want One record. Then I addes some too my select statement so it now looks like this:
select * from table1 a, table b where a.ID=2 AND a.No=b.No

Now I got the right result, but if this is the only way to do it, there is no recent to make all the relations i the DB between the tables.

Thank You... Jonas

View 1 Replies View Related

Table Relations

Jun 13, 2005

Code:


TBL1
ID User
1 Tom
2 Alice
3 Alex
4 Melissa
5 Carl





Code:


TBL2
ID User WebSite
1 Tom http://www.Tom.com
2 Alice http://www.Alice.com
3 Alex http://www.Alex.com
4 Carl http://www.Carl.com



I want to join tables above in only one query.
I tried something I couldn't.

there are 5 users in TBL1
TBL2 is for their web sites records.

But while tom,alice,alex and carl have web site,Melissa hasn't.

I want to print like below


Code:


Tom http://www.Tom.com
Alice http://www.Alice.com
Alex http://www.Alex.com
Melissa
Carl http://www.Carl.com



my tests, although users who have web site were printed, Melissa wasn't printed.
Although she hasnot a website, I want to get her , too.

View 9 Replies View Related

Handle 1:n Relations

May 4, 2004

Hi,

I build a local cube from a relation database. In the database there are 1:n relations.
Is there a way to handle 1:n relations?
For example:
I have a table LOGGEDFLAW and a table LOGGEDREASON with a 1:n relation between them. We create a select statement of these tables and as an result we get duplicate records of LOGGEDFLAW each time more than 1 record of LOGGEDREASON are associated to 1 record of LOGGEDFLAW - this is the standard result I get with an relational JOIN operation. Now I want to count the LOGGEDFLAWs without the duplicates generated by the 1:n relationship.

Best regards,
Thorsten

View 2 Replies View Related

Relations Error -

May 23, 2006

Dim verilaDataAdapter1 As SqlCeDataAdapter
Dim verilaDataAdapter2 As SqlCeDataAdapter
Dim iliski As DataRelation
Dim Sorgu1 As String
Dim Sorgu2 As String

Dim A1Dataset As DataSet
Dim parametre1 As SqlCeParameter
Dim parametre2 As SqlCeParameter
Dim ssceconn As New SqlCeConnection("Data Source =Program FilesAtisoftDAatisoft.sdf")
ssceconn.Open()
Dim sqlInsertRow As SqlCeCommand = ssceconn.CreateCommand()

Sorgu1 = "SELECT * FROM StokKartTbl WHERE StokGrupKodu like @StokGrupKodu Order By Stokismi ASC;"
Sorgu2 = "SELECT * FROM SarzKartTbl WHERE SarzID like @Sarzid"

A1Dataset = New DataSet

verilaDataAdapter1 = New SqlCeDataAdapter(Sorgu1, ssceconn) 'StokkartTbl
parametre1 = New SqlCeParameter("@StokGrupKodu", SqlDbType.NText, 25)
parametre1.Value = Trim(Mid(CBStGrupAciklama.Text, 1, 6)) + "%"
verilaDataAdapter1.SelectCommand.Parameters.Add(parametre1)
verilaDataAdapter1.Fill(A1Dataset, "StokKartTbl")

verilaDataAdapter2 = New SqlCeDataAdapter(Sorgu2, ssceconn) 'SarzKartTbl
parametre2 = New SqlCeParameter("@Sarzid", SqlDbType.NText, 50)
parametre2.Value = Trim(Mid(CBSarzID.Text, 1, 25)) + "%"
verilaDataAdapter2.SelectCommand.Parameters.Add(parametre2)
verilaDataAdapter2.Fill(A1Dataset, "SarzKartTbl")

Dim p As DataColumn
Dim c As DataColumn
p = A1Dataset.Tables("StokKartTbl").Columns("StokID")
c = A1Dataset.Tables("SarzKartTbl").Columns("StokID")
iliski = New DataRelation("Elemanlar", p, c)
A1Dataset.Relations.Add(iliski)


DataGridStokKartTbl.DataSource = A1Dataset.Tables("StokKartTbl")

'Error

System.ArgumentException was unhandled
Message="Hata iletisi içeren isteğe bağlı bir kaynak derlemesi bulunamadığından bir hata iletisi görüntülenemiyor"
StackTrace:
System.Data.ConstraintCollection.AddForeignKeyConstraint()
System.Data.ConstraintCollection.Add()
System.Data.ConstraintCollection.Add()
at DataSetRelationCollection.AddCore()
System.Data.DataRelationCollection.Add()
AtisoftDA.siparis.Button2_Click()
System.Windows.Forms.Control.OnClick()
System.Windows.Forms.Button.OnClick()
System.Windows.Forms.ButtonBase.WnProc()
System.Windows.Forms.Control._InternalWnProc()
Microsoft.AGL.Forms.EVL.EnterMainLoop()
System.Windows.Forms.Application.Run()
AtisoftDA.siparis.Main()





View 1 Replies View Related

SELECT RELATIONS

Aug 26, 2007

HI Everyone.

how i can get the related tables.

my problem as example

i have a table name TT have relation with two table the first one TT1 and second TT2
i want to get the name of two table TT1 and TT2 by select statement.

thanks alot.

View 5 Replies View Related

Relations Without Contrains

Jan 26, 2006

If I created relation between 2 tables without any constarins what will be the value of this relation or when SQL Server will use this relation

View 1 Replies View Related

Maintain A Consistent DB Connection Through Application Lifecycle

Nov 10, 2006

Hey everyone, I'm new to .NET and I've recently inheirited a rather large and busy asp.net website.  I was asked to add a testimonials section on each page that will randomly pull a testimonial out of the db.  This is fine, however, I'm getting random errors about the DB connection either being closed or connecting.  Here is the code for the testimonials class: 1 public SqlDataReader GetTestimonials(ref SqlDataReader reader, int iCatID, string sLanguageType)2 {3 SqlCommand cmd = new SqlCommand("sp_DVX_Testimonials_Fetch", Connection);4 cmd.CommandType = CommandType.StoredProcedure;5
6 cmd.Parameters.Add(new SqlParameter("@Cat_ID", iCatID));7 cmd.Parameters.Add(new SqlParameter("@LanguageType", sLanguageType));8
9 reader = cmd.ExecuteReader();10
11 return reader;12 } I know this isn't the best way to do this (especially for each page[this site averages about 1000 hits a day]), so I was wondering was--is there a way to maintain a single DB connection that's set up in the Application_Start that will maintain the connection so I don't have to worry about this error.  If not, does anyone and any ideas as to what would help? Thanks in advance! 

View 4 Replies View Related

How To Maintain Users Permission And Access Level

Mar 9, 2007

Hi guys,
We have a scenario where there are about 50 tables in our database and we want to build an intranet web application for users to with the office to access those tables.
Users ability to access tables falls into diferent category:

Some users can NOT view some tables at all
Some users can ONLY view some tables but not insert/update any field
Some users can view and also insert/update some tables (in the same time they might not have view(select) permision on some other tables)
Now, what is the right way to implement this.
I say we have to have a Role, RolePermission, User, UserPermission inside our database to implement this (something which would look like the Roles and Users inside MSSQL) and we only have one user for our Database (MachineName/ASPUSER) to access the database and all the tables within
My colleague says NO, instead of creating all these tables and implement this, we add every user of our application as a Database user inside MSSQL in the Databse Users.
All the web application I have seen so far, DNN, CommunityServer, ... the have tables to implement all these and they don't add users inside the MSSQL.
Now which way is the way to go with, and what problem might we fall into if we use SQL users, is this possible at all. How can I convince him that we have to make and use our own tables to manage this.
 Thanks for any help,Mehdi

View 2 Replies View Related

How Can I Use SELECT DISTINCT And Maintain The Original Order

Apr 26, 2007

Say I have a result set with two fields numbers and letters.

1 A3 A1 B2 B


 The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order? When I tryselect distinct Number from MyResultSet

it will reorder the new result set by the Number field and return

123

 However, I'd like maintain the Letter order and return

132

View 1 Replies View Related

How To Maintain Transaction Order For Perpetual Inventory

Feb 9, 2008

Hello

I would appreciate If any one could help me in this matter.

problem is : how to maintain perpetual inventory transaction table order in batch mode updation ?.

I have designed a table to hold all inventory transactions. The table order is perfectly maintained in online system of updation. But if I go with batch updation then the order of the transaction is collapsing. For example consider the following table design. (note I used auto number to maintain the order).

version used : SQL Server 2000 with service pack updates.


LINEID TRANS_TYPE TRANS_ID TRAN_DATE STOCKNO QUANTITY RATE

1 Receipts 2 01/01/2000 xxxxx1 2 2.0
2. Issue 1 01/01/2000 yyyyy1 1
3 Receipts 3 02/01/2000 xxxxx2 4 3.0
4 Issue 2 02/01/2000 yyyyy2 1

the above is well in online mode. But in batch mode if i update issue 2 before receipts 3 then i am getting order like below

1 Receipts 2 01/01/2000 xxxxx1 2 2.0
2. Issue 1 01/01/2000 yyyyy1 1
3 Issue 2 02/01/2000 yyyyy2 1
4 Receipts 3 02/01/2000 xxxxx2 4 3.0

ofcourse if the order collapse means costing can not be accurate. so please any one could help me to solve this problem. because many software packages are not posting in sequence if we choose in batch mode.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved