[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

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

0
JohnPell
Asked:
JohnPell
1 Solution
 
Aneesh RetnakaranDatabase 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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now