Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to insert data from invoice to database. The problem is, there are multiple rows in the invoice which should be inserted to the table. Here is problem. Please suggest.

---- my tables, only required fields are shown -----
CREAT TABLE PATIENT
(
	PATIENT_ID int IDENTITY(1,1) PRIMARY KEY,
	PATIENT_NAME NVARCHAR(200) NOT NULL
);
values are >> 1, JOE || 2, MARK || 3, SAM

CREAT TABLE RX
(
	RX_ID int IDENTITY(1,1) PRIMARY KEY,
	RX_NAME NVARCHAR(200) NOT NULL
);
values are >> 1, RX-1 || 2, RX-3, || 3, RX-10 || 4, RX-05

CREAT TABLE RX_RATE
(
	RX_RATE_ID int IDENTITY(1,1) PRIMARY KEY,
	RX_RATE DECIMAL (19,10) NOT NULL,
	RX_ID INT FOREIGN KEY REFERENCES RX(RX_ID)
);
values are >> 1, 100, 1 || 2, 150, 2 || 3, 100, 3 || 4, 200, 4


I've a billing UI, where there is text box for PATIENT NAME, dropdowns for RX NAME, TEXTBOX for QUANTITY. Then the RATE is fetched from RX_RATE table. then the amount is calculated.

The number of records in the bill is dynamic. I mean some times number of mediciines purchased by the user can be 5 or 10 or 20 etc. etc. This is how my UI form looks like.

---------------------------------------------------------------------------------- 
Bill No.  >> 100
Patient Name >> Joe 
 
 rx name	 quantity	    rate	 amount
 RX-1	          2	          100	 200
 RX-05	          1	          200	 200
                              total      400
SUBMIT_BTN 
---------------------------------------------------------------------------------------  


Now I've to insert this information in TRANSACTION table. my problem is how can I insert the whole data from the bill to TRANSACTION table on submit button click.

CREAT TABLE TRANSACTION
(
	TRANSACTION_ID int IDENTITY(1,1) PRIMARY KEY,
	BILL_ID INT IDENTITY(100,1),
	P_ID INT FOREIGN KEY REFERENCES PATIENT(PATIENT_ID),
	RX_ID INT FOREIGN KEY REFERENCES RX(RX_ID),
	RX_QUANTITY INT NOT NULL,
	AMOUNT DECIMAL (19,10) NOT NULL, 
	DATE_TXN DATE DEFAULT GETDATE()
);


This is how the data in this table will be>>
1, 100, 1, 1, 2, 200, 2015-07-22  
2, 100, 1, 4, 1, 200, 2015-07-22

Please suggest.
Posted

You didn't post the actual code you use to insert any data, but inserting multiple rows is basically the same thing as inserting one. You just repeat the insert step as many times as you need to. So in short pseudo code:
C#
open SqlConnection
create SqlCommand // using parameters
start transaction
loop while rows to insert exist
   try
       set parameter values for the row
       execute the insert command
   catch error
      rollback
end loop
commit transaction if operation was succesful
close connection
 
Share this answer
 
Comments
Espen Harlinn 22-Jul-15 15:22pm    
:-)
Wendelius 23-Jul-15 4:32am    
Hi, Espen. Nice to see you :)
Espen Harlinn 24-Jul-15 9:26am    
Thanks,

Good to see that you're still here :-)
nischalinn 22-Jul-15 21:21pm    
My problem is how to insert the data from UI to database. I do not have any idea how to do this.
Wendelius 23-Jul-15 4:31am    
Do you mean you don't know how to build the loop I mentioned or something else? Do you have some sort of grid where the data is?
I think your problem is that you need a detail level transaction table to store the rx associated with the transaction.

SQL
CREAT TABLE TRANSACTION
(
	TRANSACTION_ID int IDENTITY(1,1) PRIMARY KEY,
	BILL_ID INT IDENTITY(100,1),
	P_ID INT FOREIGN KEY REFERENCES PATIENT(PATIENT_ID),
	DATE_TXN DATE DEFAULT GETDATE()
);

CREAT TABLE TRANSACTION_DETAIL
(
	TRANSACTION_DETAILID int IDENTITY(1,1) PRIMARY KEY,
	TRANSACTION_ID INT FOREIGN KEY REFERENCES TRANSACTION(TRANSACTION_ID),
	RX_ID INT FOREIGN KEY REFERENCES RX(RX_ID),
	RX_QUANTITY INT NOT NULL,
	AMOUNT DECIMAL (19,10) NOT NULL, 
);


Using this structure your transaction can contain as many RX items as you like.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900