when i executing this sql statement from web application it is providing some error

Question

3.00/5 (2 votes)

See more:

SQL

select sum(amountpaid) from tbl_payments where uid=29

it is getting null

how to check the condition
i am trying like this

if(cmd.executescaler()==null)

but it is generating the

invalid cast dbnull cannot be cast to other types.

C#

SqlCommand cmd = new SqlCommand("select sum(isnull(amountpaid,0)) from tbl_payments tbl_payments where uid=" + Label2.Text, con);
                amount = Convert.ToInt64(cmd.ExecuteScalar());
                if(amount!=0)
                 {

when the scalar sum funtion returing null.it is getting the error like cannot cast from dbnull to other types

Posted 15-Apr-12 19:18pm

syedkhaleel

Updated 15-Apr-12 19:48pm

sravani.v

v3

Add a Solution

Comments

Nilesh Patil Kolhapur 16-Apr-12 1:58am

hey check my updated solution.
u can use Both Way k hope ur problem is solved now k
happy coding

5 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)

Dipesh202 · Answer 1 · 2012-04-15T19:29:00

Solution 2

you can try like this

SQL

select isnull(sum(isnull(amountpaid,0)),0) from tbl_payments where uid=29

Posted 15-Apr-12 19:29pm

Dipesh202

Updated 15-Apr-12 20:10pm

v3

Comments

[no name] 16-Apr-12 1:36am

i am getting the same error

Vipin_Arora 16-Apr-12 1:41am

Hi dipesh,
I agree but... what if uid=29 does not exist?? then you have to handle it on front end only..

Dipesh202 16-Apr-12 2:10am

then you can use this
select isnull(sum(isnull(amountpaid,0)),0) from tbl_payments where uid=29

pravallika tumuluri · Answer 2 · 2012-04-15T19:25:00

Solution 1

Hi,
you can assign this cmd execute to any integer variable,and check that condition like this.

int result = Convert.ToInt32(cmd.ExecuteScalar());
if(result==0)
{

}

Posted 15-Apr-12 19:25pm

pravallika tumuluri

Updated 15-Apr-12 19:44pm

v2

Comments

[no name] 16-Apr-12 1:36am

i am getting the same error

Deepak_Sharma_ · Answer 3 · 2012-04-15T19:56:00

Hi,

You can following code:

C#

string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
SqlConnection con = new SqlConnection(ConString);
string CmdString = "select sum(isnull(amountpaid,0)) from tbl_payments where uid=@UID";
SqlCommand cmd = new SqlCommand(CmdString, con);
cmd.Parameters.AddWithValue("@UID", 5734999994);
con.Open();
double AmountPaid;
if (cmd.ExecuteScalar() != null)
{
    AmountPaid = Convert.ToDouble(cmd.ExecuteScalar());
}
con.Close();

Mohamed Mitwalli · Answer 4 · 2012-04-15T20:12:00

Hi ,
Try this make stored procedure

SQL

CREATE PROCEDURE usp_test
(@id int )
AS
BEGIN
SET NOCOUNT ON;
declare @count int ;
SELECT @count = (SELECT SUM(amountpaid) from  tbl_payments where  uid=@id)
IF(@count is null)
BEGIN
SET @count = -1;
END
 select @count;
END

In your code behind

C#

int sum=Convert.TOInt64(cmd.executescaler().tostring());
if(sum == -1)
{
//do something 
}
else
{
//something else
}

Best Regards
M.Mitwalli

Nilesh Patil Kolhapur · Answer 5 · 2012-04-15T19:35:00

Hi,

You can try this

SQL

select sum(isnull(amountpaid,0)) as [sum]  from tbl_payments where uid=29

and in aspx.cs file write following code

C#

----------------------First Solution-----------------------------

  if (Cmd.ExecuteScalar().GetType().ToString() != "System.DBNull")
 {
    // your code
 }

----------------------Second Solution----------------------------

DataTable dt=new DataTable();
SqlDataAdapter da=new SqlDataAdapter(cmd);
da.Fill(dt);
if(dt.Rows.Count > 0)
{
   int sum=Convert.TOInt64(dt.Rows[0][0].ToString());
   // Your Code
}

Check It Out

Best Luck
Happy Coding :)

when i executing this sql statement from web application it is providing some error

5 solutions

Solution 2

Solution 1

Solution 4

Solution 5

Solution 3

Add your solution here

Preview 0