Link to home
Start Free TrialLog in
Avatar of johnaryan
johnaryanFlag for Ireland

asked on

Error on Parameterized Queries using Enterprise Library 4.0

I'm creating a simple Data Access Layer for an app using the Enterprise Library 4.0. I can execute simple sql queries and they return data. However, when I try to create a "Parameterized Query"  the following exceptio occurs.

System.Data.SqlClient.SqlException was caught
  Message="Parameterized Query '(@symbol_id int)Select * from Symbol where [symbol_id] = @symbol' expects parameter @symbol_id, which was not supplied."
  Source=".Net SqlClient Data Provider"

The parameter never gets set and I can't see why!!
 

private static readonly string SQL_SELECT = @"Select * from Symbol where [symbol_id] = @symbol_id";
        
        public IDataReader Select(System.Nullable<int> id)
        {
            try
            {
                Database db = DatabaseFactory.CreateDatabase("myConnection");
                DbCommand cmd = db.GetSqlStringCommand(SQL_SELECT);
                cmd.Parameters.Add(new SqlParameter("@symbol_id", SqlDbType.Int));
                cmd.Parameters["@symbol_id"].Value = id;
                return db.ExecuteReader(cmd);
            }
            catch
            {
                throw;
            }
        }

Open in new window

Avatar of johnaryan
johnaryan
Flag of Ireland image

ASKER

Just realised it didn't like when I passed in a null value.
I fixed it like this(changed line 4)

Does anyone have a better solution?

Database db = DatabaseFactory.CreateDatabase("myConnection");
DbCommand cmd = db.GetSqlStringCommand(SQL_SELECT);
cmd.Parameters.Add(new SqlParameter("@symbol_id", SqlDbType.Int));
cmd.Parameters["@symbol_id"].Value = id==null?DBNull.Value:(object)id;
return db.ExecuteReader(cmd);

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Anurag Thakur
Anurag Thakur
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I don't have the code in front of me now, but wouldn't I get an Invalid conversion?