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

Question

Hello friends,

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"].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+"'";

thatworks fine too

Problem: 1. 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

2 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

Answer 1

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

Answer 2

You need to construct your query as follows:

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

then

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