I want to retrieve last digits from the invoice number. I want to get value why I am getting this error. which is the keyword. I have tried [] also but it's giiving error.
public string generateInvoiceNo(string PRODUCTNAME) {
try {
con.Open();
string number;
OleDbDataAdapter DA1 = new OleDbDataAdapter
("SELECT Max(INVOICE_NO.SUBSTRING(9,5)+1) AS MAXINVOICE FROM PRODUCT_DETAILS", con);
DataTable DT1 = new DataTable();
DA1.Fill(DT1);
if (DT1.Rows.Count > 0)
{ number = DT1.Rows[0]["MAXINVOICE"].ToString();
}
else
{ number = "00001"; }
OleDbCommand cmd2 = new OleDbCommand
("SELECT [D_NO].SUBSTRING(0,2) AS [D_NO] FROM [PRODUCT_MASTER] WHERE [PRODUCT_NAME]='" + PRODUCTNAME + "'", con);
OleDbDataAdapter DA2 = new OleDbDataAdapter(cmd2);
DataTable DT2 = new DataTable();
DA2.Fill(DT2);
string dno = DT2.Rows[0]["D_NO"].ToString() + System.DateTime.Today.ToString(); return dno; }
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return null;
}
finally
{
con.Close();
}
}
Loading
Posted Jun 21, 2012, 12:33 AM
SELECT Max(INVOICE_NO.SUBSTRING(9,5)+1) AS MAXINVOICE FROM PRODUCT_DETAILS
instead of this,
First get the max value
Do the substring
convert the sub-stringed value to int
Add 1 with the converted integer value.
SELECT convert(int,Max(INVOICE_NO).SUBSTRING(9,5))+1 AS MAXINVOICE FROM PRODUCT_DETAILS
I'm not tested the above query,but try to apply the logic.
Hope this helps you.
In which line, you're getting this error ?