Hey Guys!

I'm about done with a personal application im making for my small furniture factory. I'm having a little problem retrieving the total sum of my employee's loans discounts.It comes up perfectly using the statement:

"SELECT sum(TotalDiscount)as TotalDiscount FROM EmployeeDiscounts WHERE EmployeeID=val(" + Me.txtEmployeeID.Text + ")and PayRollID=val(" + Me.TxtPayrollID.Text + ")"

the problem is that it only works when the employee has discounts but if the database if empty it gives me this error:

"Conversion from type 'DBNull' to type 'String' is not valid."

I want to look up database and if it is empty then display "$0.00" in TotalDiscounts textbox.

I have tried every way possible but cant seem to make it work.PLEASE HELP!!

the simplest way would be to surround your retrieve code with

Try
			'retrieve code here
		Catch ex as InvalidCastException
			Debug.WriteLine("Value is DBNULL")
			'set value to $0.00
		End Try

I think that try..catch should be used, but without checking the error you might get false error messages (value is DBNULL).

I find it easier and more productive to leave error handling do what is supposed to do and change your SQL statement to:

"SELECT isnull(sum(TotalDiscount),0) as TotalDiscount FROM EmployeeDiscounts WHERE EmployeeID=val(" + Me.txtEmployeeID.Text + ")and PayRollID=val(" + Me.TxtPayrollID.Text + ")"

the isnull(,0 ) function around sum(TotalDiscount) will have the server check the result of sum() and if it's null it will replace it with the 0.
PS: This assumes that you are using MS SQL or SQL Express as db.

You should start a new account with your username = "I do not properly mark my threads as solved"

... or you should check first the return value of your query before assigning it.

This article has been dead for over six months. Start a new discussion instead.