Click here to Skip to main content
15,881,812 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
My program tried to upload excel file and save into database but failed due to below below error :
{"ORA-01008: not all variables bound\n"}
{System.Data.OracleClient.OracleException}

What I have tried:

C#
using ClosedXML.Excel;
using System;
using System.Data.OracleClient;
using MESIntWeb.DataAccess;
 

protected void getExcelFileName()
        {
            DataTable dt = new DataTable();

            dynamic worksheet = 1;
			//filepath on server 
            string[] filename = Directory.GetFiles(@"\\tmpfs\data\insite\QA\BRCM_LotCheck", "*.xlsx");

            foreach (var item in filename)
            {
                Console.WriteLine(Path.GetFileNameWithoutExtension(item));
                using (XLWorkbook workBook = new XLWorkbook(item))
                {
                    //Read the first Sheet from Excel file.
                    IXLWorksheet workSheet = workBook.Worksheet(worksheet);

                    string readRange = "1:1";
                    //Loop through the Worksheet rows.
                    bool firstRow = true;
                    foreach (IXLRow row in workSheet.Rows())
                    {
                        //Use the first row to add columns to DataTable.
                        if (firstRow)
                        {
                            foreach (IXLCell cell in row.Cells(readRange))
                            {
                                dt.Columns.Add(cell.Value.ToString());
                            }
                            //validation on if column value is empty then ignore
                            firstRow = false;
                        }

                        else
                        {
                            int i = 0;
                            DataRow toInsert = dt.NewRow();
                            foreach (IXLCell cell in row.Cells(1, dt.Columns.Count))
                            {
                                try
                                {
                                    toInsert[i] = cell.Value.ToString();
                                }
                                catch (Exception ex)
                                {

                                }
                                i++;
                                //}

                                if (toInsert[0].ToString() != "")
                                {
                                    try
                                {

                                    OracleConnection oc = DBUtility.GetInstance().GetIntConnection();
                                    OracleCommand cmdInsert = oc.CreateCommand();

                                    cmdInsert.CommandText = "INSERT INTO LOT_CHECKING_1 (DATECREATED, DEVICE, LOTID, QTY, FG_PART) VALUES (:DATECREATED, :DEVICE, :LOTID, :QTY, :FG_PART)";
                                        

                                    cmdInsert.Parameters.AddWithValue(":DATECREATED", DATECREATED);
                                    cmdInsert.Parameters.AddWithValue(":DEVICE", DEVICE);
                                    cmdInsert.Parameters.AddWithValue(":LOTID", LOTID);
                                    cmdInsert.Parameters.AddWithValue(":QTY", QTY);
                                    cmdInsert.Parameters.AddWithValue(":FG_PART", FG_PART);

                                    //cmdInsert.Parameters.AddWithValue(":DATECREATED", toInsert[0]);
                                    //cmdInsert.Parameters.AddWithValue(":DEVICE", toInsert[1]);
                                    //cmdInsert.Parameters.AddWithValue(":LOTID", toInsert[2]);
                                    //cmdInsert.Parameters.AddWithValue(":QTY", toInsert[3]);
                                    //cmdInsert.Parameters.AddWithValue(":FG_PART", toInsert[4]);

                                    cmdInsert.ExecuteNonQuery();
                                    LabelBRCM.Text = "Data Sucessfully Uploaded";
                                    }
                                    catch (Exception ex)
                                    {

                                    }
                                    finally
                                    {
                                        DBUtility.GetInstance().CloseConnection();
                                    }
                                    //}
                                }
                            }
                        }
                    }
                    dt.Columns.Clear();
                    LabelBRCM.Text = "File Generated";
                }

            }
        }
Posted
Updated 4-Aug-22 21:46pm
v2

1 solution

It looks that your code never assigns any values to the variables DATECREATED, DEVICE, LOTID, ...
If the content of a variable is null (because not assigning it or it's the actual value) you should use DBNull.Value[^] instead when assigning a value to the parameter.
 
Share this answer
 

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