Reading Data to Public class from database to VB Webservice

[jrfairhall]

Hi,

I've been pulling my hair out over a problem and I'm sure its my lack of experience and probably easily answered. I've done a lot of searching but I'm probably not clued up enough to know what I'm actually looking for.

I'm creating an XML web service, which is reading data from a MySQL database. I just cant seem to get the value from the database into Class for the xml - keep getting direct cast errors (which I dont understand)or when I try a different way I'm now getting "Value of type 'String' cannot be converted to Soap.Topic"

Here's relevant bits of my code, please help!

<WebMethod()> _
    Public Function Categories() As Topics
        Dim CG As New Topics
Dim Temp As New ArrayList
        Dim count As Integer = 0
        SQLConnection.ConnectionString = ServerString
        Try
            If SQLConnection.State = ConnectionState.Closed Then
 
                strQuery = "SELECT categories.Name " & _
                "FROM categories"
                SQLCmd = New MySqlCommand(strQuery, SQLConnection)
                SQLConnection.Open()
                DR = SQLCmd.ExecuteReader
 Do While (DR.Read())
                    Temp.Add(DR.Item("name"))
                Loop
                count = 0
                For Each dato As String In Temp
                    CG.Category(count) = dato
                    count = count + 1
                Next
                CG.Result = ("Successfully read")
                DR.Close()
                SQLConnection.Close()
            Else
                SQLConnection.Close()
                Result = ("Connection is Closed.")
            End If
        Catch ex As Exception
            CG.Result = (ex.ToString)
        End Try
        Return CG
    End Function
Public Class Topics
    Public Category() As Topic
    Public Result As String
End Class
Public Class Topic
    Public Top As String
End Class
 

[markmcgookin]

John (Remember me? Saw this on fb and thought I would pop on and help)

So from what I gather, you are retrieving your string successfully from your database, but in sending in a SOAP packet you are running into an issue? Is that right?

[jrfairhall]

I do indeed mate. Thanks very much! The power of social networks eh?

As usual I wont have explained it very well. Please bear in mind I've not done any coding since being a student and even then it was only Pascal so I may have some fundamentals very wrong.

I've defined the XML object for the SOAP packet as Public class. One the XML elements that gets populated is called categories, of which there can be many so I set this xml class element as an array. I called a new instance of the xml in the main code (I think I may have done this wrong) and I can't get it to populate with data that I've read from a database and added to a listarray. All the messages seem to indicate its the wrong data types, or I've created the object wrong. Sorry can't really explain it better than that.

If you have time I've popped the project on my public dropbox at:
http://dl.dropbox.com/u/14603871/TestSoap.zip

You'll probably be able to see much better than I can explain, if you just take the MySQL read out and instead populate the listarray with the typical content the DB has such as Web, Mobile, etc.

Will find a way to buy you xmas beer if you can help :) I'm sure I'm just doing something really dumb but I've kind of jumped in at the deep end rather than trying to learn VB from the ground up.

[jrfairhall]

A colleagues come in and helped me. As I thought it was me being dumb. Simply changing the Public class topics.category to an ArrayList rather than an array and changed the read to:

CG.Category = New ArrayList
Do While (DR.Read())
'Temp.Add(DR.Item("name"))
CG.Category.Add(DR.Item("name"))
Loop

Thanks very much for trying to help Mark, much appreciated!

[markmcgookin]

lol np, just back from lunch and about to look at it for you.

Array in VB is probably static, and an ArrayList will grow in size, like a Vector.

Glad you got it sorted.