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. :)
