SQL query statement in excel sheet

Question

I am having a SQL query of the form:

Update TableName Set TaskStatus=xxx where TaskID=xx;

I have this SQL query in my C# code.

In an excel file, under B cell, I have a set of statements like Rejected, Completed, Closed and so on. My C# code reads those cells and takes appropriate actions. For example, if it reads Completed, then the below query gets executed:

Update TableName Set TaskStatus=Completed where TaskID=xx;

My problem is, in a cell, I am having the value Can't Complete. When my code reads this cell and merges this into the query,

 Update TableName Set TaskStatus=Can't Complete where TaskID=xx;

it throws an error stating that "quotes should be closed" It is assuming the quote in Can't as a SQL keyword or SQL quotation. How do I get over this?

You need to quote and escape the value, i.e. Update TableName Set TaskStatus='Can''t Complete' where TaskID=xx; - surround with single quotes and double any single quotes within the string. Where's the code that assembles the SQL - can you do this? Actually if your query is in your C# code you should be using a parameterized query instead so you can pass the value to Oracle without inserting it into your string. — Rup
– Rup, Commented Jul 9, 2012 at 11:31
@Rup it works...y didnt u post it as an answer?? i would hav accepted it.. — user1501034
– user1501034, Commented Jul 9, 2012 at 11:43
Posted as a comment because I wasn't sure if you were generating the SQL in Excel or in your C# code - I probably just didn't read the question well enough. However you should use the parameterized query in Csabo's answer instead. — Rup
– Rup, Commented Jul 9, 2012 at 11:44

Csaba Benko · Accepted Answer · 2012-07-09 11:34:25Z

3

AFAIK using parameterized SQL command solve this problem, sincs SqlParameter objects do the excaping for you in these situations. Something like this in your code should solve your problem:

string sql = "Update TableName Set TaskStatus=@status where TaskID=@id;";
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("status", status_excel_call_value);
cmd.Parameters.AddWithValue("id", your_task_id);

cmd.ExecuteNonQuery();

answered Jul 9, 2012 at 11:34

Csaba Benko

1,1618 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dan Over a year ago

Also this is safer as stop injection

stb · Accepted Answer · 2012-07-09 11:41:45Z

1

You can replace ' with ''. The quotes define a text, so your code opens, but doesnt close the text 'area' by inserting Can't.

edited Jul 9, 2012 at 11:41

answered Jul 9, 2012 at 11:34

stb

3,5332 gold badges19 silver badges24 bronze badges

3 Comments

user1501034 Over a year ago

hi..am getting an error "quoted string not properly terminated"..all i did was to change the text as CAN'''T COMPLETE in the cell in excel sheet

user1501034 Over a year ago

actually, only two quotes is enough...Can''t complete....three quotes does not work :)

stb Over a year ago

oh sorry, i typed three quotes and it need to be two. (The first will escape the following)

Collectives™ on Stack Overflow

SQL query statement in excel sheet

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related