I want to chose distinct maxno with where class from SQL Server table in C#

Question

If there is no result in the table it should return zero, but currently, it is returning -1. I build my logic that if there is zero do this and if it is not zero mean record exist to do this. I am new to C# and SQL Server. Kindly let me know if there is any mistake in my code.

int UserExist = 0;

using (SqlCommand cmd1 = new SqlCommand(@"select DISTINCT max(billno) AS bill from sale where ([date]='" + bildt.Text + "')  ", con)) {
    UserExist = (int)cmd1.ExecuteNonQuery();

    if (UserExist > 0) { 
        do this
    } else {   
        do some thing   
    }
}

Abhinav Galodha · Answer 1 · 2016-05-14T05:59:03.233

You need to use ExecuteScalar instead of ExecuteNonQuery.

ExecuteScalar executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

ExecuteNonQuery executes a Transact-SQL statement against the connection and returns the number of rows affected. For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

int UserExist = 0;
using (SqlCommand cmd1 = new SqlCommand(@"select DISTINCT max(billno) AS bill from sale where ([date]='" + bildt.Text + "')  ", con))
{
     UserExist = (int)cmd1.ExecuteScalar();
     if (UserExist > 0)
     { 
        do this
     }
     else
     {   
       do some thing   
     }

}

Also some additional points.

Rather then using the Parameters as String, try to use Parameterized Query. For more information read here.

score 0 · Answer 2 · edited May 14 '16 at 09:24

After search on internet i found solution.

int UserExist = 0;
                using (SqlCommand cmd1 = new SqlCommand(@"select COUNT(*) from sale where ([date]=@date)  ", con))
                {   cmd1.Parameters.AddWithValue("date", bildt.Text);
                    UserExist = (int)cmd1.ExecuteScalar();
                   if (UserExist > 0)
                    {using (SqlCommand cmd = new SqlCommand(@"select DISTINCT max(billno) AS bill from sale where ([date]='" + bildt.Text + "')  ", con))
                        { using (SqlDataReader dr = cmd.ExecuteReader())
                            {while (dr.Read())
                                {
                                    String x = dr["bill"].ToString();
                                    txttemp.Text = x;
                                    int a;
                                    int.TryParse(txttemp.Text, out a);
                                    billno.Text = (a + 1).ToString();
                                    txttemp.Text = "";
                                }
                                dr.Close(); dr.Dispose();
                            }
                        }
                        }
                    else {do some thing }

You should use a Parameter for the second `cmd` as well - especially if you need to cast a TextBox text value to a Date. — Filburt, May 14 '16 at 09:26

score 0 · Answer 3 · edited May 23 '17 at 11:52

0

To obtain Single value result from tsql query in C# you need to call ExecuteScalar.

You can read more HERE

edited May 23 '17 at 11:52

Community

1
1

answered May 14 '16 at 09:17

Vahid Farahmandian

6,081
7
42
62

score -2 · Answer 4 · answered May 14 '16 at 05:54

-2

I am not familiar with C#, but I believe that ExecuteNonQuery() is used when you want to do UPDATE, INSERT, or DELETE

I think you need to use ExecuteReader

I hope that helps

answered May 14 '16 at 05:54

Mocas

1,403
13
20

I want to chose distinct maxno with where class from SQL Server table in C#

4 Answers4