Parameters supplied for object which is not a function. If the parameters are intended as a table hint, a WITH keyword is required

Question

I'm running Windows 7 and II7 and SQL server 2008 R2 . I have an aspx program and when I try to run it I get the following error

Parameters supplied for object 'users' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.

What I've coded is this :

  public ArrayList GetGoodsList(string type, string goodsType, string user, string payType, bool flag)
    {
        conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["Conn"].ToString());

        DataSet ds = new DataSet();
        sSql = "select count(*) from users('" + type + "','" + goodsType + "','" + user + "','" + payType + "')";
        if (flag == true)
        {
            sSql += "where IsCommend = 1";
        }

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = sSql;
        conn.Open();
        int maxRow = Int32.Parse(cmd.ExecuteScalar().ToString());

        sSql = "select * from users('" + type + "','" + goodsType + "','" + user + "','" + payType + "')";
        if (flag == true)
        {
            sSql += "where IsCommend = 1";
        }
        cmd.CommandText = sSql;
        SqlDataReader reader = cmd.ExecuteReader();

        ArrayList gInfos = new ArrayList();
        GoodsInfo gInfo;

        for (int i = 0; i < maxRow; i++)
        {
            if (reader.Read())
            {
                gInfo = new GoodsInfo();
                gInfo.G_ID = Int32.Parse(reader["G_ID"].ToString());
                gInfo.G_Name = reader["G_Name"].ToString();
                gInfo.Type = reader["Type"].ToString();
                gInfo.GoodsType = reader["GoodsType"].ToString();
                gInfos.Add(gInfo);
            }
        }
        conn.Close();
        return gInfos;
    }

Any idea? Thanks!

You should look into Parametrized queries. And possibly show the values of `type`, `goodsType`, etc. (all of the function parameters). That's likely where the issue is coming from. — Jesse, May 03 '13 at 00:53
Parameterized queries: [explanation of why](http://stackoverflow.com/a/5468460/238722); [explains how](http://stackoverflow.com/a/7197005/238722). Also note that [Alastair Pitts' answer](http://stackoverflow.com/a/16349872/238722) is on track to fixing the issue you're seeing. Parameterized queries simply help with preventing [SQL Injection Attacks](http://en.wikipedia.org/wiki/SQL_injection), plus performance, plus caching, etc. — Jesse, May 03 '13 at 04:52

score 2 · Answer 1 · answered May 03 '13 at 00:36

2

Without giving away the answer, your issue in in your SELECT statement, sSql = ...

It's not the correct SQL syntax.

Have a read of this wikipedia article on the SELECT statement.

answered May 03 '13 at 00:36

Alastair Pitts

19,423
9
68
97

Parameters supplied for object which is not a function. If the parameters are intended as a table hint, a WITH keyword is required

1 Answers1