Click here to Skip to main content
12,952,792 members (35,323 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
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 1:28am
Updated 22-Jan-13 17: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 22-Jan-13 7:38am
   
Yess bro...
you are right..
but how to solve this error..
do you have any solution..
Abhinav S 22-Jan-13 7:41am
   
Try to use additional where clauses in your query so that join returns a unique id.
Zafar Sultan 22-Jan-13 7:44am
   
use select top1 balance from mytransaction where emailid=@id order by 'TheAppropriateColumn'
Abhinav S 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
Top Experts
Last 24hrsThis month
OriginalGriff 6,389
CHill60 3,490
Maciej Los 3,103
Jochen Arndt 1,975
ppolymorphe 1,920


Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 22 Jan 2013
Copyright © CodeProject, 1999-2017
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