sqladapter and using multidimensional arrays

[bullet]

Hi Guys

I have been stuck on this problem for a while now and I wandered whether anyone could shed some light on a possible answer. My code is below:

this is my 2 - dimensional array declared globally within the apllication

Private Arrorg(5, 5) AsString

What I am trying to do in this function is be able to resize the array by using Redim method so the array will hold the total size of the record count from resultset of the stored procedure.

ReDim Arrorg(PBlAdapter("RowCount"), PBlAdapter("RowCount") + 1)
The error msg I get is:

Class 'System.Data.SqlClient.SqlDataAdapter' cannot be indexed because it has no default property.


PublicFunction retrieveData() AsBoolean


Dim PrvStr3 AsString

Dim PblDBCon AsNew SqlClient.SqlConnection()

Dim PBlDataRdr As SqlClient.SqlDataReader

Dim PBlCommand AsNew SqlClient.SqlCommand()

Dim PblAppIdParam AsNew SqlClient.SqlParameter("@Application_ID", "")

Dim PblStr AsString

Dim strFirstChar AsString

Dim PblFile AsInteger = FreeFile()

Dim PblChkFile AsInteger = FreeFile()

Dim PBlDataSet AsNew DataSet()

'initializing the DataTable object

Dim PBlTable As DataTable

'initializing the DataRow Object

Dim PBlRow As DataRow

'initializing the DataAdapter Object

Dim PBlAdapter As SqlClient.SqlDataAdapter


'setting the connection string'

Try
PblStr = ""

'passing connection string parameters to the connection object'
PblDBCon.ConnectionString = PblStr

'opening the database'
PblDBCon.Open()
PBlCommand.Connection = PblDBCon

'specifying the method of the process being used'
PBlCommand.CommandType = CommandType.StoredProcedure
PBlCommand.CommandText = "SP_GenerateApplication_Organisation_Links"

'PBlCommand.CommandText = "Select * from Organisation"

'assignment of the parameter value being obtained from stored proc'

'PblAppIdParam.Value = getapplication_id
PblAppIdParam.Value = "{FB6566EC-46DF-45F4-8AC4-D4FB33FD7C54}"
PBlCommand.Parameters.Add(PblAppIdParam)

'PBlDataRdr = PBlCommand.ExecuteReader(CommandBehavior.CloseConnection)
PBlAdapter =
New SqlClient.SqlDataAdapter("SP_GenerateApplication_Organisation_Links", PblDBCon)
PBlAdapter.SelectCommand = PBlCommand

PBlAdapter.Fill(PBlDataSet, "organisation")
PBlTable = PBlDataSet.Tables("organisation")
PBlCommand.Dispose()

'Dim Arrorg(5, PBlDataSet.Tables(0).Rows.Count) As String

Dim arrayCounter AsInteger

Dim MaxRow AsInteger = PBlTable.Rows.Count - 1

Dim RowCount AsInteger = PBlDataSet.Tables(0).Rows.Count


MsgBox("MaxRow")

'If PBlDataRdr.Read = True Then

If File.Exists(getFileLocation & "test2.htm") = TrueThen
Kill(getFileLocation & "test2.htm")

EndIf
arrayCounter = 0

ReDim Arrorg(PBlAdapter("RowCount"), PBlAdapter("RowCount") + 1)

'MsgBox(PBlTable.Rows.Count)

' If PBlDataRdr.Read = True Then

'FileOpen(PblFile, "C:\MyFirstCOMTemplate\COM\FirstCOM\organisationmodule\Html\test2.htm", OpenMode.Append, OpenAccess.ReadWrite, OpenShare.LockReadWrite)

' GenerateHTMLFile(PBlDataRdr("OrgEmail"))

'End If
PrvStr3 = ""

For arrayCounter = 0 To MaxRow
Arrorg(0, arrayCounter) = PBlTable.Rows(arrayCounter)("unique_name").ToString()
Arrorg(1, arrayCounter) = PBlTable.Rows(arrayCounter)("email_addr").ToString()
Arrorg(2, arrayCounter) = PBlTable.Rows(arrayCounter)("website_addr").ToString()
Arrorg(3, arrayCounter) = PBlTable.Rows(arrayCounter)("addressline2").ToString()

Next

For arrayCounter = 0 To MaxRow
PrvStr3 = vbCrLf & (Arrorg(0, arrayCounter) & ", " & (Arrorg(1, arrayCounter) & "<BR>") & " , " & (Arrorg(2, arrayCounter) & "address 2" & (Arrorg(3, arrayCounter) & "<BR>")))
GenerateHTMLFile(PrvStr3)

Next



'If PBlDataRdr("OrgEmail") Is DBNull.Value = False And PBlDataRdr("OrgWebsite") Is DBNull.Value = False Then

'If PBlDataRdr("OrgEmail").ToString().Trim() <> "" Then

'PrvStr3 = PblEmailWebsiteLinkArray(0, 0) = (PBlDataRdr("OrgEmail").ToString()) And PblEmailWebsiteLinkArray(0, 0) = (PBlDataRdr("OrgWebsite").ToString())

'arrayCounter = arrayCounter + 1

'vbCrLf & "<a href=" & Chr(34) & "mailto:" & PBlDataRdr("OrgEmail") & Chr(34) & ">" & PBlDataRdr("OrgEmail") & "</a><BR>" & vbCrLf

'PrvStr3 = vbCrLf & "<a href=" & Chr(34) & "C:\MyFirstCOMTemplate\COM\FirstCOM\organisationmodule\Html\org.htm" & Chr(34) & ">" & PBlDataRdr("OrgName").ToString().Trim() & "</a><BR>"

'vbCrLf & "<A href=" & Chr(34) & PBlDataRdr("OrgName").ToString.Trim() & Chr(34) & ">" & PBlDataRdr("OrgName").ToString.Trim() & "</A>" & vbCrLf & _

'' Insert code for creating the organisation pages themselves!

'End If

Catch ex As Exception
MsgBox("Error in retrieving data from the DB")
MsgBox(ex.ToString)

EndTry

EndFunction

I read some articles stating this is not possible as this feature used to belong to visual basic 6 and is not in Vb.Net . It does seem to work if I use Datareader instead but I end up with a different error message. Any ideas would be much appreciated thanks in advance.

 

[jhomminga]

You mean like ADODB.Recordset does oRs.GetRows?

Looks like maybe you are trying to get the datatable into a multidimensional array? Then redim to the row size and column count and loop the datatable with a counter and update the array

Change ReDim Arrorg(PBlAdapter("RowCount"), PBlAdapter("RowCount") + 1) 
To.... ReDim Arrorg(PBlDataSet.Tables("organisation").Rows.Count-1,PBlDataSet.Tables("organisation").Columns.Count-1)

And fill the array like this...

For i as integer = 0 to PBlDataSet.Tables("organisation").Rows.Count-1
......For j as integer = 0 to PBlDataSet.Tables("organisation").Columns.Count-1
.........Arrorg(i,j) = PBlDataSet.Tables("organisation").Rows(i).Item(j)
......Next
Next

Note:
You couldn't want the the dataadapter itself. It has Select, Update, Insert, and Delete commands each with parameters, connections, etc.