?
Solved

Getting value after executing a stored procedure in ASP.net

Posted on 2006-06-30
6
Medium Priority
?
375 Views
Last Modified: 2008-02-26
Hello,

I am getting the value from the stored procedure

/////////////////////////////////////////////////////////////////////////////////////////////////////////
      public int LogRecipients()
      /////////////////////////////////////////////////////////////////////////////////////////////////////////
      {
            if (!Log || Db == null || Recipients.Count == 0)
                  return -1;

        Db.InitialiseProcedure("z360core_insert_mail_message");

            Db.SetParam("@subject", Msg.Subject, System.Data.DbType.String  );
            Db.SetParam("@recipient", Recipients[0].ToString(), System.Data.DbType.String  );
            Db.SetParam("@sender", Msg.FromAddress, System.Data.DbType.String  );
            Db.SetParam("@email_date", System.DateTime.Now, System.Data.DbType.DateTime );
            Db.SetParam("@processed", System.DateTime.Now, System.Data.DbType.DateTime );
            Db.SetParam("@processed_by", App.UserId, System.Data.DbType.Int32 );
            Db.SetParam("@ticket_number", TicketNumber, System.Data.DbType.Int32 );

            if (ParentMailMessageId != "")
                  Db.SetParam("@mail_message_id", ParentMailMessageId, System.Data.DbType.Int32 );

            int MailMessageId = int.Parse(Db.ExecuteScalarProcedure().ToString());
       

            if (MailMessageId < 0)
                  return -1;

Return MailMessageId ;

}


and below is my stored procedure

ALTER proc [dbo].[z360core_Insert_Mail_Message]
(
@Recipient nvarchar (100) = null,
@Subject nvarchar (250) = null,
@sender nvarchar (250)  = null,
@sender_name nvarchar (250)  = null,
@message_id varchar (250)  = null,
@content_type varchar (50)  = null,
@email_date datetime  = null,
@mail_message_id int  = null,
@processed datetime  = null,
@processed_by int  = null,
@ticket_number int = null
)
AS
if (@message_id is not null)
begin
      if Exists( select 0 from z360core_mail_message where message_id = @message_id and recipient = @Recipient )
      BEGIN
            select -1
            return
      END
end

INSERT INTO z360core_mail_message (
      recipient,
      Subject ,
      sender ,
      sender_name,
      message_id ,
      content_type ,
--      raw_message ,
      email_date,
      zmail_template_id,
      processed,
      processed_by,
      ticket_number
      )
       VALUES (
      @Recipient  ,
      @Subject  ,
      Lower(@sender)  ,
      @sender_name,
      @message_id  ,
      @content_type  ,
--      @raw_message  ,
      @email_date,
      @mail_message_id,
      @processed,
      @processed_by,
      @ticket_number
      )
       
      select  @@IDENTITY



I am getting the jsut inserted identity value like this.
int MailMessageId = int.Parse(Db.ExecuteScalarProcedure().ToString());

I just wanted to know if this is the one causing errors in other functions. Thanks in advance..

0
Comment
Question by:sureshraina
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 9

Expert Comment

by:zaghaghi
ID: 17019671
best to use SqlHelper

and do like this
SqlHelper.ExecuteScalar(ConnectionString,"SPNAME", value1, value2, ... )

or

DataReader dr = SqlHelper.ExecuteReader(ConnectionString,"SPNAME", value1, value2, ... )
dr.read()
then use dr[0] and convert it to correct type
dr.close()
0
 
LVL 5

Expert Comment

by:CyberneticsConnoisseur
ID: 17020372
Hi Suresh,

You haven't given any details about the error you are receiving.

As per my assumption, you might be receiving an error if the insert statement itself fails in the stored procedure due to which there is an SQL Error.

There is no error handling done in the SP.

To know if there is an SQL error in the SP
write this before the insert statement


Declare @Error int, @Identity int


and just after the insert statement, instead of writing ' select  @@IDENTITY' , write


Select @Error = @@Error, @Identity = @@Identity

IF (@Error = 0)
      select @Identity
Else
      select -1



In the code you will have to check the value of
 'MailMessageId' for any errors before proceeding with your logic.

If you give the error details, I guess I'll be able to help better.

Carpe Diem
0
 

Author Comment

by:sureshraina
ID: 17020408
The error I am getting is 'Input String is not in Correct Format'
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 9

Expert Comment

by:zaghaghi
ID: 17020507
Db.ExecuteScalarProcedure don't return an int value, go ion debug and check the value
0
 
LVL 18

Expert Comment

by:Elvio Lujan
ID: 17021106
change "     select  @@IDENTITY"
for
"     return  @@IDENTITY"
0
 
LVL 5

Accepted Solution

by:
vinodhsomasekharan earned 1500 total points
ID: 17023519

Hai,

      Since u r returning a value from a stored procedure try using output parameter..

so u r stored procedure will have a new output parameter...

ALTER proc [dbo].[z360core_Insert_Mail_Message]
(
@Recipient nvarchar (100) = null,
@Subject nvarchar (250) = null,
@sender nvarchar (250)  = null,
@sender_name nvarchar (250)  = null,
@message_id varchar (250)  = null,
@content_type varchar (50)  = null,
@email_date datetime  = null,
@mail_message_id int  = null,
@processed datetime  = null,
@processed_by int  = null,
@ticket_number int = null,
@messageId int OUTPUT                 -- output parameter to be added which returns the new identity value
)
AS
..etc

and instead of "select  @@IDENTITY"
just give
" set @messageId = @@IDENTITY "

=============

now in your asp.net code.. just add another parameter [output direction] for the stored procedure
eg..
Db.SetParam("@messageId ", System.Data.DbType.Int32 );
.Direction = ParameterDirection.Output   // mention the parameter as output param..

regards
Vinodh
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question