i want to return 2 values with Stored procedure

Question

0.00/5 (No votes)

See more:

, +

I have a tables that name are Ogrenci(Student) and belge(document).ı dont want to have many same record in my database.

ALTER proc [dbo].[BelgeKayit]
(
@OgrenciNo int,
@BelgeId int,
@BelgeBaslik nvarchar(30),
@BelgeAciklama nvarchar(250),
@Durum int output,
@Sonuc int output
)

as 
declare @OgrenciKayitKontrol int
declare @BelgeKayitKontrol int

Set @OgrenciKayitKontrol=(select count(0) from Ogrenci where Ogrenci.OgrenciNo=@OgrenciNo)

if(@OgrenciKayitKontrol=1)
begin
insert into OgrenciBelge(OgrenciNo,BelgeId,BelgeBaslik,BelgeAciklama) values(@OgrenciNo,@BelgeId,@BelgeBaslik,@BelgeAciklama)

set @Durum=1
end
else
begin
set @Durum=0
end
return @Durum

Set @BelgeKayitKontrol=(select count(0) from OgrenciBelge where OgrenciBelge.OgrenciNo=@OgrenciNo and OgrenciBelge.BelgeId=@BelgeId)

if(@BelgeKayitKontrol=1)
begin

insert into OgrenciBelge(OgrenciNo,BelgeId,BelgeBaslik,BelgeAciklama) values(@OgrenciNo,@BelgeId,@BelgeBaslik,@BelgeAciklama)

set @Sonuc=1
end
else
begin
set @Sonuc=0
end
return @Sonuc

my asp c# code is

SqlParameter[] paramdizi = new SqlParameter[6]
            {   
                new SqlParameter("@OgrenciNo",SqlDbType.Int),
                new SqlParameter("@BelgeId",SqlDbType.Int) ,
                new SqlParameter("@BelgeBaslik",SqlDbType.NVarChar),
                new SqlParameter("@BelgeAciklama",SqlDbType.NVarChar) ,              
                new SqlParameter("@Durum",SqlDbType.Int),
                new SqlParameter("@Sonuc",SqlDbType.Int)
            };
            paramdizi[0].Value = TxtOgrenciNo.Text;
            paramdizi[1].Value = DrpBelgeTuru.SelectedValue;
            paramdizi[2].Value = TxtDosyaBaslik.Text;
            paramdizi[3].Value = TxtAciklama.Text;
            paramdizi[4].Direction = ParameterDirection.Output;
            paramdizi[5].Direction = ParameterDirection.Output;
            SqlDataReader dr = baglan.ExecuteReader("BelgeKayit", CommandType.StoredProcedure, paramdizi);
            List<BelgeGoster> belgeler = new List<BelgeGoster>();
            while (dr.Read())
            {
                BelgeGoster belge = new BelgeGoster(dr.GetInt32(0), dr.GetInt32(1), dr.GetString(2), dr.GetString(3));
                belgeler.Add(belge);
            }
            GridViewBelgeler.DataSource = belgeler;
            GridViewBelgeler.DataBind();
            if (paramdizi[4].Value.ToString() == "0")
            {
                LabelDurum.Text = "   Böyle bir öğrenci numarası bulunmamaktadır. ";
            }
            else if (paramdizi[5].Value.ToString() == "0")
            {
                LabelDurum.Text = "   0 ";
            }
            else
            {
                LabelDurum.Text = " 1 ";
            }      
SqlParameter[] paramdizi = new SqlParameter[6]
            {   
                new SqlParameter("@OgrenciNo",SqlDbType.Int),
                new SqlParameter("@BelgeId",SqlDbType.Int) ,
                new SqlParameter("@BelgeBaslik",SqlDbType.NVarChar),
                new SqlParameter("@BelgeAciklama",SqlDbType.NVarChar) ,              
                new SqlParameter("@Durum",SqlDbType.Int),
                new SqlParameter("@Sonuc",SqlDbType.Int)
            };
            paramdizi[0].Value = TxtOgrenciNo.Text;
            paramdizi[1].Value = DrpBelgeTuru.SelectedValue;
            paramdizi[2].Value = TxtDosyaBaslik.Text;
            paramdizi[3].Value = TxtAciklama.Text;
            paramdizi[4].Direction = ParameterDirection.Output;
            paramdizi[5].Direction = ParameterDirection.Output;
            SqlDataReader dr = baglan.ExecuteReader("BelgeKayit", CommandType.StoredProcedure, paramdizi);
            List<BelgeGoster> belgeler = new List<BelgeGoster>();
            while (dr.Read())
            {
                BelgeGoster belge = new BelgeGoster(dr.GetInt32(0), dr.GetInt32(1), dr.GetString(2), dr.GetString(3));
                belgeler.Add(belge);
            }
            GridViewBelgeler.DataSource = belgeler;
            GridViewBelgeler.DataBind();
            if (paramdizi[4].Value.ToString() == "0")
            {
                LabelDurum.Text = "   Böyle bir öğrenci numarası bulunmamaktadır.
// you dont have student ID ın your ogrenci table// ) ";
            }
            else if (paramdizi[5].Value.ToString() == "0")
            {
                LabelDurum.Text = "u add this file (now) ";
            }
            else
            {
                LabelDurum.Text = " u added this file from now";
            }

i always see "u dont have this fi1e" on label durum.actually ı always see "1"? why ? how can ı correct this ?

ıf student havent transcript document ı want to add transcripr document i want see massage on label durum LabelDurum.Text = "u add this file (now) "
but student have transcript document ı dont want to add transcript document. iwant to see message on label durum
LabelDurum.Text = " u added this file from now";

[edit]Code block added, "Ignore HTML..." option disabled - OriginalGriff[/edit]

Posted 20-Jul-11 21:59pm

Ccsnow

Updated 20-Jul-11 22:03pm

OriginalGriff

v2

Add a Solution

Comments

Herman<T>.Instance 21-Jul-11 4:02am

whar are the values of paramdizi[4].Value and paramdizi[5].Value when you debug the code?

Ccsnow 21-Jul-11 4:09am

paramdizi4 value = 0 if student ıd havent in ogrenci table(student table)
paramdizi5 value =1 always

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Herman<T>.Instance · Answer 1 · 2011-07-20T22:19:00

in your store procedure all your code below return @Durum is not executed.

You should use the return statement at the end of the stored procedure'.
Since the return statement kan only hold 1 integer you cannot use return.
You should end with select @Durum, @Sonuc

You don't need output parameters in your call to the database.

Your stored procedure should be:

ALTER proc [dbo].[BelgeKayit]
(
@OgrenciNo int,
@BelgeId int,
@BelgeBaslik nvarchar(30),
@BelgeAciklama nvarchar(250)
)

as
declare @OgrenciKayitKontrol int
declare @BelgeKayitKontrol int
declare @Durum int
declare @Sonuc int

set @Durum=0
set @Sonuc=0

Set @OgrenciKayitKontrol=(select count(0) from Ogrenci where Ogrenci.OgrenciNo=@OgrenciNo)

if(@OgrenciKayitKontrol=1)
begin
    insert into OgrenciBelge(OgrenciNo,BelgeId,BelgeBaslik,BelgeAciklama)
    values(@OgrenciNo,@BelgeId,@BelgeBaslik,@BelgeAciklama)

    set @Durum=1
end

Set @BelgeKayitKontrol=(select count(0) from OgrenciBelge where OgrenciBelge.OgrenciNo=@OgrenciNo and OgrenciBelge.BelgeId=@BelgeId)

if(@BelgeKayitKontrol=1)
begin
    insert into OgrenciBelge(OgrenciNo,BelgeId,BelgeBaslik,BelgeAciklama)
    values(@OgrenciNo,@BelgeId,@BelgeBaslik,@BelgeAciklama)

    set @Sonuc=1
end
select @Durum, @Sonuc

Your c# should possibly be (see my questions in the code):

				int durum = 0;
				int sonuc = 0;
				SqlParameter[] paramdizi = new SqlParameter[4]
                  {   
                        new SqlParameter("@OgrenciNo",SqlDbType.Int),
                        new SqlParameter("@BelgeId",SqlDbType.Int) ,
                        new SqlParameter("@BelgeBaslik",SqlDbType.NVarChar),
                        new SqlParameter("@BelgeAciklama",SqlDbType.NVarChar)
                  };
                  paramdizi[0].Value = TxtOgrenciNo.Text;
                  paramdizi[1].Value = DrpBelgeTuru.SelectedValue;
                  paramdizi[2].Value = TxtDosyaBaslik.Text;
                  paramdizi[3].Value = TxtAciklama.Text;
                  using (SqlDataReader dr = baglan.ExecuteReader("BelgeKayit", CommandType.StoredProcedure, paramdizi)) // read durum and sonic from db result
				  {
					while (dr.Read())
					  {
						durum = dr.GetInt32(0);
						sonuc = dr.GetInt32(1);
					  }
				  }
				  
				  
				 // Other DB call
				 // what would be the values for 2 and 3? I suspect the TXT and DRP values
                  GridViewBelgeler.DataSource = new BelgeGoster(TxtOgrenciNo.Text, DrpBelgeTuru.SelectedValue, TxtDosyaBaslik.Text, TxtAciklama.Text);
                  GridViewBelgeler.DataBind();
				  
                  if (durum == 0)
                  {
                        LabelDurum.Text = "   Böyle bir ögrenci numarasi bulunmamaktadir. ";
                  }
                  else if (sonuc == 0)
                  {
                        LabelDurum.Text = "   0 ";
                  }
                  else
                  {
                        LabelDurum.Text = " 1 ";
                  }       

// i do not understand the belower part, what do you want do here?			  
SqlParameter[] paramdizi = new SqlParameter[6]
                  {   
                        new SqlParameter("@OgrenciNo",SqlDbType.Int),
                        new SqlParameter("@BelgeId",SqlDbType.Int) ,
                        new SqlParameter("@BelgeBaslik",SqlDbType.NVarChar),
                        new SqlParameter("@BelgeAciklama",SqlDbType.NVarChar) ,                     
                        new SqlParameter("@Durum",SqlDbType.Int),
                        new SqlParameter("@Sonuc",SqlDbType.Int)
                  };
                  paramdizi[0].Value = TxtOgrenciNo.Text;
                  paramdizi[1].Value = DrpBelgeTuru.SelectedValue;
                  paramdizi[2].Value = TxtDosyaBaslik.Text;
                  paramdizi[3].Value = TxtAciklama.Text;
                  paramdizi[4].Direction = ParameterDirection.Output;
                  paramdizi[5].Direction = ParameterDirection.Output;
                  SqlDataReader dr = baglan.ExecuteReader("BelgeKayit", CommandType.StoredProcedure, paramdizi);
                  List&lt;BelgeGoster&gt; belgeler = new List&lt;BelgeGoster&gt;();
                  while (dr.Read())
                  {
                        BelgeGoster belge = new BelgeGoster(dr.GetInt32(0), dr.GetInt32(1), dr.GetString(2), dr.GetString(3));
                        belgeler.Add(belge);
                  }
                  GridViewBelgeler.DataSource = belgeler;
                  GridViewBelgeler.DataBind();
                  if (paramdizi[4].Value.ToString() == "0")
                  {
                        LabelDurum.Text = "   Böyle bir ögrenci numarasi bulunmamaktadir.
</b>// you dont have student ID in your ogrenci table// </b>) ";
                  }
                  else if (paramdizi[5].Value.ToString() == "0")
                  {
                        LabelDurum.Text = "u add this file (now) ";
                  }
                  else
                  {
                        LabelDurum.Text = " u added this file from now";
                  }

shefeekcm · Answer 2 · 2011-07-20T23:05:00

Solution 2

Hi,

use a list to recieve values from the procedure.Use OO concepts in programming

regards,
shefeek

Posted 20-Jul-11 23:05pm

shefeekcm