SQL Server 2005 create relationship one-to-one with Management Studio And create Query

Question

Hi all 

I have database in Microsoft access with 4 tables are connected together with relationship one-to-one as it is showing down

I imported 4 tables to SQL Server 2005 with Management Studio and I am trying to create a relationship one-to-one same as in Access after creating Primary Key for the columns (Badge) for all the tables, then I Drag and drop from table Emp field (Badge) to CarPass table filed (Badge) I get this showing

if you can see above primary key from Emp table (Badge) which i think ok , but it the CarPass Table is named as ( Foreign Key ) Badge ( I think it should be Primary key like Microsoft Access ) . correct me if i am wrong and how to make both of them Primary key?

after continue linking the tables to the Emp primary key it ends up with the pic down  

---

[e-mail]

Answer 1

and when i try to save the work i got this massage

 

'PhoneDirectory' table
- Unable to create relationship 'FK_PhoneDirectory_Emp'.  
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_PhoneDirectory_Emp". The conflict occurred in database "SBAHC_DataBase", table "dbo.Emp", column 'Badge'.

After that i end up with inability to save the diagram :-(

any help here to do the same with Microsoft Access.

Best regards to all

---

[e-mail]

Answer 2

I think you confused your "primary" and "secondary" tables.

In Management Studio it looks like you tried to define the foreign key from the PhoneDirectory table to the Emp table.
However your Access diagram looks like it's the Emp table referencing the PhoneDirectory table.

So the foreign key that you want needs to be defined on the Emp table and reference the PhoneDirectory table.

In general, that error message means that there are records in the table which conflict with the foreign key, meaning, there are rows in PhoneDirectory with a "Badge" value that don't exist in the Emp table.

---

Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

Answer 3

Dear EitanBlumin ,

Thank you again , i managed to get it right after making the tables equal to each other at ( Badge ).

see here >>>>>>>>

I made the query ready and it is ready as it is showing under >>>>>>>>

when I tried to save it is being saved as file ...... how can i make available and showing at the Object explorer in SQL server to be used by my projects at Visual Studio 2010.

really very thankful and waiting for your response.

---

[e-mail]

Answer 4

You could create it as a "View" like this:

CREATE VIEW MyViewName
AS
SELECT Emp.Badge,...

---

Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

Answer 5

EitanBlumin Thank you 

We are learning here soooo much from you

my last question (I have 2 computer they have same SQL server and same database for me to learn in both home and office , every time i make change in database (like now I made view and saved it) to one computer I will need to do the same in the other computer :-( ,

Is there a file I can copy and take with me in order to paste it to thwe other computer to have all database and change (something like backup !

if there is any like that (when I paste it to another computer name , do I need to change any thing ?

I would highly appreciate your help here

Best regards

---

[e-mail]

Answer 6

Hi Adel, I'm happy to help!

There are several methods to do what you say.

It depends mostly on whether you wish to change the data as well, or only the schema changes (views etc.).

If you wish to change the ENTIRE database (data included) then you could either BACKUP and RESTORE, or DETACH and ATTACH.

Backup and Restore can be performed when the source database is still online.

Detaching a database will require taking the database offline, and then it's a simple copying of the database files to another machine.

If you only wish to change the schema (i.e. the view), then you merely need to execute the same creation script on all other databases.
You can use the "GENERATE CREATE SCRIPT" function in SQL Server Management Studio to easily create a script which you can run on the other databases.

if there are a lot of changes, or you don't know which objects should be scripted in order to make two different databases identical, then I'd recommend using a Database Comparison tool such as the one in SQL Data Tools, or a third party tool such as RedGate's SQL Compare.

---

Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

Answer 7

EitanBlumin Thank you 

We are happy to have one like you here ... to make it easy for us to learn

Best regards 

---

[e-mail]

Answer 8

It's my pleasure, Adel!

Good luck

---

Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

Answer 9

Dear EitanBlumin,

Correct me if I am wrong what about if i just copy the 2 files showing under and then I paste them to the same location to the other computer to have the same database and settings for the SBAHC_DataBase, is this correct ? will it work ( didn't try it yet ) if you agree i will try it :

---

[e-mail]

Answer 10

This is possible if you perform DETACH and ATTACH as I mentioned before.

Before touching the database's files (even copying them) you need to DETACH the database from the SQL Server instance.

After that you can copy them wherever you want, and ATTACH them to the SQL Server instance you want them copied to.

For more information on DETACH and ATTACH: http://msdn.microsoft.com/en-us/library/ms187858.aspx

Without detaching the database files, you're risking inconsistency because SQL Server may not have "flushed" all the changes to disk yet (SQL Server prefers working in the RAM memory before writing to the files on disk). This could result in corrupt databases and/or lost data.

More information about the differences between BACKUP/RESTORE and DETACH/ATTACH can be found here: http://strictlysql.blogspot.co.il/2011/10/backuprestore-vs-detach-attach.html

---

Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

Answer 11

Dear EitanBlumin , hope when you receive this you are fine

I created a view as (Emp_Query) and it works fine with select to view the content of for tables

but when i tried to update i got this message

(View or function 'Emp_Query' is not updatable because the modification affects multiple base tables.)

can you help me out how to solve this i need also to insert new records as well to (Emp_Query) so that the updated and inserted records gores to the other four tables.

i know you can solve and wish i can contact you directlly please.

this is my code to update 

Savecommand.CommandText = "UPDATE Emp_Query SET Employee = '" & Trim(TextBox1.Text) & _
            "' , SEX = '" & Trim(ComboBox2.Text) & "' , Cost = '" & Trim(TextBox4.Text) & "' , Department = '" & Trim(TextBox5.Text) & _
            "' , Position_Title = '" & Trim(TextBox7.Text) & "', Nat = '" & Trim(ComboBox1.Text) & "', Cat = '" & Trim(ComboBox3.Text) & _
            "' , Housing = '" & Trim(ComboBox4.Text) & "' , UNIT = '" & Trim(TextBox14.Text) & "' , TYPE = '" & Trim(ComboBox6.Text) & _
            "' , OCC = '" & Trim(ComboBox8.Text) & "' , REL = '" & Trim(ComboBox5.Text) & "' , GRADE = '" & Trim(ComboBox7.Text) & _
            "' , MOBILE = '" & Trim(TextBox19.Text) & "' , Dept_Section = '" & Trim(TextBox13.Text) & _
            "' , Date_of ='" + Date1 + "', Cont_End = '" + Date2 + "' , Ext = '" & Trim(TextBox20.Text) & _
            "' , Pager = '" & Trim(TextBox21.Text) & "' , Location = '" & Trim(TextBox22.Text) & _
            "' , Fax = '" & Trim(TextBox23.Text) & "' , Mobile2 = '" & Trim(TextBox24.Text) & "' , Email = '" & Trim(TextBox25.Text) & _
            "' , PassNo = '" & Trim(TextBox11.Text) & "' , Place = '" & Trim(ComboBox9.Text) & "' , Brand = '" & Trim(TextBox6.Text) & _
            "' , Model = '" & Trim(TextBox10.Text) & "' , PlateLet = '" & Trim(TextBox28.Text) & "' , PlateNo = '" & Trim(TextBox27.Text) & _
            "' , MakeYear = '" & Trim(TextBox26.Text) & "' , Color = '" & Trim(TextBox29.Text) & "' , UnitAllowed = '" & Trim(TextBox12.Text) & _
            "' , Rec_Date = '" + Date4 + "' , Exp_Date = '" + Date3 + "'  WHERE Employee ='" & n & "'"

In addition do I have to make diagram (relationship), if yes what is it for ?

because i noticed that to make a view i don't have to make relationship to get it working, the only messing is updating and inserting and deleting from view.

---

[e-mail]

Answer 12

Hello again Adel!

I'm fine thanks I hope all is well with you too.

There are two main ways to update several tables at once using a view:

1. Create an INSTEAD OF TRIGGER on the view which will perform the manual logic required to update each of the tables separately.
Here is a blog post with a nice simple example of using INSTEAD OF TRIGGER on Views: http://sqlandme.com/2011/05/02/inserting-to-a-view-instead-of-trigger-sql-server/

2. Use a STORED PROCEDURE to perform your changes instead of the view. The procedure would be called from within the application with the relevant parameters, and it will implement the logic necessary to modify all the relevant tables.
For more info on SQL Server stored procedures: http://msdn.microsoft.com/en-us/library/aa174792(SQL.80).aspx
For more info on executing a SQL Server stored procedure from a C# application: http://support.microsoft.com/kb/320916

Also: No, you don't need to make a diagram to make a view work. The diagrams in SQL Server are used as a graphical representation only. Nothing requires them for any reason.

---

Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

Answer 13

Dear EitanBlumin , Thank you for all your efforts here to teach us SQL Server 

hope you have time to take us step by step to understand in how to Create an INSTEAD OF TRIGGER

here is my all tables (Emp) as main table, (Granada) second table, (PhoneDirectory) as third table, (CarPass) as fourth table I manged to make a view and named it (Emp_Query) it works perfectly fine with my project in Visual Studio 2010 project and I can browse between records and search

 

and here is when I tried to create TRIGGER (which I really couldn't understand :-( , I dont really know what to do with it

finnaly here is the code in my project i want to use to update the recoards in my view (Emp_Query) in order to pass the change to the source table such as (Emp , Granada , PhoneDirectory and CarPass) tables :

        Dim Savecommand As New SqlCommand
        Savecommand.Connection = newconnection
        Savecommand.CommandText = "UPDATE Emp_Query SET Employee = '" & Trim(TextBox1.Text) & _
            "' , SEX = '" & Trim(ComboBox2.Text) & "' , Cost = '" & Trim(TextBox4.Text) & "' , Department = '" & Trim(TextBox5.Text) & _
            "' , Position_Title = '" & Trim(TextBox7.Text) & "', Nat = '" & Trim(ComboBox1.Text) & "', Cat = '" & Trim(ComboBox3.Text) & _
            "' , Housing = '" & Trim(ComboBox4.Text) & "' , UNIT = '" & Trim(TextBox14.Text) & "' , TYPE = '" & Trim(ComboBox6.Text) & _
            "' , OCC = '" & Trim(ComboBox8.Text) & "' , REL = '" & Trim(ComboBox5.Text) & "' , GRADE = '" & Trim(ComboBox7.Text) & _
            "' , MOBILE = '" & Trim(TextBox19.Text) & "' , Dept_Section = '" & Trim(TextBox13.Text) & _
            "' , Date_of ='" + Date1 + "', Cont_End = '" + Date2 + "' , Ext = '" & Trim(TextBox20.Text) & _
            "' , Pager = '" & Trim(TextBox21.Text) & "' , Location = '" & Trim(TextBox22.Text) & _
            "' , Fax = '" & Trim(TextBox23.Text) & "' , Mobile2 = '" & Trim(TextBox24.Text) & "' , Email = '" & Trim(TextBox25.Text) & _
            "' , PassNo = '" & Trim(TextBox11.Text) & "' , Place = '" & Trim(ComboBox9.Text) & "' , Brand = '" & Trim(TextBox6.Text) & _
            "' , Model = '" & Trim(TextBox10.Text) & "' , PlateLet = '" & Trim(TextBox28.Text) & "' , PlateNo = '" & Trim(TextBox27.Text) & _
            "' , MakeYear = '" & Trim(TextBox26.Text) & "' , Color = '" & Trim(TextBox29.Text) & "' , UnitAllowed = '" & Trim(TextBox12.Text) & _
            "' , Rec_Date = '" + Date4 + "' , Exp_Date = '" + Date3 + "'  WHERE Employee ='" & n & "'"
        newconnection.Open()
        Savecommand.ExecuteNonQuery()
        newconnection.Close()
        MsgBox("Editing and Saving currant record was successfully done.")
        Me.Close()
        main.Show()

wish you can guide in how to do that ... and i will apply it to my SQL server and my project to make it easy for end user to update the records throught my VB project.

Best regards

---

[e-mail]

Answer 14

Hello Adel,

It's my pleasure helping you, however there's no need for me to take you step by step when there's already so much material available on the internet.

The link I gave you above provides a sort of step by step guide on creating an INSTEAD OF INSERT trigger: http://sqlandme.com/2011/05/02/inserting-to-a-view-instead-of-trigger-sql-server/

Please read through it to understand how to create such a trigger.

Ignore the "CREATE TRIGGER" option in the Management Studio context menu because it opens a "template" which can be confusing for new users.
First, study the syntax using regular (manual) means.

In short: A trigger is almost the same as a stored procedure, except it automatically executes upon a DML operation on the table. Instead of parameters, it can access in-memory tables called "INSERTED" and "DELETED" which receive the exact structure of the overlaying table (or view in our case).
Using these in-memory tables you can access the records that were inserted, the records that were deleted, and the records that were updated (which put the old values in the "DELETED" table and the new values in the "INSERTED" table).

In your case, you can use the "INSERTED" table to insert records into each of the 3 separate tables.
As I said, please read the article provided above for more info.

Good luck and let me know if you need more help.

P.S.
Here is another useful article on creating INSTEAD OF TRIGGERS on Views: http://sqlserverpedia.com/blog/sql-server-bloggers/instead-of-triggers-on-views/

---

Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

Answer 15

Dear EitanBlumin , I appreciate your presence here

:-( I know i wont be able to use INSTEAD OF TRIGGERS by my self alone here , I am sure the example you added is great for those who have experiences using SQL (but not for me as a beginner) .... :-(

here is a link for my project in the form Edit/Insert/Delete I really don't know how to do what you post above (I am new to SQL server) and I have little knowledge in VB which i used to build my project with Access and now i want to move it to use SQL Server ... my problem here with Edit/Insert/Delete .

I decide to upload my project hoping that you have some time to go through it and tell me how to change my code in order to use INSTEAD OF TRIGGERS ... am good if you guide me to the first steps ......

http://www.mediafire.com/?91lyccrog1nku2w

wish you have a time to explain to us here how can edit my code in order to communicate with SQL to update/insert/delete records.

Hope i am not asking too much.

---

[e-mail]

Answer 16

Hello Adel,

I'll do my best to help you when I have the time.

In the meantime, could you please post the create scripts for the 3 tables + the view?

Thanks

---

Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

Answer 17

Dear EitanBlumin ,

Here is the four tables i used to create the view (Emp, Granada , PhoneDirectory and CarPass were created by importing from Microsoft Access) I hope this pic help in giving the information you are looking from

this is the script of the view 

SELECT     dbo.Emp.Badge, dbo.Emp.Employee, dbo.Emp.Cost, dbo.Emp.Department, dbo.Emp.Dept_Section, dbo.Emp.Position_Title, dbo.Emp.Cat, dbo.Emp.SEX, dbo.Emp.Nat, 
                      dbo.Emp.Date_of, dbo.Emp.Cont_End, dbo.Emp.Pic, dbo.Granada.Housing, dbo.Granada.UNIT, dbo.Granada.TYPE, dbo.Granada.OCC, dbo.Granada.REL, 
                      dbo.Granada.GRADE, dbo.Granada.MOBILE, dbo.PhoneDirectory.Mobile2, dbo.PhoneDirectory.Ext, dbo.PhoneDirectory.Fax, dbo.PhoneDirectory.Location, 
                      dbo.PhoneDirectory.Pager, dbo.PhoneDirectory.Email, dbo.CarPass.PassNo, dbo.CarPass.Place, dbo.CarPass.Brand, dbo.CarPass.Model, dbo.CarPass.PlateLet, 
                      dbo.CarPass.PlateNo, dbo.CarPass.MakeYear, dbo.CarPass.Color, dbo.CarPass.UnitAllowed, dbo.CarPass.Rec_Date, dbo.CarPass.Exp_Date
FROM         dbo.Emp LEFT OUTER JOIN
                      dbo.PhoneDirectory ON dbo.Emp.Badge = dbo.PhoneDirectory.Badge FULL OUTER JOIN
                      dbo.Granada ON dbo.Emp.Badge = dbo.Granada.Badge FULL OUTER JOIN
                      dbo.CarPass ON dbo.Emp.Badge = dbo.CarPass.Badge

I have been studying the sample you sent me 

http://sqlandme.com/2011/05/02/inserting-to-a-view-instead-of-trigger-sql-server/

I couldn't understand whet ( I ) stand for! 

I wish if you explain to us here where we should place the script of creating INSTEAD OF TRIGGERS ? and what about the code in my VB project (post it above) script statement for UPDATING 

"UPDATE Emp_Query SET Employee = ...........

do I need to change it or add anything after creating  INSTEAD OF TRIGGERS !

Highly appreciated your help every effort here from your side help will help all MSDN users

Best regards

---

[e-mail]

Answer 18

I tried changing the update statement and use ( GO ) and add each table alone like this (without creating  INSTEAD OF TRIGGERS )

  Savecommand.CommandText = "UPDATE Emp SET Employee = '" & Trim(TextBox1.Text) & "' , Cost = '" & Trim(TextBox4.Text) & "' , Department = '" & Trim(TextBox5.Text) & "' , Dept_Section = '" & Trim(TextBox13.Text) & "' , Position_Title = '" & Trim(TextBox7.Text) & "', Cat = '" & Trim(ComboBox3.Text) & "' , SEX = '" & Trim(ComboBox2.Text) & "', Nat = '" & Trim(ComboBox1.Text) & "' , Date_of ='" + Date1 + "', Cont_End = '" + Date2 + "' WHERE Badge ='" & n & _
            "' GO UPDATE Granada SET Housing = '" & Trim(ComboBox4.Text) & "' , UNIT = '" & Trim(TextBox14.Text) & "' , TYPE = '" & Trim(ComboBox6.Text) & "' , OCC = '" & Trim(ComboBox8.Text) & "' , REL = '" & Trim(ComboBox5.Text) & "' , GRADE = '" & Trim(ComboBox7.Text) & "' , MOBILE = '" & Trim(TextBox19.Text) & "' WHERE Badge ='" & n & _
            "' GO UPDATE PhoneDirectory SET Mobile2 = '" & Trim(TextBox24.Text) & "' , Ext = '" & Trim(TextBox20.Text) & "' , Fax = '" & Trim(TextBox23.Text) & "' , Location = '" & Trim(TextBox22.Text) & "' , Pager = '" & Trim(TextBox21.Text) & "' , Email = '" & Trim(TextBox25.Text) & "' WHERE Badge ='" & n & _
            "' GO UPDATE CarPass SET PassNo = '" & Trim(TextBox11.Text) & "' , Place = '" & Trim(ComboBox9.Text) & "' , Brand = '" & Trim(TextBox6.Text) & "' , Model = '" & Trim(TextBox10.Text) & "' , PlateLet = '" & Trim(TextBox28.Text) & "' , PlateNo = '" & Trim(TextBox27.Text) & "' , MakeYear = '" & Trim(TextBox26.Text) & "' , Color = '" & Trim(TextBox29.Text) & "' , UnitAllowed = '" & Trim(TextBox12.Text) & "' , Rec_Date = '" + Date4 + "' , Exp_Date = '" + Date3 + "' WHERE Badge ='" & n & "'"

and i got this error

 

---

[e-mail]

Answer 19

Hello again Adel,

"GO" is not a real SQL or TSQL command.

It is a keyword identified by SQL Server Management Studio as a divider between batches. It is not recognized in other programs.

However, you CAN use the semicolon character ";" to separate statements.

---

Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

Answer 20

Dear EitanBlumin , YOU ARE THE BEST EVER

it is working perfectly when I want to edit  (without creating  INSTEAD OF TRIGGERS) i use this with my project to edit

        Savecommand.CommandText = "UPDATE Emp SET Employee = '" & Trim(TextBox1.Text) & "' , Cost = '" & Trim(TextBox4.Text) & "' , Department = '" & Trim(TextBox5.Text) & "' , Dept_Section = '" & Trim(TextBox13.Text) & "' , Position_Title = '" & Trim(TextBox7.Text) & "', Cat = '" & Trim(ComboBox3.Text) & "' , SEX = '" & Trim(ComboBox2.Text) & "', Nat = '" & Trim(ComboBox1.Text) & "' , Date_of ='" + Date1 + "', Cont_End = '" + Date2 + "' WHERE Badge ='" & n & _
            "' ; UPDATE Granada SET Housing = '" & Trim(ComboBox4.Text) & "' , UNIT = '" & Trim(TextBox14.Text) & "' , TYPE = '" & Trim(ComboBox6.Text) & "' , OCC = '" & Trim(ComboBox8.Text) & "' , REL = '" & Trim(ComboBox5.Text) & "' , GRADE = '" & Trim(ComboBox7.Text) & "' , MOBILE = '" & Trim(TextBox19.Text) & "' WHERE Badge ='" & n & _
            "' ; UPDATE PhoneDirectory SET Mobile2 = '" & Trim(TextBox24.Text) & "' , Ext = '" & Trim(TextBox20.Text) & "' , Fax = '" & Trim(TextBox23.Text) & "' , Location = '" & Trim(TextBox22.Text) & "' , Pager = '" & Trim(TextBox21.Text) & "' , Email = '" & Trim(TextBox25.Text) & "' WHERE Badge ='" & n & _
            "' ; UPDATE CarPass SET PassNo = '" & Trim(TextBox11.Text) & "' , Place = '" & Trim(ComboBox9.Text) & "' , Brand = '" & Trim(TextBox6.Text) & "' , Model = '" & Trim(TextBox10.Text) & "' , PlateLet = '" & Trim(TextBox28.Text) & "' , PlateNo = '" & Trim(TextBox27.Text) & "' , MakeYear = '" & Trim(TextBox26.Text) & "' , Color = '" & Trim(TextBox29.Text) & "' , UnitAllowed = '" & Trim(TextBox12.Text) & "' , Rec_Date = '" + Date4 + "' , Exp_Date = '" + Date3 + "' WHERE Badge ='" & n & "'"

I will try also to edit (Insert and Delete) forms in VB and i will tell you about 

Thank you soooooo mucccccch

Still didn't learn how to use (creating  INSTEAD OF TRIGGERS) :-( , in order to use the right way of changing records in view.

---

[e-mail]

Answer 21

Excellent, Adel.

I'm glad I was able to help.

However, please take note that the method you're using to build your SQL commands is extremely susceptible to SQL Injection attacks.

For example, if the user would include an apostrophe ( ' ) in one of the fields, it would ruin the SQL command.

As I said before, I recommend implementing your SQL commands in a stored procedure. This way, you'll be able to pass your form values using strongly-typed parameters for the procedure, instead of concatenating the values as part of the batch.

For more info on executing a SQL Server stored procedure from VB.NET visit here: http://msdn.microsoft.com/en-us/library/yy6y35y8(VS.71).aspx

When I'll have the spare time in the coming days, I'll write you that INSTEAD OF TRIGGER you asked for.

---

Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

Answer 22

Dear EitanBlumin , without people like you science will never develop

Even thought that I know what i did it is not the right way i want you to see what i did and i am hoping that in the incoming days i will be able to learn (Stored procedure) ....... this what I change to my project in vb to change to insert/delete

this is insert

      Savecommand.CommandText = "UPDATE Emp SET Employee = '" & Trim(TextBox1.Text) & "' , Cost = '" & Trim(TextBox4.Text) & "' , Department = '" & Trim(TextBox5.Text) & "' , Dept_Section = '" & Trim(TextBox13.Text) & "' , Position_Title = '" & Trim(TextBox7.Text) & "', Cat = '" & Trim(ComboBox3.Text) & "' , SEX = '" & Trim(ComboBox2.Text) & "', Nat = '" & Trim(ComboBox1.Text) & "' , Date_of ='" + Date1 + "', Cont_End = '" + Date2 + "' WHERE Badge ='" & n & _
            "' ; UPDATE Granada SET Housing = '" & Trim(ComboBox4.Text) & "' , UNIT = '" & Trim(TextBox14.Text) & "' , TYPE = '" & Trim(ComboBox6.Text) & "' , OCC = '" & Trim(ComboBox8.Text) & "' , REL = '" & Trim(ComboBox5.Text) & "' , GRADE = '" & Trim(ComboBox7.Text) & "' , MOBILE = '" & Trim(TextBox19.Text) & "' WHERE Badge ='" & n & _
            "' ; UPDATE PhoneDirectory SET Mobile2 = '" & Trim(TextBox24.Text) & "' , Ext = '" & Trim(TextBox20.Text) & "' , Fax = '" & Trim(TextBox23.Text) & "' , Location = '" & Trim(TextBox22.Text) & "' , Pager = '" & Trim(TextBox21.Text) & "' , Email = '" & Trim(TextBox25.Text) & "' WHERE Badge ='" & n & _
            "' ; UPDATE CarPass SET PassNo = '" & Trim(TextBox11.Text) & "' , Place = '" & Trim(ComboBox9.Text) & "' , Brand = '" & Trim(TextBox6.Text) & "' , Model = '" & Trim(TextBox10.Text) & "' , PlateLet = '" & Trim(TextBox28.Text) & "' , PlateNo = '" & Trim(TextBox27.Text) & "' , MakeYear = '" & Trim(TextBox26.Text) & "' , Color = '" & Trim(TextBox29.Text) & "' , UnitAllowed = '" & Trim(TextBox12.Text) & "' , Rec_Date = '" + Date4 + "' , Exp_Date = '" + Date3 + "' WHERE Badge ='" & n & "'"

and this is delete

     Savecommand.CommandText = "DELETE FROM Emp WHERE Badge ='" & Trim(TextBox2.Text) & _
                              "' ; DELETE FROM Granada WHERE Badge ='" & Trim(TextBox2.Text) & _
                              "' ; DELETE FROM PhoneDirectory WHERE Badge ='" & Trim(TextBox2.Text) & _
                              "' ; DELETE FROM CarPass WHERE Badge ='" & Trim(TextBox2.Text) & "'"

waiting for direction for the next lesson

best regards

---

[e-mail]

Answer 23

Hello Adel,

I started writing the INSTEAD OF TRIGGER but then I noticed a far more fundamental problem in your design that I missed before.

It seems like all of your 4 tables have the same Primary Key (Badge).

This means that the tables are all connected one-to-one with the Emp table.
In other words, for each record in Emp, there can be no more than one record in Granada, one record in PhoneDirectory and one record in CarPass. There's actually no point to separating the data to 4 different tables. You might as well put all the fields together in the Emp table.

This, in effect, is an un-normalized design, and it may not be the most optimal for your business requirement.

The implications are that in order to add more than one record of, for example, PhoneDirectory to the same Employee, you'll be actually forced to duplicate the Employee record to acquire a different "Badge" value. Then you'll have two records in PhoneDirectory but you'll also have two records in Emp representing the same employee.

Please look into it and make sure that these restrictions fit with your business requirement because it looks suspicious to me.

If you are not yet familiar with it, please learn more about Database Normalization to optimize your design.

I could help you with specific questions, but all in all, it seems you're missing very basic key knowledge in databases, so I'd suggest you take some basic courses or tutorials to build a stronger knowledge base. I won't be able to teach you everything through this forum.

Also, in case you didn't know, there's a "Getting Started with SQL Server" forum category available here: http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/threads

---

Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

Answer 24

Story of my database:

I get four spreadsheets from different departments as follow:

1-      Emp: (1570 records) I get it as spreadsheet from HR department, I used it as the main table.

2-      Granada: I get it also as spreadsheet from Housing Department.

3-      PhonDirectory: I get it also as spreadsheet from ID badge Security section.

4-      CarPass: I get also as it spreadsheet from Communication department.

First step:

I use Microsoft access and import the four tables as above

Then I use (Badge) for all of them as primary key, I establish relationship between them from (Emp as main table) to the other tables (One-to-One) and include all records in Emp.

Then I create a query and name it (Emp_Query) starting by all fields in Emp (with Badge filed) table as the main, Granada (all fields except Badge filed), PhonDirectory (all fields except Badge filed) and CarPass (all fields except Badge filed).

After that I export (Emp_Query) to excel sheet and divide them back again to four tables and include (Badge field) with each one of them (they will all have the same number of records (1570) but Granada will have 633 records having only Badge field only. And the others same they will have some records having only Badge field.

After that from Microsoft access I import the four tables (spreadsheets) into access.

At this stage Microsoft database is ready to be imported from SQL Server, when I have the four tables in my SQL server, I create View as you taught me (Tanks to you).

I start at Emp and I drag and drop from Emp.Badge to Granada.Badge and right click the mouse and chose (Select All rows from Emp) and I do the same for other from Emp.Badge to Granada.Badge , Emp.Badge to Phondirectory.Badge ……….

After that I save the view as (Emp_Query)  <= I do this because my project in VB select will look for (Emp_Query).

Since there are some records have empty cells from Microsoft access , SQL will show them as NULL (it will not work with me when I search for records it will allows give null , so I use this :

update Emp_Query set Housing = ''

WHERE (Housing IS NULL)

GO

update Emp_Query set UNIT= ''

WHERE (UNIT IS NULL)

GO

To remove all NULL and replace the empty (It will work perfectly with search).

The reason I make the four tables equals by number of rows (1570) because when I create view but there will be NULLs which I can't remove like above, friend told me there is a way of making SQL deal with NULL (so that I don’t have to remove null and I still be able to search with it and get a good result, if you can tell how I will not need to go through all of this process.

I forget to tell that the spreadsheet I get monthly from the same departments and I need to go with the same process again (it is too much right?!) but this is what I know so far.

I hope my explanation here is understandable

1-What I need is what I can do with SQL so that it works with NULL especially with search.

2-When I receive an updated spreadsheet from the departments is there a way to make SQL compare between the existing tables and the new tables and make the change.

Sorry it is too long I know L

Best regards 

Answer 25

Thank you EitanBlumin 

I managed to make it see below I added it to the View (Trigger) for inserting :

USE [SBAHC_DataBase]
GO
/****** Object:  Trigger [dbo].[Trig_Insert_Employee]    Script Date: 11/09/2012 21:55:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Trig_Insert_Employee]
ON [dbo].[Emp_Query]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Emp
SELECT I.Badge, I.Employee, I.Cost, I.Department, I.Dept_Section, I.Position_Title, I.Cat, I.SEX, I.Nat, I.Date_of, I.Cont_End, I.Pic
FROM INSERTED I

INSERT INTO Granada
SELECT I.Badge, I.Housing, I.UNIT, I.TYPE, I.OCC, I.REL, I.GRADE, I.MOBILE
FROM INSERTED I

INSERT INTO PhoneDirectory
SELECT I.Badge, I.Mobile2, I.Ext, I.Fax, I.Location, I.Pager, I.Email
FROM INSERTED I

INSERT INTO CarPass
SELECT I.Badge, I.PassNo, I.Place, I.Brand, I.Model, I.PlateLet, I.PlateNo, I.MakeYear, I.Color, I.UnitAllowed, I.Rec_Date, I.Exp_Date
FROM INSERTED I

END

it is working perficatlly

later i will make trigger for UPDATE/DELETE (maybe you can edit to help in UPDATE/DELETE please if ever you have a time )

best regard my friend

---

[e-mail]

Answer 26

Managed to create for Update

CREATE TRIGGER [dbo].[Trig_Update_Employee]
ON [dbo].[Emp_Query]
INSTEAD OF update
AS
BEGIN

  SET NOCOUNT ON;

update Emp
set Badge = I.Badge, Employee = I.Employee, Cost = I.Cost, Department = I.Department, Dept_Section = I.Dept_Section, Position_Title = I.Position_Title, Cat = I.Cat, SEX = I.SEX, Nat = I.Nat, Date_of = I.Date_of, Cont_End = I.Cont_End, Pic = I.Pic
FROM INSERTED I
where Emp.Badge = I.Badge

update Granada
set Badge = I.Badge, Housing = I.Housing, UNIT = I.UNIT, TYPE = I.TYPE, OCC = I.OCC, REL = I.REL, GRADE = I.GRADE, MOBILE = I.MOBILE
FROM INSERTED I
where Granada.Badge = I.Badge

update PhoneDirectory
set Badge = I.Badge, Mobile2 = I.Mobile2, Ext = I.Ext, Fax = I.Fax, Location = I.Location, Pager = I.Pager, Email = I.Email
FROM INSERTED I
where PhoneDirectory.Badge = I.Badge

update CarPass
set Badge = I.Badge, PassNo = I.PassNo, Place = I.Place, Brand = I.Brand, Model = I.Model, PlateLet = I.PlateLet, PlateNo = I.PlateNo, MakeYear = I.MakeYear, Color = I.Color, UnitAllowed = I.UnitAllowed, Rec_Date = I.Rec_Date, Exp_Date = I.Exp_Date
FROM INSERTED I
where CarPass.Badge = I.Badge

END

and become 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [Trig_Update_Employee]
ON [dbo].[Emp_Query]
INSTEAD OF update
AS
BEGIN

  SET NOCOUNT ON;

update Emp
set Badge = I.Badge, Employee = I.Employee, Cost = I.Cost, Department = I.Department, Dept_Section = I.Dept_Section, Position_Title = I.Position_Title, Cat = I.Cat, SEX = I.SEX, Nat = I.Nat, Date_of = I.Date_of, Cont_End = I.Cont_End, Pic = I.Pic
FROM INSERTED I
where Emp.Badge = I.Badge

update Granada
set Badge = I.Badge, Housing = I.Housing, UNIT = I.UNIT, TYPE = I.TYPE, OCC = I.OCC, REL = I.REL, GRADE = I.GRADE, MOBILE = I.MOBILE
FROM INSERTED I
where Granada.Badge = I.Badge

update PhoneDirectory
set Badge = I.Badge, Mobile2 = I.Mobile2, Ext = I.Ext, Fax = I.Fax, Location = I.Location, Pager = I.Pager, Email = I.Email
FROM INSERTED I
where PhoneDirectory.Badge = I.Badge

update CarPass
set Badge = I.Badge, PassNo = I.PassNo, Place = I.Place, Brand = I.Brand, Model = I.Model, PlateLet = I.PlateLet, PlateNo = I.PlateNo, MakeYear = I.MakeYear, Color = I.Color, UnitAllowed = I.UnitAllowed, Rec_Date = I.Rec_Date, Exp_Date = I.Exp_Date
FROM INSERTED I
where CarPass.Badge = I.Badge

END

Now am going to look for delete :-(

any one can help please

---

[e-mail]

Answer 27

For Delete i used this to creat trigger

CREATE TRIGGER [dbo].[Trig_Delete_Employee]
ON [dbo].[Emp_Query]
INSTEAD OF delete
AS
BEGIN

  SET NOCOUNT ON;

delete from Emp
FROM INSERTED I
where Emp.Badge = I.Badge

delete from Granada
FROM INSERTED I
where Granada.Badge = I.Badge

delete from PhoneDirectory
FROM INSERTED I
where PhoneDirectory.Badge = I.Badge

delete from CarPass
FROM INSERTED I
where CarPass.Badge = I.Badge

END

and get this

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [Trig_Delete_Employee]
ON [dbo].[Emp_Query]
INSTEAD OF delete
AS
BEGIN

  SET NOCOUNT ON;

delete from Emp
FROM INSERTED I
where Emp.Badge = I.Badge

delete from Granada
FROM INSERTED I
where Granada.Badge = I.Badge

delete from PhoneDirectory
FROM INSERTED I
where PhoneDirectory.Badge = I.Badge

delete from CarPass
FROM INSERTED I
where CarPass.Badge = I.Badge

END

but it didn't work :-( i need help here please

---

[e-mail]

Answer 28

In the DELETE trigger you don't have Inserted table. You have Deleted instead. Replace Inserted with Deleted and I assume your trigger will work (I haven't looked too close).

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 29

Fixed ..... :-)

CREATE TRIGGER [dbo].[Trig_Delete_Employee]
ON [dbo].[Emp_Query]
INSTEAD OF delete
AS
BEGIN

  SET NOCOUNT ON;

delete Emp
FROM Emp, deleted 
where Emp.Badge = deleted.Badge

delete Granada
FROM Granada, deleted
where Granada.Badge = deleted.Badge

delete PhoneDirectory
FROM PhoneDirectory, deleted 
where PhoneDirectory.Badge = deleted.Badge

delete CarPass
FROM CarPass, deleted 
where CarPass.Badge = deleted.Badge

END

best regards to ( EitanBlumin ) without his help i wont be able to solve this

---

[e-mail]

Answer 30

Thank you Naomi N

you were right ...

thanx for passing by

---

[e-mail]

Answer 31

Hi Adel,

I'm sorry I didn't have time to reply but I'm glad you managed to do it by yourself and get the help you needed from Naomi.

Keep using this forum to learn new things, and good luck!

---

Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

Answer 32

Please pass by this 

http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/35d0d6de-2c68-4b64-9027-8b1f54cd55bb

i need your help there

---

[e-mail]