Expert: Nick - 2/8/2008

Question
QUESTION: Hello,

Thanks for all the previous replies.
Kindly help me in the below

I have converted reading xls to sqlserver(through some forum) 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

Hemalatha

1    {
2    try
3    {
4    mycon.Open();
5    if (chooseuser == "Single")
6    
7    string fname = FileUpload.PostedFile.FileName.ToString();
8    
9    string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fname + "; Extended Properties=Excel 8.0;";
10   using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
11   {
12   OleDbCommand command = new OleDbCommand();
13   OleDbDataAdapter dbcomm = new OleDbDataAdapter();
14   string strSelectString = "Select * FROM [Sheet1$]";
15   command = new OleDbCommand(strSelectString, connection);
16   dbcomm = new OleDbDataAdapter(strSelectString, connection);
17   connection.Open();
18   
19   using (IDataReader dr = command.ExecuteReader())
20   {
21   using (SqlBulkCopy bulkCopy = new SqlBulkCopy(mycon))
22   {
23   bulkCopy.DestinationTableName = "ImportData";
24   bulkCopy.ColumnMappings.Add("CreatedBy", "CreatedBy");
25   bulkCopy.ColumnMappings.Add("createddate", "CreatedDate");
26   bulkCopy.ColumnMappings.Add("DownloadDate", "DownloadDate");
27   bulkCopy.ColumnMappings.Add("FileName", "FileName");
28   bulkCopy.ColumnMappings.Add("AccountNo/ChartNo", "Acc_ChartNo");
29   bulkCopy.ColumnMappings.Add("Billing/CaseNo", "Billing_CaseNo");
30   bulkCopy.ColumnMappings.Add("DOS", "DateOfService");
31   bulkCopy.ColumnMappings.Add("Provider", "Provider");
32   bulkCopy.ColumnMappings.Add("PatientName", "PatientName");
33   bulkCopy.ColumnMappings.Add("CPT", "CPT");
34   bulkCopy.ColumnMappings.Add("BilledAmount", "BilledAmount");
35   bulkCopy.ColumnMappings.Add("Copay", "Copay");
36   bulkCopy.ColumnMappings.Add("Users", "Users");
37   bulkCopy.ColumnMappings.Add("PostingDate", "PostingDate");
38   bulkCopy.WriteToServer(dr);
39   }
40   dr.Close();
41   }
42   connection.Close();
43   }
44   }
45   }


ANSWER: Just as a funny side note, when I was searching for some possible answers to your question, I found that you asked this question on another forum: http://www.thescripts.com/forum/thread768102.html

Hopefully they will get back to you before I do :-P

To be honest, this is not my area of expertise. However, I can search for an answer given a little more information. It seems like you are trying to copy data from an excel spreadsheet and insert it into an SQL database table. From what you explained, it is clear that you do not want the "Created By" and "Created Date" to come from the Excel spreadsheet. However, I am not certain where you do want them to come from. You said you want them to come from a textbox, but I am not sure what textbox you mean. Do you mean a textbox on a Windows Form you have created? Perhaps you mean a textbox in another Excel spreadsheet? Do you want a single value to be used for each "Created By" column and another single value to be used for each "Created Date" column? Or, do you want the user to be able to enter a new value for each different column? As you can see, I am a little unsure of exactly what you want, which makes it hard to answer your questions. However, I'll give a few tries below.

For one, I noticed "createddate" has no upper case letters in it, although every other value listed does. Is this case senstive? If so, perhaps that could be your problem.

Also, is it necessary to use bulk copy to perform these operations? Perhaps you could perform the operations on individual columns. I've had experience with doing operations on individual columns and could probably help you out with inserting custom values using that method.

If none of that helps, give me some more details and I may be able to assist you further.

---------- FOLLOW-UP ----------

QUESTION: Hello Nick,
None replied in the forums.The createdby will be the login name which will be typed in the textbox(web application).
and similarly they will be entering  the date in textbox.

These two values will NOT BE provided in the excel sheet.
Hence we are stuck in the process.
I have put forward this question in thescripts.com too.
But no answer

Regards
Hemalatha


Answer
I still don't completely understand your question. However, I created some code that illustrates, in a simple manner, how to insert custom values into an SQL table from C#. First, you must get whatever values you need from your Excel source. Then, you will connect to the SQL database. Then, you will create a series of commands that insert each row into the SQL database. The row will be comprised of whatever data you like (some of it can come from the Excel data source and some can come from the value entered by the user into the textbox). For the moment, I am assuming you know how to get the data from the Excel data source. Here is the code that inserts data into an SQL table (obviously, you will have to make modifications to suit your needs):

private void MyMethod()
{

   // Variables.
   string createdBy = "Me";
   DateTime createdDate = DateTime.Now;
   string personName = "John Doe";
   string connectionString = "enter your connection string here.";


   // Prepare command.
   System.Data.SqlClient.SqlConnection sqlConn =
       new System.Data.SqlClient.SqlConnection(connectionString);
   System.Data.SqlClient.SqlCommand sqlCommand =
       new System.Data.SqlClient.SqlCommand(
       "INSERT INTO MyTable (CreatedBy, CreatedDate, PersonName) " +
       "VALUES (@createdBy, @createdDate, @personName)", sqlConn);


   // Add parameters to command.
   sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("createdBy", createdBy));
   sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("createdDate", createdDate));
   sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("personName", personName));


   // Execute command.
   sqlConn.Open();
   sqlCommand.ExecuteNonQuery();
   sqlConn.Close();

}