Why can't I execute a long INSERT SQL statement?

  • Thread starter Thread starter moonriver
  • Start date Start date
M

moonriver

I attempt to insert rows into a table in SQL Server 2000,
by using SQLCommand.ExecuteNonQuery() of C# .Net. Since
there are many columns in the table, the Insert SQL
statement is very long, leading to the following exception:

CommandText = Insert into job values
('2003.10.19026246',262144,0,64,1,0,'2003-10
-19 00:00:06.000','2003-10-19
00:00:27.000',0,'SpeedCall ','65866077 ',
301989888,0,0,46,0,'SH 1505L ',487498,'1970-01-01
08:00:00.000',0,1,4986,'
',1,11,0,'65866077
',' ')
System.Data.SqlClient.SqlException: String or binary data
would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at dump_tool.Dump_Load.load_rec()
Neither record is written to the database!
Load rec #1 into DB error!!!

However, to be confused, I am able to execute the above
SQL command smoothly on SQL Query Analyzer. In addition,
if I just insert two columns into the table, it can work
as well.

Now I attach my source codes below for your experts'
guides:

namespace dump_tool
{
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Windows.Forms;
using System.Diagnostics;
using System.IO;

public class Dump_Load
{
string job_num;
uint fstatus;
uint fstatus1;
uint dm_status;
uint dm_status1;
uint old_job_num;
DateTime entry_time;
DateTime confirm_time;
uint cust_num;
string cust_name;
string account_num;
uint sys_ftypes;
uint sys_ftypes1;
ushort veh_class;
ushort operator_id;
ushort job_fleet;
string license_plates;
uint driver_id;
DateTime pick_time;
uint pick_addrs_id;
ushort quantity;
ushort fi_taxi;
string dest_info;
ushort service_recnum;
ushort jobtype_recnum;
ushort license_recnum;
string phone_1;
string phone_2;

short day_off;
DateTime that_day;
string src_dir;
string dest_dir;
string log_dir;
string src_file;
string dest_file;
string log_file;
DateTime orig_time;

SqlConnection myConnection;
SqlCommand myCommand;
SqlTransaction myTrans;

public Dump_Load(string[] args)
{
src_dir = args[1];
dest_dir = args[2];
log_dir = args[3];
day_off = Convert.ToInt16( args[0] );
orig_time = new DateTime( 1970, 1, 1, 8, 0,
0 );
that_day = DateTime.Now.AddDays( -day_off );
src_file = src_dir + "\\" + that_day.ToString
("MMMyy").ToLower() + "\\" + Convert.ToString
(that_day.Day) + "\\fares.fl";
dest_file = dest_dir + "\\" + that_day.ToString
("yyyy") + "\\" + that_day.ToString("MMM").ToLower()
+ "\\" + Convert.ToString(that_day.Day) + "\\fares.txt";
log_file = log_dir + "\\logs\\" + that_day.ToString
("yyyy") + "\\" + that_day.ToString("MMM").ToLower()
+ "\\" + Convert.ToString(that_day.Day)
+ "\\dump_load.log";
Console.WriteLine("src_file = {0}", src_file);
Console.WriteLine("dest_file = {0}", dest_file);
Console.WriteLine("log_file = {0}", log_file);
}

static void Main(string[] args)
{
if( args.Length != 4 )
{
Console.WriteLine("Usage: dump_load day_off
src_dir dest_dir log_dir");
Process.GetCurrentProcess().Kill();
}
Dump_Load dl = new Dump_Load( args );
dl.run();
}

public void run()
{
int nBytesRead;
int k;

myConnection = new SqlConnection("server=(local)
\\DANIEL;Trusted_Connection=yes;database=daniel1");
myCommand = new SqlCommand();
// Assign the connection property.
myCommand.Connection = myConnection;
myCommand.CommandType = CommandType.Text;

// Open the connection.
try
{
myConnection.Open();
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine("myConnection.Open() fails!!!");
return;
}

int num_recs = Convert.ToInt32( new FileInfo
(src_file).Length / 2048 - 1 );
Console.WriteLine( "num_recs = {0}", num_recs );

using( FileStream fs = File.Open(src_file,
FileMode.Open, FileAccess.Read, FileShare.None) )
{
// Read 2k bytes into an array from the specified
file.
byte[] ByteArray=new byte[2048];
nBytesRead = fs.Read(ByteArray, 0, 2048);
for( k = 1; k <= 1; k++ )
{
try
{
Console.WriteLine( "k = {0}", k );
nBytesRead = fs.Read(ByteArray, 0, 2048);
if( nBytesRead != 2048 )
{
Console.WriteLine("Read rec #{0} error:
nBytesRead = {1}!", k, nBytesRead);
continue;
}
// Process the kth record
if( !proc_rec( ByteArray ) )
{
Console.WriteLine("Process rec #{0} error!!!",
k);
continue;
}
if( !load_rec() )
{
Console.WriteLine("Load rec #{0} into DB
error!!!", k);
continue;
}
if( k % 100 == 0 )
Console.WriteLine("{0} records have been read
from the specified file!", k);
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine("Rec #{0} fails!!!", k);
continue;
}
}
fs.Close();
}
// Close the connection.
try
{
myConnection.Close();
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine("myConnection.Close() fails!!!");
return;
}
}

public bool load_rec()
{
try
{
// Begin the transaction.
myTrans = myConnection.BeginTransaction();
// Assign transaction object for a pending local
transaction
myCommand.Transaction = myTrans;

// Insert the first record.
myCommand.CommandText = "Insert into job values(";
myCommand.CommandText += "\'" + Convert.ToString(
job_num ) + "\'" + ",";
myCommand.CommandText += Convert.ToString(
fstatus ) + ",";
myCommand.CommandText += Convert.ToString(
fstatus1 ) + ",";
myCommand.CommandText += Convert.ToString(
dm_status ) + ",";
myCommand.CommandText += Convert.ToString(
dm_status1 ) + ",";
myCommand.CommandText += Convert.ToString(
old_job_num ) + ",";
myCommand.CommandText += "\'" + entry_time.ToString
( "yyyy-MM-dd HH:mm:ss.fff" ) + "\'" + ",";
myCommand.CommandText += "\'" +
confirm_time.ToString( "yyyy-MM-dd HH:mm:ss.fff" ) + "\'"
+ ",";
myCommand.CommandText += Convert.ToString(
cust_num ) + ",";
myCommand.CommandText += "\'" + Convert.ToString(
cust_name ) + "\'" + ",";
myCommand.CommandText += "\'" + Convert.ToString(
account_num ) + "\'" + ",";
myCommand.CommandText += Convert.ToString(
sys_ftypes ) + ",";
myCommand.CommandText += Convert.ToString(
sys_ftypes1 ) + ",";
myCommand.CommandText += Convert.ToString(
veh_class ) + ",";
myCommand.CommandText += Convert.ToString(
operator_id ) + ",";
myCommand.CommandText += Convert.ToString(
job_fleet ) + ",";
myCommand.CommandText += "\'" + Convert.ToString(
license_plates ) + "\'" + ",";
myCommand.CommandText += Convert.ToString(
driver_id ) + ",";
myCommand.CommandText += "\'" + pick_time.ToString
( "yyyy-MM-dd HH:mm:ss.fff" ) + "\'" + ",";
myCommand.CommandText += Convert.ToString(
pick_addrs_id ) + ",";
myCommand.CommandText += Convert.ToString(
quantity ) + ",";
myCommand.CommandText += Convert.ToString(
fi_taxi ) + ",";
myCommand.CommandText += "\'" + Convert.ToString(
dest_info ) + "\'" + ",";
myCommand.CommandText += Convert.ToString(
service_recnum ) + ",";
myCommand.CommandText += Convert.ToString(
jobtype_recnum ) + ",";
myCommand.CommandText += Convert.ToString(
license_recnum ) + ",";
myCommand.CommandText += "\'" + Convert.ToString(
phone_1 ) + "\'" + ",";
myCommand.CommandText += "\'" + Convert.ToString(
phone_2 ) + "\'" + ")";

Console.WriteLine("CommandText = {0}",
myCommand.CommandText);
myCommand.ExecuteNonQuery();
Console.WriteLine("Before myTrans.Commit()");
myTrans.Commit();
Console.WriteLine("1 record has been written to
the database!");
return true;
}
catch(Exception e)
{
myTrans.Rollback();
Console.WriteLine(e.ToString());
Console.WriteLine("Neither record is written to
the database!");
return false;
}
}
public bool proc_rec( byte[] ByteArray )
{
char[] CharArray = new char[2048];
uint entry_value;
try
{
for( int k = 0; k < 2048; k++ )
CharArray[k] = Convert.ToChar( ByteArray[k] );

entry_value = conv_uint( ByteArray, 872, 4 );
Console.WriteLine( "entry_value = {0}",
entry_value );
entry_time = orig_time.AddSeconds( Convert.ToDouble
( entry_value ) );
Console.WriteLine( "entry_time = {0}",
entry_time );
confirm_time = orig_time.AddSeconds(
Convert.ToDouble( conv_uint( ByteArray, 884, 4 ) ) );
pick_time = orig_time.AddSeconds( Convert.ToDouble
( conv_uint( ByteArray, 936, 4 ) ) );
job_num = entry_time.ToString( "yyyy.MM.dd" ) +
Convert.ToString( conv_uint( ByteArray, 0, 4 ) ).PadLeft(
6, '0' );
fstatus = conv_uint( ByteArray, 4, 4 );
fstatus1 = conv_uint( ByteArray, 8, 4 );
dm_status = conv_uint( ByteArray, 12, 4 );
dm_status1 = conv_uint( ByteArray, 16, 4 );
old_job_num = conv_uint( ByteArray, 1836, 4 );
cust_num = conv_uint( ByteArray, 20, 4 );
cust_name = new String( CharArray, 665,
15 ).ToString();
account_num = new String( CharArray, 1506,
13 ).ToString();
sys_ftypes = conv_uint( ByteArray, 40, 4 );
sys_ftypes1 = conv_uint( ByteArray, 44, 4 );
veh_class = conv_ushort( ByteArray, 1378, 2 );
operator_id = conv_ushort( ByteArray, 868,
2 );
job_fleet = conv_ushort( ByteArray, 1364, 2 );
license_plates = new String( CharArray, 1563,
9 ).ToString();
driver_id = conv_uint( ByteArray, 28, 4 );
pick_addrs_id = 0;
quantity = conv_ushort( ByteArray, 1350, 2 );
fi_taxi = conv_ushort( ByteArray, 1360, 2 );
dest_info = new String( CharArray, 1949,
41 ).ToString();
service_recnum = conv_ushort( ByteArray, 1992, 2 );
jobtype_recnum = conv_ushort( ByteArray, 1996,
2 );
license_recnum = conv_ushort( ByteArray, 2000, 2 );
phone_1 = new String( CharArray, 708, 14 ).ToString
();
phone_2 = new String( CharArray, 728, 14 ).ToString
();
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine("Fails in proc_rec()!!!");
return false;
}
return true;
}
public uint conv_uint( byte[] ByteArray, int offset,
int nbytes )
{
uint value = 0;

for( int k = 0; k <= nbytes-1; k++ )
value = value * 256 + Convert.ToUInt32( ByteArray
[offset+k] );
return value;
}
public ushort conv_ushort( byte[] ByteArray, int
offset, int nbytes )
{
ushort value = 0;

for( int k = 0; k <= nbytes-1; k++ )
{
value *= 256;
value += Convert.ToUInt16( ByteArray
[offset+k] );
}
return value;
}
}
}
 
Let me ask a few questions .... You say that neither statement is
updated... so will both of them also work in QA? I know that's probably a
stupid question but just being safe. This problem usually happens when you
try to stick something in a field that's is smaller than what you are trying
to use. As a general approach, I'd highly recommend getting rid of all of
the concatenations and instead, replace them with Paramaters (ideally a
Stored Proc too if that's an option). When you add the parameters, make
sure you match the type and size so they match the db exactly. That will
definetely give you a cleaner implementation and make it easier to debug.
I've certianly seen code where the commandtext's length is a lot bigger than
your so I don't think lenght is the issue. You may also want to try
removing a few of the params and see which one is the one that causes it to
fail.

HTH,

Bill
moonriver said:
I attempt to insert rows into a table in SQL Server 2000,
by using SQLCommand.ExecuteNonQuery() of C# .Net. Since
there are many columns in the table, the Insert SQL
statement is very long, leading to the following exception:

CommandText = Insert into job values
('2003.10.19026246',262144,0,64,1,0,'2003-10
-19 00:00:06.000','2003-10-19
00:00:27.000',0,'SpeedCall ','65866077 ',
301989888,0,0,46,0,'SH 1505L ',487498,'1970-01-01
08:00:00.000',0,1,4986,'
',1,11,0,'65866077
',' ')
System.Data.SqlClient.SqlException: String or binary data
would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at dump_tool.Dump_Load.load_rec()
Neither record is written to the database!
Load rec #1 into DB error!!!

However, to be confused, I am able to execute the above
SQL command smoothly on SQL Query Analyzer. In addition,
if I just insert two columns into the table, it can work
as well.

Now I attach my source codes below for your experts'
guides:

namespace dump_tool
{
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Windows.Forms;
using System.Diagnostics;
using System.IO;

public class Dump_Load
{
string job_num;
uint fstatus;
uint fstatus1;
uint dm_status;
uint dm_status1;
uint old_job_num;
DateTime entry_time;
DateTime confirm_time;
uint cust_num;
string cust_name;
string account_num;
uint sys_ftypes;
uint sys_ftypes1;
ushort veh_class;
ushort operator_id;
ushort job_fleet;
string license_plates;
uint driver_id;
DateTime pick_time;
uint pick_addrs_id;
ushort quantity;
ushort fi_taxi;
string dest_info;
ushort service_recnum;
ushort jobtype_recnum;
ushort license_recnum;
string phone_1;
string phone_2;

short day_off;
DateTime that_day;
string src_dir;
string dest_dir;
string log_dir;
string src_file;
string dest_file;
string log_file;
DateTime orig_time;

SqlConnection myConnection;
SqlCommand myCommand;
SqlTransaction myTrans;

public Dump_Load(string[] args)
{
src_dir = args[1];
dest_dir = args[2];
log_dir = args[3];
day_off = Convert.ToInt16( args[0] );
orig_time = new DateTime( 1970, 1, 1, 8, 0,
0 );
that_day = DateTime.Now.AddDays( -day_off );
src_file = src_dir + "\\" + that_day.ToString
("MMMyy").ToLower() + "\\" + Convert.ToString
(that_day.Day) + "\\fares.fl";
dest_file = dest_dir + "\\" + that_day.ToString
("yyyy") + "\\" + that_day.ToString("MMM").ToLower()
+ "\\" + Convert.ToString(that_day.Day) + "\\fares.txt";
log_file = log_dir + "\\logs\\" + that_day.ToString
("yyyy") + "\\" + that_day.ToString("MMM").ToLower()
+ "\\" + Convert.ToString(that_day.Day)
+ "\\dump_load.log";
Console.WriteLine("src_file = {0}", src_file);
Console.WriteLine("dest_file = {0}", dest_file);
Console.WriteLine("log_file = {0}", log_file);
}

static void Main(string[] args)
{
if( args.Length != 4 )
{
Console.WriteLine("Usage: dump_load day_off
src_dir dest_dir log_dir");
Process.GetCurrentProcess().Kill();
}
Dump_Load dl = new Dump_Load( args );
dl.run();
}

public void run()
{
int nBytesRead;
int k;

myConnection = new SqlConnection("server=(local)
\\DANIEL;Trusted_Connection=yes;database=daniel1");
myCommand = new SqlCommand();
// Assign the connection property.
myCommand.Connection = myConnection;
myCommand.CommandType = CommandType.Text;

// Open the connection.
try
{
myConnection.Open();
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine("myConnection.Open() fails!!!");
return;
}

int num_recs = Convert.ToInt32( new FileInfo
(src_file).Length / 2048 - 1 );
Console.WriteLine( "num_recs = {0}", num_recs );

using( FileStream fs = File.Open(src_file,
FileMode.Open, FileAccess.Read, FileShare.None) )
{
// Read 2k bytes into an array from the specified
file.
byte[] ByteArray=new byte[2048];
nBytesRead = fs.Read(ByteArray, 0, 2048);
for( k = 1; k <= 1; k++ )
{
try
{
Console.WriteLine( "k = {0}", k );
nBytesRead = fs.Read(ByteArray, 0, 2048);
if( nBytesRead != 2048 )
{
Console.WriteLine("Read rec #{0} error:
nBytesRead = {1}!", k, nBytesRead);
continue;
}
// Process the kth record
if( !proc_rec( ByteArray ) )
{
Console.WriteLine("Process rec #{0} error!!!",
k);
continue;
}
if( !load_rec() )
{
Console.WriteLine("Load rec #{0} into DB
error!!!", k);
continue;
}
if( k % 100 == 0 )
Console.WriteLine("{0} records have been read
from the specified file!", k);
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine("Rec #{0} fails!!!", k);
continue;
}
}
fs.Close();
}
// Close the connection.
try
{
myConnection.Close();
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine("myConnection.Close() fails!!!");
return;
}
}

public bool load_rec()
{
try
{
// Begin the transaction.
myTrans = myConnection.BeginTransaction();
// Assign transaction object for a pending local
transaction
myCommand.Transaction = myTrans;

// Insert the first record.
myCommand.CommandText = "Insert into job values(";
myCommand.CommandText += "\'" + Convert.ToString(
job_num ) + "\'" + ",";
myCommand.CommandText += Convert.ToString(
fstatus ) + ",";
myCommand.CommandText += Convert.ToString(
fstatus1 ) + ",";
myCommand.CommandText += Convert.ToString(
dm_status ) + ",";
myCommand.CommandText += Convert.ToString(
dm_status1 ) + ",";
myCommand.CommandText += Convert.ToString(
old_job_num ) + ",";
myCommand.CommandText += "\'" + entry_time.ToString
( "yyyy-MM-dd HH:mm:ss.fff" ) + "\'" + ",";
myCommand.CommandText += "\'" +
confirm_time.ToString( "yyyy-MM-dd HH:mm:ss.fff" ) + "\'"
+ ",";
myCommand.CommandText += Convert.ToString(
cust_num ) + ",";
myCommand.CommandText += "\'" + Convert.ToString(
cust_name ) + "\'" + ",";
myCommand.CommandText += "\'" + Convert.ToString(
account_num ) + "\'" + ",";
myCommand.CommandText += Convert.ToString(
sys_ftypes ) + ",";
myCommand.CommandText += Convert.ToString(
sys_ftypes1 ) + ",";
myCommand.CommandText += Convert.ToString(
veh_class ) + ",";
myCommand.CommandText += Convert.ToString(
operator_id ) + ",";
myCommand.CommandText += Convert.ToString(
job_fleet ) + ",";
myCommand.CommandText += "\'" + Convert.ToString(
license_plates ) + "\'" + ",";
myCommand.CommandText += Convert.ToString(
driver_id ) + ",";
myCommand.CommandText += "\'" + pick_time.ToString
( "yyyy-MM-dd HH:mm:ss.fff" ) + "\'" + ",";
myCommand.CommandText += Convert.ToString(
pick_addrs_id ) + ",";
myCommand.CommandText += Convert.ToString(
quantity ) + ",";
myCommand.CommandText += Convert.ToString(
fi_taxi ) + ",";
myCommand.CommandText += "\'" + Convert.ToString(
dest_info ) + "\'" + ",";
myCommand.CommandText += Convert.ToString(
service_recnum ) + ",";
myCommand.CommandText += Convert.ToString(
jobtype_recnum ) + ",";
myCommand.CommandText += Convert.ToString(
license_recnum ) + ",";
myCommand.CommandText += "\'" + Convert.ToString(
phone_1 ) + "\'" + ",";
myCommand.CommandText += "\'" + Convert.ToString(
phone_2 ) + "\'" + ")";

Console.WriteLine("CommandText = {0}",
myCommand.CommandText);
myCommand.ExecuteNonQuery();
Console.WriteLine("Before myTrans.Commit()");
myTrans.Commit();
Console.WriteLine("1 record has been written to
the database!");
return true;
}
catch(Exception e)
{
myTrans.Rollback();
Console.WriteLine(e.ToString());
Console.WriteLine("Neither record is written to
the database!");
return false;
}
}
public bool proc_rec( byte[] ByteArray )
{
char[] CharArray = new char[2048];
uint entry_value;
try
{
for( int k = 0; k < 2048; k++ )
CharArray[k] = Convert.ToChar( ByteArray[k] );

entry_value = conv_uint( ByteArray, 872, 4 );
Console.WriteLine( "entry_value = {0}",
entry_value );
entry_time = orig_time.AddSeconds( Convert.ToDouble
( entry_value ) );
Console.WriteLine( "entry_time = {0}",
entry_time );
confirm_time = orig_time.AddSeconds(
Convert.ToDouble( conv_uint( ByteArray, 884, 4 ) ) );
pick_time = orig_time.AddSeconds( Convert.ToDouble
( conv_uint( ByteArray, 936, 4 ) ) );
job_num = entry_time.ToString( "yyyy.MM.dd" ) +
Convert.ToString( conv_uint( ByteArray, 0, 4 ) ).PadLeft(
6, '0' );
fstatus = conv_uint( ByteArray, 4, 4 );
fstatus1 = conv_uint( ByteArray, 8, 4 );
dm_status = conv_uint( ByteArray, 12, 4 );
dm_status1 = conv_uint( ByteArray, 16, 4 );
old_job_num = conv_uint( ByteArray, 1836, 4 );
cust_num = conv_uint( ByteArray, 20, 4 );
cust_name = new String( CharArray, 665,
15 ).ToString();
account_num = new String( CharArray, 1506,
13 ).ToString();
sys_ftypes = conv_uint( ByteArray, 40, 4 );
sys_ftypes1 = conv_uint( ByteArray, 44, 4 );
veh_class = conv_ushort( ByteArray, 1378, 2 );
operator_id = conv_ushort( ByteArray, 868,
2 );
job_fleet = conv_ushort( ByteArray, 1364, 2 );
license_plates = new String( CharArray, 1563,
9 ).ToString();
driver_id = conv_uint( ByteArray, 28, 4 );
pick_addrs_id = 0;
quantity = conv_ushort( ByteArray, 1350, 2 );
fi_taxi = conv_ushort( ByteArray, 1360, 2 );
dest_info = new String( CharArray, 1949,
41 ).ToString();
service_recnum = conv_ushort( ByteArray, 1992, 2 );
jobtype_recnum = conv_ushort( ByteArray, 1996,
2 );
license_recnum = conv_ushort( ByteArray, 2000, 2 );
phone_1 = new String( CharArray, 708, 14 ).ToString
();
phone_2 = new String( CharArray, 728, 14 ).ToString
();
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine("Fails in proc_rec()!!!");
return false;
}
return true;
}
public uint conv_uint( byte[] ByteArray, int offset,
int nbytes )
{
uint value = 0;

for( int k = 0; k <= nbytes-1; k++ )
value = value * 256 + Convert.ToUInt32( ByteArray
[offset+k] );
return value;
}
public ushort conv_ushort( byte[] ByteArray, int
offset, int nbytes )
{
ushort value = 0;

for( int k = 0; k <= nbytes-1; k++ )
{
value *= 256;
value += Convert.ToUInt16( ByteArray
[offset+k] );
}
return value;
}
}
}
Click to expand...
 
Back
Top