Solved

Error on Parameterized Queries using Enterprise Library 4.0

Posted on 2008-10-14
3
768 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
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:…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now