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 ?
This C# script is embedded inside SSIS. Don't worry about the SSIS. I am pretty sure that the problem is only related to C#. Please don't add an SSIS tag for this.
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[i];
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()
EXTRA (you don't really need to know about SSIS) -