Date: 09/15/07 (Asp Dot Net)    Keywords: database, sql, microsoft

I inherited a program that takes data from a csv file and inserts it into a database.

The data is in the following format:

8/20/2007 12:14:21 PM,HDFM,"Nick Yockell","Nate Wittrock",239-850-3931,239-850-2514,"Used 2004 Harley Davidson ","bought bike
CUSTOMER WANTS TO BRING HIS YAMAHA R6 IN TO TRADE AND SEE HOW MUCH PAYMENTS WOULD BE.
",Deliver,aa32ed31-f0c8-4f43-a7d4-830d9f29edcd


For some reason the function that saves the data to the database is having a problem with the customer phone number (5th column).

The database column is a varchar(50) - the same as the customer business phone number.

If the customer home phone contains anything other that digits it cannot be saved to the database.

However, the customer business phone can contain anything and gets saved just fine.

The sql statement in the second function runs fine from query analyzer so I know thats not the problem.

Is there anything in the two functions that could be causing this ?



private void ProcessFile(string filename, string vendor)
{


string path = string.Empty;
string LoggingDate = DateTime.Now.ToString();

switch (vendor.ToLower())
{
case "vpulse":
path = DIRECTORYPATHVPULSE;
break;
case "calllogpro":
path = DIRECTORYPATHCALLLOGPRO;
break;

}

try
{
OleDbConnection ExcelConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+path+"\\"+";Extended Properties=\"Text;HDR=No;FMT=Delimited()\"");
//OleDbConnection ExcelConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+this.txtSource.Text+"\\"+";Extended Properties=\"Text;HDR=No;\"");
OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM "+filename,ExcelConnection);

OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);

ExcelConnection.Open();

DataSet ExcelDataSet = new DataSet();
ExcelAdapter.Fill(ExcelDataSet);

ExcelConnection.Close();

//Process File
ProcessFileRecords(ExcelDataSet, vendor.ToLower());

//Email Copy to Alliance Group
this.EmailFile(path, filename, vendor);



}
catch (Exception exc)
{
string errorText = exc.ToString();
System.Diagnostics.EventLog.WriteEntry("Alliance Level 2 DataRetreiver", errorText);

}
finally
{
//Archive Copy
this.ArchiveFile(path, filename);
}
}




private void ProcessFileRecordVPulse(object[] record)
{
string logdate = string.Empty;
string dealership = string.Empty;
string salesperson = string.Empty;
string customername = string.Empty;
string homephone = string.Empty;
string businessphone = string.Empty;
string product = string.Empty;
string comments = string.Empty;
string sellstage = string.Empty;
string vpulsecalltaskid = string.Empty;

string LoggingDate = DateTime.Now.ToString();

try
{
logdate = Convert.ToDateTime(record[0]).ToShortDateString();
dealership = record[1].ToString();
salesperson = record[2].ToString();
customername = record[3].ToString();

try
{
homephone = record[4].ToString();
//homephone.Replace("-", "");
}
catch
{
homephone = "Bad format";
}

businessphone = record[5].ToString();
product = record[6].ToString();
comments = record[7].ToString().Replace("
", " ");

//***Get Sales Stage
sellstage = record[8].ToString();
switch(sellstage.ToLower())
{
case "greet":
sellstage = "1";
break;
case "probe":
sellstage = "2";
break;
case "identify machine":
sellstage = "3";
break;
case "presentation":
sellstage = "4";
break;
case "sit down":
sellstage = "5";
break;
case "write up":
sellstage = "6";
break;
case "close the deal":
sellstage = "7";
break;
case "finance":
sellstage = "8";
break;
case "deliver":
sellstage = "9";
break;
default:
if (sellstage.ToLower().IndexOf("service") == -1 && sellstage.ToLower().IndexOf("parts") == -1)
sellstage = "1";
else
sellstage = sellstage;
break;
}
//***Get Sales Stage

vpulsecalltaskid = record[9].ToString();

//Import lead record
SqlCommand command = this.GetCommand();

command.CommandText = @"INSERT INTO CallLogs
(LogDate,
VPulseCallTaskId,
CallLogTypeId,
DealerId,
SalesPerson,
CustomerName,
CustomerHomePhone,
CustomerBusinessPhone,
Product,
Comments,
SaleStage,
LogProcessFlag,
CSI14Days,
CSI17Months,
CSI14DaysProcessFlag,
CSI17MonthsProcessFlag)
VALUES
(@LogDate,
@VPulseCallTaskId,
@CallLogTypeId,
@DealerId,
@SalesPerson,
@CustomerName,
@CustomerHomePhone,
@CustomerBusinessPhone,
@Product,
@Comments,
@SaleStage,
@LogProcessFlag,
@CSI14Days,
@CSI17Months,
@CSI14DaysProcessFlag,
@CSI17MonthsProcessFlag)";

command.Parameters.Add("@LogDate", logdate);
command.Parameters.Add("@VPulseCallTaskId", vpulsecalltaskid);
if ((sellstage.ToLower().IndexOf("service") == -1)&&(sellstage.ToLower().IndexOf("parts") == -1))
{
command.Parameters.Add("@CallLogTypeId", "1");
}
else
{
if (sellstage.ToLower().IndexOf("service") != -1)
{
command.Parameters.Add("@CallLogTypeId", "1"); //Service (4)
dealership = dealership+"_SERVICE";
sellstage = "1";
}
else if (sellstage.ToLower().IndexOf("parts") != -1)
{
command.Parameters.Add("@CallLogTypeId", "1"); //Parts (3)
dealership = dealership+"_PARTS";
sellstage = "1";
}
}
command.Parameters.Add("@DealerId", dealership);
command.Parameters.Add("@SalesPerson", salesperson);
command.Parameters.Add("@CustomerName", customername);
command.Parameters.Add("@CustomerHomePhone", homephone);
command.Parameters.Add("@CustomerBusinessPhone", businessphone);
command.Parameters.Add("@Product", product);
command.Parameters.Add("@Comments", comments);
command.Parameters.Add("@SaleStage", sellstage);
command.Parameters.Add("@LogProcessFlag", false);
command.Parameters.Add("@CSI14Days", Convert.ToDateTime(logdate).AddDays(14).ToString());
command.Parameters.Add("@CSI17Months", Convert.ToDateTime(logdate).AddMonths(17).ToString());
command.Parameters.Add("@CSI14DaysProcessFlag", false);
command.Parameters.Add("@CSI17MonthsProcessFlag", false);

command.ExecuteNonQuery();
this.DisposeCommand(command);
}

catch (Exception exc)
{

string errorText = exc.ToString();
System.Diagnostics.EventLog.WriteEntry("Alliance Level 2 DataRetreiver", errorText);

}

Source: http://community.livejournal.com/aspdotnet/92096.html