Solved

Error on Parameterized Queries using Enterprise Library 4.0

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 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