Solved

Getting value after executing a stored procedure in ASP.net

Posted on 2006-06-30
6
350 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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 17

Expert Comment

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now