[cmrhema]

Hello,

I have converted reading xls to sqlserver successfully.


Here's the code. Now I want slight modification in the below lines, I do not want the values Created By and Created Date from excel sheet. I want to these values to be inserted from the textbox. I tried out replacing the createdby and creteddate by textbox values, but could not succeed.

bulkCopy.ColumnMappings.Add("CreatedBy", "CreatedBy");
bulkCopy.ColumnMappings.Add("createddate", "CreatedDate");



Kindly Help

Regards

cmrhema

{
try
{
mycon.Open();
if (chooseuser == "Single")

string fname = FileUpload.PostedFile.FileName.ToString();

string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" "Data Source=" + fname + "; Extended Properties=Excel 8.0;";
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand();
OleDbDataAdapter dbcomm = new OleDbDataAdapter();
string strSelectString = "Select * FROM [Sheet1$]";
command = new OleDbCommand(strSelectString, connection);
dbcomm = new OleDbDataAdapter(strSelectString, connection);
connection.Open();

using (IDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(mycon))
{
bulkCopy.DestinationTableName = "ImportData";
bulkCopy.ColumnMappings.Add("CreatedBy", "CreatedBy");
bulkCopy.ColumnMappings.Add("createddate", "CreatedDate");
bulkCopy.ColumnMappings.Add("DownloadDate", "DownloadDate");
bulkCopy.ColumnMappings.Add("FileName", "FileName");
bulkCopy.ColumnMappings.Add("AccountNo/ChartNo", "Acc_ChartNo");
bulkCopy.ColumnMappings.Add("Billing/CaseNo", "Billing_CaseNo");
bulkCopy.ColumnMappings.Add("DOS", "DateOfService");
bulkCopy.ColumnMappings.Add("Provider", "Provider");
bulkCopy.ColumnMappings.Add("PatientName", "PatientName");
bulkCopy.ColumnMappings.Add("CPT", "CPT");
bulkCopy.ColumnMappings.Add("BilledAmount", "BilledAmount");
bulkCopy.ColumnMappings.Add("Copay", "Copay");
bulkCopy.ColumnMappings.Add("Users", "Users");
bulkCopy.ColumnMappings.Add("PostingDate", "PostingDate");
bulkCopy.WriteToServer(dr);
}
dr.Close();
}
connection.Close();
}
}
}