Test sol

[Stephen Holdorf]

I have a SQL query that is returning

[PIEBALDconsult]

You probably have an operator-precedence problem due to combining OR with AND -- I can never get it right myself, so I always use parentheses. I also recommend not using sub-queries, especially with IN -- try a JOIN instead.

[Stephen Holdorf]

The only problem I see is how do I add the user and rights tables in to the query? There are no keys to access those tables.

[Richard Deeming]

Try something like this:

SELECT DISTINCT
dw.we_System_Key,
w.we_System
FROM
dod
INNER JOIN do_we_systems dw on d.dod = dw.dod
INNER JOIN lkup_we_systems w on dw.we_System_Key = w.we_System_Key
WHERE
dw.is_deleted = 0
AND
(
w.we_system != ''
OR
EXISTS
(
SELECT 1
FROM users_rights ur
INNER JOIN rights r ON r.rights_key = ur.rights_key
INNER JOIN users usr ON usr.username = ur.username
WHERE usr.username = 'RHale1'
AND r.rights_code = 'Non-Standard Test'
)
)
ORDER BY
w.we_system
;

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Lost User]

holdorf wrote:

I have a SQL query that is returning

From where? This looks weird like this; where did the rest of the question go?

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)

[Stephen Holdorf]

I know there are a lot of posts but I finally understand and did what I was told to do. I broke the query up with parameters and I am still getting the security error. My code is below the with the parameters removed from the hard coded string, the calling code, and the implementing code: The 3 classes with the SQL w/ with the parameters broken out, the calling code, and the implementing code: Class with the parameters broken out:

public class MyParam
{
public string name { get; set; }
public string value { get; set; }
}
///
/// Summary description for QueryContainer SGH
///
public class QueryContainer
{

    string \_query;

    public List parameterList = new List(); 

    public QueryContainer(string query) { \_query = query; }

    public string Query
    {
        get
        {
            return \_query;
        }

        set { \_query = value;  }
    }
}

The calling code:

    public int GetAccountSortByAccountCode(int account)
    {
        QueryContainer Instance = new QueryContainer("SELECT ac\_sort\_order FROM lkup\_account\_codes where ac\_code = [@account](http://www.codeproject.com/Members/account)");

        MyParam myParam = new MyParam();

        myParam.name = "@account";
        myParam.value = account.ToString();

        Instance.parameterList.Add(myParam);

        return Convert.ToInt32(ExecuteScaler(Instance, 1));
    } 

The implementing code:

            if (\_connection == null || \_connection.State == ConnectionState.Closed)
            {
                OpenConnection();
            }

            DbCommand command = \_provider.CreateCommand();
            command.Connection = \_connection;
            {
                command.CommandText = Instance.Query;
                command.CommandType = CommandType.Text;

                foreach (var p in Instance.parameterList)
                {
                    SqlParameter param = new SqlParameter(p.name, p.value);
                    command.Parameters.Add(param);
                }

                if (\_useTransaction) { command.Transaction = \_transaction; }

                try
                {
                    returnValue = command.ExecuteScalar();

[Stephen Holdorf]

I know there are a lot of posts but I finally understand and did what I was told to do. I broke the query up with parameters and I am still getting the security error. My code is below the with the parameters removed from the hard coded string, the calling code, and the implementing code: The 3 classes with the SQL w/ with the parameters broken out, the calling code, and the implementing code: Class with the parameters broken out:

public class MyParam
{
public string name { get; set; }
public string value { get; set; }
}
/// /// Summary description for QueryContainer SGH
///
public class QueryContainer
{

    string \_query;

    public List parameterList = new List(); 

    public QueryContainer(string query) { \_query = query; }

    public string Query
    {
        get
        {
            return \_query;
        }

        set { \_query = value;  }
    }
}

The calling code:

    public int GetAccountSortByAccountCode(int account)
    {
        QueryContainer Instance = new QueryContainer("SELECT ac\_sort\_order FROM lkup\_account\_codes where ac\_code = [@account](http://www.codeproject.com/Members/account)");

        MyParam myParam = new MyParam();

        myParam.name = "@account";
        myParam.value = account.ToString();

        Instance.parameterList.Add(myParam);

        return Convert.ToInt32(ExecuteScaler(Instance, 1));
    } 

The implementing code:

            if (\_connection == null || \_connection.State == ConnectionState.Closed)
            {
                OpenConnection();
            }

            DbCommand command = \_provider.CreateCommand();
            command.Connection = \_connection;
            {
                command.CommandText = Instance.Query;
                command.CommandType = CommandType.Text;

                foreach (var p in Instance.parameterList)
                {
                    SqlParameter param = new SqlParameter(p.name, p.value);
                    command.Parameters.Add(param);
                }

                if (\_useTransaction) { command.Transaction = \_transaction; }

                try
                {
                    returnValue = command.ExecuteScalar();
                }