How to change room status of multiple rooms?

Question

0.00/5 (No votes)

See more:

I am using code like below to change room status. It was working fine because till that time i had to change only single room status.

C#

String sqlr = "update roominformation set status = '0' where roomno = '" + txt_roomno.Text + "'";
new OleDbCommand(sqlr, sr, trans).ExecuteNonQuery();

Now i have multiple rooms in the single text box (txt_roomno) like Single-101,Single-102,Single-103. Before this i have only one room in the text box like Single-101,so there is no problem while updating this.

My Question is now how can i change the status of multiple room numbers from single text box.

It will be like First change the status of Single-101, then Single-102, after that Single-103.

I dont know how to achieve this task..

Please help.

Posted 31-Jul-13 23:54pm

Harpreet_125

Add a Solution

2 solutions

Solution 1

Hello Harpreet,

Take the multiple room number in single variable separated by comma format.
E.g.

C#

String txt_roomno.Text= 'Single-101,Single-102,Single-103' ;

And write your SQL query as below

C#

String sqlr = "update roominformation set status ='0' where roomno in '" + txt_roomno.Text + "'";

Posted 1-Aug-13 0:03am

Rohan_P

Comments

Gautam Raithatha 1-Aug-13 6:30am

this will generate query like:
update roominformation set status ='0' where roomno in 'Single-101,Single-102,Single-103'

but it should be like below:
update roominformation set status ='0' where roomno in ('Single-101','Single-102','Single-103')

Harpreet_125 1-Aug-13 6:38am

i have tried as you said but not able to achieve the task.. i think gautam is saying right..

Adarsh chauhan 1-Aug-13 6:48am

yup it will not achieve harpreet's goal.
it will generate
update roominformation set status ='0' where roomno in 'Single-101,Single-102,Single-103'

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gautam Raithatha · Accepted Answer · 2013-08-01T00:27:00

Solution 2

implement this way:

C#

string text = txt_roomno.Text;
string[] arrRoomNos = text.Split(',');

string roomNosCSV = string.Empty;
foreach (string room in arrRoomNos)
{
    if (roomNosCSV != string.Empty)
        roomNosCSV += ",";

    roomNosCSV += "'" + room.Trim() + "'";
}

String sqlr = "update roominformation set status = '0' where roomno in (" + roomNosCSV + ")";
new OleDbCommand(sqlr, sr, trans).ExecuteNonQuery();

Posted 1-Aug-13 0:27am

Gautam Raithatha

Comments

Harpreet_125 1-Aug-13 6:37am

it is not working dude..

Gautam Raithatha 1-Aug-13 6:41am

Please provide actual query that is generated. And also what is the datatype of roomno column?

Syed Shabeer 1-Aug-13 6:56am

Gautam's solution will work for sure. Harpreet, why it isnt working in your case?

Adarsh chauhan 1-Aug-13 6:56am

I agree with Gautam It will generate correct query, if your textbox returns values like Single-101,Single-102,Single-103 .
problem must be somewhere else..

Harpreet_125 1-Aug-13 6:59am

i am using string(text) data type in access database.. And it is giving error like "No value given for one or more required parameters".

Gautam Raithatha 1-Aug-13 7:03am

Then you must debug your code to find what query is generated actually and where actually error is. :)

Harpreet_125 1-Aug-13 7:10am

roomNosCSV retrieves value like 'Single-101','Single-102'.

String sqlr = "update roominformation set status = '0' where roomno in ('" + roomNosCSV + "')";
new OleDbCommand(sqlr, coo).ExecuteNonQuery();

i am using single quotation mark with roomNosCSV because my datatype is text.. I think there is the problem. because it gives error like Syntax error (missing operator) in query expression 'roomno in (''Single-101','Single-102'')'.

It gives error because c# adds single quotation marks from self. I hope u understand my problem now.

Harpreet_125 1-Aug-13 7:19am

Sorry Brother my mistake i was giving column name wrong... extremely sorry.. Appreciate your help.. I am very Very thankful of yours... Thanks..

Gautam Raithatha 1-Aug-13 7:26am

you are most welcome... :)