Calculating Tax (VAT) in ASP.NET db's

Dear DanBAtkinson,

There are many ways to calculate vat. The approach you are using is not a very good approach and probably produce an error. Its better if you calculate vat in the db, like in stored procedure and total it there too.
A second way is calculate vat in code behind file rather than rendring it in HTML through item template.

Regards,
Me

Could you please explain how to do it in the db or calculate it behind the file then please?

If you want to caluclate it in the DB, create another column and as the formula write: [ItemPrice] * 17.5%

Alternatively, use inline SQL:

SELECT [ItemPrice] * 17.5 AS ItemPriceIncVat FROM YourTable

That should be  

SELECT CAST([ItemPrice] * 17.5 AS decimal) AS ItemPriceIncVat FROM YourTable...

Thankyou.

I can't add another column in the table as that would result in a self-referential calculation. Calculating something inside the table.

Going with the SQL, I currently have this then...


 sub Page_Load
  dim dbconn,sql,dbcomm,dbread
  dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("admin.mdb"))
  dbconn.Open()
  sql="SELECT * FROM tblProducts"
  SELECT CAST([ItemPrice] * 17.5 AS decimal) AS ItemPriceIncVat FROM tblProducts END SELECT
  dbcomm=New OleDbCommand(sql,dbconn)
  dbread=dbcomm.ExecuteReader()
  admin.DataSource=dbread
  admin.DataBind()
  dbread.Close()
  dbconn.Close()
  end sub

The error (with or without END SELECT) is:

Select Case' must end with a matching 'End Select'.

What am I doing wrong?

And do I have to create a new value ItemPriceIncVat in the table (which would be blank)?
Once the calculation works, how do I call it from the <ItemTemplate>?

Thanks.

Now this error appears:

Exception Details: System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

It asks me to add <%@ Page Language="C#" Debug="true" %> to the top of the page.

If I do that then it has a problem with the subs which are clearly VB

I think you have something else going on there!

Paste the entire error message

Error:


--------------------------------------------------------------------------------

Server Error in '/' Application.
--------------------------------------------------------------------------------

IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

Source Error:

The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:

1. Add a "Debug=true" directive at the top of the file that generated the error. Example:

  <%@ Page Language="C#" Debug="true" %>

or:

2) Add the following section to the configuration file of your application:

<configuration>
   <system.web>
       <compilation debug="true"/>
   </system.web>
</configuration>

Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.

Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario.  

Stack Trace:


[OleDbException (0x80004005): IErrorInfo.GetDescription failed with E_FAIL(0x80004005).]
   Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack) +900
   ASP.index_aspx.Page_Load() +180
   System.Web.Util.ArglessEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +10
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +750


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032

Have you tried adding   <%@ Page Language="VB" Debug="true" %>   to the top of your page?

and have you tried adding the above to your web.config file?

If I replace the C# with VB, then the error is:

System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

A problem with the line: dbread=dbcomm.ExecuteReader()

You actually need to declare them as their objects, i don't really do VB so i'm not much help....however i'll give it a go:


Dim dbconn As OleDbConnection =  New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("admin.mdb"))
dbconn.Open()
 
Dim sql As String =  SELECT CAST((ItemPrice) * 17.5 AS Decimal) AS ItemPriceIncVat,* As String
 
Dim Cmd As OleDbCommand =  New OleDbCommand(sql,Cnn)
Dim reader As OleDbReader
reader = Cmd.ExecuteReader()
admin.DataSource = reader
admin.DataBind()

Dim Cmd As OleDbCommand =  New OleDbCommand(sql,Cnn)

should be

Dim Cmd As OleDbCommand =  New OleDbCommand(sql,dbconn)

This has not worked I'm afraid. I've tried a hell of a lot of different combinations.

An expression was expected from:

dim sql As String =  SELECT CAST((ItemPrice) * 17.5 AS Decimal) AS ItemPriceIncVat,* As String.

I will be back tomorrow at 09:00 GMT to check the progress of this problem and try to offer assistance with my problem.

Thanks for now.

sorry, try:

dim sql As String =  "SELECT CAST((ItemPrice) * 17.5 AS Decimal) AS ItemPriceIncVat,* FROM tblProducts"

Thankyou for that. I think that problem was solved (or at least the compiler has left it alone for now!).

But now the error is:

BC30002: Type 'OleDbReader' is not defined

with this line:

dim reader As OleDbReader

Changed that to OleDbDataReader. Seems to work.

Great....

So everything is A OK then?

No. Unfortunately. I think what I should do is close this, award you the points and open up a different question relating to the actual database working as I think that this is kind of going beyond the original question.

Points awarded. Thankyou for your help Type25.

https://www.experts-exchange.com/questions/21345132/Problems-with-database-in-ASP-NET.html

Thanks for the points