Hi! I'm attempting to insert records from a tab-delimited text file into a database. I can successfully parse the text file and get the data into a datatable. I verified this by binding the datatable to a gridview - I can see all the rows. My problem is when I try to insert those records into a SQL 2005 table. The error I get is:
System.ArgumentException: Input array is longer than the number of columns in
this table.
My database has 36 columns. When I generate the datatable, I leave out the primary key column because such a column doesn't exist in the text file. Obviously, I'll need a primary key in the database. My goal is to insert the data via stored procedure. Does my stored procedure have to have a parameter for the primary key in the database What am I doing wrong here Thanks!
Database insert problem
Hot Topic
- Com Exception is unhandeled while executing ExecuteReader
- Sharing wizard-created datasets in a solution
- .NET equivalent of std::set
- Gettting Object values from ArrayList
- Exception: DataTable internal index is corrupted: '5'. on ...
- "Could not create type..." error deploying webservice
- Validating XML Uploaded via FileUpload (ASP.NET)
- Backup and Restore a sql database using vb.net
- SQL Connection error
- top-level param default value is ignored
.NET Development
- writing as plaint-text with StreamWriter
- Deserialization Error: "Unable to find assembly"
- ASP .NET Web Service in .NET 2.0 upgrade to .NET 3.0
- Why my C# application(managed) can not talk with my C++ DLL(native) correctly in 64 bit platform?
- Conversion problem
- Warning when building Web Service..Help!
- How to retry last statement in try{}catch{} block?
- .NET vs Trolltech's QT
- .Net Remoting And ReaderWriterLock
- Read Value in xml type
Database insert problem
Leo12
Hi byronfromwesleyan
I see that in your buildType01DataTable you name the primary key "cardholderID" and in createInsertViaSprocCommand you specify it as "colTblAccountBalanceID"
Would not it be the problem
On the other hand is the primary key auto generated by you data base or not in which case it may not like you try to force it !
Cheers!
davidjmsdn
No, Do not include an autogenerated Primary Key in your parameter collection. The Db will take care of generating this member as long as the other fields contain acceptable data and the record is actually added to the Db
MickRivers.
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.
Any new suggestions are greatly appreciated. Thanks.
byronfromwesleyan
FinallyInSeattle
Thanks for the response! You said:
I see that in your buildType01DataTable you name the primary key "cardholderID" and in createInsertViaSprocCommand you specify it as "colTblAccountBalanceID"
I JUST noticed that! This is why it's better to have others look at your code; it's so easy to miss little things like that.
So, should I remove all instances where I try to reference the primary key: in my creation of the datatable and when I add parameters to my insert via sproc code
Regards,
byronfromwesleyan
siavoshkc
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;
}
}
kymaita
byronfromwesleyan,
Concerning you primary key, as I said in my previous post:
if your primary key is auto generated at the database level, you should NOT specify it in the insert statement, otherwise you must.
On the other hand, if it is auto generated, you may then need to retrieve it from the database in order to handle it.
The way of doing so depends on your database
You should have a look at the following for this:
http://msdn2.microsoft.com/en-us/library/ks9f57t0.aspx
SoulSolutions
Thanks for the code
...I think the above quoted section is what we need to focus on...
AND
Is BalanceId the primary key
pc.Add("tblAccountBalanceID", SqlDbType.Int, 0, "colTblAccountBalanceID");
Is your 36 columns include the primarykey column Your adding 36 parameters.
Have you considered using Child tables for your "cycle" fields
Jarod.Net
Rob Thomson
tblAccountBalanceID is indeed my primary key. You can see from the datatable create code that I add this column. When I first asked the question, this code was commented out, so my initial statement of :
...My database has 36 columns. When I generate the datatable, I leave out the primary key column because such a column doesn't exist in the text file. ....
was true.
Also, when I add parameters to the collection, I include the primary key. Should I not do this
Thanks,
byronfromwesleyan