无法将数据集中的值插入数据库表。 [英] not able to insert values from a dataset to a database table.

问题描述

我无法将数据集中的值插入到数据库表中。代码没有显示任何错误，但是值没有进入表格.plkease帮助我

I am not able to insert values from a dataset to a database table.The code is not showing any error but the values are not entering into the table.plkease help me out

private void button1_Click(object sender, EventArgs e)
        {
           DataSet ds = new DataSet();
            using (OleDbConnection con = new OleDbConnection())
            {
                con.ConnectionString = "Data Source=XE; User ID=ER; Password=ER;Provider=msdaora";
                con.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = con;
                cmd.CommandText = "select * from bill where BILL_NO = '" + Convert.ToString(comboBox1.SelectedValue) + "' ";
                cmd.CommandType = CommandType.Text;
                OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
                adp.Fill(ds);
                con.Close();
            }
          string insqry = "INSERT INTO BILL2 (BILL_NO,CUSTOMER__NAME,ITEM_TAKEN,PRICE,MODE_OF_PAYMENT,TOTAL,DATE_PUR,SALE_FROM) VALUES (:BILL_NO,:CUSTOMER__NAME,:ITEM_TAKEN,:PRICE,:MODE_OF_PAYMENT,:TOTAL,:DATE_PUR,:SALE_FROM)";

        OracleConnection conn = new OracleConnection("Data Source=XE;Persist Security Info=True;User ID=ER;Password=ER;Unicode=True");
        conn.Open();
        OracleTransaction trans = conn.BeginTransaction();
        OracleDataAdapter ad = new OracleDataAdapter();
        ad.InsertCommand = new OracleCommand(insqry, conn);

            int A = 0;
            foreach (DataRow drrow in ds.Tables[A].Rows)
            {

                ad.InsertCommand.Parameters.Add(new OracleParameter(":BILL_NO", drrow["BILL_NO"]));
                ad.InsertCommand.Parameters.Add(new OracleParameter(":CUSTOMER__NAME", drrow["CUSTOMER__NAME"]));
                ad.InsertCommand.Parameters.Add(new OracleParameter(":ITEM_TAKEN", drrow["ITEM_TAKEN"]));
                ad.InsertCommand.Parameters.Add(new OracleParameter(":PRICE", drrow["PRICE"]));
                ad.InsertCommand.Parameters.Add(new OracleParameter(":MODE_OF_PAYMENT", drrow["MODE_OF_PAYMENT"]));
                ad.InsertCommand.Parameters.Add(new OracleParameter(":TOTAL", drrow["TOTAL"]));
                ad.InsertCommand.Parameters.Add(new OracleParameter(":DATE_PUR", drrow["DATE_PUR"]));
                ad.InsertCommand.Parameters.Add(new OracleParameter(":SALE_FROM", drrow["SALE_FROM"]));
                ad.InsertCommand.Transaction = trans;
                ad.InsertCommand.ExecuteNonQuery();

            }


             conn.Dispose();
             MessageBox.Show("DONE");
        }

推荐答案

正如CHill60所示，您错过了提交。但是你也没有利用using语句。在下面，我给出了我认为你的方法应该采用的模板。

As CHill60 has indicated, you are missing the commit. But you have also not taken advantage of the using statement. In the following, I give the template that I think your method should take.

private void button1_Click ( object    sender,
                             EventArgs e )
    {
    DataSet     data_set = new DataSet ( );
    DataTable   data_table = new DataTable ( );
    string      insert_statement = String.Empty;

    using ( OleDbConnection ole_db_connection =
                                new OleDbConnection ( ) )
        {
        OleDbDataAdapter  ole_db_data_adapter;
        OleDbCommand      ole_db_command ;

        ole_db_connection.ConnectionString =
            "Data Source=XE; " +
            "User ID=ER; " +
            "Password=ER; " +
            "Provider=msdaora";
        ole_db_connection.Open ( );
        ole_db_command = new OleDbCommand ( );
        ole_db_command.Connection = ole_db_connection;
        ole_db_command.CommandText =
            "select * from bill where BILL_NO = '" +
            comboBox1.SelectedValue.ToString ( ) +
            "' ";
        ole_db_command.CommandType = CommandType.Text;
        ole_db_data_adapter = new OleDbDataAdapter (
                                            ole_db_command );
        ole_db_data_adapter.Fill ( data_set );
        }

    data_table = data_set.Tables [ 0 ];

    insert_statement = "INSERT INTO BILL2 ( " +
                            "BILL_NO, " +
                            "CUSTOMER__NAME, " +
                            "ITEM_TAKEN, " +
                            "PRICE, " +
                            "MODE_OF_PAYMENT, " +
                            "TOTAL, " +
                            "DATE_PUR, " +
                            "SALE_FROM ) " +
                       "VALUES ( " +
                            ":BILL_NO, " +
                            ":CUSTOMER__NAME, " +
                            ":ITEM_TAKEN, " +
                            ":PRICE, " +
                            ":MODE_OF_PAYMENT, " +
                            ":TOTAL, " +
                            ":DATE_PUR, " +
                            ":SALE_FROM )";

    using ( OracleConnection oracle_connection =
                             new OracleConnection ( ) )
        {
        OracleDataAdapter oracle_adapter;
        OracleTransaction oracle_transaction;

        oracle_connection.ConnectionString =
            "Data Source=XE; " +
            "Persist Security Info=True; " +
            "User ID=ER; " +
            "Password=ER; " +
            "Unicode=True" );
        oracle_connection.Open ( );
        oracle_transaction =
            oracle_connection.BeginTransaction ( );
        oracle_adapter = new OracleDataAdapter ( );
        oracle_adapter.InsertCommand =
            new OracleCommand ( insert_statement,
                                oracle_connection);

        foreach ( DataRow data_row in data_table.Rows )
            {
            oracle_adapter.InsertCommand.Parameters.Add (
                new OracleParameter (
                    ":BILL_NO",
                    data_row [ "BILL_NO" ] ) );
            // :
            // :
            oracle_adapter.InsertCommand.Transaction =
                oracle_transaction;
            oracle_adapter.InsertCommand.ExecuteNonQuery ( );
            }
        oracle_transaction.Commit ( );
        }

    MessageBox.Show ( "DONE" );
    }
}

请注意，第一次使用中的ole_db_connection不需要关闭。通过为oracle_connection添加第二个，不需要关闭它。在oracle_connection使用结束之前，发生了oracle_transaction提交。



希望有所帮助。

Note that the ole_db_connection in the first using need not be closed. And by adding a second using for oracle_connection, there is no need to close it. Just before the end of the oracle_connection using, the oracle_transaction Commit occurs.

Hope that helps.

之前而不是

conn.Dispose();

你需要

trans.Commit();

这篇关于无法将数据集中的值插入数据库表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！