Problem excel C#

Question

User1527544554 posted

hello this is my code:

              string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DBtb.Text + ";Extended Properties=Excel 12.0";
            OleDbConnection oledbConn = new OleDbConnection(conn);
            try
            {
                oledbConn.Open();
                OleDbCommand cmd = new OleDbCommand("SELECT O FROM [Sheet4$]", oledbConn);
                OleDbDataAdapter oleda = new OleDbDataAdapter();
                oleda.SelectCommand = cmd;
                cmd.CommandText = "INSERT INTO [Sheet4] (B5, C5, D5) VALUES(1,\"Fake Record\",\"Fake Record\")";
                cmd.ExecuteNonQuery();

but the command text doesnt get excuted why is that?

EDIT...
by the way i dont get any erros the values just dont show up 

Answer 1

User-744022866 posted

retrieve the return value from ExecuteNonQuery and see how many results are inserted

e.g.

int records = cmd.ExecuteNonQuery()

check the value in records to see how many records inserted.

Answer 2

User1527544554 posted

when this is running:

                int records = cmd.ExecuteNonQuery();
                
im getting an Exception

 

Answer 3

User-821857111 posted

string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DBtb.Text + ";Extended Properties=Excel 12.0";
using(OleDbConnection oledbConn = new OleDbConnection(conn)){
  oledbConn.Open();
  OleDbCommand cmd = new OleDbCommand("INSERT INTO [$Sheet4] (B5, C5, D5) VALUES(1,'Fake Record','Fake Record')", oledbConn);
  cmd.ExecuteNonQuery();
}

You do have columns named C5, D5 etc, don't you? You can use the Excel column names. You have to provide your own as the first row in the spreadsheet.

Answer 4

User1527544554 posted

listen the "Dbtb.Text" is wrong it says its:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Tzahi\\Desktop\\Xlsx Files\\Clal data base 20110930 - Copy.xls;Extended Properties=Excel 12.0"
u see the dubble \\ which arent really there theres a problem 

even when i do this:
 string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Tzahi\Desktop\Xlsx Files\Clal data base 20110930 - Copy.xls;Extended Properties=Excel 12.0";

after it when i put breakpoints i see it changes during the runtime 

Answer 5

User-821857111 posted

Why don't you move the file to App_Data within your site, then use this connection stirng:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|Clal data base 20110930 - Copy.xls;Extended Properties=Excel 12.0

Answer 6

User1527544554 posted

never mind problem solved but im having another problem
when im trying to select a sheet it doesnt work
code:

  OleDbCommand cmd = new OleDbCommand("SELECT O FROM [Main Table]", oledbConn);

error:

The Microsoft Access database engine could not find the object 'Main Table'. Make sure the object exists and that you spell its name and the path name correctly. If 'Main Table' is not a local object, check your network connection or contact the server administrator.

 

Answer 7

User-821857111 posted

You need to prefix sheet names with a $ sign:

OleDbCommand cmd = new OleDbCommand("SELECT O FROM [$Main Table]", oledbConn);

Answer 8

User1527544554 posted

You need to prefix sheet names with a $ sign:

OleDbCommand cmd = new OleDbCommand("SELECT O FROM [$Main Table]", oledbConn);

didnt work

Answer 9

User-821857111 posted

Try wrapping the sheet name in single quotes if it has a space in it:

OleDbCommand cmd = new OleDbCommand("SELECT O FROM ['$Main Table']", oledbConn);

Answer 10

User1527544554 posted

Try wrapping the sheet name in single quotes if it has a space in it:

OleDbCommand cmd = new OleDbCommand("SELECT O FROM ['$Main Table']", oledbConn);

could u help me again write the connection to etsablish with a xlsx file and a connection with it to a Work sheet?

Answer 11

User1527544554 posted

Try wrapping the sheet name in single quotes if it has a space in it:

OleDbCommand cmd = new OleDbCommand("SELECT O FROM ['$Main Table']", oledbConn);

could u help me again write the connection to etsablish with a xlsx file and a connection with it to a Work sheet?