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.
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:
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:
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);
}
}