SqlCommand.DeriveParameters failed because the SqlCommand.CommandText property value is an invalid multipart name, the current limit of "4" is insufficient.

Topics: Data Access Application Block
Jun 24, 2009 at 12:21 PM

Hi,

I'm currently getting the error:
SqlCommand.DeriveParameters failed because the SqlCommand.CommandText property value is an invalid multipart name, the current limit of "4" is insufficient.

When I try to run the following code:
Database db = DatabaseFactory.CreateDatabase("DBConnString");
string sqlCommand = string.Format(System.Configuration.ConfigurationManager.AppSettings.Get("SqlQuery"),this.ID);          
using (IDataReader dataReader = db.ExecuteReader(sqlCommand))
     {
     while (dataReader.Read())
     {
          this.Links.Add(new Link { displayText = dataReader["Link_Title"].ToString(), URL = dataReader["Link"].ToString() });
     }
}

with the following SQL statement:

SELECT * FROM Openquery(iwslink,
	'SELECT LINK_Title, Link 
	FROM IWS.USER_LINKS 
	WHERE NO_INT_USER = (
		SELECT NO_INT_USER 
		FROM iws.users 
		WHERE employeeNumber = {0}) 
	UNION 
		SELECT Link_Title, Link 
		FROM IWS.USER_LINKS 
		WHERE NO_INT_GROUP IN (
			SELECT GROUPS.no_int_group 
			FROM IWS.GROUPS, IWS.MEMBERS 
			WHERE GROUPS.NO_INT_GROUP = MEMBERS.NO_INT_GROUP 
			AND 
			MEMBERS.NO_INT_USER = (
				SELECT NO_INT_USER 
				FROM iws.users 
				WHERE employeeNumber = {0}))')

When running the same query on SQL management studio (of course when replacing the {0} with a real value), everything works fine.

Finally, the following code:

string cs = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(cs))
            {
                try
                {
                    con.Open();
                    SqlCommand dbCommand = new SqlCommand(sqlCommand,con);
                    using (SqlDataReader r = dbCommand.ExecuteReader())
                    {
                        while (r.Read())
                        {
                            this.Links.Add(new Link { displayText = r["Link_Title"].ToString(), URL = r["Link"].ToString() });
                        }
                    }
                }
                catch (Exception)
                {
                    
                    throw;
                }
            }

runs fine.

Any help on this will be greatly appreciated.

 

<font size="2" color="#008000"><font size="2" color="#008000">

 

</font></font><font size="2" color="#008000">

 

</font>

 


 

Jun 25, 2009 at 4:51 AM

What version of entlib is this?  There's no overload in 4.1 for ExecuteReader that accepts an sql statement, there is one accepting a string but it should be the stored procedure name.  Probably you are using this overload but I'm not sure since I'm getting a different error if I pass an sql statement.

 

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.

entlib.support@avanade.com

Jun 25, 2009 at 8:39 AM

My fault. Sorry for that.