SQL Paramters

I am in a SQL Class and I am starting to learn about SQL Parameters in conjunction with using them from a C# program. I have a couple of questions.

1. When I include an Output parameter in the list or paramters that I send, SQL always is returning a message indicating that the parameter was missing, when I know in fact that I am passing the paramter (@RtnMsg)

2. When I remove any reference to "@RtnMsg", which it can not find, and execute the stored procedure it always returns a -1 and never inserts the record.

Here is my code for the 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),

@RtnMsg as varchar(50) output

as

declare @Book_ID as char(5);

begin

Set @RtnMsg = 'Insert Successful'

return 0

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 C# program code (this version is the one where SQL tells me @RtnMsg is missint.


SqlParameter[] parms = new SqlParameter[16];

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

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

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

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

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

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

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

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

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

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

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

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

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

parms.Value = (txtAuthorDOD.Text.Length == 0 ? (object) DBNull.Value : Convert.ToInt32(txtAuthorDOD.Text) );

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

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

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

parms.Value = (txtYearPublished.Text.Length == 0 ? (object) DBNull.Value : Convert.ToInt32(txtYearPublished.Text) );

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

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

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

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

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

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

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

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

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

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

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

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

parms[15] = new SqlParameter("@RtnMsg", SqlDbType.VarChar, 50);

parms[15].Direction = ParameterDirection.Output;

foreach(SqlParameter parm in parms)

{

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

{ parm.Direction = ParameterDirection.Input; }

}

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

SqlConnection cn = new SqlConnection(conStr);

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

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddRange(parms);



cn.Open();

int rtnCode = cmd.ExecuteNonQuery();