CODEWITHSUNDEEP
c#sqldatatable
user10698708
user10698708

Reputation:

Datatable rows.count == 0 gives 1 although there not found records in the table

I have a problem and I don't know why it's happening!

I am trying to find Max value in a column inside the database table, I use this code:

private void FrmCustomer_New_Load(object sender, EventArgs e)
{
    int NewID;           
    DataTable _Dt = CusVar.GetCustomersIDs();
    if (_Dt.Rows.Count == 0)
    {
        NewID = 1;
        this.txt1.Text = NewID.ToString();
        DataRow _Row = CusVar.GetCustomersIDs().Rows[0];
        MessageBox.Show(Convert.ToString(_Dt.Rows[0]["MaxID"]));
    }
}

the code is working but it gives 1 although there are no records in the table? I use C# and Access database ACCDB.

I use this function in Cls_Customers:

public DataTable GetCustomersIDs()
{
    DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
    DataTable Dt = new DataTable();
    Dt = DAL.DataSelect("Select Max(CustomerID) AS MaxID From TblCustomers", null);
    DAL.CloseConn();
    return Dt;
}

what is the problem, please?

Upvotes: 0

Views: 1678

Answers (3)

Eliseo
Eliseo

Reputation: 126

Try with this: SELECT MAX(T1.CustomerID) AS MaxID From TblCustomers T1

Upvotes: 0

Ashok
Ashok

Reputation: 753

The Sql query might be returning null and if there are no rows that match the criteria.

If the intention is to find the number of rows returned.Change the sql query to return the count instead of using aggregate function.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

This is your query:

Select Max(CustomerID) AS MaxID
From TblCustomers

It is an aggregation query. Aggregation queries with no group by always return one row -- regardless of whether any rows match.

The value returned in the single row is NULL for MaxId.

I am highly suspicious of what you want to do. If you want the maximum id -- and no rows if the table is empty -- then do:

select c.CustomerID
from TblCustomers c
order by c.CustomerID desc
fetch first 1 row only;

(This uses ANSI/ISO standard syntax so the exact logic might depend on your database.)

My suspicion is that you then want to use this id for an insert -- and that is a bad approach. Almost all databases support some sort of auto-incremented column (with syntax elements such as auto_increment, serial, or identity). That is the right way to assign a unique incrementing id to a column in a table.

Upvotes: 3

Related Questions