Looping a DataTable twice

• blasto_max

  SSCertifiable

  Points: 5025

  More actions

  November 5, 2013 at 10:51 am

  #279728

  I am using c# and .NET 3.5 in visual studio 2008. I wanted to loop over a result set stored in a data table twice. I used the following script and looped once successfully. When I loop it for the second time, i get an exception (given below). How do I fix this ? From my Java knowledge, I suspect it has something to with a row pointer reaching the last position. Correct or not ?

  Notes about the code - A database table has only one varchar column named OneColumn. It has 3 rows - One, two and three. I select this column and save it to an Object variable called "ResultSet". My C# script is supposed to iterate over the rows, join them and show them as a single string.

  After, that repeat the script. Although you might not need it, I have added the SSIS diagram also.

  using System;

  using System.Data;

  using Microsoft.SqlServer.Dts.Runtime;

  using System.Windows.Forms;

  using System.Xml;

  using System.Data.OleDb;

  namespace ST_bde893ffaa5d49efb7aed9d752cb900c.csproj

  {

  [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

  public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

  {

  #region VSTA generated code

  enum ScriptResults

  {

  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

  };

  #endregion

  public void Main()

  {

  OleDbDataAdapter oleDA = new OleDbDataAdapter();

  DataTable dt = new DataTable();

  DataColumn col1 = null;

  DataRow row = null;

  string strCols = "";

  oleDA.Fill(dt, Dts.Variables["ResultSet"].Value);

  col1 = dt.Columns["OneColumn"];

  int lastRow = dt.Rows.Count - 1;

  for (int i = 0; i <= lastRow - 1; i++)

  {

  row = dt.Rows;

  strCols = strCols + row[col1.Ordinal].ToString() + ", ";

  }

  row = dt.Rows[lastRow];

  strCols = strCols + row[col1.Ordinal].ToString();

  MessageBox.Show(strCols);

  Dts.TaskResult = (int)ScriptResults.Success;

  }

  }

  }

  Error -

  Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IndexOutOfRangeException: There is no row at position -1.

  at System.Data.RBTree`1.GetNodeByIndex(Int32 userIndex)

  at System.Data.RBTree`1.get_Item(Int32 index)

  at System.Data.DataRowCollection.get_Item(Int32 index)

  at ST_bde893ffaa5d49efb7aed9d752cb900c.csproj.ScriptMain.Main()

  --- End of inner exception stack trace ---

  at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)

  at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)

  at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

  at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)

  at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

  SSIS Package -

  

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  November 5, 2013 at 11:53 am

  #1664213

  You could greatly simplify your code by using a foreach instead of a for loop. This way you don't have to try to control the row counter. Your entire Main method could be shortened to this:

  OleDbDataAdapter oleDA = new OleDbDataAdapter();

  DataTable dt = new DataTable();

  oleDA.Fill(dt, Dts.Variables["ResultSet"].Value);

  System.Text.StringBuilder sbCols = new StringBuilder();

  foreach(DataRow dr in dt.Rows)

  {

  sbCols.Append(dr["OneColumn"].ToString());

  }

  MessageBox.Show(sbCols.ToString());

  Dts.TaskResult = (int)ScriptResults.Success;

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/