Unable to Read Excel data in Asp.net

Rishi Chatterjee

I have a excel sheet1 and sheet2.I want to check excel data is exist or not exist in database.First loop working fine. Now the problem is in second loop data is exist or not exist same error showing("Data Already Exist"). not able to Read data.please give me Solution for that.

private bool ValidateUserBO(DataTable sheet1, DataTable sheet2) {
try {
bool isValid = true;
string messageText = string.Empty;
for (int i = 0; i < sheet1.Rows.Count; i++) {
UserCode1 = sheet1.Rows[i]["UserCode"].ToString();
string sp = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
SqlConnection con = new SqlConnection(sp);
con.Open();
SqlCommand cmd = new SqlCommand("select UserCode from test where UserCode=@UserCode", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserCode", UserCode1);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read()) {
label1.Text = "Data Already Exist";
isValid = false;
}
reader.Close();
cmd.Dispose();
con.Close();
}
for (int b = 0; b < sheet2.Rows.Count; b++) {
UserCode2 = sheet2.Rows[b]["UserCode"].ToString();
string sp1 = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
SqlConnection conn1 = new SqlConnection(sp1);
conn1.Open();
SqlCommand sql1 = new SqlCommand("select UserCode from test where UserCode=@UserCode", conn1);
sql1.CommandType = CommandType.StoredProcedure;
sql1.Parameters.AddWithValue("@UserCode", UserCode2);
SqlDataReader reader = sql1.ExecuteReader();
if (reader.Read()) {
label1.Text = "Data Already Exist";
isValid = false;
}
reader.Close();
conn1.Close();
}
return isValid;
} catch (Exception ex) {
throw ex;
}
}
protected void btnUpload_Click(object sender, EventArgs e) {
if (!ValidateUserBO(DataTable sheet1, DataTable sheet2) ValidFile = false;
}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rishi Chatterjee6y ago
HI,

Now I am Create Store Procedure, Same problem Happen, Please help me
private bool ValidateUserBO(DataTable sheet1, DataTable sheet2) {
try {
  bool isValid = true;
  string messageText = string.Empty;
  for (int i = 0; i < sheet1.Rows.Count; i++) {
   UserCode1 = sheet1.Rows[i]["UserCode"].ToString();
   string sp = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
   SqlConnection con = new SqlConnection(sp);
   con.Open();
   SqlCommand cmd = new SqlCommand("Test", con);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.AddWithValue("@UserCode", UserCode1);
   SqlDataReader reader = cmd.ExecuteReader();
   if (reader.Read()) {
    label1.Text = "Data Already Exist";
    isValid = false;
   }
   reader.Close();
   cmd.Dispose();
   con.Close();
  }
  for (int b = 0; b < sheet2.Rows.Count; b++) {
   UserCode2 = sheet2.Rows[b]["UserCode"].ToString();
   string sp1 = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
   SqlConnection conn1 = new SqlConnection(sp1);
   conn1.Open();
   SqlCommand sql1 = new SqlCommand("Test", conn1);
   sql1.CommandType = CommandType.StoredProcedure;
   sql1.Parameters.AddWithValue("@UserCode", UserCode2);
   SqlDataReader reader = sql1.ExecuteReader();
   if (reader.Read()) {
    label1.Text = "Data Already Exist";
    isValid = false;
   }
   reader.Close();
   conn1.Close();
  }
  return isValid;

} catch (Exception ex) {
  throw ex;
}
}

protected void btnUpload_Click(object sender, EventArgs e) {
  if (!ValidateUserBO(sheet1, sheet2) ValidFile = false;
  }
Jignesh Kumar6y ago
Hi,

You are using inline Query, your commandtype should Tesxt but you have used Storeprocedure.
Below line need to change,
sql1.CommandType = CommandType.StoredProcedure;
sql1.CommandType = CommandType.Text;