mixing records

hi
in asp.net i have one interface which uploads excel file to the system.if single user use this interface than it is working perfectly...but simulatenous more than one user uses this than in my system it mixed the records..i mean suppose one user is uploading excel file of icicibank and another user hdfc bank file..then records of hdfc file come along with icicbank with id of icicibank file...

how to reolve it?

Hi,Problem is not clear.Are you uploading the excel sheet and then transforming it into another database..

hi

thanx for reply...

i m reading one record at a time from excel and inserting into database table...if u want i can post my code also

Hi,Are you using Data Transformation Service(DTS) of sql server for transforming data.Or, you have described function that is transforming data from excel to sqlserver.In either case post logic/Code and table structure.

hi

this code is for reading data from excel file.

private DataTable ReadExcelDateInDataTable()
{
DataTable dtExcelData=new DataTable();
OleDbDataAdapter daExcelData=new OleDbDataAdapter();
string strFile;
string strFilePath;
int nFileLength;
HttpPostedFile FileSent;
byte[] FileData;
string[] arrFile;
string strConn;

strFilePath=inFile.Value;
strFile=strFilePath;
FileSent=inFile.PostedFile;
nFileLength=FileSent.ContentLength;
FileData=new byte[nFileLength];
FileSent.InputStream.Read(FileData,0,nFileLength);
arrFile= FileSent.FileName.ToString().Split('\\');
fileName=arrFile[arrFile.Length -1];

WriteToFile("..\\Resources\\UploadFiles\\" + fileName,FileData);
strFilePath = Server.MapPath("..\\Resources\\UploadFiles\\" + fileName);
fileName=Path.GetFileName(FileSent.FileName);
// strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"+" Data Source =" + strFilePath +"; "+ "Extended Properties = Excel 8.0;";
strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"+" Data Source =" + strFilePath +"; Extended Properties="+ Convert.ToChar(34).ToString()+"Excel 8.0;HDR=No;IMEX=1"+Convert.ToChar(34).ToString();
//strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"+" Data Source =" + strFilePath +"; Extended Properties="+ Convert.ToChar(34).ToString()+"Excel 8.0;HDR=Yes;IMEX=1"+Convert.ToChar(34).ToString();

try
{
OleDbConnection ExcelConnection=new OleDbConnection(strConn);
ExcelConnection.Open();
OleDbCommand ExcelCommand=new OleDbCommand();
ExcelCommand.Connection=ExcelConnection;
DataTable tblExcelSheet=ExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,new object [] {null,null,null,"TABLE"});
string SpreadSheetName = "["+tblExcelSheet.Rows[0]["TABLE_NAME"].ToString()+"]";
ExcelConnection.Close();
DataTable dtExcelSheet=new DataTable();
daExcelData=new OleDbDataAdapter("Select * from "+ SpreadSheetName,ExcelConnection);
daExcelData.TableMappings.Add("Table","ExcelData");
daExcelData.Fill(dtExcelSheet);
return dtExcelSheet;
}
catch(Exception ex)
{
throw ex;
//lblMessage.Text=ex.Message;
}
}
private void WriteToFile(string strPath, byte[] Buffer)
{
//Create a file
FileStream newFile = new FileStream(Server.MapPath(strPath), FileMode.Create);

//write to data
newFile.Write(Buffer,0,Buffer.Length);

//close the file
newFile.Close();
}

this code for inserting data into table

public int InsertCMSUploadMaster(CustomEntity.CMSUploadMaster objEntCMSUploadMaster)
{
try
{
SqlParameter[] storedparams = new SqlParameter[15];
storedparams[0]=new SqlParameter("@.CommandType",SqlDbType.VarChar);
storedparams[0].Value ="Insert";
storedparams[1]=new SqlParameter("@.CMS_Upload_Master_ID",SqlDbType.Decimal);
storedparams[1].Value =objEntCMSUploadMaster.CMSUpload_MasterID;
storedparams[2]=new SqlParameter("@.File_Name",SqlDbType.VarChar);
storedparams[2].Value =objEntCMSUploadMaster.FileName;
storedparams[3]=new SqlParameter("@.Bank_Name",SqlDbType.VarChar);
storedparams[3].Value =objEntCMSUploadMaster.BANK;
storedparams[4]=new SqlParameter("@.Rec_Count",SqlDbType.Decimal);
storedparams[4].Value =objEntCMSUploadMaster.RecCount;
storedparams[5]=new SqlParameter("@.Amount",SqlDbType.Float );
storedparams[5].Value =objEntCMSUploadMaster.CMS_Amount;
storedparams[6]=new SqlParameter("@.Status",SqlDbType.VarChar);
storedparams[6].Value =objEntCMSUploadMaster.CMS_Status ;
storedparams[7]=new SqlParameter("@.Maker",SqlDbType.Decimal);
storedparams[7].Value =objEntCMSUploadMaster.CMS_Maker;
storedparams[8]=new SqlParameter("@.Make_Date",SqlDbType.DateTime);
storedparams[8].Value =objEntCMSUploadMaster.CMS_Make_Date;
storedparams[9]=new SqlParameter("@.Checker",SqlDbType.Decimal);
storedparams[9].Value =objEntCMSUploadMaster.CMS_Checker;
storedparams[10]=new SqlParameter("@.Check_Date",DbType.DateTime);
storedparams[10].Value =objEntCMSUploadMaster.CMS_Check_Date;
storedparams[11]=new SqlParameter("@.AuthStatus",DbType.Decimal);
storedparams[11].Value =objEntCMSUploadMaster.CMS_AuthStatus;
storedparams[12]=new SqlParameter("@.Optype",DbType.Decimal);
storedparams[12].Value =objEntCMSUploadMaster.CMS_Optype ;
storedparams[13]=new SqlParameter("@.Return_CMSUploadMstId",DbType.Decimal);
storedparams[13].Direction=ParameterDirection.Output;
storedparams[14]=new SqlParameter("@.DataFormatId",SqlDbType.Decimal);
storedparams[14].Value=objEntCMSUploadMaster.DataFormat_Id;

if (SqlHelper.ExecuteNonQuery(strConn,CommandType.StoredProcedure ,"Usp_CMSUploadMaster",storedparams)>0)
{
return int.Parse(storedparams[13].Value.ToString()) ;
}
else
{
return 0;
}
}
catch(SqlException ex)
{
throw ex;
}
catch(Exception ex)
{
throw ex;
}
}

Try applying following...

Is identity column is described in table..
Try applying transaction while inserting record...
Try to check data in dtExcelSheet means while debugging check you are getting proper data from excel sheet...

hi i m using transaction in page..

see this is the actual inserting code .

public void DoExcelToSqlCMS(int CmsMstId,DataTable l_obj_DataTable)
{
//string lengthstr="";
//string strconcate="";
int colcount;
//TextWriter objwrite=new StreamWriter("C:\\LengthFile.csv",true);
string strConn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

SqlTransaction l_obj_SqlTran;
SqlConnection l_obj_SqlCon = new SqlConnection(strConn);
l_obj_SqlCon.Open();
l_obj_SqlTran = l_obj_SqlCon.BeginTransaction();

try
{
string sqlstr ="SELECT "+ strDataFields +" FROM Tbl_CMS_UploadDetails where 1=2";

SqlDataAdapter l_obj_SqlAdp = new SqlDataAdapter(sqlstr,l_obj_SqlCon);

l_obj_SqlAdp.SelectCommand.Transaction = l_obj_SqlTran;

SqlCommandBuilder l_obj_SqlBuilder = new SqlCommandBuilder(l_obj_SqlAdp);

DataSet l_obj_DataSet = new DataSet();
l_obj_SqlAdp.Fill(l_obj_DataSet);

for (int i=1 ; i < l_obj_DataTable.Rows.Count; i++)
{
if (ddlTemplateName.SelectedItem.Text.Trim().ToUpper () =="CHANNEL")
{
if (l_obj_DataTable.Rows[i][4].ToString ()=="" || l_obj_DataTable.Rows[i][2].ToString ()==null)
{
break;
}

}
else if (l_obj_DataTable.Rows[i][2].ToString ()=="" || l_obj_DataTable.Rows[i][2].ToString ()==null)
{
break;
}
DataRow l_obj_Row = l_obj_DataSet.Tables[0].NewRow();
colcount=l_obj_DataSet.Tables[0].Columns.Count;
if (ddlTemplateName.SelectedItem.Text.Trim().ToUpper () =="IBANK")
{
colcount=colcount-1;
}
for(int j = 0 ; j < colcount ; j++)
{

if (j==0)
{
l_obj_Row[l_obj_DataSet.Tables[0].Columns[j].ColumnName] = CmsMstId;
}
else
{
//lengthstr= (l_obj_DataTable.Rows[i][j-1].ToString().Length.ToString());

//strconcate = strconcate+","+lengthstr+","+l_obj_DataTable.Rows[i][j-1].ToString()+",";

switch (l_obj_DataSet.Tables[0].Columns[j].ColumnName)
{
case "Amount":
{

double Amt;

string amt=l_obj_DataTable.Rows[i][j-1].ToString();
l_obj_Row.BeginEdit();

//Added by Nachiket for amount in brackets
amt=amt.Replace("(","-");
amt=amt.Replace(")","");
amt=amt.Replace(",","");

Amt=Double.Parse(amt);

l_obj_Row[l_obj_DataSet.Tables[0].Columns[j].ColumnName]=Amt;
l_obj_Row.EndEdit();
break;

}
//check for no. of columns in the format with uploading file
case "Folio_No":

{
//if (ddlTemplateName.SelectedItem.Text.Trim()=="HDFC Failure".ToUpper() || ddlTemplateName.SelectedItem.Text.Trim() =="HDFC Success".ToUpper())
if (ddlTemplateName.SelectedItem.Text.Trim() =="HDFC SUCCESS" || ddlTemplateName.SelectedItem.Text.Trim() =="HDFC FAILURE")
{
string[] strFolioNo = (l_obj_DataTable.Rows[i][j-1].ToString()).Split(' ');
l_obj_Row[l_obj_DataSet.Tables[0].Columns[j].ColumnName] = strFolioNo[0].ToString();
break;
}
else
{
string[] strFolioNo = (l_obj_DataTable.Rows[i][j-1].ToString()).Split('/');
l_obj_Row[l_obj_DataSet.Tables[0].Columns[j].ColumnName] = strFolioNo[0].ToString();
break;
}
}
//case "TradesDate":
case "TradeDate":
{
DateTime dt;
if(l_obj_DataTable.Rows[i][j-1].ToString().IndexOf("/")>0)
{
string[] strDate = (l_obj_DataTable.Rows[i][j-1].ToString()).Split('/');
// format years
//if (int.Parse(strDate[2]).ToString().Length ==1)
if (strDate[2].ToString().Length ==1)
{
strDate[2]= "200" +int.Parse(strDate[2]).ToString();
}
if (strDate[2].Length ==2)
{
strDate[2]= "20" +strDate[2].ToString();
}
// format years end

if(int.Parse(strDate[1])>12)
//dt = new DateTime(int.Parse(strDate[2]),int.Parse(strDate[0]),int.Parse(strDate[1])); Commented AM/PM convertto int problem Gopal 15-jan-07
dt = new DateTime(int.Parse(strDate[2].Substring(0,4)),int.Parse(strDate[0]),int.Parse(strDate[1]));

else

// dt = new DateTime(int.Parse(strDate[2]),int.Parse(strDate[1]),int.Parse(strDate[0])); Commented AM/PM convertto int problem Gopal 15-jan-07

dt = new DateTime(int.Parse(strDate[2].Substring(0,4)),int.Parse(strDate[1]),int.Parse(strDate[0]));
}
else
{
//Added 20 -feb -07 covert date time
if (ddlTemplateName.SelectedItem.Text.Trim() =="IBANK SUCCESS" || ddlTemplateName.SelectedItem.Text.Trim() =="IBANK FAILURE")
{
string[] strDate = (l_obj_DataTable.Rows[i][j-1].ToString()).Split('-');
// format years
//if (int.Parse(strDate[2]).ToString().Length ==1)
if (strDate[2].ToString().Length ==1)
{
strDate[2]= "200" +int.Parse(strDate[2]).ToString();
}
if (strDate[2].Length ==2)
{
strDate[2]= "20" +strDate[2].ToString();
}
// format years end

if(int.Parse(strDate[1])>12)
//dt = new DateTime(int.Parse(strDate[2]),int.Parse(strDate[0]),int.Parse(strDate[1])); Commented AM/PM convertto int problem Gopal 15-jan-07
dt = new DateTime(int.Parse(strDate[2].Substring(0,4)),int.Parse(strDate[0]),int.Parse(strDate[1]));

else

// dt = new DateTime(int.Parse(strDate[2]),int.Parse(strDate[1]),int.Parse(strDate[0])); Commented AM/PM convertto int problem Gopal 15-jan-07

dt = new DateTime(int.Parse(strDate[2].Substring(0,4)),int.Parse(strDate[1]),int.Parse(strDate[0]));
// Gopal Ends
}
else
{
// commented 20 -feb -07
dt = Convert.ToDateTime(l_obj_DataTable.Rows[i][j-1]);
}
}

if (l_obj_DataTable.Rows[i][j-1]!=System.DBNull.Value && dt.Year>1900)
{
l_obj_Row[l_obj_DataSet.Tables[0].Columns[j].ColumnName]=dt;
}
else
{
l_obj_Row[l_obj_DataSet.Tables[0].Columns[j].ColumnName]=Convert.ToDateTime("01/01/1900");
}
break;
}
case "Instrm_Date":
{

DateTime dt=Convert.ToDateTime("01/01/1900");
if(l_obj_DataTable.Rows[i][j-1].ToString().IndexOf("/")>0)
{
//string[] strDate = Convert.ToDateTime(l_obj_DataTable.Rows[i][j-1]).ToString().Split('/');
string[] strDate = l_obj_DataTable.Rows[i][j-1].ToString().Split('/');
// format years
if (strDate[2].ToString().Length ==1)
{
strDate[2]= "200" +int.Parse(strDate[2]).ToString();
}
if (strDate[2].Length ==2)
{
strDate[2]= "20" +strDate[2].ToString();
}
// format years end
if(int.Parse(strDate[1])>12)
{
//dt = new DateTime(int.Parse(strDate[2]),int.Parse(strDate[0]),int.Parse(strDate[1]));Commented AM/PM convertto int problem Gopal 15-jan-07
dt = new DateTime(int.Parse(strDate[2].Substring(0,4)),int.Parse(strDate[0]),int.Parse(strDate[1]));
}
else
{
//dt = new DateTime(int.Parse(strDate[2]),int.Parse(strDate[1]),int.Parse(strDate[0]));Commented AM/PM convertto int problem Gopal 15-jan-07
dt = new DateTime(int.Parse(strDate[2].Substring(0,4)),int.Parse(strDate[1]),int.Parse(strDate[0]));

}
}
else
{
if (l_obj_DataTable.Rows[i][j-1]!=System.DBNull.Value && dt.Year>1900)
{
dt = Convert.ToDateTime(l_obj_DataTable.Rows[i][j-1]);
}

}
if (l_obj_DataTable.Rows[i][j-1]!=System.DBNull.Value && dt.Year>1900)
{
l_obj_Row[l_obj_DataSet.Tables[0].Columns[j].ColumnName]=dt;
}
else
{
l_obj_Row[l_obj_DataSet.Tables[0].Columns[j].ColumnName]=Convert.ToDateTime("01/01/1900");
}

break;
}
default:
{
l_obj_Row[l_obj_DataSet.Tables[0].Columns[j].ColumnName] = l_obj_DataTable.Rows[i][j-1];
break;
}
}
}

}
l_obj_DataSet.Tables[0].Rows.Add(l_obj_Row);
//objwrite.WriteLine(strconcate);

//lengthstr="";
//strconcate="";
}
//objwrite.Close();
l_obj_SqlAdp.Update(l_obj_DataSet.Tables[0]);
l_obj_SqlTran.Commit();
//return 1;
}

Hi,

There is code seems writing to the Excel file, so I think the Excel part should be transact too. Hope it helps.

hi thanx for reply.

i m not writing to excel file ..but i m reading from excel file and inserting into table.....

can anybody give me some suggestion for this?