Solved

Error on Parameterized Queries using Enterprise Library 4.0

Posted on 2008-10-14
3
780 Views
Last Modified: 2008-10-22
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

0
Comment
Question by:johnaryan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 5

Author Comment

by:johnaryan
ID: 22716667
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

0
 
LVL 26

Accepted Solution

by:
Anurag Thakur earned 500 total points
ID: 22718316
dont do a boxing operation as in (object)id;
cmd.Parameters["@symbol_id"].Value = id == null ? DBNull.Value : id;
0
 
LVL 5

Author Comment

by:johnaryan
ID: 22718884
I don't have the code in front of me now, but wouldn't I get an Invalid conversion?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question