Database insert problem

Your wish is my command:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Text.RegularExpressions;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{

private static string _currentRecordTypeCode;

public static string currentRecordTypeCode
{
get
{
return _currentRecordTypeCode;
}
set
{
_currentRecordTypeCode = value;
}
}

private static string _previousRecordTypeCode;

public static string previousRecordTypeCode
{
get
{
return _previousRecordTypeCode;
}
set
{
_previousRecordTypeCode = value;
}
}

DataTable tbl = new DataTable();
DataSet dsTransactions = new DataSet();

protected void Page_Load(object sender, EventArgs e)
{
try
{
// TODO: Need to build the datatable based on the specific record type
buildType01DataTable();

// read the source VFC4 text file
using (StreamReader sr = new StreamReader(@"C:\Documents and Settings\byronking\My Documents\Visual Studio 2005\WebSites\ParseVCF4\VCF40002_test2.txt"))
{
string line = sr.ReadLine();

while (line != null)
{
if (line.StartsWith("8"))
{
// match the load transaction code at the header transaction block
Regex reg = new Regex(@"(?<=\s)\d{2}(?=\s)");
String recordTypeCode = Convert.ToString(Regex.Match(line, reg.ToString()));

// set the value of the transaction code property for further use
currentRecordTypeCode = recordTypeCode;
}

// Load transaction code key:
// 1 = add transactions
// 2 = delete transactions
// 3 = change transactions
// 4 = add/change transactions

else if (line.StartsWith("4"))
{
// TODO: write code to process the transaction lines from the file
// i.e., pump them into a dataset, then a database

if (previousRecordTypeCode == null)
{
string[] row = line.Split(new string[] { "\t" }, StringSplitOptions.None);
tbl.Rows.Add(row);
}
else if (previousRecordTypeCode == currentRecordTypeCode)
{
Console.WriteLine(line);
}
}
else if (line.StartsWith("9"))
{
//parseVCF4 pg = new parseVCF4();
previousRecordTypeCode = currentRecordTypeCode;
}

// Go to the next line in the text file
line = sr.ReadLine();
}
}
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{
// Bind the DataTable to a GridView to see the data as an alternative to creating a dataset
gv1.DataSource = tbl;
gv1.DataBind();

// Add the DataTable to a DataSet and bind that to the GridView
//dsTransactions.Tables.Add(tbl);
//gv1.DataSource = dsTransactions;
//gv1.DataBind();
}
}

static DataTable buildType01DataTable()
{
DataTable tbl = new DataTable();

DataColumn colTblAccountBalanceID = new DataColumn();
//colTblAccountBalanceID.DataType = System.Type.GetType("System.Int32");
colTblAccountBalanceID.ColumnName = "cardholderID";
colTblAccountBalanceID.AutoIncrement = true;
tbl.Columns.Add(colTblAccountBalanceID);

DataColumn colLoadTransactionCode = new DataColumn();
//colLoadTransactionCode.DataType = System.Type.GetType("System.Int32");
colLoadTransactionCode.ColumnName = "colLoadTransactionCode";
tbl.Columns.Add(colLoadTransactionCode);

DataColumn colAccountNumber = new DataColumn();
//colAccountNumber.DataType = System.Type.GetType("System.Int64");
colAccountNumber.ColumnName = "colAccountNumber";
tbl.Columns.Add(colAccountNumber);

DataColumn colClosingDate = new DataColumn();
//colClosingDate.DataType = System.Type.GetType("System.String");
colClosingDate.ColumnName = "colClosingDate";
tbl.Columns.Add(colClosingDate);

DataColumn colPeriod = new DataColumn();
//colPeriod.DataType = System.Type.GetType("System.Int32");
colPeriod.ColumnName = "colPeriod";
tbl.Columns.Add(colPeriod);

DataColumn colPreviousBalance = new DataColumn();
//colPreviousBalance.DataType = System.Type.GetType("System.Int64");
colPreviousBalance.ColumnName = "colPreviousBalance";
tbl.Columns.Add(colPreviousBalance);

DataColumn colCurrentBalance = new DataColumn();
//colCurrentBalance.DataType = System.Type.GetType("System.Double");
colCurrentBalance.ColumnName = "colCurrentBalance";
tbl.Columns.Add(colCurrentBalance);

DataColumn colCreditLimit = new DataColumn();
//colCreditLimit.DataType = System.Type.GetType("System.Double");
colCreditLimit.ColumnName = "colCreditLimit";
tbl.Columns.Add(colCreditLimit);

DataColumn colCurrentAmountDue = new DataColumn();
//colCurrentAmountDue.DataType = System.Type.GetType("System.Double");
colCurrentAmountDue.ColumnName = "colCurrentAmountDue";
tbl.Columns.Add(colCurrentAmountDue);

DataColumn colPastDueCount = new DataColumn();
//colPastDueCount.DataType = System.Type.GetType("System.Int32");
colPastDueCount.ColumnName = "colPastDueCount";
tbl.Columns.Add(colPastDueCount);

DataColumn colPastDueAmount = new DataColumn();
//colPastDueAmount.DataType = System.Type.GetType("System.Double");
colPastDueAmount.ColumnName = "colPastDueAmount";
tbl.Columns.Add(colPastDueAmount);

DataColumn colDisputedAmount = new DataColumn();
//colDisputedAmount.DataType = System.Type.GetType("System.Double");
colDisputedAmount.ColumnName = "colDisputedAmount";
tbl.Columns.Add(colDisputedAmount);

DataColumn colBillingCurrencyCode = new DataColumn();
//colBillingCurrencyCode.DataType = System.Type.GetType("System.Int32");
colBillingCurrencyCode.ColumnName = "colBillingCurrencyCode";
tbl.Columns.Add(colBillingCurrencyCode);

DataColumn colAmountPastDueCycle1 = new DataColumn();
//colAmountPastDueCycle1.DataType = System.Type.GetType("System.Int64");
colAmountPastDueCycle1.ColumnName = "colAmountPastDueCycle1";
tbl.Columns.Add(colAmountPastDueCycle1);

DataColumn colAmountPastDueCycle2 = new DataColumn();
//colAmountPastDueCycle2.DataType = System.Type.GetType("System.Int64");
colAmountPastDueCycle2.ColumnName = "colAmountPastDueCycle2";
tbl.Columns.Add(colAmountPastDueCycle2);

DataColumn colAmountPastDueCycle3 = new DataColumn();
//colAmountPastDueCycle3.DataType = System.Type.GetType("System.Int64");
colAmountPastDueCycle3.ColumnName = "colAmountPastDueCycle3";
tbl.Columns.Add(colAmountPastDueCycle3);

DataColumn colAmountPastDueCycle4 = new DataColumn();
//colAmountPastDueCycle4.DataType = System.Type.GetType("System.Int64");
colAmountPastDueCycle4.ColumnName = "colAmountPastDueCycle4";
tbl.Columns.Add(colAmountPastDueCycle4);

DataColumn colAmountPastDueCycle5 = new DataColumn();
//colAmountPastDueCycle5.DataType = System.Type.GetType("System.Int64");
colAmountPastDueCycle5.ColumnName = "colAmountPastDueCycle5";
tbl.Columns.Add(colAmountPastDueCycle5);

DataColumn colAmountPastDueCycle6 = new DataColumn();
//colAmountPastDueCycle6.DataType = System.Type.GetType("System.Int64");
colAmountPastDueCycle6.ColumnName = "colAmountPastDueCycle6";
tbl.Columns.Add(colAmountPastDueCycle6);

DataColumn colAmountPastDuePlusBillingCycles = new DataColumn();
//colAmountPastDuePlusBillingCycles.DataType = System.Type.GetType("System.Int32");
colAmountPastDuePlusBillingCycles.ColumnName = "colAmountPastDuePlusBillingCycles";
tbl.Columns.Add(colAmountPastDuePlusBillingCycles);

DataColumn colPastDueBillingCycle1Count = new DataColumn();
//colPastDueBillingCycle1Count.DataType = System.Type.GetType("System.Int32");
colPastDueBillingCycle1Count.ColumnName = "colPastDueBillingCycle1Count";
tbl.Columns.Add(colPastDueBillingCycle1Count);

DataColumn colPastDueBillingCycle2Count = new DataColumn();
//colPastDueBillingCycle2Count.DataType = System.Type.GetType("System.Int32");
colPastDueBillingCycle2Count.ColumnName = "colPastDueBillingCycle2Count";
tbl.Columns.Add(colPastDueBillingCycle2Count);

DataColumn colPastDueBillingCycle3Count = new DataColumn();
//colPastDueBillingCycle3Count.DataType = System.Type.GetType("System.Int32");
colPastDueBillingCycle3Count.ColumnName = "colPastDueBillingCycle3Count";
tbl.Columns.Add(colPastDueBillingCycle3Count);

DataColumn colPastDueBillingCycle4Count = new DataColumn();
//colPastDueBillingCycle4Count.DataType = System.Type.GetType("System.Int32");
colPastDueBillingCycle4Count.ColumnName = "colPastDueBillingCycle4Count";
tbl.Columns.Add(colPastDueBillingCycle4Count);

DataColumn colPastDueBillingCycle5Count = new DataColumn();
//colPastDueBillingCycle5Count.DataType = System.Type.GetType("System.Int32");
colPastDueBillingCycle5Count.ColumnName = "colPastDueBillingCycle5Count";
tbl.Columns.Add(colPastDueBillingCycle5Count);

DataColumn colPastDueBillingCycle6Count = new DataColumn();
//colPastDueBillingCycle6Count.DataType = System.Type.GetType("System.Int32");
colPastDueBillingCycle6Count.ColumnName = "colPastDueBillingCycle6Count";
tbl.Columns.Add(colPastDueBillingCycle6Count);

DataColumn colPastDuePlusBillingCyclesCount = new DataColumn();
//colPastDuePlusBillingCyclesCount.DataType = System.Type.GetType("System.Int32");
colPastDuePlusBillingCyclesCount.ColumnName = "colPastDuePlusBillingCyclesCount";
tbl.Columns.Add(colPastDuePlusBillingCyclesCount);

DataColumn colPastDueCyclesCount = new DataColumn();
//colPastDueCyclesCount.DataType = System.Type.GetType("System.Int32");
colPastDueCyclesCount.ColumnName = "colPastDueCyclesCount";
tbl.Columns.Add(colPastDueCyclesCount);

DataColumn colLastPaymentAmount = new DataColumn();
//colLastPaymentAmount.DataType = System.Type.GetType("System.Double");
colLastPaymentAmount.ColumnName = "colLastPaymentAmount";
tbl.Columns.Add(colLastPaymentAmount);

DataColumn colLastPaymentDate = new DataColumn();
//colLastPaymentDate.DataType = System.Type.GetType("System.Int32");
colLastPaymentDate.ColumnName = "colLastPaymentDate";
tbl.Columns.Add(colLastPaymentDate);

DataColumn colPaymentDueDate = new DataColumn();
//colPaymentDueDate.DataType = System.Type.GetType("System.Int32");
colPaymentDueDate.ColumnName = "colPaymentDueDate";
tbl.Columns.Add(colPaymentDueDate);

DataColumn colHighBalance = new DataColumn();
//colHighBalance.DataType = System.Type.GetType("System.Double");
colHighBalance.ColumnName = "colHighBalance";
tbl.Columns.Add(colHighBalance);

DataColumn colOptionalField1 = new DataColumn();
//colOptionalField1.DataType = System.Type.GetType("System.String");
colOptionalField1.ColumnName = "colOptionalField1";
tbl.Columns.Add(colOptionalField1);

DataColumn colOptionalField2 = new DataColumn();
//colOptionalField2.DataType = System.Type.GetType("System.String");
colOptionalField2.ColumnName = "colOptionalField2";
tbl.Columns.Add(colOptionalField2);

DataColumn colOptionalField3 = new DataColumn();
//colOptionalField3.DataType = System.Type.GetType("System.String");
colOptionalField3.ColumnName = "colOptionalField3";
tbl.Columns.Add(colOptionalField3);

DataColumn colOptionalField4 = new DataColumn();
//colOptionalField4.DataType = System.Type.GetType("System.String");
colOptionalField4.ColumnName = "colOptionalField4";
tbl.Columns.Add(colOptionalField4);

return tbl;
}

static void submitUpdatesViaSproc()
{
SqlDataAdapter daTransactions = new SqlDataAdapter();
daTransactions.InsertCommand = createInsertViaSprocCommand();
daTransactions.Update(buildType01DataTable());
}

static SqlCommand createInsertViaSprocCommand()
{
// Insert the records into the database
String strConn = ConfigurationManager.AppSettings["sqlConnectionString"];
SqlConnection dbConn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand("spInsertType1TransactionData", dbConn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameterCollection pc = cmd.Parameters;
pc.Add("tblAccountBalanceID", SqlDbType.Int, 0, "colTblAccountBalanceID");
pc.Add("loadTransactionCode", SqlDbType.Int, 0, "colLoadTransactionCode");
pc.Add("accountNumber", SqlDbType.VarChar, 0, "colAccountNumber");
pc.Add("closingDate", SqlDbType.VarChar, 0, "colClosingDate");
pc.Add("period", SqlDbType.Int, 0, "colPeriod");
pc.Add("previousBalance", SqlDbType.Decimal, 0, "colPreviousBalance");
pc.Add("currentBalance", SqlDbType.Decimal, 0, "colCurrentBalance");
pc.Add("creditLimit", SqlDbType.Decimal, 0, "colCreditLimit");
pc.Add("currentAmountDue", SqlDbType.Decimal, 0, "colCurrentAmountDue");
pc.Add("pastDueCount", SqlDbType.Int, 0, "colPastDueCount");
pc.Add("pastDueAmount", SqlDbType.Decimal, 0, "colPastDueAmount");
pc.Add("disputedAmount", SqlDbType.Decimal, 0, "colDisputedAmount");
pc.Add("currencyBillingCode", SqlDbType.Int, 0, "colCurrencyBillingCode");
pc.Add("amountPastDueCycle1", SqlDbType.Decimal, 0, "colAmountPastDueCycle1");
pc.Add("amountPastDueCycle2", SqlDbType.Decimal, 0, "colAmountPastDueCycle2");
pc.Add("amountPastDueCycle3", SqlDbType.Decimal, 0, "colAmountPastDueCycle3");
pc.Add("amountPastDueCycle4", SqlDbType.Decimal, 0, "colAmountPastDueCycle4");
pc.Add("amountPastDueCycle5", SqlDbType.Decimal, 0, "colAmountPastDueCycle5");
pc.Add("amountPastDueCycle6", SqlDbType.Decimal, 0, "colAmountPastDueCycle6");
pc.Add("amountPastDuePlusBillingCycles", SqlDbType.Decimal, 0, "colAmountPastDueCycle6");
pc.Add("pastDueBillingCycle1Count", SqlDbType.Int, 0, "colPastDueBillingCycle1Count");
pc.Add("pastDueBillingCycle2Count", SqlDbType.Int, 0, "colPastDueBillingCycle2Count");
pc.Add("pastDueBillingCycle3Count", SqlDbType.Int, 0, "colPastDueBillingCycle3Count");
pc.Add("pastDueBillingCycle4Count", SqlDbType.Int, 0, "colPastDueBillingCycle4Count");
pc.Add("pastDueBillingCycle5Count", SqlDbType.Int, 0, "colPastDueBillingCycle5Count");
pc.Add("pastDueBillingCycle6Count", SqlDbType.Int, 0, "colPastDueBillingCycle6Count");
pc.Add("pastDuePlusBillingCyclesCount", SqlDbType.Decimal, 0, "colAmountPastDueCycle6");
pc.Add("pastDueCyclesCount", SqlDbType.Int, 0, "colAmountPastDueCycle6");
pc.Add("lastPaymentAmount", SqlDbType.Decimal, 0, "colLastPaymentAmount");
pc.Add("lastPaymentDate", SqlDbType.VarChar, 0, "colLastPaymentDate");
pc.Add("paymentDueDate", SqlDbType.VarChar, 0, "colPaymentDueDate");
pc.Add("highBalance", SqlDbType.Decimal, 0, "colHighBalance");
pc.Add("optionalField1", SqlDbType.VarChar, 0, "colOptionalField1");
pc.Add("optionalField2", SqlDbType.VarChar, 0, "colOptionalField2");
pc.Add("optionalField3", SqlDbType.VarChar, 0, "colOptionalField3");
pc.Add("optionalField4", SqlDbType.VarChar, 0, "colOptionalField4");

return cmd;
}
}

Thanks for the reply. I did as you said and removed the code that defines the primary key in the parameter collection and when I create the datatable. Unfortunately, I still get the same error:

System.ArgumentException: Input array is longer than the number of columns in this table.