none
updating values in to a table using ado.net in c# and saving those values with data adapters

    Question

  • i have a table "employe" which has eight columns

    "EmpId,EmpFirstName,EmpLastName,EmpDeptNo,EmpAddress,EmpCity,EmpState,EmpZipCode,EmpCountry  and table

    "det" which is department table where i have two columns EmpDeptNo,EmpDeptName.where "EmpDeptName" is the

     combobox column now using an inner join i join both the tables.now the problem is i am using the query=       "select
      employe.EmpId,employe.EmpFirstName,employe.EmpLastName,employe.EmpAddress,employe.EmpCity,employe.EmpState,employe.EmpZipCode,employe.EmpCountry,employe.EmpDeptNo,det.EmpDeptName from employe inner join  det  on employe.EmpDeptNo=det.EmpDeptNo whose sp is "emplo".Now i need to update this sp
     
    and save the values to it where "EmpDeptNo" should not be visible in the front end that is"gridview" but visible in the backend

    "sqlserever 2005 edition" and " EmpDeptName" a combo box column  as given above with th  rest of all the columns.now

     to my "department" table

     EmpDeptNo EmpDeptName
    200              Products
    201              QualityAssurance
    201              Human Resources

    now i need to insert values in to  sp "emplo" where i am able to insert and save using the sqlparameters     sqlinsert1="insert into employe(EmpId,EmpFirstName,EmpLastName,EmpDeptNo,EmpAddress,EmpCity,EmpState,EmpZipCode,EmpCountry)values(@EmpId,@EmpFirstName,@EmpLastName,200,@EmpAddress,@EmpCity,@EmpState,@EmpZipCode,@EmpCountry)";

    since "EmpDeptNo"  should not be visible in the "FrontEnd" that is the " grid view " ive pass the value "200" through the
     
    sqlparameteres auto matically in my combobox column of "EmpDeptName" i have the products saved and "200" to

    "EmpDeptNo" automaticaly and rest of all the columns updated and saved .

    now the problem is  when i click on the "HumanResources"or ""QualityAssurance" or "Products" i need to get my

     "EmpDeptNo" automaticaly saved .the problem iam able to save only one EmpDeptName corespondingly EmpDeptNo

    using sqlparameters now how could i give along with 200 , 201and 202 to sql parameters while inserting so that accordingly

    when i click on any of the "EmpDeptName" the "EmpDeptNo" get saved in the back end. i  hope u understood my problem
     
    if there ar any doughts plz let me know. ive been working on this problem since long time.so plz hel me out. thanks in

     advance. i need to crack this problem.
     



    Monday, October 27, 2008 11:01 AM

Answers

  • Hi swps

     

    Welcome to MSDN. I have read your post. The following paragraph is the question and solution. Please look at it and tell me if you have any question and feel free to tell me if I misunderstood your meaning.

     

    "EmpDeptNo" should not be visible in the front end that is"gridview" but visible in the backend

    Could you please give me a screenshot? (You can use SkyDrive which is one of the service of live to give me the screenshot)

     

    SP: employe(EmpId,EmpFirstName,EmpLastName,EmpDeptNo,EmpAddress,EmpCity,EmpState,EmpZipCode,EmpCountry)values(@EmpId,@EmpFirstName,@EmpLastName,200,@EmpAddress,@EmpCity,@EmpState,@EmpZipCode,@EmpCountry)";

     

    Here you insert the EmpDeptNo with 200, I think you need to insert it with ComboBox selected value. Please look at this sample.

    Code Snippet

    public partial class Form1 : Form

        {

            private DataTable dtEmp = new DataTable();

            private DataTable dtDept = new DataTable();

     

            public Form1()

            {

                InitializeComponent();

     

                #region Data for Test

                dtEmp.Columns.Add("EmpName", typeof(string));

                dtEmp.Columns.Add("DeptNo", typeof(int));

     

                dtEmp.Rows.Add("John", 200);

                dtEmp.Rows.Add("Jack", 201);

                dtEmp.Rows.Add("Peter", 202);

     

                dtDept.Columns.Add("DeptNo", typeof(int));

                dtDept.Columns.Add("DeptName", typeof(string));

     

                dtDept.Rows.Add(200, "Products");

                dtDept.Rows.Add(201, "QualityAssurance");

                dtDept.Rows.Add(202, "Human Resources");

                #endregion

     

                DataGridViewComboBoxColumn cmbDept = new DataGridViewComboBoxColumn();

                cmbDept.HeaderText = "DeptName";

                cmbDept.DataSource = dtDept;

                cmbDept.DisplayMember = "DeptName";

                cmbDept.ValueMember = "DeptNo";

                cmbDept.DataPropertyName = "DeptNo";

     

                dataGridView1.Columns.Add(cmbDept);

                dataGridView1.DataSource = dtEmp;

            }

    }

     

     

    When you select a Dept in your ComboBoxColumn, it will submit the value to the dtEmp. So you can put a for statement to get each employee’s deptNo value and pass it to the stored procedure.

     

    Sincerely,

    Kira Qian

    Wednesday, October 29, 2008 4:05 AM
  • Hi swps

     

    Let me answer your second question.

    SP:

    sqlupdate = "update employe set EmpFirstName=@EmpFirstName  where EmpFirstName=@oldEmpFirstName";

     

    SqlParameter:

    SqlParameter[] pupdate = new SqlParameter[2];

     pupdate[0] = new SqlParameter("@EmpFirstName", SqlDbType.VarChar, 40, "EmpFirstName");

                 pupdate[1] = new SqlParameter("@oldEmpFirstName", SqlDbType.Int, 15, "EmpFirstName");

    How did you get the oldEmpFirstName? Your stored procedure show me that you want to update the EmpFirstName by its oldEmpFirstName. But from the SqlParameter, I found you use the same name in this case. EmpFirstName and oldEmpFirstName are the same.

     

    This code:

    da.UpdateCommand = cmdupdate;

     ds.AcceptChanges();

        this.da.Fill(ds);

       this.dataGridView1.DataSource = ds;

              this.dataGridView1.DataSource = ds.Tables[0];

                this.da.Update(ds);

    I found you have use auto update instead of your Stored Procedure.

     

    In this case, if you only need to update a single table, I have already recommended you to use SqlCommandBuilder.

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3707715&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3637384&SiteID=1

    If you want to update more than one tables, you can either use a stored procedure or write the sqlcommand text by yourself.

     

    Sincerely,

    Kira Qian

    Thursday, October 30, 2008 3:07 AM
  • Hi swps

     

    I quite know your situation, I’m also know you have worked on this project for a long time. Since I have taken your case for such a long time. Due to the limitation of the forum, I just have a whole figure of your project. I guess you are developing an “Employee Management System” or something similar to it.

     

    First, Let me list the basic point of doing a Management System with ADO.NET. I think this will give you the whole concept of developing your project.

     

    1. Get data from database use ADO.NET, fill the data into a DataTable or DataSet and bind it to a DataGridView. (You have no problem)

    2. Insert/Update/Delete data to database. Use auto command build(You have no problem).

    3. Insert/Update/Delete data to database. Use Sql command text. Write Sql to operate the database. (No problem).

    4. Insert/Update/Delete data to database. Use Stored procedure.(You have some problem with it).

     

    Second, let us solve the detail problem.

    "EmpId,EmpFirstName,EmpLastName,EmpAddress,EmpCity,EmpState,EmpZipCode,EmpCountry and combobox of EmpDeptName where EmpDeptNo will be visible in the backend "sqlserver 2005 adition" but not be visible in the "grid". With the use of  "this.dataGridView1.Columns["EmpDeptNo"].Visible = false;".

     

    You don’t need to set the EmpDeptNo column Visible property to false. Actually the example of my first post just show the DeptName with no DeptNo because once you have bind the ComboBoxColumn to the DeptNo column, it will just show the Name not the Value.

     

    now my issue is as soon i insert the row and save its fine while updating the combobox of "EmpDeptName" that is when I click on it and updates its value no change occurs the same saved value i get.Suppose for example the row is already saved

     

    I think you have some problem with the stored procedure(the 4th point of the above list). When you use a stored procedure to update the record.

    Update Emptable set EmpDeptNo = @empDeptNo where EmpNo = @empNo.

    Look at this example sql. If you change the comboBox selected item, the value of the EmpDeptNo will be change under the DataSrouce(DataTable). So you need to pick the changed row and get the value of EmpNo and EmpDeptNo. Transmit it to the Stored procedure. It will update the database. E.g. Update Emptable set EmpDeptNo = 201 where EmpNo = 2.

     

    Can you understand my meaning? Wish you good luck!

     

    Sincerely,

    Kira Qian

    Friday, October 31, 2008 3:21 AM

All replies

  • Hi swps

     

    Welcome to MSDN. I have read your post. The following paragraph is the question and solution. Please look at it and tell me if you have any question and feel free to tell me if I misunderstood your meaning.

     

    "EmpDeptNo" should not be visible in the front end that is"gridview" but visible in the backend

    Could you please give me a screenshot? (You can use SkyDrive which is one of the service of live to give me the screenshot)

     

    SP: employe(EmpId,EmpFirstName,EmpLastName,EmpDeptNo,EmpAddress,EmpCity,EmpState,EmpZipCode,EmpCountry)values(@EmpId,@EmpFirstName,@EmpLastName,200,@EmpAddress,@EmpCity,@EmpState,@EmpZipCode,@EmpCountry)";

     

    Here you insert the EmpDeptNo with 200, I think you need to insert it with ComboBox selected value. Please look at this sample.

    Code Snippet

    public partial class Form1 : Form

        {

            private DataTable dtEmp = new DataTable();

            private DataTable dtDept = new DataTable();

     

            public Form1()

            {

                InitializeComponent();

     

                #region Data for Test

                dtEmp.Columns.Add("EmpName", typeof(string));

                dtEmp.Columns.Add("DeptNo", typeof(int));

     

                dtEmp.Rows.Add("John", 200);

                dtEmp.Rows.Add("Jack", 201);

                dtEmp.Rows.Add("Peter", 202);

     

                dtDept.Columns.Add("DeptNo", typeof(int));

                dtDept.Columns.Add("DeptName", typeof(string));

     

                dtDept.Rows.Add(200, "Products");

                dtDept.Rows.Add(201, "QualityAssurance");

                dtDept.Rows.Add(202, "Human Resources");

                #endregion

     

                DataGridViewComboBoxColumn cmbDept = new DataGridViewComboBoxColumn();

                cmbDept.HeaderText = "DeptName";

                cmbDept.DataSource = dtDept;

                cmbDept.DisplayMember = "DeptName";

                cmbDept.ValueMember = "DeptNo";

                cmbDept.DataPropertyName = "DeptNo";

     

                dataGridView1.Columns.Add(cmbDept);

                dataGridView1.DataSource = dtEmp;

            }

    }

     

     

    When you select a Dept in your ComboBoxColumn, it will submit the value to the dtEmp. So you can put a for statement to get each employee’s deptNo value and pass it to the stored procedure.

     

    Sincerely,

    Kira Qian

    Wednesday, October 29, 2008 4:05 AM
  • thank u very much Mr.KiraQuian its working but now i have another  issue i as soon as i update a row and save it then updations are

    saved but after saving the same row i need to make changes to it i change a value of the specific column of that row.

    Suppose in my table "employe" where completely my values are inserted and save it suppose if i change the

    "EmpFirstName" value of the already saved row   that is when i need to "update" its value i am getting an exception. so how do i deal with it. actually i ve used the updation through

     the sql parameters   sqlupdate = "update employe set EmpFirstName=@EmpFirstName  where EmpFirstName=@oldEmpFirstName";

                SqlParameter[] pupdate = new SqlParameter[2];

     pupdate[0] = new SqlParameter("@EmpFirstName", SqlDbType.VarChar, 40, "EmpFirstName");

                 pupdate[1] = new SqlParameter("@oldEmpFirstName", SqlDbType.Int, 15, "EmpFirstName");

     SqlCommand cmdupdate = new SqlCommand(sqlupdate,sqlconn);

     cmdupdate.Parameters.AddRange(pupdate);


    da.UpdateCommand = cmdupdate;
     ds.AcceptChanges();
        this.da.Fill(ds);
       this.dataGridView1.DataSource = ds;
              this.dataGridView1.DataSource = ds.Tables[0];
                this.da.Update(ds);

    but still i am not able to crack the problem plz help me out. if u have any doughts regarding my problem plz let me know.

    thanks in advance.


    Wednesday, October 29, 2008 2:16 PM
  • Hi swps

     

    Let me answer your second question.

    SP:

    sqlupdate = "update employe set EmpFirstName=@EmpFirstName  where EmpFirstName=@oldEmpFirstName";

     

    SqlParameter:

    SqlParameter[] pupdate = new SqlParameter[2];

     pupdate[0] = new SqlParameter("@EmpFirstName", SqlDbType.VarChar, 40, "EmpFirstName");

                 pupdate[1] = new SqlParameter("@oldEmpFirstName", SqlDbType.Int, 15, "EmpFirstName");

    How did you get the oldEmpFirstName? Your stored procedure show me that you want to update the EmpFirstName by its oldEmpFirstName. But from the SqlParameter, I found you use the same name in this case. EmpFirstName and oldEmpFirstName are the same.

     

    This code:

    da.UpdateCommand = cmdupdate;

     ds.AcceptChanges();

        this.da.Fill(ds);

       this.dataGridView1.DataSource = ds;

              this.dataGridView1.DataSource = ds.Tables[0];

                this.da.Update(ds);

    I found you have use auto update instead of your Stored Procedure.

     

    In this case, if you only need to update a single table, I have already recommended you to use SqlCommandBuilder.

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3707715&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3637384&SiteID=1

    If you want to update more than one tables, you can either use a stored procedure or write the sqlcommand text by yourself.

     

    Sincerely,

    Kira Qian

    Thursday, October 30, 2008 3:07 AM
  • thank u Mr.Kira Qian for ur response u ar correct abt use of the command builder where automaticaly updations can be done.

    suppose for example if i need to do update "EmpFirstName" i am able to do it.But my problem is when as u know my sp
    "emplo" handles the query    "select  employe.EmpId,employe.EmpFirstName,employe.EmpLastName,employe.EmpAddress,employe.EmpCity,employe.EmpState,employe.EmpZipCode,employe.EmpCountry,employe.EmpDeptNo,det.EmpDeptName from employe inner join  det  on employe.EmpDeptNo=det.EmpDeptNo" .But now on the grid i wouild get

    "EmpId,EmpFirstName,EmpLastName,EmpAddress,EmpCity,EmpState,EmpZipCode,EmpCountry and combobox of

    EmpDeptName where EmpDeptNo will be visible in the backend "sqlserver 2005 adition" but not be visible in the "grid". with

    the use of  "this.dataGridView1.Columns["EmpDeptNo"].Visible = false;".
    now my issue is as soon i insert the row and save its fine while updating the combobox of "EmpDeptName" that is when i

    click on it and updates its value no change occurs the same saved value i get.Suppose for example the row is already saved

    with the "EmpDeptName"as "Products" after saving it when i want to update it i reclick on the combo box of

    "EmpDeptName"and want to change "Products" to "Human Resources"no change occurs the original  " Products " only

    remains. I  think i need to use a proper update

    statement through sql parameters so that i could crack the problem. ive been working on this issue for a long time . so plz

    help me out.if u have any doughts plz let m know.

    thanks in advance for ur concern.





    Thursday, October 30, 2008 2:00 PM
  • Hi swps

     

    I quite know your situation, I’m also know you have worked on this project for a long time. Since I have taken your case for such a long time. Due to the limitation of the forum, I just have a whole figure of your project. I guess you are developing an “Employee Management System” or something similar to it.

     

    First, Let me list the basic point of doing a Management System with ADO.NET. I think this will give you the whole concept of developing your project.

     

    1. Get data from database use ADO.NET, fill the data into a DataTable or DataSet and bind it to a DataGridView. (You have no problem)

    2. Insert/Update/Delete data to database. Use auto command build(You have no problem).

    3. Insert/Update/Delete data to database. Use Sql command text. Write Sql to operate the database. (No problem).

    4. Insert/Update/Delete data to database. Use Stored procedure.(You have some problem with it).

     

    Second, let us solve the detail problem.

    "EmpId,EmpFirstName,EmpLastName,EmpAddress,EmpCity,EmpState,EmpZipCode,EmpCountry and combobox of EmpDeptName where EmpDeptNo will be visible in the backend "sqlserver 2005 adition" but not be visible in the "grid". With the use of  "this.dataGridView1.Columns["EmpDeptNo"].Visible = false;".

     

    You don’t need to set the EmpDeptNo column Visible property to false. Actually the example of my first post just show the DeptName with no DeptNo because once you have bind the ComboBoxColumn to the DeptNo column, it will just show the Name not the Value.

     

    now my issue is as soon i insert the row and save its fine while updating the combobox of "EmpDeptName" that is when I click on it and updates its value no change occurs the same saved value i get.Suppose for example the row is already saved

     

    I think you have some problem with the stored procedure(the 4th point of the above list). When you use a stored procedure to update the record.

    Update Emptable set EmpDeptNo = @empDeptNo where EmpNo = @empNo.

    Look at this example sql. If you change the comboBox selected item, the value of the EmpDeptNo will be change under the DataSrouce(DataTable). So you need to pick the changed row and get the value of EmpNo and EmpDeptNo. Transmit it to the Stored procedure. It will update the database. E.g. Update Emptable set EmpDeptNo = 201 where EmpNo = 2.

     

    Can you understand my meaning? Wish you good luck!

     

    Sincerely,

    Kira Qian

    Friday, October 31, 2008 3:21 AM
  • Thank u Mr.Kira Qian its excelently working without u i would not have completed my tasks .uve been always in help to me.

    Thank u very much  once again iam very greatful to u.
    Friday, October 31, 2008 6:40 AM
  • I am very glad you can complete your project under my help. It's my duty to offer service to Microsoft user. It's my pleasure! Any question in using develop technology, feel free to come to MSDN. We will do our best to solve it.

     

    Kira Qian

    Friday, October 31, 2008 6:48 AM
  • Hi Kira,

    I m using OracleCommandBuilder to update my dataset changes to database. In that if i specify my SELECT statement in code itself  and populate dataset then it works fine.
    But if i fill my dataset using stored procedure then it throws an error as - {System.InvalidOperationException: Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information.
    I read post on this article about command builder - http://msdn.microsoft.com/en-us/library/ms971491.aspx and my code satisfies all the condition as select query output must contain primary key, shuld not contain joins , column has read-write operation access,column name does not conatin any special character etc.
    My code is as follows - 

    OracleCommand

    cmd = new OracleCommand();
    cmd.CommandType =
    CommandType.StoredProcedure;
    OracleDataAdapter oda = new OracleDataAdapter("GETRECORDSWITHWHERE", oracleConnection);
    oda.SelectCommand.CommandType =
    CommandType.StoredProcedure;

    OracleParameter p = new OracleParameter("parentid", 22);
    p.Direction =
    ParameterDirection.Input;
    oda.SelectCommand.Parameters.Add(p);

     

     

    OracleParameter p1 = new OracleParameter();
    p1.ParameterName =
    "cur_OUT1";
    p1.OracleType =
    OracleType.Cursor;
    p1.Direction =
    ParameterDirection.Output;
    oda.SelectCommand.Parameters.Add(p1);

     

    OracleCommandBuilder builder = new OracleCommandBuilder(oda);
    DataSet d = new DataSet();
    oda.Fill(d,
    "emp");

    d.Tables[

    "emp"].Columns["MENUID"].Unique = true;
    d.Tables[
    "emp"].Rows[0]["NAVIGATEURL"] = "kunal";
    oda.Update(d,
    "emp");

     

    And my stored procedure is as follows - 
    CREATE OR REPLACE PROCEDURE GETRECORDSWITHWHERE(parentid IN NUMBER,cur_OUT1 out SYS_REFCURSOR)
     As
      begin
      
        open cur_OUT1 for
        SELECT MENUID,MENUNAME, NAVIGATEURL, PARENTMENUID FROM OCTOPUS_TREESTRUCTURE WHERE PARENTMENUID=parentid;

       end;

    Thanks for your kind cooperation.

    Regards,
    Kunal
      

    Wednesday, July 01, 2009 6:43 AM