How to use nested queries

I want to update [msg] and [friendid] field in friends table where friendid is in members table. How can I achieve this? I am trying to use nested query as:

string sql = "update friends set msg=@msg where uid=@uid and friendid IN (select id from members where email=@email)";

            using (OleDbConnection con = ConnectionManager.getConnection())
            {
                OleDbCommand cmd = new OleDbCommand(sql, con);
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@msg", "You have received a friend request from " + returnName(uid));
                cmd.Parameters.AddWithValue("@uid", uid);
                cmd.Parameters.AddWithValue("@email", email);
                cmd.ExecuteNonQuery();
            }

Even I don't know wheather I should use NonQuery for update or Scalar for select. Plz help me.

Re: How to use nested queries

Use ExecuteNonQuery()

Re: How to use nested queries

OleDb parameters are recognized by position, not by their name. So at first sight, you seem to add them in the right order. But the problem with subquerys is, that they will be processed first. So the correct order of the parameters is: email, msg, uid

cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@msg", "You have received a friend request from " + returnName(uid));
cmd.Parameters.AddWithValue("@uid", uid);

And yes, ExecuteNonQuery is the right method

By the way, is ConnectionManager.getConnection() returning an open connection? If not, you need to open the connection before calling ExecuteNonQuery

Re: How to use nested queries

siddharth_kcr
want to update [msg] and [friendid] field in friends table where friendid is in members table



Try the following query,

update friends set msg = @msg ,friendID =members.id from (select @email as tmpEmail) as temptable

join members on temptable.tmpEmail = members.email where friends.uid = @uid


I dont't find any meanig from the above operation becuase you mentioed,


siddharth_kcr
"@msg", "You have received a friend request from " + returnName(uid)


You have used "uid" as request sender but again updating the same user.. please check that too..

Regards,
Nivash Ramachandran.

Re: How to use nested queries

rnivash
Try the following query,

update friends set msg = @msg ,friendID =members.id from (select @email as tmpEmail) as temptable

join members on temptable.tmpEmail = members.email where friends.uid = @uid

Do you really think this syntax is correct?

Re: How to use nested queries

Hello siddharth_kcr

Your codes seem right

1 Do any modifies data contents to the db's tablessuch as insertingdeleting or updatingyou should use ExecuteNonQuery()

2Do to fetch single record value from db's tableyou should use ExecuteScalar()something like "select top 1 XXXField from XXX"

Reguards