System.InvalidCastException: Operator is not valid for type 'Long' and type 'DBNul

[vuhua]

Hello friend,

I have a problem with my coding that drive me nut for the past three days. Hope someone can help me.

The code is first, I update the current existing record from a database. I use the SQL UPDATE query to update the record after searching for it from an access database. After the update has been complete, I use the SQL SELECT query to search for the column "Amount" in access and fill it up in a database called datDataSet. I then use a FOR loop and loop through each row and column and sum the amount up for each column/row. After I got the new sum of the amount column, I will then display the new sum. But, when I run the code, I receive the error "System.InvalidCastException: Operator is not valid for type 'Long' and type 'DBNul" when it come to the part that executing the code and sum up the "amount" total. I doesn't know why, I use the Debug method and execute each line of code and every line seem to be functioning correctly, except the this line of code:
mDebAmt += datDataSet.Tables(0).Rows(mI)(0). Any help would be appreciated.

[/COLOR]
Try
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'1 -> Create SQL UPDATE Query to data from the database
[/COLOR][/SIZE][SIZE=2]OleDataAdapter.UpdateCommand.CommandText = _
"UPDATE Expense SET Amount = " & txtAmount.Text & ", " & _
"Dates = '" & Convert.ToDateTime(txtDate.Text) & "', " & _
"Months = " & cboMonth.SelectedIndex & ", " & _
"Years = " & cboYear.SelectedIndex & ", " & _
"Debit = '" & radDebit.Checked & "', " & _
"Credit = '" & radCredit.Checked & "', " & _
"ExpensesType = " & cboExpType.SelectedIndex & ", " & _
"Summary = '" & txtSummary.Text & "';"
[/SIZE][SIZE=2][COLOR=#008000]'2 -> Execute the SQL UPDATE query
[/COLOR][/SIZE][SIZE=2]OleDataAdapter.UpdateCommand.ExecuteNonQuery()
[/SIZE][SIZE=2][COLOR=#008000]'<-------------------------------------------------------------------->
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'3 -> Clear the CreDataSet from the previous operation
[/COLOR][/SIZE][SIZE=2]datDataSet.Clear()
[/SIZE][SIZE=2][COLOR=#008000]'4 -> Use SQL SELECT query to gather the update expense amount and sum
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'---> it up so the a new debit, credit, total, and balance amount can be
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'---> updated.
[/COLOR][/SIZE][SIZE=2]OleDataAdapter.SelectCommand.CommandText = _
"SELECT Amount FROM Expense WHERE " & _
"Debit = '" & [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2] & "' "
[/SIZE][SIZE=2][COLOR=#008000]'5 -> Fill the datDataSet with the data from the SELECT query
[/COLOR][/SIZE][SIZE=2]OleDataAdapter.Fill(datDataSet)
[/SIZE][SIZE=2][COLOR=#008000]'6 -> Store the total of record found from the datDataSet into the
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'---> variable mCreMax
[/COLOR][/SIZE][SIZE=2]mMax = datDataSet.Tables(0).Rows.Count
[/SIZE][SIZE=2][COLOR=#008000]'7 -> If the DataSet is not empty
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] datDataSet.Tables(0).Rows.Count <> 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'8 -> Use FOR loop to loop through every single data and 
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'---> sum up the Debit amount. Store the sum in variable
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'---> mDebAmt
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] mI = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] mMax - 1
[/SIZE][SIZE=2][COLOR=#008000]'9 -> Sum up the debit amount and store it in the variable
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'---> mCreAmt
[/COLOR][/SIZE][SIZE=2]mDebAmt += datDataSet.Tables(0).Rows(mI)(0)
[/SIZE][SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE][SIZE=2] mI
[/SIZE][SIZE=2][COLOR=#008000]'10 -> Store the Debit amount in textbox txtDebit.Text
[/COLOR][/SIZE][SIZE=2]txtDebit.Text = mDebAmt
[/SIZE][SIZE=2][COLOR=#008000]'11 -> Set the mDebAmt back to the default value of 0
[/COLOR][/SIZE][SIZE=2]mDebAmt = 0
[/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'12 -> Set the textbox txtDebit.Text = 0 if there's no data from the
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'----> database
[/COLOR][/SIZE][SIZE=2]txtDebit.Text = 0
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'CreDataSetSet.Tables(0).Rows.Count <> 0
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] OleException [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Data.OleDb.OleDbException
[/SIZE][SIZE=2][COLOR=#008000]'6 -> Print the Error report if there's a problem updating the record.
[/COLOR][/SIZE][SIZE=2]Console.WriteLine(OleException.StackTrace)
[/SIZE][SIZE=2][COLOR=#008000]'7 -> Display the Error message
[/COLOR][/SIZE][SIZE=2]MessageBox.Show(OleException.ToString)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][COLOR=black]

 

[Bob Langlade]

Have you tried to use the IsDBNull function to check if the debit is not Null at least once, before adding it to mDebAmt ?

Public Function IsDBNull(ByVal Expression As Object) As Boolean

 

[vuhua]

Thanks,

I will try that when I got home from work today. Again, thanks for your help. Any other comment or advice would be appreciated.

 

[TechGnome]

You need to first check the field to make sure it isn't NULL. If it is, skip it, if it's not then add it in.

-tg

 

[cjard]

Actually, i have noted when i'm using my table adapters that parameters like Doubles, that allow nulls actually go in as System.Nullable(Of Double) or System.Nullable(Of Long)

I'd have to research how to do this in your case.. but til then i have some more advice:

OleDataAdapter.UpdateCommand.CommandText = _
"UPDATE Expense SET Amount = " & txtAmount.Text & ", " & _
"Dates = '" & Convert.ToDateTime(txtDate.Text) & "', " & _
"Months = " & cboMonth.SelectedIndex & ", " & _
"Years = " & cboYear.SelectedIndex & ", " & _
"Debit = '" & radDebit.Checked & "', " & _
"Credit = '" & radCredit.Checked & "', " & _
"ExpensesType = " & cboExpType.SelectedIndex & ", " & _
"Summary = '" & txtSummary.Text & "';"

1: use CODE tags on this forum.. for more info click the words VB CODE in the little box at the bottom left of this page

2: Nooooooo.. dont make sqls likle that.. theyre ugly in code and very unsafe. if a user types a ' into the txtsummary it will break your db instantly!
i.e. if the user writes "this summary info was taken at ten o'clock" then -> bang!
Never ever use string concatenation to make SQLs. Do this:

OleDataAdapter.UpdateCommand.CommandText = _
"UPDATE Expense SET Amount = :newAmount, Dates = :newDates, Months = :newMonths, Years = :newYears, Debit = :newDebit, Credit = :newCredit, ExpensesType = :newExpenses, Summary = :newSummary;"

see how much nicer it is?

WARNING! THIS SQL WILL UPDATE EVERY RECORD IN THE expense TABLE!! IT HAS NO WHERE CONDITION!

okay, now we have to tell the computer what the parameters mean:

OleDataAdapter.[SIZE=2]UpdateCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"newAmount"[/COLOR][/SIZE][SIZE=2], OleDb.OleDbType.Long).Value = CLng(txtAmount.Text)[/SIZE]
[SIZE=2][COLOR=#800000]OleDataAdapter.[SIZE=2]UpdateCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"newDates"[/COLOR][/SIZE][SIZE=2], OleDb.OleDbType.Date).Value = DateTime.ParseExact(txtDate.Text, "date format goes here!")
OleDataAdapter.[SIZE=2]UpdateCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"newMonths"[/COLOR][/SIZE][SIZE=2], OleDb.OleDbType.Long).Value = cboMonth.SelectedIndex[/SIZE]
[SIZE=2]

[/SIZE]

and so on. why do this? to improve security and performance. lets break downa parameter creation:
OleDataAdapter.UpdateCommand.Parameters.Add("newAmount", OleDb.OleDbType.Long).Value = CLng(txtAmount.Text)

in red is the parameter name. Our statement SQL contains :newAmount so our parameter name removes the colon : to leave newAmount

next we tell the computer what the data type is we are passing in. this prevents the sql server messing up when converting. the data type here should really be the SAME type of the column in the database. if Dates is a DateTime column ,then use a datetime data type. its an enum, so its easy

because the ADD method returns the parameter it just added, we can say Add(...).Value = (something) and this will set the value of the parameter, so i do this too

Do this for all parameters to your sql. whats the advantages?

The database now knows the sql wont change so it doesnt have to re-parse it (slower) when it runs it. if youre executing a statement over and over, you really should use parameterized queries
Also, ther eis no way we can break it! before if we entered something like an apostrophe in the summary text box, it would break the sql string.. now no matter what you enter, it will be treated as a string

What's more, once your parameters is initialized, you just change the value so do this setup in some class variable somewher eor something.. and hold it...

we can say:

UpdateCommand.Parameters("newAmount").Value = 1234
UpdateCommand.ExecuteNonQuery();
UpdateCommand.Parameters("newAmount").Value = 2345
UpdateCommand.ExecuteNonQuery();

and the query runs twice. each time the only param that changed was the value of newAmount.. can you see why we use this for repeated invokations of the query?

your code looks much nicer too! and because your statement is prepared and set up properly on the client side you will be more able to trap errors...

if you set up a parameter as number and then try to stuff text in there, it will complain right away and you can debug it easier..


Incidentally, in your case, i think you might find that datDataSet.Tables(0).Rows(mI)(0) IS NULL! You cant add a null to a number!
[/SIZE][/COLOR][/SIZE]