locked
SQL Stored Procedure - First Try RRS feed

  • Question

  • I am getting an error telling me one of the expected parms was not present.  Here is my stored procedure..

     

    ALTER procedure [dbo].[Insert_Biblio]

    @ISBN as char(11),

    @CALL_NO as char(20),

    @DEWEY_NO as char(20),

    @AUTHOR as char(5),

    @TITLE as char(100),

    @AUTHOR_BORN as int,

    @AUTHOR_DIED as int,

    @PUBLISHER as char(30),

    @YEAR_PUBLISHED as int,

    @BOOK_INFO as char(49),

    @BOOK_HEIGHT as int,

    @BIBLIO_INFO as char(100),

    @COST as money,

    @TOPIC_1 as char(30),

    @TOPIC_2 as char(30)

    as

    declare @Book_ID as char(5);

    begin

    select @Book_ID = max(book_ID) + 1

    from collection

    insert into biblio

    (BOOK_ID,ISBN, CALL_NO, DEWEY_NO, AUTHOR, TITLE,

    AUTHOR_BORN, AUTHOR_DIED, PUBLISHER, YEAR_PUBLISHED,

    BOOK_INFO, BOOK_HEIGHT, BIBLIO_INFO, COST,

    TOPIC_1, TOPIC_2)

    values (@BOOK_ID,@ISBN, @CALL_NO, @DEWEY_NO,

    @AUTHOR, @TITLE, @AUTHOR_BORN,

    @AUTHOR_DIED, @PUBLISHER, @YEAR_PUBLISHED,

    @BOOK_INFO, @BOOK_HEIGHT, @BIBLIO_INFO,

    @COST, @TOPIC_1, @TOPIC_2)

    return 0

    end

     

     

    Here is the code I use to build my parameters and load up the values....

     

    object objNull = (object)null;

    SqlParameter[] parms = new SqlParameter[16];

    parms[0] = new SqlParameter("@ISBN", SqlDbType.Char, 11);

    parms[0].Value = (txtISBN.Text.Length == 0 ? objNull : txtISBN.Text);

    parms[1] = new SqlParameter("@CALL_NO", SqlDbType.Char, 20);

    parms[1].Value = (txtCallNbr.Text.Length == 0 ? objNull : txtCallNbr.Text);

    parms[2] = new SqlParameter("@DEWEY_NO", SqlDbType.Char, 20);

    parms[2].Value = (txtDewerNbr.Text.Length == 0 ? objNull : txtDewerNbr.Text);

    parms[3] = new SqlParameter("@AUTHOR", SqlDbType.Char, 5);

    parms[3].Value = (txtAuthor.Text.Length == 0 ? objNull : txtAuthor.Text);

    parms[4] = new SqlParameter("@TITLE", SqlDbType.Char, 100);

    parms[4].Value = (txtTitle.Text.Length == 0 ? objNull : txtTitle.Text);

    parms[5] = new SqlParameter("@AUTHOR_BORN", SqlDbType.Int);

    parms[5].Value = (txtAuthorDOB.Text.Length == 0 ? objNull : Convert.ToInt32(txtAuthorDOB.Text) );

    parmsDevil = new SqlParameter("@AUTHOR_DIED", SqlDbType.Int);

    parmsDevil.Value = (txtAuthorDOD.Text.Length == 0 ? objNull : Convert.ToInt32(txtAuthorDOD.Text) );

    parms[7] = new SqlParameter("@PUBLISHER", SqlDbType.Char, 30);

    parms[7].Value = (txtPublisher.Text.Length == 0 ? objNull : txtPublisher.Text);

    parmsMusic = new SqlParameter("@YEAR_PUBLISHED", SqlDbType.Int);

    parmsMusic.Value = (txtYearPublished.Text.Length == 0 ? objNull : Convert.ToInt32(txtYearPublished.Text) );

    parms[9] = new SqlParameter("@BOOK_INFO", SqlDbType.Char, 49);

    parms[9].Value = (txtBookInfo.Text.Length == 0 ? objNull : txtBookInfo.Text);

    parms[10] = new SqlParameter("@BOOK_HEIGHT", SqlDbType.Int);

    parms[10].Value = (txtBookHeight.Text.Length == 0 ? objNull : Convert.ToInt32(txtBookHeight.Text));

    parms[11] = new SqlParameter("@BIBLIO_INFO", SqlDbType.Char, 100);

    parms[11].Value = (txtBiblioInfo.Text.Length == 0 ? objNull : txtBiblioInfo.Text);

    parms[12] = new SqlParameter("@COST", SqlDbType.Money);

    parms[12].Value = (txtCost.Text.Length == 0 ? objNull : Convert.ToDouble(txtCost.Text));

    parms[13] = new SqlParameter("@TOPIC_1", SqlDbType.Char, 30);

    parms[13].Value = (txtTopic1.Text.Length == 0 ? objNull : txtTopic1.Text);

    parms[14] = new SqlParameter("@TOPIC_2", SqlDbType.Char, 30);

    parms[14].Value = (txtTopic2.Text.Length == 0 ? objNull : txtTopic2.Text);

    parms[15] = new SqlParameter("@ReturnValue", SqlDbType.Int);

    parms[15].Direction = ParameterDirection.ReturnValue;

    foreach(SqlParameter parm in parms)

    {

    if (parm.ParameterName != "@ReturnValue")

    { parm.Direction = ParameterDirection.Input; }

    }

    string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

    SqlConnection cn = new SqlConnection(conStr);

    SqlCommand cmd = new SqlCommand("dbo.Insert_Biblio", cn);

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddRange(parms);

    cn.Open();

    cmd.ExecuteNonQuery();

     

     

    I know that order is important, but I have built them in the same order.  I think there is a property you can set somewhere that will get SQL to ignore order and match them up, but I am providing in the right sequence.

     

    So what is wrong here?

    Wednesday, October 31, 2007 12:19 AM

All replies

  • Remove the "@ReturnValue" parameter from the list, it isn't declared in the Proc.

    Wednesday, October 31, 2007 1:29 AM
  • Removing @ReturnValue resulted in the same error.

     

    Do you not have to declare a parameter to return the return value from the stored procedure?  I was thinking that I did not have to declare it in the Stored Procedure, but I had to account for the parameter when building my SQL Parms.

     

     

    Wednesday, October 31, 2007 1:51 AM
  • I would suggest that you change your table definition so that your BOOK_ID field is an identity field.  Using your current method of selecting MAX(BOOK_ID) + 1 could cause problems.  For example, if your transaction finds the max book_id and adds 1 so that your new value will be 8, then another transaction does the same thing before your transaction posts the record, then you will have two transactions trying to post with a value of 8.  Not good.

     

    Instead, change the column type to an identity and then when you do your insert, just leave out the book_id field and the database will create the next number automatically for you.  Much nicer.

     

    You have defined BOOK_ID as type char(5).  Is this correct, as you are selecting MAX and adding 1 to it.

     

    Also, you are not declaring your out parameter in your stored procedure definition.  I would hazard a guess that you are wanting to return the id of the last book inserted.  If this is the case, then I would suggest changing it to the following

     

    CREATE procedure [dbo].[Insert_Biblio]

    @ISBN char(11),

    @CALL_NO char(20),

    @DEWEY_NO char(20),

    @AUTHOR char(5),

    @TITLE char(100),

    @AUTHOR_BORN int,

    @AUTHOR_DIED int,

    @PUBLISHER char(30),

    @YEAR_PUBLISHED int,

    @BOOK_INFO char(49),

    @BOOK_HEIGHT int,

    @BIBLIO_INFO char(100),

    @COST money,

    @TOPIC_1 char(30),

    @TOPIC_2 char(30),

    @BOOK_ID int OUTPUT

    as

    begin tran

    insert into biblio

    (ISBN, CALL_NO, DEWEY_NO, AUTHOR, TITLE,

    AUTHOR_BORN, AUTHOR_DIED, PUBLISHER, YEAR_PUBLISHED,

    BOOK_INFO, BOOK_HEIGHT, BIBLIO_INFO, COST,

    TOPIC_1, TOPIC_2)

    values (@ISBN, @CALL_NO, @DEWEY_NO,

    @AUTHOR, @TITLE, @AUTHOR_BORN,

    @AUTHOR_DIED, @PUBLISHER, @YEAR_PUBLISHED,

    @BOOK_INFO, @BOOK_HEIGHT, @BIBLIO_INFO,

    @COST, @TOPIC_1, @TOPIC_2)

     

    set @BOOK_ID = @@identity

    commit tran

     

     

     

    Now, you should be able to call this by changing the line in your code

     

    parms[15] = new SqlParameter("@ReturnValue", SqlDbType.Int);

    parms[15].Direction = ParameterDirection.ReturnValue;

     

    To be

     

    parms[15] = new SqlParameter("@BOOK_ID", SqlDbType.Int);

    parms[15].Direction = ParameterDirection.ReturnValue;

     

    Now when you execute the stored procedure, it will create a new id for the record, insert it and then return the id of the new book that you have created.

    Note that you will need to change your table definition to have the book_id field as an identity (as mentioned earlier).

     

    Hope this helps.

     

    Wednesday, October 31, 2007 12:03 PM