I am having problem inserting data from the medical bill to the database. Please suggest how can I do this?
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()
);
values inserted from the bill are >>
1, 100, 1, 1, 2, 200, 2015-07-22 || 2, 100, 1, 4, 1, 200, 2015-07-22
Please suggest.