Click here to Skip to main content
15,179,246 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I use the following code to bulk insert to Access and it's successful! But..(see the next part of code)
C#
DataTable dt = new DataTable();

OleDbConnection read_con = new OleDbConnection(ConnectionString);
OleDbDataAdapter read_adp = new OleDbDataAdapter("select GID, IntInfo From OPMSIntStorage Where GID = ''", read_con);

read_adp.Fill(dt);

for (int i = 0; i < 5; i++)
{
    DataRow r = dt.NewRow();
    r["GID"] = i.ToString();    // String Type Column in OPMSIntStorage Table
    r["IntInfo"] = i;           // Int Type Column in OPMSIntStorage Table
    dt.Rows.Add(r);
}

OleDbConnection save_con = new OleDbConnection(ConnectionString);
OleDbDataAdapter save_adp = new OleDbDataAdapter("select GID, IntInfo From OPMSIntStorage Where GID = ''", save_con);

save_adp.InsertCommand = new OleDbCommand("Insert into OPMSIntStorage (GID, IntInfo) Values (P_GID, P_IntInfo)");
save_adp.InsertCommand.Parameters.Add("P_GID", OleDbType.BSTR, 255, "GID");
save_adp.InsertCommand.Parameters.Add("P_IntInfo", OleDbType.Integer, 4, "IntInfo");
save_adp.InsertCommand.Connection = save_con;

save_adp.InsertCommand.Connection.Open();
save_adp.Update(dt);
save_adp.InsertCommand.Connection.Close();


I want to change the code and then it can bulk insert into Oracle. I simply replaced "OleDb" to "Oracle" and "OleDbType.BSTR" to "OracleType.VarChar" and "OleDbType.Integer" to "OracleType.Int32". But there was a error occur!(ORA-01036: illegal variable name/number
). I have reviewed many times, the var name and number and type are both right! Please help me! Thanks!
C#
DataTable dt = new DataTable();

OracleConnection read_con = new OracleConnection(ConnectionString);
OracleDataAdapter read_adp = new OracleDataAdapter("select GID, IntInfo From OPMSIntStorage Where GID = ''", read_con);

read_adp.Fill(dt);

for (int i = 0; i < 5; i++)
{
    DataRow r = dt.NewRow();
    r["GID"] = i.ToString();    // String Type Column in OPMSIntStorage Table
    r["IntInfo"] = i;           // Int Type Column in OPMSIntStorage Table
    dt.Rows.Add(r);
}

OracleConnection save_con = new OracleConnection(ConnectionString);
OracleDataAdapter save_adp = new OracleDataAdapter("select GID, IntInfo From OPMSIntStorage Where GID = ''", save_con);

save_adp.InsertCommand = new OracleCommand("Insert into OPMSIntStorage (GID, IntInfo) Values (P_GID, P_IntInfo)");
save_adp.InsertCommand.Parameters.Add("P_GID", OracleType.VarChar, 255, "GID");
save_adp.InsertCommand.Parameters.Add("P_IntInfo", OracleType.Int32, 4, "IntInfo");
save_adp.InsertCommand.Connection = save_con;

save_adp.InsertCommand.Connection.Open();
save_adp.Update(dt);
save_adp.InsertCommand.Connection.Close();
Posted

1 solution

Check the generated statements. In Oracle to pass correctly the parameter use a : (Colon) prefix and not @ ( at-sign) like sql server.

https://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleparametercollection.aspx[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900