JohnPell
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</B LEventID>
<ContactTS>2009-12-22T12:5 9:53.84074 22-05:00</ ContactTS>
<CallPurpose />
<CallerID />
<CallerName>Jim</CallerNam e>
<CallerType />
<CallLen>299</CallLen>
<AddedBy>D33456</AddedBy>
<Entity_ID>1</Entity_ID>
<ConMethod />
<Event>Billing & Sell</Event>
<EventTS>2009-12-22T12:59: 53.8407422 -05:00</Ev entTS>
<SubAction>Termination</Su bAction>
<Comment>testcomment</Comm ent>
</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(sq lDB)
Try
conn.Open()
Dim cmd As New Data.SqlClient.SqlCommand( "uspInsert Contact", conn)
cmd.CommandType = CommandType.StoredProcedur e
Dim p As SqlClient.SqlParameter
p = cmd.Parameters.AddWithValu e("@xmlDat a", 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').v alue('.',' bigint'),
x.d.query('./ContactTS').v alue('.',' DateTime') ,
x.d.query('./CallPurpose') .value('.' ,'Varchar( 25)'),
x.d.query('./CallerID').va lue('.','V archar(10) '),
x.d.query('./CallerName'). value('.', 'Varchar(4 0)'),
x.d.query('./CallerType'). value('.', 'Varchar(8 )'),
x.d.query('./CallLen').val ue('.','IN T'),
x.d.query('./AddedBy').val ue('.','Va rchar(10)' ),
x.d.query('EntityID').valu e('.','INT ')
FROM @xmlData.nodes('/DocumentE lements/CE vents') x(d)
END
The data I am sending in an XML string:
<DocumentElement>
<CEvents>
<BLEventID>356091259107</B
<ContactTS>2009-12-22T12:5
<CallPurpose />
<CallerID />
<CallerName>Jim</CallerNam
<CallerType />
<CallLen>299</CallLen>
<AddedBy>D33456</AddedBy>
<Entity_ID>1</Entity_ID>
<ConMethod />
<Event>Billing & Sell</Event>
<EventTS>2009-12-22T12:59:
<SubAction>Termination</Su
<Comment>testcomment</Comm
</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(sq
Try
conn.Open()
Dim cmd As New Data.SqlClient.SqlCommand(
cmd.CommandType = CommandType.StoredProcedur
Dim p As SqlClient.SqlParameter
p = cmd.Parameters.AddWithValu
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').v
x.d.query('./ContactTS').v
x.d.query('./CallPurpose')
x.d.query('./CallerID').va
x.d.query('./CallerName').
x.d.query('./CallerType').
x.d.query('./CallLen').val
x.d.query('./AddedBy').val
x.d.query('EntityID').valu
FROM @xmlData.nodes('/DocumentE
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER