[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

webform dataset

I have a webform dataset called MyInventory...there are multiple columns..The two columns im having problems with are intQtyonHand and strCode

intQtyonHand has null values sprinkled thru the dataset precluding the following :

xResult = MyInventory.Tables(0).Compute("sum(intQtyonHand)", "strCode = 'whatever'")

I get : " Cast from type 'DBNull' to type 'Integer' is not valid." when going to the aspx page

I was thinking of the following but cant figure it out

 Run a update against the dataset and replace all null values with 0 for the field intQtyonHand ..


Another question... The same line of code work with a winform without any problem but not in a web form....anyone know why?


thanks
Steve
0
stephenz43
Asked:
stephenz43
  • 2
  • 2
  • 2
1 Solution
 
TorrwinCommented:
You need to check for the null value before you try to use it like this:

Dim mySum as Integer = 0
Dim Index as Integer = 0
Dim RowCount as Integer = Dataset1.tables(0).rows.count

While RowCount > Index
   If Not (MyInventory.Tables(0).Rows(Index).Item("intQtyonHand") Is DBNull.Value) Then
            mySum += MyInventory.tables(0).Rows(Index).Item("intQtyonHand")
   End If

   Index += 1
End While

xResult = mySum
0
 
stephenz43Author Commented:
Hi

Im getting the idea , but what of the filter "strCode = 'whatever'".... where is it placed in the statement?
0
 
TorrwinCommented:
Just make sure that column is also not null as well, and then test for whatever you are looking for.  One example could be:

While RowCount > Index
   If Not (MyInventory.Tables(0).Rows(Index).Item("intQtyonHand") Is DBNull.Value) Then

            If Not (MyInventory.Tables(0).Rows(Index).Item("strCode") Is DBNull.Value) Then

                    If MyInventory.Tables(0).Rows(Index).Item("strCode") = "Whatever" Then

                          mySum += MyInventory.tables(0).Rows(Index).Item("intQtyonHand")

                    End If
            End If
   End If

   Index += 1
End While
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Bob LearnedCommented:
Another way to do this is to run an SQL statement:

Dim queryString As String = "SELECT SUM(intQtyonHand) WHERE strCode = 'whatever'"

Dim cmdSelect As New OleDbCommand(queryString, connection)

Dim resultSum As Single = cmdSelect.ExecuteScalar()

Bob



0
 
stephenz43Author Commented:

this can be applied using the dataset  MyInventory?
0
 
Bob LearnedCommented:
No, it will retrieve that information from the table, and not the dataset.

Bob
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now