Hi,I am totally new in ASP.NET.I have a query which may be very foolish to all of you but despite I'll be really thankfull if I get the answer urgently.

PROBLEM: described in following steps...

1) I have 3 tables in database

       A) employees : containing columns :: (emp_code,emp_name,dept)

      B) emp_prof_qualifications : containg columns::(emp_code,prof_qualification_id)

      C) professional_qualifications : contain columns::(prof_qualification_id,professional_qualification)

2) I have got a single interface where i'm having  controls for all these fields...

         A) emp_code : TEXTBOX

         B) emp_name : TEXTBOX

         C) dept : DROPDOWNLIST

         D) Professional Qualifications : CHECKBOXLISTS ( Showing data from table numbered C ,i.e. "Professional Qualification" table )

         E) INSERT and CANCEL button.

3) Now what I want is that when the insert button will be pressed then " emp_code ", "emp_name ", " dept" these values will go into "employees" table (table no. A)

      and the same "emp_code" and value checked from checkboxlist( professional_qualifications) will be entered into "emp_prof_qualifications"  table(table no. B)

 How to do that ?????

APPROACH TAKEN :

1) I tried to solve this problem by using formview which is working properly.

SPECIFIC QUERIES :

1)  What is the efficient way of entering data into mutiple tables in database from a single interface?

2) Will it be possible by using formview?

 
LANGUAGE USING : C#  

              i'll be be really thankfull if someone helps me in a descriptive manner.

Any kind of help will be really appreciated.Please help me urgently

Thanks in Advance. 

Normalize your database. In other words establish the foreign key and primary key relationships.

 See: http://en.wikipedia.org/wiki/Database_normalization

Modification:
a) employees : containing columns :: (emp_code,emp_name,dept, prof_qualification_id)
b) professional_qualifications : contain columns::(prof_qualification_id,professional_qualification)

  Thanks Steven 4 ur reply,

But I have already normalised the database, 

In my table structure "emp_prof_qualifications.employee_code" is referenceing to the "employees.employee_code' and  "emp_prof_qualifications.professional_qualification" references to "professional_qualifications.professional_qualification"

So I think the Database is normalised. But my core problem is,

From a single formview how to insert data into multiple table by clicking a single "insert" button.

or,

Is thr any better way to insert data into multiple tables from one interface and a single "insert"

button in ASP.NET?

It will be really really help for ur novice brothers like me.Also if the normalisation is not done properly then please correct me.

Thanks again 4 ur reply,'cose this is the 1st reply tht i've got from the forum and expect few more.
 

From my experience, if I understand your question, you would have to either write a stored procedure (preferable) or define multiple insert commands. I know there is a way to execute multiple inserts in a single line/command with SQL Server, but the Oracle client doesn't allow it.

Oracle (ODP.NET) does in fact allow multiple inserts, see this article I wrote from 2005: http://aspalliance.com/621_Using_ODPNET_to_Insert_Multiple_Rows_within_a_Single_Round_Trip

I now see what you want to do. You want a table to hold one or more qualification per employee. I should have looked closer :-) There was another post that stated use of multiple inserts or SP. I agree!

begin ...
insert into table1 (...,...) values (...,...)
insert into table2 (...,...) values (...,...)
insert into table3 (...,...) values (...,...)

Thank You Steven for guiding me so well. I have read your article and it seems that the procedure explained there will work, but the only problem I am facing is that....

create or replace procedure
     proc_add_emp_prf_qual(
        employee_code in varchar2,
        employee_name in varchar2,
        designation in varchar2,
        academic_qualification in varchar2,
        qualification_area in varchar2,
        office_code in varchar2,
        date_of_joining in varchar2,
        salary_grade in number,
        salary in number,
        professional_qualification in varchar2
        )is
  begin
    insert into employees values(
        employee_code,
        employee_name,
        designation,
        academic_qualification,
        qualification_area,
        office_code,
        to_date(date_of_joining,'dd/mm/yy'),
        salary_grade,salary);

    insert into emp_prof_qualifications values(
        employee_code,
        professional_qualification);
  end;
/
 in the above stored procedure I'm having a date argument  to be passed. What I have done is that I have passed the argument as "VARCHAR2" and converted it to date when 1st SQL command is triggered. that properly working in SQL*Plus. But when I am trying to send a value from form control of corresponding aspx.cs file....Always the following exception is being
caught.

    ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'PROC_ADD_EMP_PRF_QUAL' ORA-06550: line 1, column 7: PL/SQL: Statement ignored 

     and my 7th col is "date_of_joining",i.e. date field.Can you please guide me to understand
the reason of this error?

I have passed value into that argument in following way....

OracleParameter date_of_joiningParam = new OracleParameter("Joining Date", OracleDbType.Varchar2);
            date_of_joiningParam.Direction = ParameterDirection.Input;
            date_of_joiningParam.Value =date_of_joiningArr;
            cmd.Parameters.Add(date_of_joiningParam);

And another very important query..that is I am building this Application locally...

Now at the time of deployment...for using "Oracle.DataAccess.dll" ,is there any thing else needed other than Oracle10gServer ? 

Thanks, steven your article was really excellent, and that has solved my core problem.... 

I have another small problem , can u send me any good link of "Stored Procedure" tutorial. 

Take a look at the following: http://www.ics.com/support/docs/dx/1.5/tut6.html

You also may want to look for a book from Oracle Press on the subject: http://www.mhprofessional.com/category/?cat=7

Steven,

I'm very sorry but I again need help of you and all other forum frnds regarding the same subject.I'm Trying to explain it as clearly as possible....

<pre>     UI

              |================================= |
              |   |||||||||||||||||||||<---(Emp_code TextBox)             |
              |   ||||||||||||||||||||||<---(Emp_code TextBox)            |
              |   etc.                                                                |
              |    ++++++++++++++++++++++                   |
              |  + |||||<---(Prf_exp1 CheckBox)  +  <---(CheckBox List of ASP Controls)
              |  + |||||<---(Prf_exp2 CheckBox)  +                   |
              |   + + + + + + + + + + + + + + +                    |
              |================================= |

</pre>

  just look at the image(assume as an image).

Here, the UI contains ,

         3 Controls, 2 TextBoxes and 1 CheckBox list.It is just a sample part of the UI which I have tried to show to explain the problem. 

Emp_code, Emp_name, Emp_salary etc.(All are in a table named "employees" in the database)

 Emp_code,Prf_qualification is contained in  a different table named "emp_prf_qualfn"

          Now it is clearly visible that, for a single employee, there may be multiple professional qualifications....which are going to be selected from the "CheckBoxList"

                 Now when I'll press the "Insert" button the following steps sholud happen...

                  1) Connection will be built and opened with the DB ( working properly according to steven's article)

                  2) Stored Procedure Should be called.( working properly according to steven's article)

                  3) Now this store procedure will take values from that text box and checkbox list as the argument passed to it( working properly according to steven's article)

                  4) inside the Stored Procedure, there are 2 insert statement ( The Stored Procedure is mentioned by me in two posts back,to see just scroll up a bit).

                              a) one insert statement will take value from first two text boxes(all r controls are not showen,thats why specified 2 textboxes) and will keep inside "employees" table.

                              b) another insert statement will take one value from the textbox called "EMP_CODE" and other values from specified checkboxlist and will be entered to the "emp_prf_qualfn" table.something like following..

<pre> 

  Table1 :-

emp_code | emp_name | etc......
==========================
12             |  XYZ          |
11             | ABC          |

 Table 2 :-

emp_ code  | prf_qualfn
====================
 12             |  prf_qualfn1
 12             | prf_qualfn2

</pre>

                                5) So when the insert buton will be executed then all the datas should have to be entered into above explained manner.

                              Now initially, I tried with,

            "      cmd.Parameters.Add("Salary", OracleDbType.Decimal, SALARYTextBox.Text, ParameterDirection.Input); "

                        syntax to try to insert one row at atime and that was fine. One professional qualification was accepted and entered perfectly.

         But to insert multiple row in one go when I followed Steven's article..I faced a problem...

                i.e. My emp_code column will be always same , that will be used multiple time with multiple number of professional qualifications and those datas will be stored into the "emp_prof_qualfn" table. But it is not happening and throwing error "Integrity Constraint Violated: Primary Key not found" Error.Which logically should not happened 'cause in the first insert statement I have already insert the parent key value into the "employees" table....

 I know  this post is not only quite big , and may also seems messy...but please help in this issue...beacause this can solve all the issues which preventing me to develop an application.

Please kindly let me know if any other informations needed to understand the problem propose the solution....

please help your novice friend, just to make him your true friend in all aspect in future..

Please its really very urgent.... 

Thanks... 

after the insert in the master table use:

1    COMMIT;
2    DECLARE @JUSTINSERTED BIGINT; 
3    -- I ASSUME THIS SQL TYPE AND AUTO INCREMMENT ON THE KEY COLUMN
4    SELECT @JUSTINSERTED = SCOPE_IDENTITY();
5    INSERT INTO CHILD_TABLE (FK_PARENT_ID [, ...])
6           VALUES (@JUSTINSERTED [, ...]);