[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error on Parameterized Queries using Enterprise Library 4.0

Posted on 2008-10-14
3
Medium Priority
?
792 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 1000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month19 days, 1 hour left to enroll

834 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