sure, just need to use the RAISERROR in sql server.
in your ASP.Net application, inside a try{...} add your query, and in the catch prepare a label to receive the Error Message, that will be the message from SqlServer.
example:
try
{
DataBase.Execute("declare @temp int" +
"select @temp=0" +
"if @temp=0" +
"RAISERROR('[Error Message]', 16, 1)");
}
catch (Exception ex)
{
lblInfo.Text = ex.Message;
}
after executing this the text of the label will be: [Error Message]
hope it helps.