Click here to Skip to main content
15,882,063 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
How can i make this query by C# and Access database rather than Sql server
I mean, How I can replace null value by 1 with Max function
C#
public Int64 GetAccCode(Int64 Result,FRMAccounts xx)
{
    DataTable dt = new DataTable();             
    dt.Clear();             
    SqlDataAdapter da = new SqlDataAdapter();            
    da = new SqlDataAdapter("select isnull(MAX( AccCode),1) from Accounts where AccParent = " + Result + " ", ConClass.con);             
    da.Fill(dt);              
    Int64 MyNewID;             
    MyNewID = Convert.ToInt64(dt.Rows[0][0]);

    if (xx.AccType.SelectedIndex == 0)
    {
        if (MyNewID ==1)
        {
            MyNewID = int.Parse(Result.ToString() + "0".ToString() + 1);
        }
        else
        {
            MyNewID = (Convert.ToInt64(dt.Rows[0][0]) + 1);
        }
    }
    else if (xx.AccType.SelectedIndex == 1)
    {
        if (MyNewID ==1)
        {
            MyNewID = int.Parse(Result.ToString() + "0".ToString() + "0".ToString() + "0".ToString() + 1);
        }
        else
        {
            MyNewID = (Convert.ToInt64(dt.Rows[0][0]) + 1);
        }
    }

    return MyNewID;
    da.Dispose();            
    dt.Dispose();
}


What I have tried:

I try to replace null value by 1
in max function.
Posted
Updated 4-Jan-24 10:41am
v3
Comments
PIEBALDconsult 4-Jan-24 18:20pm    
0) Doesn't Access use OleDB?
1) I recommend against using a data adapter, especially for this case.
2) "0".ToString() -- is just freaking stupid.
3) You don't need to Clear a new DataTable.

There are a few issues and potential improvements in your code is require
1. SQL Injection Vulnerability:
The code directly concatenates the Result variable into the SQL query string, which can lead to SQL injection vulnerabilities. You should use parameterized queries to prevent this.
C#
da = new SqlDataAdapter("SELECT ISNULL(MAX(AccCode),1) FROM Accounts WHERE AccParent = @Result", ConClass.con);
da.SelectCommand.Parameters.AddWithValue("@Result", Result);
Unused Code:
The da.Dispose() and dt.Dispose() lines will never be executed because they are after the return statement. It's better to use the using statement to ensure proper disposal of resources. Here's an updated version of your code:
C#
public Int64 GetAccCode(Int64 Result, FRMAccounts xx)
{
    DataTable dt = new DataTable();
    using (SqlDataAdapter da = new SqlDataAdapter("SELECT ISNULL(MAX(AccCode),1) FROM Accounts WHERE AccParent = @Result", ConClass.con))
    {
        da.SelectCommand.Parameters.AddWithValue("@Result", Result);
        da.Fill(dt);
    }
    Int64 MyNewID = Convert.ToInt64(dt.Rows[0][0]);
    if (MyNewID == 1)
    {
        MyNewID = Int64.Parse($"{Result}{"0".PadLeft(xx.AccType.SelectedIndex * 2, '0')}1");
    }
    else
    {
        MyNewID = MyNewID + 1;
    }
    return MyNewID;
}
In Access databases, the NZ function is typically used to replace null values with another specified value. Additionally, Access doesn't support named parameters like SQL Server, so you'll need to use ? as a placeholder for parameters in the query. Here's how you can modify your code for an Access database:
C#
public Int64 GetAccCode(Int64 Result, FRMAccounts xx)
{
    DataTable dt = new DataTable();
    using (OleDbDataAdapter da = new OleDbDataAdapter("SELECT NZ(MAX(AccCode), 1) FROM Accounts WHERE AccParent = ?", ConClass.con))
    {
        da.SelectCommand.Parameters.AddWithValue("@Result", Result);
        da.Fill(dt);
    }
    // Rest of your code
}
 
Share this answer
 
v2
Comments
Maciej Los 7-Jan-24 3:02am    
5ed!
saddam1 8-Jan-24 11:53am    
Iam sorry but it's not work.
I have visual studio 2012.
saddam1 8-Jan-24 11:54am    
my code above is working correctlly in C# With Sql server.
According to M Imran Ansari answer, i'd suggest to use OleDbCommand.ExecuteScalar method[^] instead of OleDbDataAdapter.

Why? Because...
Quote:
Use the ExecuteScalar method to retrieve a single value, for example, an aggregate value, from a data source. This requires less code than using the ExecuteReader method, and then performing the operations that are required to generate the single value using the data returned by an OleDbDataReader.


So, changed method might look like this:
C#
public Int64 GetAccCode(Int64 Result, FRMAccounts xx)
{
    using (OleDbCommand odc = new OleDbCommand("SELECT MAX(AccCode) FROM Accounts WHERE AccParent = ?", ConClass.con))
    {
        odc.Parameters.Add(new OleDbParameter(){OleDbType = OleDbType.Integer, Value=Result);
        var vVal = odc.ExecuteScalar();
        Int64 rVal = DBNull.Value.Equals(vVal) ? 1 : (Int64)vVal;
        return rVal;
    }
}



Note:
I forgot that Nz function is not accessible in OleDb. I'm using DBNull.Value to check if returned value is not null.

See:
DBNull Class (System) | Microsoft Learn[^]
 
Share this answer
 
v2
I assume, if the resultis null (the output table is blank), then Isnull(max(AccCode),1) won't work here. After you get the result, check the condition, if result (da) is null then set to 1.
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900