help im a beginner... insert - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

help im a beginner... insert

Author

Topic

mercury76
Starting Member

7 Posts

Posted - 2007-08-17 : 01:53:39

Hi guys,

I am new to database programming... i know the answer is somewhere in the past forums and i unfortunately i can't locate it... I have this problem i hope you can help me.

I am trying to insert values into table hosp_wareitem and one of it is the itemid where in i would like to get the item id of an existing medicine... (i hope you can understand me guys) here is my query.. right now im puzzled and i know the answer is already there.. i just cant see it...

set xact_abort on
declare @id as integer
begin transaction
select @id (select medicines_id from
hosp_medicines where medicines_id = medicines_id)
insert into Hosp_WareItem
(ItemID,
Department_ID,
SalesGL_Code,
COSGL_Code,
InventoryGL_Code,
ExpenseGL_Code,
ReadersFeeGL_Code,
CreateBy,
UpdateBy,
DeleteBy,
CreateDate,
UpdateDate,
DeleteDate)
values
(@ID,
'1',
'AAA-111',
'AAA-000',
'AAA-112',
'',
'',
'0',
'',
'',
'1/1/1900 12:00:00 AM',
'1/1/1900 12:00:00 AM',
'')
Update Hosp_Counter set Counter_ID = @ID
where Table_Description = 'wareitem'
commit transaction

it returns an error

(1 row(s) affected)

(24 row(s) affected)

Server: Msg 515, Level 16, State 2, Line 6
Cannot insert the value NULL into column 'ItemID', table 'Medix_Hospital.dbo.Hosp_wareitem'; column does not allow nulls. INSERT fails.

it tries to insert a null value in itemid because it cant get the value of the medicine_id.

any help is much appreciated!

puzzled
joel

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-17 : 02:08:00

select @id (select medicines_id from
hosp_medicines where medicines_id = medicines_id)

should be

select @id =medicines_id from
hosp_medicines where medicines_id = medicines_id

But why did you use "where medicines_id = medicines_id"?

Also make sure that you should assign single value to @id

Madhivanan

Failing to plan is Planning to fail

mercury76
Starting Member

7 Posts

Posted - 2007-08-17 : 02:45:00

thanks Madhivanan

Actually i used medicines_id = medicines_id because i want to test it first if it will work, i assumed that the query can identify the medicines_id immediately but i just found out that it only returns a null value. so i guess i have to do it like this

set xact_abort on
declare @id as integer
begin transaction
select @id = medicines_id from
hosp_medicines where medicines_id = /*integer value from the dbo.hosp_medicines*/
insert into Hosp_WareItem
(ItemID,
Department_ID,
SalesGL_Code,
COSGL_Code,
InventoryGL_Code,
ExpenseGL_Code,
ReadersFeeGL_Code,
CreateBy,
UpdateBy,
DeleteBy,
CreateDate,
UpdateDate,
DeleteDate)
values
(@ID,
'1',
'AAA-111',
'AAA-000',
'AAA-112',
'',
'',
'0',
'',
'',
'1/1/1900 12:00:00 AM',
'1/1/1900 12:00:00 AM',
'')
Update Hosp_Counter set Counter_ID = @ID
where Table_Description = 'wareitem'
commit transaction

Thanks.. i'll try it first and i will let you know.

Thanks again!

Joel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources