Compilation error on store procedure

Hi all,
Here is my error: Server: Msg 245, Level 16, State 1, Procedure NewAcctTypeSP, Line 10
Syntax error converting the varchar value'The account type is already exist' to a column of data type int.
Here is my procedure:
ALTER PROC NewAcctTypeSP
(@.acctType VARCHAR(20), @.message VARCHAR (40) OUT)
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
SET @.message = 'The account type is already exist'
RETURN @.message
END

BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)
--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Insertion failure on AcctTypeCatalog table.'
RETURN @.message

END
ELSE
BEGIN

COMMIT TRANSACTION
END

RETURN @.@.ROWCOUNT
END
GO

--execute the procedure
DECLARE @.message VARCHAR (40);
EXEC NewAcctTypeSP 'CDs', @.message;
I am not quite sure where I got a type converting error in my code and anyone can help me solve it?
(p.s. I want to return the @.message value to my .aspx page)
Thanks.

You should probably get rid of the RETURN @.@.ROWCOUNT line. It may be getting confused that sometimes you return a varchar and other times int (the rowcount).
Marcie|||Hi Marcie,
I have get rid of the @.@.RowCount, however, I didn't get value from my @.message, it just returned 'undefined'.
do u know why, and how can i fix it?
Thanks.|||Where is it undefined, from your code or still running your test proc?
Marcie|||

when I executed my .aspx page, I input a duplicated value in a field intentionally and since it should return an error message that I have defined in my dtore procedure, however, I just got 'undefined' instead of my error message...
so, here is my .aspx code:
public function SubmitClick (sender:Object, e:EventArgs) : void
{
if (Page.IsValid)
{
myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"));
var acctTypeDA : SqlDataAdapter = new SqlDataAdapter ("select * from AcctTypeCatalog", myConnection);

acctTypeDA.InsertCommand = new SqlCommand("NewAcctType", myConnection);
acctTypeDA.InsertCommand.CommandType = CommandType.StoredProcedure;
acctTypeDA.InsertCommand.Parameters.Add(new SqlParameter("@.acctType", SqlDbType.VarChar, 20)).Value = accountType.Text;

var myParm : SqlParameter = acctTypeDA.InsertCommand.Parameters.Add("@.message", SqlDbType.VarChar, 40);
myParm.Direction = ParameterDirection.Output;

<%--try to get value from @.message and assign it to the variable --%>
var msg : String = acctTypeDA.InsertCommand.Parameters("@.message").Value;
msgLabel.Text = msg;

BindGrid();

}
else
{
msgLabel.Text = "The Page contains error!"
}
}
my store procedure:
ALTER PROC NewAcctTypeSP
(@.acctType VARCHAR(20), @.message VARCHAR (40) OUT)
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
SET @.message = 'The account type is already exist'
RETURN
END

BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)

--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Insertion failure on AcctTypeCatalog table.'
RETURN
END
ELSE
BEGIN
SET @.message = 'Insertion Successful!'
COMMIT TRANSACTION
END
RETURN
END
GO
I have tested my procedure, it works fine in SQL server, however, don't know why I couldn't get the value from @.message on my .aspx page.
Any idea?
Thanks

|||What language is that? You won't be able to get the value of your OUTPUT parameter until right after the command has executed, something like an ExecuteNonQuery line.
Marcie|||hi Marcie,
I used JScript to create my page with using the store procedure. (JScipt is very similar with C#)
I have reviewed the tutorial from the ASP.net and it doesn't have any ExecuteNonQuery statement on the following example:

<script language="JScript" runat="server">
publicfunction GetEmployees_Click(sender : Object, e : EventArgs) :void
{
var myConnection : SqlConnection =new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("NWString"));
var myCommand : SqlDataAdapter =new SqlDataAdapter("SalesByCategory", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.CategoryName", SqlDbType.NVarChar, 15));
myCommand.SelectCommand.Parameters("@.CategoryName").Value = SelectCategory.Value;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.OrdYear", SqlDbType.NVarChar, 4));
myCommand.SelectCommand.Parameters("@.OrdYear").Value = SelectYear.Value;
var ds : DataSet =new DataSet();
myCommand.Fill(ds, "Sales");
MyDataGrid.DataSource=ds.Tables("Sales").DefaultView;
MyDataGrid.DataBind();
}
</script>

So, should I use the ExecuteNonQuery statement to get my output parameter? any example you could show me for getting back the output parameter value with using store procedure(it's ok if that's written in VB or C#)?
i have tried to find some material about this problem, but can't get any of it ...
Thanks again.
|||In the example you show here, the Command gets executed in the myCommand.Fill line, your code didn't have anything like that.
Since you have your command object set up as the InsertCommand of a DataAdapter object, I believe your command would automatically fire when the .Update method is called.
Marcie|||The problem is that return parameters in T-SQL arealways integers. That is whay you got the compilation error, and why you are not getting the value you expect in your client-side code.
Even though you have @.message defined as an output parameter, the RETURN @.message is causing you to error out.|||

Thanks pjmcb,
I already got my problem fixed.