Newbish question - Trying to get a table link to another table using SQL Server Management Studio 2005

colonelblue
colonelblue used Ask the Experts™
on
I have 2 tables and trying to link them up

Table1:

ID - Primary Key ( incremental)
Client_Name
Show_Image


Table 2:

ID - Primary Key
ID_Menu
Mod1
Mod2
Mod3

I have an insert  form with fields to both tables. How do I set the tables up so that in effect Table1.ID stays incremental and is linked to Table 2?

I would suppose Table1.ID would equal Table2.ID but how would that work if Table1.ID is an incremental?

Thanks in advance.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Thank you rashmi I had checked every single link.

I'm afraid to say that I am a bit more novice than that and use the GUI on SSMS2005.
Is there a simpler way to make the tables relative?
I could have just created one table for all columns instead of creating 2 and make them relative but I just thought it would make more sense to keep the columns on Table 2 in its own table according to its content.


Top Expert 2010

Commented:
Dear colonelblue:

what kind of relation ship do you want between these 2 tables

is it a one to one relationship.

i mean for every record in table1, there will be only one record in table 2

or is it going to be a one to many

like for one single record in table 1 there will be many records in table 2.

----

if you have a one to one which i guess from your question and since table 1 is incremental.

i guess you need to change the Id in table 2 to be a foreign key for the ID in table1 and primary key as well.

but incase you have one to many, just make the Id in table 2  a foreign key to the Id in table 1 and not a primary key.

to make these relationships, the easiest way is to create a database diagram and add the tables and drag and drop the ID fields, and create the relation

check image
relation-ship.png
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Author

Commented:
Hello AmmarR, thank you.

Yes it is the latter:
"but incase you have one to many, just make the Id in table 2  a foreign key to the Id in table 1 and not a primary key."

Do you know where I can find some easy steps using the GUI to do such a thing?

Just to refresh I am trying to accomplish something like this:

Web Table:
ID
Section
Colors
Logo
Menu

Menu Table
ID
Home
Contact
FAQ

The idea is to have each ID in the Web Table to have its own unique Menu Table.
So Web Table ID=1 would have a row in Menu Table with its own ID, Home, Contact and FAQ

To gather that info I will use a form with all the elements as filed boxes and then insert the ID ( auto incremental ) into Web Table and then tie and insert the menu table fields according to the Web Table ID.

It sounds like it would be simple and have seen this sort of structure many times before.

I appreciate any help.

Thank you in advance.


Top Expert 2010

Commented:
hi

i just want to make sure i understood you right

--------
Option 1

The idea is to have each ID in the Web Table to have its own unique Menu Table.
So Web Table ID=1 would have a row in Menu Table with its own ID, Home, Contact and FAQ
so in this case

for Web Table ID = 1
you have only one row in the Menu table

if the above is true then its a one to one relation ship
and you will have
Web table ID as Primary key
Menu Table ID as primary key and foreign key to MenuID field in Web table

E.g
records will be

Web Table:
ID   Section   Colors   Logo   MenuID
1    Main        yellow  lg         1

Menu Table
ID  Home  Contact  FAQ
1   yes     112123   ok
---------

on the other hand option 2

The idea is to have each ID in the Web Table to have its own unique Menu Table.
So Web Table ID=1 would have few rows in Menu Table with its own ID, Home, Contact and FAQ

for Web Table ID = 1
you have only many rows in the Menu table

if the above is true then its a one to many relation ship
and you will have
Web table ID as Primary key
Menu Table ID as foreign key to MenuID field in Web table

E.g
records will be

Web Table:
ID   Section   Colors   Logo   MenuID
1    Main        yellow  lg         1

Menu Table
ID  Home  Contact  FAQ
1   yes     112123   ok
1   no        737427 ok
1  yes    88347   ok
---------------

no for GUI tools to create relation ships between 2 tables
check these links

Basic
http://dotnetslackers.com/Community/blogs/bmdayal/archive/2010/06/05/creating-a-table-relationship-using-sql-server-2008-management-studio.aspx

Advance and in depth
http://www.mssqltips.com/tip.asp?tip=1816



hope its clear now and let me know if you need any help

Regards

Author

Commented:
Hello Ammar, thank you that was VERY concise indeed.

It is option 1.

But how do I get Web Table:Menu ID to match Menu Table ID as a foreign key?
In a simple form at submit will I have to insert the field into 2 places  ( Web Table:Menu ID and Menu Table: ID )  or will Menu Table:ID automatically insert its value into Web Table:ID since it is a foreign key? Furthermore what if Web Table ID is incremental?

Either way if I may say, I have no worries changing things around except for the fact that Web Table ID must be incremental but as long as I can make the 2 tables "connected" and inserted into accordingly when the user submits the form.

Thank you again for your patience, generosity and expertise.

Top Expert 2010

Commented:
hi and you are most welcome colonelblue:

see below answers to your questions


1 . But how do I get Web Table:Menu ID to match Menu Table ID as a foreign key?

check image below and the script i added in the code snippet

-----
2. In a simple form at submit will I have to insert the field into 2 places

yes you will have to insert in both
----
3. What if  Web Table ID is incremental

use the following insert script to add
----
INSERT INTO [Web] ([Section] ,[Colors] ,[Logo] ,[Menu])
     VALUES ('s','c','m','l')

INSERT INTO [Menu] ([ID],[Home],[Contact],[FAQ])
     VALUES(@@IDENTITY,'home1','contact1','faq1')

-- @@IDENTITY ,its the newly added incremental id
----


explanation

i made 2 tables Web and Menu and i made the Id in the web table as primary key and incremental
in the menu table i made the id primary key but not incremental
i made a one to one relation ship between Web and menu

now for every record you have in web, you can only have one record for it in menu.

you cannot create a record in menu with an id that is not available in the web table.

---

if you need any more explanation, dont hesitate to ask

all the best

/* create the two tables and the one to one relations ship*/
CREATE TABLE [dbo].[Web](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Section] [nchar](10) NULL,
	[Colors] [nchar](10) NULL,
	[Logo] [nchar](10) NULL,
	[Menu] [nchar](10) NULL,
 CONSTRAINT [PK_Web] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Menu]    Script Date: 10/27/2010 16:23:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Menu](
	[ID] [int] NOT NULL,
	[Home] [nchar](10) NULL,
	[Contact] [nchar](10) NULL,
	[FAQ] [nchar](10) NULL,
 CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  ForeignKey [FK_Menu_Web]    Script Date: 10/27/2010 16:23:11 ******/
ALTER TABLE [dbo].[Menu]  WITH CHECK ADD  CONSTRAINT [FK_Menu_Web] FOREIGN KEY([ID])
REFERENCES [dbo].[Web] ([ID])
GO
ALTER TABLE [dbo].[Menu] CHECK CONSTRAINT [FK_Menu_Web]
GO

Open in new window

Web-menu-1-to-1.png

Author

Commented:
Ammar, could you be any nicer?? Wow. :)

I promise you I am not slow in the head but I do admit that getting a grasp of this is taking me some time.

I know basic SQL, maybe a bit more but I have to mention that I used a WYSIWYG such as Dreamweaver to create the statement and I am having some difficulty working your detailed excellent syntax into what I have.
Would it be OK if I showed you what I had generated?

Regards.
Top Expert 2010

Commented:
Sure

let me see it

Author

Commented:
Hello AmmarR !
Thank you again for taking the time to look at this.
I am hoping that making sense of the exact code I am actually working on with what you have been so graciously been instructing me with will finally make it clear to me and make sense.

What I have provided is a page that runs in asp.
It is a simple form that involves entries with fields I would hope would insert into two tables when submitted.

dbo.ClientTable and
dbo.RepTable

dbo.ClientTable
ID ( primary key )
Client_Name
Short_Name
Welcome
rep_ID

dbo.RepTable
ID ( primary key )
rep_ID
Rep
greeting
contact_info

I am trying to accomplish what you had mentioned  above as Option 1.
I can surely restructure the fields as you see fit to make the desired effect.

"Option 1"

The idea is to have each ID in the Web Table to have its own unique Menu Table.
So Web Table ID=1 would have a row in Menu Table with its own ID, Home, Contact and FAQ
so in this case

for Web Table ID = 1
you have only one row in the Menu table

if the above is true then its a one to one relation ship
and you will have
Web table ID as Primary key
Menu Table ID as primary key and foreign key to MenuID field in Web table

 
Attached is a snapshot of the simple form and the page with asp page with the SQL and form data.

I SINCERELY appreciate any of your help and or insight.

Regards.



Form.jpg
LLMM.txt
Top Expert 2010

Commented:
ok

i haven't worked with old asp for few years, but i remember the concepts very well.  it will take me some time to figure it out.

any way can you attached the include file as well
<!--#include virtual="/Connections/ConnLLMMProp.asp" -->
--

let me tell you what i understood so far so that i can get you the right answer.

you are inserting data in dbo.ClientTable and  in dbo.RepTable

but since the ClientTable.ID is an auto number, you need to get it after inserting data into ClientTable, and use it to insert it as rep_ID in dbo.RepTable table

if that so, just post me the connection include file and i will change your asp code accordingly.

one more question i have, why do you have an ID (primary key) in your dbo.RepTable table ???

Regards

Author

Commented:
Hello Ammar, yes you are right on!  ( as usual ) :)

I have included the connection.asp

As for the ID ( primary key ) in dbo.RepTable, obviously a show of my still needing to grasp what needs to involved when having 2 tables work with another as such.

Again, I am extremely grateful for your patience and generosity.

Author

Commented:
The attachment is here.
It would not let me include as an asp page so made it into a txt file.

ConnLLMMProp.txt
Top Expert 2010

Commented:
Dear i did few changes to the asp file that you sent

LLMM.txt

check the attached and its now working the way you want

check the changes

i added an output parameter.


LLMM.txt

Author

Commented:
Hello AmmarR, thank you again.

I am getting an error:
 
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'  
[Microsoft][ODBC SQL Server Driver][SQL  Server]Incorrect syntax near '='.  
/LLMM.asp, line 72

71 MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("OutputParam1", 3, 4, 2) ' output Inserted record
 72   MM_editCmd.Execute
73   InsertedID = MM_editCmd.Parameters("OutputParam1").Value
 74  MM_editCmd.ActiveConnection.Close

I have tried for several hours trying to figure it out.

But I did get to figure out how to insert into 2 tables at the same time with one form.
Please see included file. The only thing I need and is missing is how can I get the ID of the inserted record in SQL     database. After doing a lot of research, I know it involves @@IDENTITY but I do not know how to add that ( presumably RSClients.ID into my code so that it inserts it into the second table ( dbo.RepTable ) as "Rep_ID".


if I could learn how to add that value, it would be like teaching me how to fish!

AmmarR, thank you again.

LLMM2.txt
Top Expert 2010
Commented:
Sure colonelblue:

Ok

first of all, i will start with the tables (since you decided to go with option 1, that is for every Id in ClientTable you want only one Id in RepTable)

this way you need to only have the following fields

dbo.ClientTable
ID ( primary key) -- auto incremental
Client_Name
Short_Name
Welcome

dbo.RepTable
Rep_ID ( primary key )
Rep
greeting
contact_info

Were RepTable.Rep_ID  is a primary key to Reptable and is a foreign Key to ClientTable.ID

you dont need the column Rep_ID in the clienttable, its useless.

-----

2. in the coding

i changed the line that inserts value into clienttable

MM_editCmd.CommandText = "INSERT INTO dbo.ClientTable (Client_Name, Short_Name, Welcome) VALUES (?, ?, ?) ;"

to

MM_editCmd.CommandText = "INSERT INTO dbo.ClientTable (Client_Name, Short_Name, Welcome) VALUES (?, ?, ?) ; set ? = @@identity"

were i added set ? = @@identity

this will create an output parameter that holds the value of the incremented ID

MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("OutputParam1", 3, 4, 2)

the syntax for adding parameters is

commandobject.CreateParameter (Name, Type, Direction, Size, Value)

check this link for values of Type, direction, size

---

now after executing the command, i take in the output value and insert it into an asp variable

Dim InsertedID
MM_editCmd.Execute
InsertedID = MM_editCmd.Parameters("OutputParam1").Value

--

now the variable
InsertedID holds the incremented ID.

so i can insert this value into the Reptable.

first i change the commandtext to

MM_editCmd.CommandText = "INSERT INTO dbo.RepTable (rep_id,Rep, greeting, contact_info) VALUES (?,?, ?, ?)"

and i use these parameters

MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param0", 3, 1, 4, InsertedID) ' adInteger
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 201, 1, 50, Request.Form("Rep_Name")) ' adLongVarChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 201, 1, 50, Request.Form("Rep_Greeting")) ' adLongVarChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 201, 1, 50, Request.Form("Rep_Contact_Info")) ' adLongVarChar

--

the only difference from your code, is i added the "param0", which takes in the InsertedID variable
--

this way the ID from clienttable is inserted into the Rep_ID of reptable.

--

so this explains the changes i made to your original code, if you need any explanation to any part of the code other than what i mentioned, post it
--


now about the rror you are getting

its probably because of the line 66

MM_editCmd.CommandText = "INSERT INTO dbo.ClientTable (Client_Name, Short_Name, Welcome) VALUES (?, ?, ?) ; set ? = @@identity"

i think its because of  ; set ? = @@identity..

may be your driver isnt allowing such syntax.

what SQL server are you using

or try changing your connection string to

MM_ConnLLMMProp_STRING = "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=databasename;userid=user;password=pwdI"

i hope you can fish now.
Top Expert 2010

Commented:
i forgot to post the link

the syntax for adding parameters

http://www.devguru.com/technologies/ado/quickref/command_createparameter.html

Author

Commented:
Hello AmmarR. Thank you yet again.
Not the best luck here. :(

I initialluy was getting an error about an "=" sign.


Then I changed the connection string of course with the right credentials.
MM_ConnLLMMProp_STRING = "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=databasename;userid=user;password=pwdI"

 But now I get this error:
Microsoft OLE DB Provider for SQL Server error  '80040e14'  
Must declare the scalar variable "@@".
Top Expert 2010

Commented:
are you using sql 2000 and if so which service pack

Author

Commented:
Hello AmmarR. I'm so sorry for all the trouble I certainly did not mean to do so.
I understand if I had exhausted your help.

I am using SQL 2005

I use MSSM Studio version 9

Thank you.
Top Expert 2010

Commented:
dear colonelblue:

its no trouble at all, and dont worry about this problem, we will get it sort out.

i dont know what made me think it was sql 2000,

just give me some time i will send you the script that should work with sql 2005, and if you get any trouble with it, post back, without hesitation,

will will fix it.
Top Expert 2010

Commented:
ok i did tests on sql 2005

there is not change in the LLMM.txt (asp page)

its only the connection string provider that i changed and it worked on sql 2005

so change your connection string to

MM_ConnLLMMProp_STRING = "Provider=SQLNCLI;Data Source=servername;Initial Catalog=databasename;userid=user;password=pwdI"

try it and let me know

Top Expert 2010

Commented:
Hi colonelblue:

did it work out or not yet, i tried it on 2 machines and it worked for me,

let me know if it hasn't worked yet.

Regards

Author

Commented:
Hello AmmarR. I;m SO sorry for the late reply.
Yes that DID work.
I had to remove a "MM_editCmd.Execute" after the INSERT @@ and it worked. :)!!
Thank you so much!

Curious, effectively If I followed the steps and principles you have taught me utilizing the  "set ? = @@identity " , if in the future I needed to , I can just add another insert to a third table ius that right?


AmmarR, thank you again. I am SO SO grateful and thankful.
May the world be as good to you! ;)
Top Expert 2010

Commented:
Sure as long as you store the output value in a variable like we did in this example we stored the @@identity in a variable called InsertedID

so you can use it every where.

its my pleasure

wish you all the best and your most welcome.

Regards

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial