将数据从excel导入sql server时，会错过第一条记录 [英] First record is missed when importing data from excel to sql server

问题描述

大家好，



我使用下面的代码从asp.net和c＃导入excel到sql server的所有数据。它的工作正常但是当在while循环中使用reader读取记录时，第一条记录会被遗漏。当我没有使用while循环时，它的工作正常，可用于第一个记录但我们需要excel表中的所有记录。

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.

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.

推荐答案

，excelConnection）;
excelConnection.Open（）;
OleDbDataReader dReader;
dReader = cmd.ExecuteReader（）;
dReader.Read（）;

SqlConnection sqlcon = new SqlConnection（Constring）;
sqlcon.Open（）;

string aa = dReader [ 0 ]。ToString（）;
字符串 aa1 = dReader [ 1 ]。ToString（）;
string aa2 = dReader [ 2 ]。ToString（ ）;


while （dReader.Read（））
{
i ++;



SqlCommand command = < span class =code-keyword> 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（））;

}
}

", 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.

DataReader是一个只能用于向前迭代的数据读取器。一旦使用 dReader.Read（）; ，那么将获取第一行，如果再次使用它，则只能获得第二行，但是您无法获得第一行值。



这里你也像这样使用..

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..

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（）; ，所以你错过了第一行。



只需评论或删除这些行并检查..



dReader.Read（）;

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..

我想你在打开你的while循环时就会增加你的i ++，导致第一次失去记录。



尝试使用i ++作为while循环中的最后一个语句进行检查。

另外，注释获取dReader.Read（）的行;在开始时。由于datareader只能向前方向迭代，在这种情况下我们会错过第一行。



希望这会有所帮助。

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.

这篇关于将数据从excel导入sql server时，会错过第一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！