Arrays

[sanderson]

I want to return an array from a function.

dt is a DataTable that is global

        Dim dr As DataRow

        For x As Integer = 0 To adoc.GetUpperBound(0)
            dr = ds.Tables(0).NewRow()
            ' dr.Item(0) = GetRoot(adoc(x))

            Dim abom() As String = GetVantageData(x)
            dr.Item(1) = abom(0)
            dr.Item(2) = abom(1)
            dr.Item(3) = abom(2)
            dr.Item(4) = abom(3)
            dr.Item(5) = abom(4)
            dr.Item(6) = abom(5)

            dt.Rows.Add(dr)

        Next

        dgvFBOM.DataSource = dt

GetVantageData(x) calls the item number where x is the index

    Private Function GetVantageData(ByVal index As Integer) As Array
        sql = "SELECT tblPart.PartNum, tblPart.PartDesc, tblPart.TypeCode, tblPart.UnitPrice, tblPartRev.RevisionNum, tblPartRev.Approved, tblPartBin.Onhandqty " & _
              "FROM SharePointProgress.dbo.tblPart " & _
              "LEFT OUTER JOIN SharePointProgress.dbo.tblPartRev ON tblPart.PartNum = tblPartRev.PartNum " & _
              "LEFT OUTER JOIN SharePointProgress.dbo.tblPartBin ON tblPart.PartNum = tblPartBin.PartNum " & _
              "WHERE tblPartRev.Approved = 1 " & _
              "AND tblPart.PartNum = '031770' "
        ' "AND tblPart.PartNum = '" & GetRoot(vb.Left(adoc(index), InStr(adoc(index), ".") + 7)) & "' "

        Dim cn_i As New SqlConnection(cn)
        cn_i.Open()
        Dim da As SqlDataAdapter = New SqlDataAdapter(sql, cn_i)
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        da.Fill(dt)
        cn_i.Close()

        MessageBox.Show(dt.Columns.Count)

        Dim abom2() As String
        ReDim abom2(dt.Columns.Count - 1)
        For x As Integer = 0 To dt.Columns.Count - 1
            abom2(x) = dt.Rows(0)(x)

        Next

        Return abom2

    End Function

I'm definitely missing something here. I am trying to return an array forn the sql statement, chich I can reference as abom() in the original source.

The ultuimate goal is to manually populate dt.

Thanks in advance...

[Shaggy Hiker]

Return As String(), rather than As Array.

[sanderson]

In doing this...

Private Function GetVantageData(ByVal index As Integer) As String

I get string conversion errors...

[Shaggy Hiker]

Not String, String()

Those trailing () indicate an array of strings rather than a single string.

[sanderson]

Do you see any reason why it is such a struggle to return the array inside this loop? Let's say x=30

        For x As Integer = 0 To adoc.GetUpperBound(0)
            dr = ds.Tables(0).NewRow()
            ' dr.Item(0) = GetRoot(adoc(x))

            Dim abom() As String = GetVantageData(x)
            dr.Item(1) = abom(0)
            dr.Item(2) = abom(1)
            dr.Item(3) = abom(2)
            dr.Item(4) = abom(3)
            dr.Item(5) = abom(4)
            dr.Item(6) = abom(5)

            dt.Rows.Add(dr)

        Next

[Shaggy Hiker]

It shouldn't be a struggle. Are you still getting errors? If so, what is the error message?

[sanderson]

No errors, it's just not looping...

If I rem out some stuff and do the first action, it works great!

           For x As Integer = 0 To adoc.GetUpperBound(0)
            dr = ds.Tables(0).NewRow()
            dr.Item(0) = GetRoot(adoc(x))

            'Dim abom() As String = GetVantageData(x)
            'dr.Item(1) = abom(0)
            'dr.Item(2) = abom(1)
            'dr.Item(3) = abom(2)
            'dr.Item(4) = abom(3)
            'dr.Item(5) = abom(4)
            'dr.Item(6) = abom(5)

            dt.Rows.Add(dr)

        Next

[sanderson]

The issue may be that it's not running the function right, for whatever reason, as the MessageBox.Sow is not being shown:

MessageBox.Show(dt.Columns.Count)

Listing

    Private Function GetVantageData(ByVal index As Integer) As String()
        sql = "SELECT tblPart.PartNum, tblPart.PartDesc, tblPart.TypeCode, tblPart.UnitPrice, tblPartRev.RevisionNum, tblPartRev.Approved, tblPartBin.Onhandqty " & _
              "FROM SharePointProgress.dbo.tblPart " & _
              "LEFT OUTER JOIN SharePointProgress.dbo.tblPartRev ON tblPart.PartNum = tblPartRev.PartNum " & _
              "LEFT OUTER JOIN SharePointProgress.dbo.tblPartBin ON tblPart.PartNum = tblPartBin.PartNum " & _
              "WHERE tblPartRev.Approved = 1 " & _
              "AND tblPart.PartNum = '031770' "
        ' "AND tblPart.PartNum = '" & GetRoot(vb.Left(adoc(index), InStr(adoc(index), ".") + 7)) & "' "

        Dim cn_i As New SqlConnection(cn)
        cn_i.Open()
        Dim da As SqlDataAdapter = New SqlDataAdapter(sql, cn_i)
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        da.Fill(dt)
        cn_i.Close()

        MessageBox.Show(dt.Columns.Count)

        Dim abom2() As String
        ReDim abom2(dt.Columns.Count - 1)
        For x As Integer = 0 To dt.Columns.Count - 1
            abom2(x) = dt.Rows(0)(x)

        Next

        Return abom2

    End Function

[Shaggy Hiker]

rem??? You're showing your age, there. How many people even know what that means.

I'd have to say that I'm surprised by that behavior. It doesn't look like that method should have any impact on the loop, yet it looks like it does. You could test that by turning abom into

Dim abom(6) As String

and not calling the method. That should work fine, though, so it has to be that method that is causing trouble.

I did note that the method is inefficient. You are creating a datatable for the sole purpose of filling an array. A datareader would be more appropriate and faster, in that case. The dataadapter and datatable are more memory and CPU intensive (though not by a whole lot), whereas a datareader is a relatively light construct. Still, it shouldn't make any difference.

I also note that you had GetRoot commented out before, and now have it uncommented. I don't know what that does, but it could be the problem....or not. What I would do would be to put a breakpoint on the For loop, then, when execution stops at the breakpoint, use F11 to step through that method to see what is happening.

[sanderson]

abom(6) produces an error of 'Explicit initialization is not permitted for arrays declared with explicit bounds.'

Could it be that I need to redim?

Yes, better efficiency is a good thing - once I get this working at all. Am I doing the datasets right?

Thanks!

[sanderson]

dt is declared at the top of the class.

    Dim dt As New DataTable("Table 1")

Perhaps it is not recognizing it in the GetVantageData function???

[sanderson]

Getting closer...

This produces A first chance exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll

Code...

    Private Sub LoadDataGridViewM()
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim dc0 As New DataColumn("PartNum")
        dt.Columns.Add(dc0)
        Dim dc1 As New DataColumn("PartDesc")
        dt.Columns.Add(dc1)
        Dim dc2 As New DataColumn("TypeCode")
        dt.Columns.Add(dc2)
        Dim dc3 As New DataColumn("UnitPrice")
        dt.Columns.Add(dc3)
        Dim dc4 As New DataColumn("RevisionNum")
        dt.Columns.Add(dc4)
        Dim dc5 As New DataColumn("Approved")
        dt.Columns.Add(dc5)
        Dim dc6 As New DataColumn("Onhandqty")
        dt.Columns.Add(dc6)
        Dim dc7 As New DataColumn("InABOM")
        dt.Columns.Add(dc7)

        Dim dr As DataRow

        For x As Integer = 0 To adoc.GetUpperBound(0)
            dr = ds.Tables(0).NewRow()
            dr.Item(0) = GetRoot(adoc(x))

            Dim abom() As String = GetVantageData(x)
            ReDim abom(7)
            dr.Item(1) = abom(0)
            dr.Item(2) = abom(1)
            dr.Item(3) = abom(2)
            dr.Item(4) = abom(3)
            dr.Item(5) = abom(4)
            dr.Item(6) = abom(5)
            dr.Item(7) = abom(6)

            dt.Rows.Add(dr)

        Next

        dgvFBOM.DataSource = dt

    End Sub

and...

    Private Function GetVantageData(ByVal index As Integer) As String()
        sql = "SELECT tblPart.PartNum, tblPart.PartDesc, tblPart.TypeCode, tblPart.UnitPrice, tblPartRev.RevisionNum, tblPartRev.Approved, tblPartBin.Onhandqty " & _
              "FROM SharePointProgress.dbo.tblPart " & _
              "LEFT OUTER JOIN SharePointProgress.dbo.tblPartRev ON tblPart.PartNum = tblPartRev.PartNum " & _
              "LEFT OUTER JOIN SharePointProgress.dbo.tblPartBin ON tblPart.PartNum = tblPartBin.PartNum " & _
              "WHERE tblPartRev.Approved = 1 " & _
              "AND tblPart.PartNum = '031770' "
        ' "AND tblPart.PartNum = '" & GetRoot(vb.Left(adoc(index), InStr(adoc(index), ".") + 7)) & "' "

        Debug.WriteLine("sql: " & sql)

        Dim cn_i As New SqlConnection(cn)
        cn_i.Open()
        Dim da As SqlDataAdapter = New SqlDataAdapter(sql, cn_i)
        Dim ds As New DataSet
        Dim dt2 As New DataTable("Table 2")
        ds.Tables.Add(dt2)
        da.Fill(dt2)
        cn_i.Close()

        Debug.WriteLine(dt2.Columns.Count)
        Dim abom2() As String
        ReDim abom2(dt2.Columns.Count - 1)
        For x As Integer = 0 To dt2.Columns.Count - 1
            If String.IsNullOrEmpty(dt.Rows(0)(x)) Then
                abom2(x) = "0"

            Else
                abom2(x) = dt2.Rows(0)(x)

            End If
            Debug.WriteLine("abom2(x): " & abom2(x))

        Next

        Return abom2

    End Function

[Shaggy Hiker]

abom(6) produces an error of 'Explicit initialization is not permitted for arrays declared with explicit bounds.'

Yeah, that was just a test, and a pretty useless one, too.

What line are you getting the new error on? That one usually means that something isn't sized right.

[sanderson]

I'm getting the error from the immediate window. No data ever makes it into the datagridview as a result.

There are 7 columns and the rows very. I'm certain it was not the rows, since it works for the first item.

Note, there are 8 columns on the datagridview, since column 1 comes from another source.

I'm not following where the program stops, and thus fails to return abom2

        MessageBox.Show(dt2.Columns.Count) ' This line works!
        Dim abom2() As String
        ReDim abom2(dt2.Columns.Count - 1)
        For x As Integer = 0 To dt2.Columns.Count - 1
            If String.IsNullOrEmpty(dt.Rows(0)(x)) Then
                abom2(x) = "0"

            Else
                abom2(x) = dt2.Rows(0)(x)

            End If
            MessageBox.Show("abom2(x): " & abom2(x))

        Next

        Return abom2

    End Function

Thanks again!

[Shaggy Hiker]

When an error occurs, it should take you to the line that threw the error. Is that not happening?

I would guess that your error is right here:

(dt.Rows(0)(x))

Shouldn't that be dt2?