只有一个记录从datagridview插入到Oracle数据库中 [英] Only one record inserted into Oracle database from datagridview
问题描述
我正在尝试从 DataGridView 中读取数据，并将其插入数据库。当一条记录在 DataGridView 中时，一切都可以正常工作，但是当 DataGridView 中有多个记录时，我得到只有一个记录插入，所有其他记录未插入到数据库中。
I am trying to read data from DataGridView and insert it into database. Everything works fine when one record is in the DataGridView, but when there is more than one record within the DataGridView I got only one record inserted and all other records are not inserted into database.
我附加了Oracle函数代码以获取更多详细信息。
I have attached the Oracle function code for more details.
我认为第一个表顺序之后由函数引起的问题应该有一个记录，子表order_details 应该有所购物品的清单。因此，函数执行一次，然后由于第一个表（Order）中的主键异常而停止。
I think the issue caused by function since the first table order should have one record and the sub table order_details should have the list of purchased items. So the function execute once and then stop because of primary key exception on the first table (Order).
I不知道这是否正确，但如果是正确的，那么如何解决和保持事务发生在一个交易中，所以所有的交易都将成功，否则会回滚所有事情已经完成？
I don't know if that is right but in case it is right then how to solve and keep thing happen in one transaction so all transaction will success, or it will rollback all things has been done before?
string connstr = @"Data Source=orcl; User Id=user; password=pwd;";
string insertcmdtxt = @"F_INS_ORDER_DATA";
using (OracleConnection conn = new OracleConnection(connstr))
using (OracleCommand cmd = new OracleCommand(insertcmdtxt, conn))
{
try
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = insertcmdtxt;
foreach (DataGridViewRow Row in DGV_INVOICE.Rows)
{
cmd.Parameters.Clear();
cmd.Parameters.Add(":vORDER_ID", OracleDbType.Int32, ParameterDirection.ReturnValue);
cmd.Parameters.Add(new OracleParameter(":P_CUSTOMER_ID", OracleDbType.Int32)).Value = TB_CUSTOMER_ID.Text;
cmd.Parameters.Add(new OracleParameter(":P_ORDER_NOTE", OracleDbType.Varchar2)).Value = TB_ORDER_NOTE.Text;
cmd.Parameters.Add(new OracleParameter(":P_PRODUCT_ID", OracleDbType.Int32)).Value = Row.Cells[DGV_INVOICE.Columns["DGV_PRODUCT_ID"].Index].Value;
cmd.Parameters.Add(new OracleParameter(":P_UNIT_PRICE", OracleDbType.Int32)).Value = Row.Cells[DGV_INVOICE.Columns["DGV_UNIT_PRICE"].Index].Value;
cmd.Parameters.Add(new OracleParameter(":P_QUANTITY", OracleDbType.Int32)).Value = Row.Cells[DGV_INVOICE.Columns["DGV_QUANTITY"].Index].Value;
cmd.Parameters.Add(new OracleParameter(":P_DISCOUNT", OracleDbType.Int32)).Value = Row.Cells[DGV_INVOICE.Columns["DGV_DISCOUNT"].Index].Value;
cmd.Parameters.Add(new OracleParameter(":P_ORDER_STATUS", OracleDbType.Varchar2)).Value = '1';
cmd.Parameters.Add(new OracleParameter(":P_ITEM_NOTE", OracleDbType.Varchar2)).Value = Row.Cells[DGV_INVOICE.Columns["DGV_ITEM_NOTE"].Index].Value;
cmd.ExecuteNonQuery();
}
TB_INVOICE_ID.Text = (cmd.Parameters[":vORDER_ID"].Value).ToString();
}
catch (Exception EX)
{
MessageBox.Show(EX.Message, "حدث خطاء", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
功能代码 p>
function Code
CREATE OR REPLACE FUNCTION F_INS_Order_Data (P_CUSTOMER_ID IN NUMBER,
P_ORDER_NOTE IN VARCHAR2,
P_PRODUCT_ID IN NUMBER,
P_UNIT_PRICE IN NUMBER,
P_QUANTITY IN NUMBER,
P_DISCOUNT IN NUMBER,
P_ORDER_STATUS IN VARCHAR2,
P_ITEM_NOTE IN VARCHAR2)
RETURN NUMBER
IS
VOrder_Id NUMBER; --ORDER_ID Filled by trigger
vCreated_by VARCHAR2 (64) := 'SYSTEM';
vCreated_On DATE := SYSDATE;
sql_stmt VARCHAR2 (4000);
ERR_CODE VARCHAR2(64);
ERR_MSG VARCHAR2(1024);
BEGIN
SAVEPOINT Setp1;
sql_stmt := 'INSERT INTO orders (ORDER_ID,
CUSTOMER_ID,
NOTES,
CREATED_BY,
CREATED_ON)
VALUES (NULL, --ORDER_ID Filled by trigger
:PCUSTOMER_ID, --CUSTOMER_ID
:POrderNote, --NOTES
:PCREATED_BY, --CREATED_BY
:PCREATED_ON) --CREATED_ON
RETURNING ORDER_ID INTO :vORDER_ID';
EXECUTE IMMEDIATE sql_stmt USING P_CUSTOMER_ID,
P_ORDER_NOTE,
vCreated_by,
vCreated_ON
RETURNING INTO vORDER_ID;
--DBMS_OUTPUT.PUT_LINE (sql_stmt); /* For Testing Purpose */
sql_stmt:='INSERT INTO ORDER_DETAILS (ORDER_ID,
PRODUCT_ID,
UNIT_PRICE,
QUANTITY,
DISCOUNT,
ORDER_STATUS,
NOTES,
CREATED_BY,
CREATED_ON)
VALUES ( :PvORDER_ID, --ORDER_ID,
:PPRODUCT_ID, --PRODUCT_ID
:PUNIT_PRICE, --UNIT_PRICE
:PQUANTITY, --QUANTITY
:PDISCOUNT, --DISCOUNT
:PORDER_STATUS, --ORDER_STATUS
:PItem_Note, --NOTES
:PCREATED_BY, --CREATED_BY
:PCREATED_ON --CREATED_ON
)';
EXECUTE IMMEDIATE sql_stmt USING vORDER_ID,
P_PRODUCT_ID,
P_UNIT_PRICE,
P_QUANTITY,
P_DISCOUNT,
P_ORDER_STATUS,
P_ITEM_NOTE,
vCreated_by,
vCreated_On;
--DBMS_OUTPUT.PUT_LINE (sql_stmt); /* For Testing Purpose */
RETURN (VOrder_Id);
EXCEPTION WHEN OTHERS THEN
ROLLBACK TO Setp1;
ERR_CODE := SQLCODE;
ERR_MSG := SUBSTR(SQLERRM, 1, 1024);
sql_stmt := F_INS_ERROR_LOG(SYSDATE, --P_ERROR_TIME,
vCreated_by, --P_USER_ID,
'F_INS_Order_Data', --P_PROGRAM_UNIT,
NULL, --P_ERROR_LOCATION,
NULL, --P_KEY_DATA_DESC,
ERR_CODE, --P_ERROR_CODE,
ERR_MSG); --P_ERROR_MSG)';
RETURN -1;
END F_INS_Order_Data;
/
推荐答案
这不一定是问题但。 cmd.Parameters.Add（：vORDER_ID，OracleDbType.Int32，ParameterDirection.ReturnValue）;
你没有在你的诉讼程序中使用orderid，而是将其传入。
This is not necessarily the issue but. cmd.Parameters.Add(":vORDER_ID", OracleDbType.Int32, ParameterDirection.ReturnValue); You are not using orderid in your proceuder and you are passing it in.
第二，
foreach是否为每个数据行循环？你没有提供足够的细节来帮助你调试这个。
Second, Is the foreach actually looping for each data row? You have not provided enough detail to help you debug this.
第三，检查查询执行方法。我不认为它将返回vorder_id
ExecuteNonQuery（）
Third, Check the query execution method. I dont think its going to return the vorder_id ExecuteNonQuery()
最后，vorderid将针对插入的每个记录进行更改，文本将仅显示插入的最后一个vordid。
Finally, the vorderid is going to change for each record inserted and the text would only show the last vorderid inserted.
这篇关于只有一个记录从datagridview插入到Oracle数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
