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?
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'";
User contributions licensed under CC BY-SA 3.0