none
how to use the myAdapter.Update and OleDbCommandBuilder from dataGridView to update the db2 database RRS feed

  • Question

  • hello,everybody ,it a question makes me crazy .my code is that :

                 

            public string tableName = "";
            public DataSet ds;
            public OleDbDataAdapter myAdapter;
            public string sql = "";
            private   void button12_Click_1(object sender, EventArgs e)
            {

                sql = textBox5.Text.Trim();
                OleDbConnection Conn = new OleDbConnection(st_contractconn);     
                Conn.Open();
                this.comboBox1.Items.Add(this.textBox5.Text);
                try
                {
                    System.Text.RegularExpressions.Regex r = new System.Text.RegularExpressions.Regex(@"(?<=from\s)\w+(?=\s)");
                    System.Text.RegularExpressions.Match B = r.Match(sql);
                    tableName = B.ToString();

                   OleDbCommand cmd = new OleDbCommand("SELECT * FROM PT_PO",Conn);
                   Conn.Close();
                    myAdapter = new OleDbDataAdapter(cmd);
                    ds = new DataSet();
                    myAdapter.Fill(ds, "PT_PO");
                    dataGridView2.DataSource = ds.Tables["PT_PO"].DefaultView;                   
                    this.groupBox2.Text = "共查询到" + ds.Tables[0].Rows.Count.ToString() + "条记录" + tableName;

                }
                catch (Exception ex)
                {

                    MessageBox.Show(ex.Message);

                }
            }
            private void button26_Click(object sender, EventArgs e)
            {
                OleDbConnection Conn = new OleDbConnection(st_contractconn);
                Conn.Open();
                OleDbCommandBuilder Builder = new OleDbCommandBuilder(myAdapter);
                Validate() ;            
                myAdapter.Update(ds.Tables["PT_PO"]);
                Conn.Close();
                ds.AcceptChanges() ; 
            }

        }

    when it run to the myAdapter.Update(ds.Tables["PT_PO"]);  it comes out a wrong "对于不返回任何基表信息的 SelectCommand 不支持动态 SQL 生成。" who knows  it is why ,my QQ is 501212848,thankyou very much.

    Thursday, January 15, 2015 3:10 PM

Answers

  • Hello,

    We were a IBM-DB2 shop for over 20 years, when we moved to .NET we found it was better to use the native DB2 data provider which you can download from IBM for free.

    Main objects

    IBM.Data.DB2.iSeries.iDB2Connection
    IBM.Data.DB2.iSeries.iDB2Command
    IBM.Data.DB2.iSeries.iDB2Parameter
    

    So you would not use adapters but write code using the key objects above. Then to get new primary keys we would use

    SELECT nextval for SOMETABLE_REF_AUTONUMBER_SEQUENCE from sysibm.sysdummy1
    Bottom line is the structure is create a connection, create a command, set the connection and command text along with creating parameters as needed.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Proposed as answer by Carl Cai Monday, January 26, 2015 10:34 AM
    • Marked as answer by Fred BaoModerator Tuesday, January 27, 2015 9:43 AM
    Thursday, January 15, 2015 4:39 PM

  • what is the meaning about you ? db2 cannt using adapters  to update the datebase?

    DB2 is an IBM product, despite what is always suggested is SQL Transact just a kind of code every company makes for themselves and have their own dialects.

    Therefore Kevin adviced you to search first for the native IBM .Net providers and try to find help at that company. This is a Microsoft forum you know. If you build a Ford engine in a Mercedes, they seldom have much knowledge about that motor. The same it is in this case. 

    I found this but have no knowledge about it.

    http://www-03.ibm.com/systems/power/software/i/access/windows/dotnet.html


    Success
    Cor

    • Proposed as answer by Carl Cai Monday, January 26, 2015 10:34 AM
    • Marked as answer by Fred BaoModerator Tuesday, January 27, 2015 9:43 AM
    Friday, January 16, 2015 8:34 AM

All replies

  • Hello,

    We were a IBM-DB2 shop for over 20 years, when we moved to .NET we found it was better to use the native DB2 data provider which you can download from IBM for free.

    Main objects

    IBM.Data.DB2.iSeries.iDB2Connection
    IBM.Data.DB2.iSeries.iDB2Command
    IBM.Data.DB2.iSeries.iDB2Parameter
    

    So you would not use adapters but write code using the key objects above. Then to get new primary keys we would use

    SELECT nextval for SOMETABLE_REF_AUTONUMBER_SEQUENCE from sysibm.sysdummy1
    Bottom line is the structure is create a connection, create a command, set the connection and command text along with creating parameters as needed.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Proposed as answer by Carl Cai Monday, January 26, 2015 10:34 AM
    • Marked as answer by Fred BaoModerator Tuesday, January 27, 2015 9:43 AM
    Thursday, January 15, 2015 4:39 PM
  • Hello,

    We were a IBM-DB2 shop for over 20 years, when we moved to .NET we found it was better to use the native DB2 data provider which you can download from IBM for free.

    Main objects

    IBM.Data.DB2.iSeries.iDB2Connection
    IBM.Data.DB2.iSeries.iDB2Command
    IBM.Data.DB2.iSeries.iDB2Parameter

    So you would not use adapters but write code using the key objects above. Then to get new primary keys we would use

    SELECT nextval for SOMETABLE_REF_AUTONUMBER_SEQUENCE from sysibm.sysdummy1
    Bottom line is the structure is create a connection, create a command, set the connection and command text along with creating parameters as needed.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    what is the meaning about you ? db2 cannt using adapters  to update the datebase?
    Friday, January 16, 2015 7:48 AM

  • what is the meaning about you ? db2 cannt using adapters  to update the datebase?

    DB2 is an IBM product, despite what is always suggested is SQL Transact just a kind of code every company makes for themselves and have their own dialects.

    Therefore Kevin adviced you to search first for the native IBM .Net providers and try to find help at that company. This is a Microsoft forum you know. If you build a Ford engine in a Mercedes, they seldom have much knowledge about that motor. The same it is in this case. 

    I found this but have no knowledge about it.

    http://www-03.ibm.com/systems/power/software/i/access/windows/dotnet.html


    Success
    Cor

    • Proposed as answer by Carl Cai Monday, January 26, 2015 10:34 AM
    • Marked as answer by Fred BaoModerator Tuesday, January 27, 2015 9:43 AM
    Friday, January 16, 2015 8:34 AM
  • thankyou
    Friday, January 16, 2015 12:06 PM
  • Hello,

    When you install the client side software for IBM database which by default in the setup has the .NET data provider option un-checked, checking this option will install the data provider.

    We did test between using OleDb and the native DB2 providers and found that the DB2 provider was much more efficient coupled with providing capabilities not even possible with the OleDb or ODBC data providers.

    In regards to using adapters, that is possible but we never did as the suggestions I mentioned allow for much more control over adapters but that is just an opinion where you may rather use them which is possible as they are in the data provider, use them as you would any adapter but be forewarn they are not available by dragging them onto a form, only in code.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.


    Friday, January 16, 2015 12:22 PM
  • ok,i think i know that ,thankyou very much!
    Friday, January 16, 2015 3:15 PM