Building on Marcus' solution:
Shouldn't the Stored Procedure alias the result of the
MAX(invoice_no)
like:
ALTER PROCEDURE USP_Get_Invoice_No
AS
BEGIN
SELECT MAX(invoice_no) as "Max_invoice_no" from tbl_cleaning_purchase_categories
END
and then use
"Max_invoice_no"
as the column/key:
drInvoiceNo = obj.GET_INVOICE_NO();
while (drInvoiceNo.Read())
{
var max_invoice_no = drInvoiceNo["Max_invoice_no"].ToString();
if ( string.IsNullOrEmpty(max_invoice_no) )
{
max_invoice_no = txt_InvoiceNo.Text;
}
if ( !Int32.TryParse( max_invoice_no, out InvoiceNO ) )
{
InvoiceNO = ??;
}
txt_InvoiceNo.Text = (InvoiceNO + 1).ToString();
}
Another possibility, if you cannot change the stored procedure, would be to just get the 0-th index in
drInvoiceNo
since the Max is the only value returned from the stored procedure.