First record is missed when importing data from excel to sql server

Question

3.86/5 (3 votes)

See more:

Hi All,

I am using the code below to import all data from excel to sql server from asp.net and c#. Its working fine but when record fetching with reader in while loop the first record gets missed. When I used without while loop its working fine for first record. But we needed all the records from excel sheet.

C#

protected void btnUpload_Click(object sender, EventArgs e)
{
    int i=0;
    
    if (FileUpload1.HasFile)
    {
    string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName).ToString ();
    string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName).ToString ();
    string Filepath = Path .GetFullPath (FileUpload1.PostedFile.FileName).ToString ();
    string excelConnectionString =
            @"Provider=Microsoft.Jet.OLEDB.4.0;
            Data Source=I:\Cast_cencus.xls;
            Extended Properties=""Excel 8.0;HDR=YES;""";
    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
    OleDbCommand cmd = new OleDbCommand
    ("Select [Community],[Subcast],[State] from [Sheet2$]", excelConnection);
    excelConnection.Open();
    OleDbDataReader dReader;
    dReader = cmd.ExecuteReader();
    dReader.Read();
    
    SqlConnection sqlcon=new SqlConnection (Constring);
    sqlcon.Open ();
    
    string aa= dReader[0].ToString();
    string aa1=dReader[1].ToString();
    string aa2=dReader[2].ToString();
                
    
    while (dReader.Read())
    {
        i++;
    
                         
    
    SqlCommand command =new SqlCommand ("CS_sp_ImportFromExce",sqlcon);
    command.CommandType =CommandType.StoredProcedure ;
    command.Parameters.Add("@Community", dReader[0].ToString());
    command.Parameters.Add("@SubCast", dReader[1].ToString());
    command.Parameters.Add("@State", dReader[2].ToString());
    command.Parameters.Add("@Sheet_Name", FileName.ToString());
    command.ExecuteNonQuery();
    
    }
    
    sqlcon.Close ();
    Response.Write(i.ToString ());
    
    }
}

Any help is appreciated.

Thanks.

Posted 10-Jan-12 0:27am

Bhanu Pratap Verma

Updated 10-Jan-12 0:32am

Slacker007

v2

Add a Solution

Comments

Slacker007 10-Jan-12 6:32am

Edits made: tags, title, code block, and readability.

3 solutions

Solution 2

Hi,

I guess you are incrementing your i++ as soon as you open your while loop which is causing the first record loss.

Try checking with the i++ as the last statement in your while loop.
Also, comment the line that fetched the dReader.Read(); in the start.As datareader can iterate through forward direction only and we would miss the first line in this case.

Hope this helps.

Posted 10-Jan-12 0:42am

manognya kota

Updated 10-Jan-12 0:48am

v2

Solution 3

You just simple use do...while

do
{
i++;

SqlCommand command =new SqlCommand ("CS_sp_ImportFromExce",sqlcon);
command.CommandType =CommandType.StoredProcedure ;
command.Parameters.Add("@Community", dReader[0].ToString());
command.Parameters.Add("@SubCast", dReader[1].ToString());
command.Parameters.Add("@State", dReader[2].ToString());
command.Parameters.Add("@Sheet_Name", FileName.ToString());
command.ExecuteNonQuery();

}while (dReader.Read());

Posted 24-Apr-14 4:00am

toofull

Updated 24-Apr-14 4:01am

Comments

CHill60 24-Apr-14 10:12am

Whilst this is factually correct, solution 1 explained the reason why 2 years ago.

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

*Karthik Harve* · Accepted Answer · 2012-01-10T00:42:00

DataReader is a one using which you can iterate through forward direction only. Once used dReader.Read();, then first row will be fetched and if you use it again, you can get only second row, but you cannot get the first row values.

here also you used like this..

C#

dReader = cmd.ExecuteReader();
dReader.Read();

SqlConnection sqlcon=new SqlConnection (Constring);
sqlcon.Open ();

string aa= dReader[0].ToString();
string aa1=dReader[1].ToString();
string aa2=dReader[2].ToString();

so, here you got first row, but again in while loop you have used dReader.Read();, so you missing the first row.

just comment or remove these lines and check..

dReader.Read();

string aa= dReader[0].ToString();<br />
    string aa1=dReader[1].ToString();<br />
    string aa2=dReader[2].ToString();

hope it works..

First record is missed when importing data from excel to sql server

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

First record is missed when importing data from excel to sql server

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us