指定的演员阵容无效

Question

            SqlDataReader reader;
            string r="C:\\Users\\Shivam\\Documents\\";
            if ((FileUpload1.PostedFile != null)&&(FileUpload1.PostedFile.ContentLength > 0))
            {
                r += System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName);

            }

            OleDbConnection oconn =
              new OleDbConnection
                (@"Provider=Microsoft.Jet.OLEDB.4.0;"
                 + "Data Source="+r+";"
                 + @"Extended Properties=""Excel 8.0;HDR=Yes;""");
            oconn.Open();
            conn.Open();
            OleDbCommand dbcom = new OleDbCommand("SELECT * FROM [Sheet1$]", oconn);
            OleDbDataReader dbreader = dbcom.ExecuteReader();

            //dbread = dbreader;
            int rni = dbreader.GetOrdinal ("RollNo");
            int mki = dbreader.GetOrdinal ("marks");
            int rowcount =0;
            while(dbreader.Read())
            { rowcount++; }
            //dbreader.Close();
            //OleDbDataReader dbread = dbcom.ExecuteReader();
            int[] rn = new int[rowcount];
            int[] gr = new int[rowcount];


            while (dbreader.Read())
            {

                int o = 0;
                for(int i=0;i<rowcount;i++)
                {
                    int q = (int)dbreader.GetValue(rni);
                    int p = (int)dbreader.GetValue(mki);
                    rn[i] = q;
                    gr[i] = p;
                    //roll[i] = valid(odr, 0);//Here we are calling the valid method
                    //marks[i] = valid(odr, 1);
                    //i++;
                    if (gr[i] >= 11)
                    { o=i; }
                }
                if(o!=0)
                { break; }
                TextBox4.Text += rn + "\t" + gr;

                //Here using this method we are inserting the data into the database

                x = TextBox2.Text.Substring(0, 1);
                y = TextBox2.Text.Substring(1, 1);

                //for (int s = 0; s < roll.Length; s++)
                //{

                    //SqlDataAdapter sda = new SqlDataAdapter("select StudentID from Student where APID=" + int.Parse(y)+ "and Semester=" + int.Parse(z) + "and Roll_No=" + int.Parse(RollNo), conn);
                    //DataSet ds = new DataSet();
                    //sda.Fill(ds);
                    //GridView1.DataSource = ds;
                    //GridView1.DataBind();
                    SqlCommand command = new SqlCommand();
                    command.Connection = conn;
                    //command.Connection.Open();
                    command.CommandType = CommandType.Text;
                    int c = rn.Length;
                    for (int n = 0; n<rn.Length; n++)
                    {
                        command.CommandText = "Select StudentID from Student where APID=" + int.Parse(x) + "and Semester=" + int.Parse(y) + "and Roll_No=" + rn[n];

                    }
                reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        TextBox4.Text = reader.GetInt32(0).ToString();
                        a = (int)reader["StudentID"];
                        for (int v = 0; v < rn.Length; v++)
                        {
                            insertdataintosql(rn[v], gr[v]);
                        }
                    }
                //}

            }
            conn.Close();
            oconn.Close();

这里的问题是 while(dbreader.read()) 中的语句没有被执行，而是直接执行 conn.Close() 。如果我在关闭前一个数据读取器后使用相同的命令获取另一个数据读取器，则会在“int q = (int)dbreader.GetValue(rni);”处抛出错误“指定的转换无效”。请帮助我...提前致谢

Answer 1

DataReader 是只向前的。你不能“重用”它。

您所做的就是根据记录数分配一个数组。首先运行 COUNT 查询来获取记录数，或者在一个循环中动态重新分配数组。

OleDbCommand dbcom = new OleDbCommand("SELECT COUNT(*) as RowCount FROM [Sheet1$]", oconn);
 dbreader = dbcom.ExecuteReader();
dbreader.Read();
rowcount = dbreader.GetOrdinal("RowCount");
dbcom.Close();

dbcom = new OleDbCommand("SELECT * FROM [Sheet1$]", oconn);
 dbreader = dbcom.ExecuteReader();

...继续你的第二个循环

关于正确释放资源还有更多要说的。最佳实践是将参数 CommandBehavior.CloseConnection 添加到 ExecuteReader，在 using() 构造中创建数据读取器并发出 cmd.Dispose()最后确保 SQL 连接资源得到正确释放。

..虽然实际上因为它是您正在使用的本地文件，所以它可能并不重要，但一般来说您应该这样做。否则很容易发现孤立的 DataReader 尚未释放其连接。