Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET
I am getting this error..
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
I have written this procedure..
///////////////////////////////////////////////////////////////////////////////
alter procedure myprocedure
(
@flag varchar(1),
@id  varchar(25),
@amt decimal(10,2)
)
as
begin
	if @flag='D'
	begin
		declare @balance decimal(10,2)
			set @balance=(select balance from mytransaction where emailid=@id);
			if (@balance IS NULL)
			begin
				set @balance=@amt;
			end
			else
			begin
			set @balance=@balance+@amt;
			end
			insert into mytransaction values(@id,@balance,GETDATE(),'DEPOSIT',@amt)
	end
	    else if @flag='W'
	       begin
			declare @chkbalance decimal(10,2)
			set @chkbalance=(select balance from mytransaction where emailid=@id);
				if (@amt>@chkbalance)
				begin
					SELECT 'Amount cannot be greater than balance.' AS 'Result'
				end
				  else 
					begin
					declare @wbalance decimal(10,2)
			set @wbalance=(select balance from mytransaction where emailid=@id);
			set @wbalance=@wbalance-@amt;
			insert into mytransaction values(@id,@wbalance,GETDATE(),'WITHDRAW',@amt)
			SELECT 'You have withdraw ammount of -->' + str(@amt) AS 'Result'
			end
	        end
end
//////////////////////////////////////////////////////////////////////////////
 
and have written follwing line of code in code file
///////////////////////////////////////////////////////////////////////////////
 
protected void btntransaction_Click(object sender, EventArgs e)
    {
        cn.Open();
        if (rdbwithdraw.Checked == true)
        {
            cmd = new SqlCommand("myprocedure", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@flag", SqlDbType.VarChar).Value = "W";
            cmd.Parameters.AddWithValue("@id", SqlDbType.VarChar).Value = hdnname.Value;
            cmd.Parameters.AddWithValue("@amt",SqlDbType.Decimal).Value = txtamt.Text;           
            da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
 
            if (dt != null && dt.Rows.Count > 0)
                Response.Write(dt.Rows[0]["Result"].ToString());
 
        }
        else
        {
            cmd = new SqlCommand("myprocedure", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@flag", SqlDbType.VarChar).Value = "D";
            cmd.Parameters.AddWithValue("@id", SqlDbType.VarChar).Value = hdnname.Value;
            cmd.Parameters.AddWithValue("@amt", SqlDbType.Decimal).Value = txtamt.Text;
            cmd.ExecuteNonQuery();
            Response.Write("You have deposited ammount of -->" + txtamt.Text);
            txtamt.Text = "";
 
        }
        cn.Close();
    }
Posted 22-Jan-13 2:28am
Edited 22-Jan-13 18:24pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

select balance from mytransaction where emailid=@id
This is possibly returning more than one result. Thus you are getting this error.
  Permalink  
Comments
Sachin Shinde11088 at 22-Jan-13 7:38am
   
Yess bro...
you are right..
but how to solve this error..
do you have any solution..
Abhinav S at 22-Jan-13 7:41am
   
Try to use additional where clauses in your query so that join returns a unique id.
Zafar Sultan at 22-Jan-13 7:44am
   
use select top1 balance from mytransaction where emailid=@id order by 'TheAppropriateColumn'
Abhinav S at 22-Jan-13 8:45am
   
Top 1 will work. Somehow though, I don't think it is going to provide the right solution in the context of the query.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

if u have any column such as dateTime,transactionid which will give u current last transaction....then with top 1 ..u have to use order by desc date,transaction id ...it will give u last transaction row
exa.
"select top 1 balance from myTransaction where emailid=@id order by dateTime1 desc "
  Permalink  
v2

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

  Print Answers RSS
0 OriginalGriff 295
1 DamithSL 295
2 Sergey Alexandrovich Kryukov 197
3 Peter Leow 155
4 Afzaal Ahmad Zeeshan 154
0 OriginalGriff 7,510
1 DamithSL 5,494
2 Sergey Alexandrovich Kryukov 4,954
3 Maciej Los 4,936
4 Kornfeld Eliyahu Peter 4,514


Advertise | Privacy | Mobile
Web03 | 2.8.141223.1 | Last Updated 22 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100