Solved

Error on Parameterized Queries using Enterprise Library 4.0

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

756 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