Having Trouble with DBNULL

[acepeda]

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!!

[GeekByChoiCe]

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

[adam_k]

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"

[jireh]

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