locked
XML Data read from my database and save into a varible in my Application (asp.net)/VB.Net code behind RRS feed

  • Question

  • User-1871264887 posted

    Hello everyone, hopefully I'm in the right spot, but I've been racking my brain on this issue... So far I am able to save to my SQL Database my XML string into a XML type on the database. That part works, but the issue I am having is trying to then load that xml data back into a varable in my application to then be used to load the xml data into my RiaOLAPGrid...

    Here is the code I am working with, and if you have any thoughts, please let me know (I have tried several things and thus some of the code is commented out.) - The idea is that when the user clicks on the child node in my tree view this event fires and it loads the data.

    Code (VB.NET)

    Protected Sub myTree_SelectedNodeChanged(ByVal sender As Object, ByVal e As EventArgs) Handles myTree.SelectedNodeChanged
            Dim xmlstring As String = RiaOLAPGrid1.Serializer.XMLString
            Dim LoadCommand As New SqlCommand
            Dim connectionString As String

            connectionString = ConfigurationManager.ConnectionStrings("SynapseIA").ConnectionString
            Dim dbConnection As New SqlConnection
            dbConnection.ConnectionString = connectionString

            Try

                LoadCommand.CommandText = "Select LayoutXML FROM Reports " & " WHERE ReportID = @ReportID"
                LoadCommand.Parameters.Add("@ReportID", SqlDbType.Int).Value = 1                                                    'Me.myTree.Nodes.Item(0) - What I was using, but then gave it the value of 1 to test.
                Dim resultSet As DataSet = RunQuery(LoadCommand)                                                                               ' I tested the runquery function I made and will put the code below, but that part seems to work.

            ------------------------------------------------------------------------------------------------------------------------------ ' So there is less confusion, the following between the ---- are all commented out as I tried this before going to a dataset.
                'Dim myAdapter As New SqlDataAdapter("GetLayout", dbConnection)
                'myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
                'myAdapter.SelectCommand.Parameters.Add(New SqlParameter("@ReportID", SqlDbType.Int))
                'myAdapter.SelectCommand.Parameters("@ReportID").Value = node.Value
                'myAdapter.SelectCommand.Parameters.Add(New SqlParameter("@LayoutXML", SqlDbType.Xml))
                'xmlstring = myAdapter.SelectCommand.Parameters("@LayoutXML").Direction = ParameterDirection.Output


                'LoadCommand.CommandText = "GetLayout" ' Stored proc to call
                'LoadCommand.CommandType = CommandType.StoredProcedure
                'LoadCommand.Connection = dbConnection
                'LoadCommand.Parameters.Add("@ReportID", SqlDbType.NVarChar, 50, "ReportID").Value = node.Value

                'dbConnection.Open()
                'Dim myReader As SqlDataReader
                'myReader = LoadCommand.ExecuteReader()

                '            While myReader.Read()
                'xmlstring = myReader.Item(0).ToString
                'End While

    -------------------------------------------------------------------------------------------------------------------------------------------------------------------
                MsgBox("Trying to load resultset as xml data")
                RiaOLAPGrid1.Serializer.ReadXML(resultSet.GetXml)   ' Currently this doesn't return the value that is stored in the xml data type in my database.
                MsgBox("ReadXML passed, does the following?")
                RiaOLAPGrid1.Load(resultSet.GetXml)


            Catch ex As Exception
                MsgBox("Do'h!")                 ' The message I see when this failes...
            End Try
            'dbConnection.Close()



        End Sub


    Here is the code for the run query funtion.

    Function RunQuery(ByVal sqlQuery As SqlCommand) As DataSet
            Dim connectionString As String
            connectionString = ConfigurationManager.ConnectionStrings("SynapseIA").ConnectionString
            Dim dbConnection As New SqlConnection
            dbConnection.ConnectionString = connectionString
            Dim dbadapter As New SqlDataAdapter
            dbadapter.SelectCommand = sqlQuery
            sqlQuery.Connection = dbConnection
            Dim resultsDataSet As DataSet = New DataSet
            Try
                dbadapter.Fill(resultsDataSet)
            Catch ex As Exception
                'MsgBox("The Run query didn't work")
            End Try
            Return resultsDataSet
        End Function


    Any help, advice, or thoughts are welcome. :)

    Friday, February 12, 2010 10:34 AM

All replies

  • User-1871264887 posted

    Ok, I ended up playing with some things and updating my RunQuery to add the following:

    dbadapter.fill(resultsDataSet).tostring()

    when this happens, my message box that I have pop up the data pops up the data from my sqldatabase however it is prefressed with <newdataset> <table> etc...

    Anyway, I'm getting closer, however I'm not there yet, just let me know. :)

    Friday, February 12, 2010 11:46 AM