Saving data from VB .Net (2005) to SQL Server(2005) table

Experts, After reading thru a number of sources, I have come up with the below code. But, no matter what I try, it will not save data to my SQL Server table. I do not get any errors. I have very little experience with SQL Server stored procedures.
The data I am sending in an XML string:
<DocumentElement>
  <CEvents>
    <BLEventID>356091259107</BLEventID>
    <ContactTS>2009-12-22T12:59:53.8407422-05:00</ContactTS>
    <CallPurpose />
    <CallerID />
    <CallerName>Jim</CallerName>
    <CallerType />
    <CallLen>299</CallLen>
    <AddedBy>D33456</AddedBy>
    <Entity_ID>1</Entity_ID>
    <ConMethod />
    <Event>Billing &amp; Sell</Event>
    <EventTS>2009-12-22T12:59:53.8407422-05:00</EventTS>
    <SubAction>Termination</SubAction>
    <Comment>testcomment</Comment>
  </CEvents>
</DocumentElement>

The code I am sending it with from VB .Net(2005)
Dim ms As New IO.MemoryStream
        CallEvents.TableName = "CEvents"
        CallEvents.WriteXml(ms, True)
        ms.Seek(0, IO.SeekOrigin.Begin)
        Dim sr As New IO.StreamReader(ms)
        Dim xmlString As String
        xmlString = sr.ReadToEnd

        sr.Close()
        sr.Dispose()

        Using conn As New SqlClient.SqlConnection(sqlDB)

            Try
                conn.Open()

                Dim cmd As New Data.SqlClient.SqlCommand("uspInsertContact", conn)
                cmd.CommandType = CommandType.StoredProcedure
                Dim p As SqlClient.SqlParameter
                p = cmd.Parameters.AddWithValue("@xmlData", xmlString)
                p.SqlDbType = SqlDbType.Xml

and last, the SQL Server (2005)  stored procedure I am sending to:
Create PROCEDURE uspInsertContact
      
      @xmlData xml
AS
BEGIN
      SET NOCOUNT ON;

      
   INSERT INTO dbo.CALL_CONTACT
          (BL_Event_ID, Caller_Contact_dts, Call_Purpose_Global_Cd,
           Caller_Contact_User_ID, Caller_Nm, Caller_Type_Global_Cd,
           Call_Length_Nbr, Contact_Event_User_ID, Entity_ID)
 SELECT
                     x.d.query('./BLEventID').value('.','bigint'),
                     x.d.query('./ContactTS').value('.','DateTime'),
      x.d.query('./CallPurpose').value('.','Varchar(25)'),
      x.d.query('./CallerID').value('.','Varchar(10)'),
      x.d.query('./CallerName').value('.','Varchar(40)'),
      x.d.query('./CallerType').value('.','Varchar(8)'),
      x.d.query('./CallLen').value('.','INT'),
      x.d.query('./AddedBy').value('.','Varchar(10)'),
      x.d.query('EntityID').value('.','INT')
      FROM @xmlData.nodes('/DocumentElements/CEvents') x(d)

END

JohnPellAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
>     FROM @xmlData.nodes('/DocumentElements/CEvents') x(d)
change the above to

     FROM @xmlData.nodes('/DocumentElement/CEvents') x(d)
0
 
JohnPellAuthor Commented:
Thanks aneeshattingal, I have smacked myself in the head for missing something so obvious. Appreciate the quick response!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.