constructing parameter query SQL - LIKE % in .cs and in grid view

I am trying to implement admin login and operators(india, australia, america) login, now operator id starts with 'IND123' 'AUS123' and 'AM123'

when operator india logs in he can see the details of only members have id 'IND%', the same applies for an australian and american users

and when admin logs in he can see details of members with id 'IND%' or 'AUS%' or 'AM%'

i have a table which defines the role i.e admin or operator and their prefix(IND,AUS respectively)

In loginpage i created a session for Role and prefix

PREFIX = myReader["Prefix"].ToString();
Session["LoginRole"] = myReader["Role&开发者_开发问答quot;].ToString(); 
Session["LoginPrefix"] = String.Concat(PREFIX + "%"); 

works fine

In main page(after login) i have to count the number of member so i wrote

string prefix = Session["LoginPrefix"].ToString();
string role = Session["LoginRole"].ToString();
          
if (role.Equals("ADMIN"))
        StrMemberId = "select count(*) from MemberList";
else
    StrMemberId = "select count(*) from MemberList where MemberID like '"+prefix+"'";

That works fine too.

Problem: I want to constructor parameter something like:

 StrMemberId = "select count(*) from MemberList where MemberID like '@prefix+'";
 myCommd.Parameters.AddWithValue("@prefix", prefix);

Which is not working

Displaying the members in gridview i need to give condition (something like if (role.Equals("ADMIN")) show all members else show member depending on the operator prefix)the list of members in operator mode and admin mode.

where to put the condition in gridview

how to apply these

please suggest something Regards

Indranil

You need to construct your query as follows:

"select count(*) from MemberList where MemberID like @prefix"

then

cmd.Parameters.AddWithValue("@prefix", prefix + "%")

1. It should be "select count(*) from MemberList where MemberID like @prefix";
2. You can better do all these checks in a sub-procedure and return the results accordingly. The resultset then can be bound to the gridview