IF A BLANK SPACE IS PRESENT IN THE COLUMN HEADING .....AND/OR operator not working in sql statement in c# .net for modifying an excel sheet

-5

A run time error is occurring if i use and/or statements in the sql statements..Pls help me solve the problem

System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbCommand myCommand;
myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
MessageBox.Show("Opening " + selectedPath);
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + rootPath+"FacultyDutiesEnd.xls" + "';Extended Properties=Excel 8.0;");
MyConnection.Open();
myCommand.Connection = MyConnection;
sql = "Update [Sheet1$] set A1 = '\u221A',A2 = '\u221A',B1 = '\u221A',B2 = '\u221A',C1 = '\u221A',C2 = '\u221A',D1 = '\u221A',D2 = '\u221A',E1 = '\u221A',E2 = '\u221A',F1 = '\u221A',F2 = '\u221A',G1 = '\u221A',G2 = '\u221A'";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
sql = "Update [Sheet1$] set A1 = 'a' where Designation = 'Prof(senior)'  and (Faculty Name = 'bob') ";// this line is not being executed unless the    and (Faculty Name = 'bob')    is removed
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();

error is too long ,,begins with System.data.oledb.oledbException(0x80040E14):Syntax error (missing operator) in query expression 'Designation = 'Prof' and Name = 'bob'........

And if
and Faculty Name = 'bob' is replaced with
and A2= '\u221A' is used it is working

Update

if I keep blank space between design ation ...even it is not working ...So is there any way where the column heading has a blank space and sql statement will be executed?

c#
sql
.net
excel
oledb
asked on Stack Overflow Jan 2, 2015 by KartheekJ • edited Jan 2, 2015 by KartheekJ

1 Answer

1

I'm pretty sure that the problem is the space in the name of your Faculty Name column and that this has nothing to do with the use of AND or OR. Please try:

sql = "Update [Sheet1$] set A1 = 'a' " +
      "where Designation = 'Prof(senior)' and [Faculty Name] = 'bob'";
answered on Stack Overflow Jan 2, 2015 by JLRishe

User contributions licensed under CC BY-SA 3.0