Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 7th, 2004, 04:23 PM
Authorized User
 
Join Date: Mar 2004
Location: Benicia, CA, USA.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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.

Reply With Quote
  #2 (permalink)  
Old April 7th, 2004, 11:29 PM
Authorized User
 
Join Date: Mar 2004
Location: Benicia, CA, USA.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It appears as though I have something wrong with my stored procedure, or the format of the xml I am sending my stored procedure, as my serialization appears to proceed without error, and if I strip my stored procedure down to just the parameter and a return that works correctly. So it appears that an xmlresponse is being sent to my stored procedure and but that it is not processing correctly as my executescalar in my try catch creates an error.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
ASP.NET 1.1,VB.NET,crystal reports, SQl server gvi Crystal Reports 1 September 11th, 2008 02:55 AM
inserting XML/ASCII data into SQL using VB.net outcast1881 Other Programming Languages 0 July 20th, 2006 07:39 AM
Parse XML doc using VB.NET into ASP.NET page kwilliams Classic ASP XML 17 November 11th, 2005 10:42 AM



All times are GMT -4. The time now is 09:44 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.