Invalid parameter binding\r\nParameter name: System.DateTime while uploading data to oracle table

Question

I want to insert records to database from Gridview on button click.

So I used this link to insert it.

But with my below code, I am getting error as

Invalid parameter binding\r\nParameter name: System.DateTime

<asp:GridView ID="grdExcelData" runat="server" AutoGenerateColumns="false" AllowPaging="true" PageSize="10" PageIndex="0" CssClass="table table-bordered">
                        <Columns>
                            <asp:BoundField ItemStyle-Width="50px" DataField="R4GSTATE" HeaderText="R4GSTATE" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="POLITICAL_STATE_NAME" HeaderText="POLITICAL_STATE_NAME" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="POLITICAL_STATE_CODE" HeaderText="POLITICAL_STATE_CODE" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="CMP" HeaderText="CMP" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="SAP_ID" HeaderText="SAP_ID" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="SITE_NAME" HeaderText="SITE_NAME" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="RFCDATE" HeaderText="RFCDATE" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="RFS_DATE" HeaderText="RFS_DATE" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="RFE1_DATE" HeaderText="RFE1_DATE" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="SITE_DROP_DATE" HeaderText="SITE_DROP_DATE" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="INFRA_PROVIDER" HeaderText="INFRA_PROVIDER" />                            
                            <asp:BoundField ItemStyle-Width="50px" DataField="IP_COLO_SITEID" HeaderText="IP_COLO_SITEID" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="VENDOR_CODE" HeaderText="VENDOR_CODE" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="MW_INSTALLED" HeaderText="MW_INSTALLED" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="DG_NONDG" HeaderText="DG_NONDG" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="EB_NONEB" HeaderText="EB_NONEB" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="TOWER_TYPE" HeaderText="TOWER_TYPE" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="ID_OD_COUNTCHANGE" HeaderText="ID_OD_COUNTCHANGE" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="ID_OD_CHANGEDDATE" HeaderText="ID_OD_CHANGEDDATE" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="RRH_COUNTCHANGE" HeaderText="RRH_COUNTCHANGE" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="IP_COLO_SITEID" HeaderText="IP_COLO_SITEID" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="RRH_CHANGEDDATE" HeaderText="RRH_CHANGEDDATE" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="TENANCY_COUNTCHANGE" HeaderText="TENANCY_COUNTCHANGE" />
                            <asp:BoundField ItemStyle-Width="50px" DataField="TENANCY_CHANGEDDATE" HeaderText="TENANCY_CHANGEDDATE" />
                        </Columns>
                    </asp:GridView>
                    
Server code                    


protected void btnSubmitUpload_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();

            ViewState["GridData"] = SelectedRecordID;
            dt = ViewState["GridData"] as DataTable;

            if (dt.Rows.Count > 0)
            {
                string consString = ConfigurationManager.ConnectionStrings["ConnectionString_IPCOLO"].ConnectionString;
                using (OracleConnection con = new OracleConnection(consString))
                {
                    con.Open();
                    using (OracleBulkCopy sqlBulkCopy = new OracleBulkCopy(con))
                    {
                        sqlBulkCopy.DestinationTableName = "TBL_IPCOLO_BILLING_MST";

                        sqlBulkCopy.ColumnMappings.Add("R4GSTATE", "R4GSTATE");
                        sqlBulkCopy.ColumnMappings.Add("POLITICAL_STATE_NAME", "POLITICAL_STATE_NAME");
                        sqlBulkCopy.ColumnMappings.Add("POLITICAL_STATE_CODE", "POLITICAL_STATE_CODE");
                        sqlBulkCopy.ColumnMappings.Add("CMP", "CMP");
                        sqlBulkCopy.ColumnMappings.Add("SAP_ID", "SAP_ID");
                        sqlBulkCopy.ColumnMappings.Add("SITE_NAME", "SITE_NAME");
                        sqlBulkCopy.ColumnMappings.Add("RFCDATE", "RFCDATE");
                        sqlBulkCopy.ColumnMappings.Add("RFS_DATE", "RFS_DATE");
                        sqlBulkCopy.ColumnMappings.Add("RFE1_DATE", "RFE1_DATE");
                        sqlBulkCopy.ColumnMappings.Add("SITE_DROP_DATE", "SITE_DROP_DATE");
                        sqlBulkCopy.ColumnMappings.Add("INFRA_PROVIDER", "INFRA_PROVIDER");
                        sqlBulkCopy.ColumnMappings.Add("IP_COLO_SITEID", "IP_COLO_SITEID");
                        sqlBulkCopy.ColumnMappings.Add("VENDOR_CODE", "VENDOR_CODE");
                        sqlBulkCopy.ColumnMappings.Add("MW_INSTALLED", "MW_INSTALLED");
                        sqlBulkCopy.ColumnMappings.Add("DG_NONDG", "DG_NONDG");
                        sqlBulkCopy.ColumnMappings.Add("EB_NONEB", "EB_NONEB");
                        sqlBulkCopy.ColumnMappings.Add("TOWER_TYPE", "TOWER_TYPE");
                        sqlBulkCopy.ColumnMappings.Add("ID_OD_COUNTCHANGE", "ID_OD_COUNTCHANGE");
                        sqlBulkCopy.ColumnMappings.Add("ID_OD_CHANGEDDATE", "ID_OD_CHANGEDDATE");
                        sqlBulkCopy.ColumnMappings.Add("RRH_COUNTCHANGE", "RRH_COUNTCHANGE");
                        sqlBulkCopy.ColumnMappings.Add("IP_COLO_SITEID", "IP_COLO_SITEID");
                        sqlBulkCopy.ColumnMappings.Add("RRH_CHANGEDDATE", "RRH_CHANGEDDATE");
                        sqlBulkCopy.ColumnMappings.Add("EB_NONEB", "EB_NONEB");
                        sqlBulkCopy.ColumnMappings.Add("TENANCY_COUNTCHANGE", "TENANCY_COUNTCHANGE");
                        sqlBulkCopy.ColumnMappings.Add("TENANCY_CHANGEDDATE", "TENANCY_CHANGEDDATE");

                        sqlBulkCopy.WriteToServer(dt);
                        con.Close();
                    }
                }
            }
        }

NOTE: I have few more columns beside this which I dont want to insert the data. Please suggest what is wrong

Answer 1

Instead of BulkCopy you could use Array Binding:

        using (OracleConnection oracleConnection = new OracleConnection("Data Source=...."))
        {
            string query = @"INSERT INTO mytable (COL1, COL2) VALUES (:col1, :col2)";

            oracleConnection.Open();

            using (var command = oracleConnection.CreateCommand())
            {
                command.CommandText = query;
                command.CommandType = CommandType.Text;
                command.BindByName = true;
                command.ArrayBindCount = dt.Rows.Count;

                command.Parameters.Add(":col1", OracleDbType.Int32, dt.AsEnumerable().Select(r => r.Field<Int32>("col1")).ToArray(), ParameterDirection.Input);
                command.Parameters.Add(":col2", OracleDbType.Date, dt.AsEnumerable().Select(r => r.Field<DateTime>("col2")).ToArray(), ParameterDirection.Input);

                int result = command.ExecuteNonQuery();
            }
        }