Link to home
Start Free TrialLog in
Avatar of JohnPell
JohnPellFlag for United States of America

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JohnPell

ASKER

Thanks aneeshattingal, I have smacked myself in the head for missing something so obvious. Appreciate the quick response!