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.
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
siddharth_kc...
Member
26 Points
30 Posts
How to use nested queries
Jan 17, 2012 05:33 AM|LINK
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.
adeelehsan
All-Star
18241 Points
2729 Posts
Re: How to use nested queries
Jan 17, 2012 07:05 AM|LINK
Use ExecuteNonQuery()
MCPD ASP.NET 4.0 and 3.5, MCTS WSS, MOSS, SharePoint 2010, MCT
Microsoft Community Contributor Award 2011
hans_v
All-Star
35986 Points
6550 Posts
Re: How to use nested queries
Jan 17, 2012 08:36 AM|LINK
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
rnivash
Member
312 Points
62 Posts
Re: How to use nested queries
Jan 17, 2012 11:47 AM|LINK
sql
hans_v
All-Star
35986 Points
6550 Posts
Re: How to use nested queries
Jan 17, 2012 12:01 PM|LINK
Do you really think this syntax is correct?
rnivash
Member
312 Points
62 Posts
Re: How to use nested queries
Jan 17, 2012 12:20 PM|LINK
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: How to use nested queries
Jan 19, 2012 01:31 AM|LINK
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！