Solved

Can you guys tell me if this is returning the correct data from a Stored Procedure? Expected value returned, success with 0 value returned

Posted on 2006-11-11
6
154 Views
Last Modified: 2011-10-03
I have executed a query in against the Db, we are having a issue of data not being returned correctly and I am trying to isolate it. The query is listed below

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[usp_i_admin_insert_catitem]
@cid int,
@catid int,
@iid int
AS
begin tran
      declare @ins_error int
      Select @ins_error = 0
      insert into t_catitem (iid, catid)
      values (@iid, @catid)
      select @ins_error = @@error
      
      IF @ins_error = 0
      BEGIN
            COMMIT TRAN
            select 'success' as status
      END
      ELSE
      BEGIN
               ROLLBACK TRAN
            select 'error' as status
      END

If I execute the stored procedure against the DB, it queries it against the db table t_catitem, however I have to pass a null value for cid as the table only has ciid, catid, and iid, so I pass a null value for cid, i put in 9 for catid and 1248 for iid (which I have verified in the DB as being there and correct) and I get a "return success" and a value "0" I would assume I should get a value back, can you guys help me figure this out, I am working on a big project because our programmer fell out and I don't have a strong background in asp .net. Your help would be greatly appreciated!
0
Comment
Question by:blakmoon91
6 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 17923391
Since your Stored Procedure neither Returns a Value, nor has a Parameter declared as either InOu or Out, how can you expect to get ANY value back from the Stored Procedure?

AW
0
 

Author Comment

by:blakmoon91
ID: 17923403
Arthur,
Appreciate the follow up, I have very little knowledge as explained before, how would you suggest going about fixing this? Can you explain further about this?
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 17923556
what value would you like to return, as this SP is doing a simple insert - that results in no specific value as a result.

In addition, since the SP never makes use of the first parameter, two questions/observations come immediately to mind:

1) Why does your Stored procedure have three input parameters, when only two of them are used?

2) since the first paramater is not used in any way, it makes ABSOLUTELY NO DIFFERENCE whatsoever, what value you give it.  cid = NULL, cid = 9, cid = 123456, cid = 999999999  all will result in exactly the same final outcome.  why worry about what value you pass as CID?

AW
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 375 total points
ID: 17924187
i dont think there is any prob with ur proc if u intented to return succes when the proc succeed and error when it fails then u are on the right track

all u got to do is


SqlConnection Con = new SqlConnection(ur Connection string);
            SqlCommand FCommand = new SqlCommand("usp_i_admin_insert_catitem", Con);
            Con.Open();
            FCommand.CommandType = CommandType.StoredProcedure;

//add ur parameters
            object obj = FCommand.ExecuteScalar();
            Con.Close();
            if (obj != null)
                MessageBox.Show(obj.ToString());
0
 
LVL 6

Expert Comment

by:so3
ID: 17924757
in your procedure why do you use
Select @ins_error = 0
select @ins_error = @@error

instead of
set@ins_error = 0
set @ins_error = @@error

and also as Arthur_Wood  said if you don't use the @cid  remove it from the parameter list
0
 
LVL 21

Expert Comment

by:mastoo
ID: 17925399
If you get an @@ERROR <> 0, your c# code is going to get a SqlException raised.  You need to have the if statement like you do in your proc to handle the commit/rollback, but returning the "success" or "error" won't be very meaningful since your code already needs to be able to catch the sqlexception.  Same kind of thing with returning an error status - it doesn't add much with your example.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 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

12 Experts available now in Live!

Get 1:1 Help Now