How to use nested queries

Question

User186553308 posted

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.

Answer 1

User-1618234021 posted

Use ExecuteNonQuery()

Answer 2

User-1199946673 posted

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 

Answer 3

User1352203401 posted

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,


"@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.

Answer 4

User-1199946673 posted

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?

Answer 5

User1352203401 posted

    

Answer 6

User3866881 posted

Hello siddharth_kcr：）

Your codes seem right——

1） Do any modifies data contents to the db's tables，such as inserting，deleting or updating——you should use ExecuteNonQuery()。

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

Reguards！