trouble shoot XML SQL ASP.net VB
I have a class generated using the XSD.exe commandline utility from VS.net
The class has a public shared function. I have a webform control that contains a series of radiobuttonlist and checkboxlist controls. My extractresponse sub iterates through my controls, checks to see if they are Checkboxlist or radiobuttonlist controls, if so the id and value are captured. The results are passed to my public shared function The function serializes the result set passes them as xml to a stored procedure where the data is inserted into two tables. Ok that is what is supposed to happen. What actually happens is NOTHING. I need help in isolating where the disconnect is occuring. I am using VS.net to develop the app.
Class:
<code>Option Strict Off
Option Explicit On
Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Reflection
Imports System.Xml.Serialization
Imports System.IO
Namespace WebModules.Accounts.Business
Public Class SResponse
Inherits System.ComponentModel.Component
#Region " Component Designer generated code "
Public Sub New(ByVal Container As System.ComponentModel.IContainer)
MyClass.New()
'Required for Windows.Forms Class Composition Designer support
Container.Add(Me)
End Sub
Public Sub New()
MyBase.New()
'This call is required by the Component Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Component overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Component Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Component Designer
'It can be modified using the Component Designer.
'Do not modify it using the code editor.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
components = New System.ComponentModel.Container
End Sub
#End Region
<System.Xml.Serialization.XmlRootAttribute([Namespace]:="http://interhealthusa.com/", IsNullable:=False)> _
Public Class Schema7
'<remarks/>
<System.Xml.Serialization.XmlElementAttribute("ihs urveyresponse", Form:=System.Xml.Schema.XmlSchemaForm.Unqualified) > _
Public Items() As Schema7Ihsurveyresponse
End Class
'<remarks/>
Public Class Schema7Ihsurveyresponse
'<remarks/>
<System.Xml.Serialization.XmlElementAttribute("ihs responsedetail", Form:=System.Xml.Schema.XmlSchemaForm.Unqualified) > _
Public ihsresponsedetail() As Schema7IhsurveyresponseIhsresponsedetail
'<remarks/>
<System.Xml.Serialization.XmlAttributeAttribute( )> _
Public surveyid As Integer
'<remarks/>
<System.Xml.Serialization.XmlIgnoreAttribute()> _
Public surveyidSpecified As Boolean
'<remarks/>
<System.Xml.Serialization.XmlAttributeAttribute( )> _
Public Userid As Integer
'<remarks/>
<System.Xml.Serialization.XmlIgnoreAttribute()> _
Public UseridSpecified As Boolean
End Class
'<remarks/>
Public Class Schema7IhsurveyresponseIhsresponsedetail
'<remarks/>
<System.Xml.Serialization.XmlAttributeAttribute( )> _
Public id As Integer
'<remarks/>
<System.Xml.Serialization.XmlIgnoreAttribute()> _
Public idSpecified As Boolean
'<remarks/>
<System.Xml.Serialization.XmlAttributeAttribute( )> _
Public question As String
'<remarks/>
<System.Xml.Serialization.XmlAttributeAttribute( )> _
Public response As Integer
'<remarks/>
<System.Xml.Serialization.XmlIgnoreAttribute()> _
Public responseSpecified As Boolean
'<remarks/>
<System.Xml.Serialization.XmlAttributeAttribute( )> _
Public result As Integer
'<remarks/>
<System.Xml.Serialization.XmlIgnoreAttribute()> _
Public resultSpecified As Boolean
End Class
Public Shared Function addresponse(ByVal newResponse As Schema7Ihsurveyresponse) As String
Dim serializer As XmlSerializer
serializer = New XmlSerializer(GetType(Schema7Ihsurveyresponse))
Dim ms As MemoryStream
Try
ms = New MemoryStream
serializer.Serialize(ms, newResponse)
Catch ex As Exception
ms.Close()
Return (-1)
End Try
Dim xmlresponse As String = System.Text.Encoding.UTF7.GetString(ms.ToArray)
ms.Close()
Dim cmd As New SqlClient.SqlCommand
cmd.CommandText = "insertResponseDetails"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlClient.SqlParameter("@xml", xmlresponse))
Dim cn As New SqlClient.SqlConnection(ConfigurationSettings.AppS ettings("SurveyDSN"))
cmd.Connection = cn
cn.Open()
Dim newResponseid As String = xmlresponse
cmd.ExecuteScalar()
Try
newResponseid = cmd.ExecuteScalar()
Catch ex As Exception
Return -1
Finally
cn.Close()
End Try
Return newResponseid
End Function
End Class
End Namespace
</code>
buttonclick -
<code> Private Function ExtractResults() As SResponse.Schema7Ihsurveyresponse
Dim currentUser As New AccBusiness.User( _
CType(Context.User, SitePrincipal))
Dim sb As StringBuilder = New StringBuilder
sb.Append("Answers: ")
Dim msgtext As String
Dim ctrl, ctl2 As Control
Dim rb As RadioButtonList
Dim lb As TextBox
Dim ck As CheckBoxList
Dim i As Integer
Dim id As Integer = (Controls().Count - 1)
Dim item As ListItem
Dim newResponse As New SResponse.Schema7Ihsurveyresponse
newResponse.surveyid = 1
'newResponse.Userid = currentUser.UserId
newResponse.Userid = 14
Dim details(id) As SResponse.Schema7IhsurveyresponseIhsresponsedetail
For i = 0 To (Controls().Count - 1)
ctrl = Controls(i)
id = -1
Select Case ctrl.GetType.Name
Case "RadioButtonList"
details(i) = New SResponse.Schema7IhsurveyresponseIhsresponsedetail
rb = CType(ctrl, RadioButtonList)
sb.Append(rb.ID & ": " & rb.SelectedValue)
details(i).question = rb.ID
'details(i).result = CInt(rb.SelectedValue)
details(i).result = 1
Case "CheckBoxList"
ck = CType(ctrl, CheckBoxList)
For Each item In ck.Items
If item.Selected Then
sb.Append(ck.ID & ": ")
sb.Append(item.Value & ", ")
'details(i) = New SResponse.Schema7IhsurveyresponseIhsresponsedetail
'details(i).question = ck.ID
'details(i).result = CInt(item.Value)
'details(i).result = 2
'details(i).responseSpecified = False
End If
Next
End Select
sb.Append(" - ")
Next
newResponse.ihsresponsedetail = details
Dim responseID As String
responseID = SResponse.addresponse(newResponse)
Surveylabel1.Text = "Response: " & responseID.ToString & sb.ToString
End Function
Private Sub btnSurveySubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSurveySubmit.Click
ExtractResults()
End Sub
</code>
stored procedure
<code>
CREATE PROCEDURE Insertresponsedetails
@XML TEXT
AS
SET NOCOUNT ON
SET XACT_ABORT ON
-- Access is not restricted.
DECLARE @idoc INT, @responseid int
EXECUTE sp_xml_preparedocument @idoc OUTPUT, @XML, '<ns xmlns:a="http://interhealthusa.com/surveyresponse.xsd"/>'
if @@error<>0
begin
select(-1)
return
end
BEGIN TRANSACTION
insert into ihsurveyresponse (userid, Surveyid, Responsedate)
select userid, surveyid, getdate()
from openxml(@idoc, '/surveyresponse', 1)
with (userid int, surveyid int)
if @@error<>0
begin
rollback transaction
select 2
return
end
set @responseid = scope_identity()
INSERT INTO IHSResponsedetail (Question,Result, Response)
SELECT Question, Result, @responseid
FROM OPENXML (@idoc, '/SurveyResponse/ihsresponsedetail')
WITH ( [Question] char(10) '@Question', Result int '@Result')
EXECUTE sp_xml_removedocument @idoc
if @@error<>0
begin
rollback transaction
select 3
return
end
COMMIT TRANSACTION
select @responseid
GO
</code>
Any help would be appreciated.
|
