locked
Having problems with null value from database and jagged array RRS feed

  • Question

  • User-256799415 posted

    I need to scan database and insert its values into an array.v But i'm having some difficulties in handling null values and array.

    The database looks like this:

    0    1    2    3    4
    30    31    32        
    33    34    
    36    37    38    39    40
    38    39    47    48   

    I want to make array rr_item which gonna looks like this:

    arr_item(1)={0,1,2,3,4}

    arr_item(2)={30,31,32}

    arr_item(3)={33,34}

    arr_item(4)={36,37,38,39,40}

    arr_item(5)={38,39,47,48}

    Every code i've tried result in error everytime i tried to insert null value into any kind of c=variable, including array which i want to use. And i'm not quite sure with what i'm doin in jagged array i want to build.

    sql_list = "SELECT * FROM retail"
        oledbCnn = New OleDbConnection(connetionString)
        Try
            oledbCnn.Open()
            oledbAdapter = New OleDbDataAdapter(sql_list, oledbCnn)
            oledbAdapter.Fill(ds)
            Dim row_count As Integer = ds.Tables(0).Rows.Count
            Dim col_count As Integer = ds.Tables(0).Columns.Count
            Dim list(row_count, col_count) As String
            For i = 1 To (row_count - 1)
                For j = 0 To (col_count - 1)
                    'inserting value into array
                Next
            Next
            oledbAdapter.Dispose()
            oledbCnn.Close()
    	Catch ex As Exception
    		MsgBox("Can not open connection ! ")
    	End Try

    I hope you can help me doing this thing

    Sunday, February 12, 2012 9:39 AM

All replies

  • User-578657687 posted

    Hi dee_nu,

    Dim row_count As Integer = ds.Tables(0).Rows.Count Dim col_count As Integer = ds.Tables(0).Columns.Count Dim list(row_count, col_count) As String For i = 1 To (row_count - 1) For j = 0 To (col_count - 1) 'inserting value into array Next Next

    You want to save the data of table(retail) to an string array. But you mayn't realize this operation is unneccessary. You also use array[index][index] this format to get the data from a two dimensional array. Why don't you use table[index][index] to retrieve the data directly or like this:

    For Each row As DataRow In ds.Tables(0).Rows
    	'retreive data with index
    	For i As Integer = 0 To tb.Columns.Count - 1
                'row[i]
    	Next
    
              'retrieve data with column name
              'row["columnName"].ToString();
    
    Next
    

    If I misunderstand your requirement or you have special purpose to save the data of table to an array, please correct me.

    Wednesday, February 15, 2012 1:39 AM
  • User-256799415 posted

    I need to use those data to be processed again later. This step is only the pre-process of my task.  That's why i assign them into array first.
    Can you help me solving it, please?

    Wednesday, February 15, 2012 9:23 AM
  • User-578657687 posted

    need to use those data to be processed again later

    Those data saved by DataTable can also be processed again later.

    Since the array can be processed later, why DataTable can't do that? For instance, you can pass DataTable as the parameter to any method in which you can surely process it.

    Wednesday, February 15, 2012 10:13 PM
  • User-578657687 posted

    If possible, please explain the purpose using array, that'll help us provide a accurate idea for your issue.

    Thursday, February 16, 2012 1:08 AM
  • User-952121411 posted

    I have 2-3 suggestions all in 1 code example. 1st instead of using multidimensional arrays you use strongly typed Integer collections instead. They give you the benefit of LINQ to Objects when needed, PLINQ, TPL, and all sorts of other cool features and are easier to navigate. 2nd, I use collection initializers, but notice in 'MyValueSet5' how Ishow adding a value to the Integer collection manually and check to see if the value is NULL from the database, and adding a '0' in that case. You could also make the Integers a nullable type and add NULL to the collection if you needed to.

            Dim MyValueSet1 As New List(Of Integer) From {0, 1, 2, 3, 4}
    
            Dim MyValueSet2 As New List(Of Integer) From {30, 31, 32}
    
            Dim MyValueSet3 As New List(Of Integer) From {33, 34}
    
            Dim MyValueSet4 As New List(Of Integer) From {36, 37, 38, 39, 40}
    
            Dim MyValueSet5 As New List(Of Integer) From {38, 39, 47, 48}
            'Showing how to account for Null values coming back from a DataSet
            MyValueSet5.Add(If(IsDBNull(ds.Tables(0).Rows(0).Item("MyValue")), 0, IsDBNull(ds.Tables(0).Item("MyValue")))
    
            Dim MyValues As New List(Of List(Of Integer)) From {MyValueSet1, MyValueSet2, MyValueSet3, MyValueSet4, MyValueSet5}

    I think the following links will help you on this topic:

    .NET Object Collections Using Generics 101:
    http://allen-conway-dotnet.blogspot.com/2009/11/net-object-collections-using-generics.html

    How To: Populate a List of Objects from a DataSet Using LINQ:
    http://allen-conway-dotnet.blogspot.com/2010/04/how-to-populate-list-of-objects-from.html

    Nullable Value Types (Visual Basic):
    http://msdn.microsoft.com/en-us/library/ms235245.aspx

    Hope this helps! Smile

    Friday, March 9, 2012 4:44 PM