none
How to bulk insert without getting "Object reference not set to an instance of an object" RRS feed

  • Question

  • Hi,

     

    I am trying to insert about couple thousand of rows into the database, but about middle of insertion, I get an "Object reference not set to an instance of an object", it able to insert first thousand rows but after that, I am getting an "Object reference not set to an instance of an object".

     

       getSrcData = GetData();
       data = getSrcData .Split('`');

     

       this.oleCon.ConnectionString = config.Target;
       this.oleCon.Open();
       this.oleCmd.Connection  = this.oleCon;

       this.oleTrn = this.oleCon.BeginTransaction();
       this.oleCmd.Transaction = this.oleTrn;

     

    //insert one row at a time

     for (int idx=0; idx < data.Length; idx++)
       {
        try
        {
         insertValues = data.GetValue(idx).ToString();

     

         //this insertValues will be like "insert into sample.table values (abc)"
         insertValues = insertQuery.Replace("XXX",insertValues);
         this.oleCmd.CommandText = insertValues;
         this.oleCmd.ExecuteNonQuery();
        }
        catch(Exception e)
        {
          WriteToLog(config.Log_dir, e.Message + " -- " + insertValues);
         }
        }
       
       this.oleTrn.Commit();
       if (oleCon.State == ConnectionState.Open)
        this.oleCon.Close();

    Tuesday, October 30, 2007 2:32 PM

All replies

  • The stack trace tells you where the exception occurred. You didn't say where, so without that piece of information I am just left to do some guessing.

     

    Is this a multithreaded application? Looks like "oleCon", etc are member variables, and as such are visible to other methods, and/or multiple threads invoking the method whose parts of contents you posted here. I would guess that another thread is changing one or more of the references while you're in the loop, if you haven't designed this properly as thread-safe.

     

    Tuesday, October 30, 2007 2:40 PM
  • oleCon is not a member variable, it is a local variable inside the function/method and there is only one thread calling this method. 

    Tuesday, October 30, 2007 5:11 PM
  •  kcchen1227 wrote:

    oleCon is not a member variable, it is a local variable inside the function/method and there is only one thread calling this method. 

    That contradicts the code snippet you posted. Let's take this one line for instance:

    Code Block

    this.oleCon.ConnectionString = config.Target;

     

    If it were a local variable, then you could not prefix it with "this.".
    Wednesday, October 31, 2007 2:22 PM
  • Yeah, you are right, it is a member variable.

    Wednesday, October 31, 2007 3:02 PM
  • Hi,

    The message you got means that your are invoking a method on a null variable. In the loop the only line that can generate this exception is this one :

     

      insertValues = data.GetValue(idx).ToString();

     

    I think that you have a null value at some index so when invoking ToString() method you got this exception. Try add and if condition to check if the data.GetValue(idx) is null or not. Or simply add a conditionnal break point.

     

    HTH

     

    Thursday, November 1, 2007 3:15 PM