Output parameter problems

Topics: Data Access Application Block
Jul 5, 2010 at 11:20 AM

Hi there,

I know this is old ground - I've read some of the existing threads on this issue but I'm not getting anywhere. I wish to retrieve the index created on an insert. I tried this ....

using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
Database _db = EnterpriseLibraryContainer.Current.GetInstance<Database>("EMSDB");

    protected void btnAdd_Click(object sender, EventArgs e)
        //Add details
        System.Data.Common.DbCommand insertCommand = null;

        insertCommand = _db.GetStoredProcCommand("sp_addDemo");
        _db.AddInParameter(insertCommand, "UserDemoID", System.Data.DbType.String, txtDemoID.Text);
        _db.AddInParameter(insertCommand, "DemoName", System.Data.DbType.String, txtDemoName.Text);
        _db.AddInParameter(insertCommand, "Duration", System.Data.DbType.Int32, Convert.ToInt32(txtDuration.Text));
        _db.AddInParameter(insertCommand, "Type", System.Data.DbType.String, ddlType.SelectedItem.Text);
        _db.AddInParameter(insertCommand, "Description", System.Data.DbType.String, txtDescription.Text);
        _db.AddInParameter(insertCommand, "DateCreated", System.Data.DbType.DateTime, DateTime.Now);
        _db.AddInParameter(insertCommand, "DateModified", System.Data.DbType.DateTime, DateTime.Now);
        _db.AddOutParameter(insertCommand, "DemoID", System.Data.DbType.Int32, int.MaxValue);


        int demoID = (int)_db.GetParameterValue(insertCommand, "DemoID");



And I get the message 'Specified cast is not valid
Then with a slightly different approach ....
   protected void btnAdd_Click(object sender, EventArgs e)
        //Add demo details
        System.Data.Common.DbCommand insertCommand = null;

        object[] paramArray = new object[8];
        int DemoID = 0;
        string UserDemoID = txtDemoID.Text;
        string DemoName = txtDemoName.Text;
        Int32 Duration = Convert.ToInt32(txtDuration.Text);
        string Type = ddlType.SelectedItem.Text;
        string Description = txtDescription.Text;
        DateTime DateCreated = DateTime.Now;
        DateTime DateModified = DateTime.Now;
        paramArray[0] = UserDemoID;
        paramArray[1] = DemoName;
        paramArray[2] = Duration;
        paramArray[3] = Type;
        paramArray[4] = Description;
        paramArray[5] = DateCreated;
        paramArray[6] = DateModified;
        paramArray[7] = DemoID;

        insertCommand = _db.GetStoredProcCommand("sp_addDemo", paramArray);

        int demoID = (int)_db.GetParameterValue(insertCommand, "DemoID");


But demoID is = 0
The new row is being created in the database.
Can you spot a problem with this code ?
Jul 6, 2010 at 4:47 AM

Unfortunately, I'm unable to reproduce your problem and both approaches works fine from my end. As far as I know  the "Specified cast is not valid" usually happens when you're casting the output parameter value to a type different from what has been defined in the AddOutParameter method.

What I can only suggest right now is to double check all the parameter DataType defined in your stored procedure and make sure it is in synch with what was defined in the application code. Also, if you have a sample app that can reproduce your problem you can mail it to us (entlib.support@avanade.com) so we can take a closer look on the problem. 

Gino Terrado
Global Technology and Solutions
Avanade, Inc.