Solved

Getting value after executing a stored procedure in ASP.net

Posted on 2006-06-30
6
361 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: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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 500 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 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