Can't Insert with Passing Parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Can't Insert with Passing Parameter

Author

Topic

ddtopgun
Starting Member

26 Posts

Posted - 2014-06-03 : 03:44:20

can anyone to help me how to make passing parameter namefactory and namedepartemen..

so if we input name of factory and name of departemen that insert to the table is idfactory and iddepartemen


ALTER PROCEDURE [dbo].[sp_isudpc]
@nmKomputer varchar(15)=NULL,
@nmUser varchar(40)=NULL,
@Email varchar(50)=NULL,
@Jenis varchar(10)=NULL,
@Procesor varchar(30)=NULL,
@Frek varchar(5)=NULL,
@Memory varchar(10)=NULL,
@Hdd varchar(10)=NULL,
@Monitor varchar(5)=NULL,
@OS varchar(15)=NULL,
@Software varchar(50)=NULL,
@Statuss char(2)=NULL,
@nmFactory char(3)=NULL,
@nmDepartemen varchar(30)=NULL,
@StatementType varchar(20)

AS	
DECLARE
	@idPC UNIQUEIDENTIFIER	
		
BEGIN	
	IF @StatementType ='Insert' 
	BEGIN
		SET NOCOUNT ON;
		INSERT INTO [dbo].[PC](Factory.idFactory,Departemen.idDepartemen,nmKomputer,nmUser,Email,Jenis,Procesor,Frek,Memory,Hdd,Monitor,OS,Software,Statuss)
		/*	VALUES ((SELECT idFactory FROM Factory INNER JOIN Factory ON  Factory.nmFactory=@nmFactory),
					(SELECT idDepartemen FROM Departemen INNER JOIN Departemen ON Departemen.nmDepartemen=@nmDepartemen),
					UPPER(@nmKomputer),UPPER(@nmUser),@Jenis,@Procesor,@Memory,@Hdd,@Monitor,@OS,@Software,@Statuss)		
		*/
		SELECT
			--idPC,
			Factory.idFactory, 
			Departemen.idDepartemen,
			nmKomputer,
			nmUser,
			Email,
			Jenis,
			Procesor,
			Frek,
			Memory,
			Hdd,
			Monitor,
			OS,
			Software,
			Statuss			
		FROM PC
		INNER JOIN Factory ON Factory.nmFactory=@nmFactory
		INNER JOIN Departemen ON Departemen.nmDepartemen=@nmDepartemen
			
		--SELECT * FROM PC 
	END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-03 : 12:44:07

You want to insert into two tables?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-03 : 13:14:57

What is the relationship between PC and Factory? And PC and Departemen? You need to join on those columns and then your variables go in the WHERE clause.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ddtopgun
Starting Member

26 Posts

Posted - 2014-06-03 : 20:47:57

quote:
Originally posted by Lamprey

You want to insert into two tables?

No, i want to insert to one table to TPC with SP but it's not inserted data to table TPC with above SP

TPC(idPC,idFactory,idDepartemen,nmKomputer,nmUser,Email,Procesor,Frek,Memory,Hdd,OS,Software,Statuss)
TFactory(idFactory,nmFactory)
TDepartemen(idDepartemen,nmDepartemen)

ddtopgun
Starting Member

26 Posts

Posted - 2014-06-03 : 20:52:55

quote:
Originally posted by tkizer

What is the relationship between PC and Factory? And PC and Departemen? You need to join on those columns and then your variables go in the WHERE clause.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

i want to input to TPC with nmfactory and nmdepartemen so if the user input nmfactory and nmdepartemen the sp insert idfactory and ideparteme to tpc..

how to insert to table TPC with seacrh id by name in other table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-03 : 20:55:15

You didn't answer my question. Need to know the relationship between the tables to help you. Show us the CREATE TABLE statements for the tables plus any foreign key constraints.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ddtopgun
Starting Member

26 Posts

Posted - 2014-06-03 : 21:08:25

quote:
Originally posted by tkizer

You didn't answer my question. Need to know the relationship between the tables to help you. Show us the CREATE TABLE statements for the tables plus any foreign key constraints.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sorry, pls see code for create table each tabel
this for TFactory



CREATE TABLE [dbo].[Factory](
	[idFactory] [uniqueidentifier] NOT NULL,
	[nmFactory] [char](3) NULL,
 CONSTRAINT [PK_Factory] PRIMARY KEY CLUSTERED 
(
	[idFactory] 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

this for Tdepartemen


CREATE TABLE [dbo].[Departemen](
	[idDepartemen] [uniqueidentifier] NOT NULL,
	[nmDepartemen] [varchar](30) NULL,
 CONSTRAINT [PK_Departemen] PRIMARY KEY CLUSTERED 
(
	[idDepartemen] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

and this for TPC


CREATE TABLE [dbo].[PC](
	[idPC] [uniqueidentifier] NOT NULL,
	[idFactory] [uniqueidentifier] NOT NULL,
	[idDepartemen] [uniqueidentifier] NOT NULL,
	[nmKomputer] [varchar](50) NULL,
	[nmUser] [varchar](100) NULL,
	[Email] [varchar](50) NULL,
	[Jenis] [varchar](10) NULL,
	[Procesor] [varchar](30) NULL,
	[Frek] [varchar](5) NULL,
	[Memory] [varchar](10) NULL,
	[Hdd] [varchar](10) NULL,
	[Monitor] [varchar](5) NULL,
	[OS] [varchar](15) NULL,
	[Software] [varchar](50) NULL,
	[Statuss] [char](2) NULL,
 CONSTRAINT [PK_PC] PRIMARY KEY CLUSTERED 
(
	[idPC] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PC]  WITH CHECK ADD  CONSTRAINT [FK_PC_Departemen] FOREIGN KEY([idDepartemen])
REFERENCES [dbo].[Departemen] ([idDepartemen])
ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[PC] CHECK CONSTRAINT [FK_PC_Departemen]
GO

ALTER TABLE [dbo].[PC]  WITH CHECK ADD  CONSTRAINT [FK_PC_Factory] FOREIGN KEY([idFactory])
REFERENCES [dbo].[Factory] ([idFactory])
ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[PC] CHECK CONSTRAINT [FK_PC_Factory]
GO

ALTER TABLE [dbo].[PC] ADD  CONSTRAINT [DF__PC__idPC__12FDD1B2]  DEFAULT (newsequentialid()) FOR [idPC]
GO

ddtopgun
Starting Member

26 Posts

Posted - 2014-06-03 : 23:43:10

quote:
Originally posted by ddtopgun

[quote]Originally posted by tkizer

You didn't answer my question. Need to know the relationship between the tables to help you. Show us the CREATE TABLE statements for the tables plus any foreign key constraints.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

case closed solved..
thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources