locked
How to use nested queries RRS feed

  • 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.

    Tuesday, January 17, 2012 1:33 AM

Answers

  • 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 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 17, 2012 4:36 AM

All replies

  • User-1618234021 posted

    Use ExecuteNonQuery()

    Tuesday, January 17, 2012 3:05 AM
  • 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 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 17, 2012 4:36 AM
  • 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.


    Tuesday, January 17, 2012 7:47 AM
  • 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?

    Tuesday, January 17, 2012 8:01 AM
  • User1352203401 posted

        

    Tuesday, January 17, 2012 8:20 AM
  • 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!

    Wednesday, January 18, 2012 9:31 PM