1
Answer

How to read a data row from Excel with more than 255 char?

Raja

Raja

7y
369
1
I have upload one excel and convert into datatable using below code.
 
 
 
public DataSet FillExcells()
{
OleDbConnection con;
System.Data.DataTable dt = null;
string conn = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + Request.Fullpath + "; Extended Properties='Excel 12.0;HDR=Yes'";
con = new OleDbConnection(conn);
try
{
con.Open();
DataSet ds = new DataSet();

dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
string getExcelSheetName = dt.Rows[i]["Table_Name"].ToString();
if (getExcelSheetName == "CRF$")
{
OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"]", con);
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
DataTable ExcelDataSet = new DataTable("SOURCE");
ExcelAdapter.Fill(ExcelDataSet);
SetValueForNull(ExcelDataSet);
//ds.Tables.Add(ExcelDataSet);

for (int j = ExcelDataSet.Rows.Count - 1; j >= 10; j--)
{
if (ExcelDataSet.Rows[j][2].ToString() == "" || ExcelDataSet.Rows[j][2].ToString() == "0")
{
ExcelDataSet.Rows[j].Delete();
}
}
ExcelDataSet.AcceptChanges();
ds.Tables.Add(ExcelDataSet);
}
}
return ds;
}
catch { throw; }
finally { con.Close(); }
 
 
 
 
 
 
but the datatable read only 255 characters how to fix it.
i have seen some solutions
  Excel 97

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel

Excel 2000 and later versions

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
 
second one regedit setting in my machine how to fix it.
 
 
Answers (1)
0
Khan Abrar Ahmed

Khan Abrar Ahmed

NA 5.8k 199.9k 10y
Hi you can try this code
private void Form1_Load(object sender, EventArgs e)
{
OleDbConnection connect = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Anas\Dropbox\C# Projects\2.Database\ClOsODb.accdb; Persist Security Info=False");
connect.Open();
string cmd = "select * from Course Number and Name";
OleDbDataAdapter adapter = new OleDbDataAdapter(new OleDbCommand(strSql, connect));
 DataSet ds = new DataSet();
adapter.Fill(ds);

course_Number_and_NameComboBox.DataSource = ds.Tables[0];
course_Number_and_NameComboBox.DisplayMember = "Course Number and Name";
course_Number_and_NameComboBox.ValueMember = "ID";
connect.Close();
}
0
Anas Tasadduq

Anas Tasadduq

NA 3 4.8k 10y
But I am using C#, not VB. Sorry I did not mention it in the question.
0
Khan Abrar Ahmed

Khan Abrar Ahmed

NA 5.8k 199.9k 10y
Hi you can try below code
Dim cn As New OleDbConnection("provider=Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\Users\Anas\Dropbox\C# Projects\2.Database\ClOsODb.accdb")
   Dim da As New OleDbDataAdapter()
   Dim dt As New DataTable()
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      cn.Open()
      da.SelectCommand = New OleDbCommand("select * from [course Number and Name]", cn)
      da.Fill(dt)
 
      ComboBox1.DataSource = dt
      ComboBox1.DisplayMember = "Course Number and Name";
      ComboBox1.ValueMember = "ID";
 
      ' or this way
      ComboBox1.Items.Clear()
      Dim r As DataRow
      For Each r In dt.Rows
         ComboBox1.Items.Add(r(1).ToString)
      Next
      cn.Close()
   End Sub