Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Getting value after executing a stored procedure in ASP.net

Posted on 2006-06-30
6
Medium Priority
?
378 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
6 Comments
 
LVL 9

Expert Comment

by:Hamed 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
Technology Partners: 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!

 
LVL 9

Expert Comment

by:Hamed 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

Technology Partners: 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

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
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…
Suggested Courses

971 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