none
Problems with AddInParameter in Data Application Block RRS feed

  • Question

  • First of I am sorry if I have put my question in a wrong thread.

    I have been using Enterprise Library and Data Application Block to configure a connection to a SQL Server. There have been no problems so far.
    However when I am trying call my stored procedure from my c# code, I am experiencing an odd error. It says that I have not provided the argument that my stored procedure needs.

    The stored procedure is all right and giving me the the data I want to (I have run that in Query Analyzer) so no problems here.
    I have tried calling a stored procedure that needs no arguments and that returns data with no problems at all.
    However I have a stored procedure called 'GetCharacter' which takes a characterId as argument, and I have used the AddInParameter for that purpose.

    string cmdTxt = "GetCharacter";
                try {
                    Database db = DatabaseFactory.CreateDatabase();
                    using (DbCommand dbCommand = db.GetStoredProcCommand(cmdTxt)) {
                        db.AddInParameter(dbCommand, "characterId", DbType.Int32, 1);
                        IDataReader reader = db.ExecuteReader(CommandType.StoredProcedure, cmdTxt);
                    }
                } catch (Exception ex) {
                    string message = ex.ToString();
                }

    It does not work as it gives me an exception saying:
    $exception   
    {"Procedure 'GetCharacter' expects parameter '@characterId', which was not supplied."}    System.Exception {System.Data.SqlClient.SqlException}

    I have even tried to prefixed with '@' (db.AddInParameter(dbCommand, "@characterId", DbType.Int32, 1)), but with no success.

    I have been looking around on the Internet at different forums, but I have not found any hint at all.
    Please can anyone here see what I am doing wrong.

    Friday, October 17, 2008 10:47 PM

Answers

  • I found out what the problem was:
    IDataReader reader = db.ExecuteReader(CommandType.StoredProcedure, cmdTxt) <- cmdTxt

    Instead of using my cmdTxt as an argument, I should use my dbCommand object. I cant believe it was that simple.

    So the right way is:
    IDataReader reader = db.ExecuteReader(CommandType.StoredProcedure, dbCommand);


    Saturday, October 18, 2008 7:14 AM

All replies

  • A better forum is ADO.Net Data Providers

    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1

     

    I don't know what the AddInParameter method is, I'm assuming is something similar to

      DbParameter p = dbCommand.CreateParameter();

      p.ParameterName = "characterId";

      p.DbType = DbType.Int32;

      p.Value = 1;  // this is the part to investigate - is it setting p.Size or p.Value?

      db.Parameters.Add(p);

     

    It's possible that your AddInParameter is setting p.Size and never setting p.Value.

    If your database stored procedure expects a non-null value, it may complain about not being supplied.

     

     

    Saturday, October 18, 2008 12:27 AM
    Moderator
  • I found out what the problem was:
    IDataReader reader = db.ExecuteReader(CommandType.StoredProcedure, cmdTxt) <- cmdTxt

    Instead of using my cmdTxt as an argument, I should use my dbCommand object. I cant believe it was that simple.

    So the right way is:
    IDataReader reader = db.ExecuteReader(CommandType.StoredProcedure, dbCommand);


    Saturday, October 18, 2008 7:14 AM