June 18, 2019 at 4:10 pm
i have bulk insertion to my table so i use XML in stored procedure. below is my stored procedure, the problem is with the date column 'DateOfBirth' in which it sets it as 1900-01-01 and not taking the date i insert in the ASP page
ALTER PROCEDURE [dbo].[PolicyBulkInsert]
@XMLData xml
AS
Create table #tempPolicy(
ClientID bigint null,
FirstName varchar(50) null,
MiddleName varchar(50) null,
LastName varchar(50) null,
[Address] varchar(50) null,
Telephone varchar(50) null,
Email varchar(50) null,
LivingPlace varchar(50) null,
PassportNo varchar(50) null,
Gender varchar(50) null,
Dateofbirth date null,
PolicyPlan varchar(50) null,
Destination varchar(50) null);
Insert into PolicyDetails(ClientID, FirstName,MiddleName, LastName, [Address], Telephone, Email, LivingPlace, PassportNo, Gender, Dateofbirth, PolicyPlan, Destination, PolicyNumber)
Select
PolicyDetail.query('ClientID').value('.', 'bigint') as ClientID,
PolicyDetail.query('FirstName').value('.', 'varchar(50)') as FirstName,
PolicyDetail.query('MiddleName').value('.', 'varchar(50)') as MiddleName,
PolicyDetail.query('LastName').value('.', 'varchar(50)') as LastName,
PolicyDetail.query('Address').value('.', 'varchar(50)') as [Address],
PolicyDetail.query('Telephone').value('.', 'varchar(50)') as Telephone,
PolicyDetail.query('Email').value('.', 'varchar(50)') as Email,
PolicyDetail.query('LivingPlace').value('.', 'varchar(50)') as LivingPlace,
PolicyDetail.query('PassportNo').value('.', 'varchar(50)') as PassportNo,
PolicyDetail.query('Gender').value('.', 'varchar(50)') as Gender,
PolicyDetail.query('DateOfBirth').value('.', 'Date') as DateOfBirth,
PolicyDetail.query('PolicyPlan').value('.', 'varchar(50)') as PolicyPlan,
PolicyDetail.query('Destination').value('.', 'varchar(50)') as Destination,
( select (isnull(max(PolicyNumber),0)+1) from PolicyDetails) as PolicyNumber
FROM
@XMLData.nodes('/PolicyDetails/PolicyDetail')AS xmlData(PolicyDetail)
RETURN
the code behind in VB.net:
Protected Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Dim txtClientID As Label = CType(Repeater1.FindControl("txtClientID"), Label)
Dim sb As StringBuilder = New StringBuilder()
sb.AppendLine("<?xml version=""1.0"" ?>")
sb.AppendLine(" <PolicyDetails>")
Dim txtFirstName As TextBox
Dim txtMiddleName As TextBox
Dim txtLastName As TextBox
Dim txtPassportNo As TextBox
Dim ComboGender As DropDownList
Dim txtBirthdate As TextBox
Dim theCultureInfo As IFormatProvider = New System.Globalization.CultureInfo("en-GB", True)
Dim StartDate As DateTime = DateTime.ParseExact(txtStartF.Text, "dd/MM/yyyy", theCultureInfo)
Dim EndDate As DateTime = DateTime.ParseExact(txtEndF.Text, "dd/MM/yyyy", theCultureInfo)
For Each row As GridViewRow In gvContacts.Rows
txtFirstName = CType(row.FindControl("txtFirstName"), TextBox)
txtMiddleName = CType(row.FindControl("txtMiddleName"), TextBox)
txtLastName = CType(row.FindControl("txtLastName"), TextBox)
txtPassportNo = CType(row.FindControl("txtPassportNo"), TextBox)
txtBirthdate = CType(row.FindControl("txtBirthdate"), TextBox)
ComboGender = CType(row.FindControl("ComboGender"), DropDownList)
txtClientID = CType(txtClientID, Label)
If txtFirstName Is Nothing OrElse txtMiddleName Is Nothing OrElse txtLastName Is Nothing Then
Return
End If
If String.IsNullOrEmpty(txtFirstName.Text.Trim()) OrElse String.IsNullOrEmpty(txtMiddleName.Text.Trim()) OrElse String.IsNullOrEmpty(txtLastName.Text.Trim()) Then
lblMsg.Text = "All fields are required!"
Return
Else
sb.AppendLine(" <PolicyDetail>")
sb.AppendLine(" <ClientID>" & txtClientID.Text.Trim() & "</ClientID>")
sb.AppendLine(" <FirstName>" & txtFirstName.Text.Trim() & "</FirstName>")
sb.AppendLine(" <MiddleName>" & txtMiddleName.Text.Trim() & "</MiddleName>")
sb.AppendLine(" <LastName>" & txtLastName.Text.Trim() & "</LastName>")
sb.AppendLine(" <Address>" & txtCity.Text.Trim() & "</Address>")
sb.AppendLine(" <Telephone>" & txtPhone.Text.Trim() & "</Telephone>")
sb.AppendLine(" <Email>" & txtEmail.Text.Trim() & "</Email>")
sb.AppendLine(" <LivingPlace>" & ComboLivingPlace.SelectedItem.Text & "</LivingPlace>")
sb.AppendLine(" <PassportNo>" & txtPassportNo.Text.Trim() & "</PassportNo>")
sb.AppendLine(" <Gender>" & ComboGender.SelectedItem.Text & "</Gender>")
sb.AppendLine(" <Dateofbirth>" & txtBirthdate.Text.Trim() & "</Dateofbirth>")
sb.AppendLine(" <PolicyPlan>" & ComboPlan.SelectedItem.Text() & "</PolicyPlan>")
sb.AppendLine(" <Destination>" & ComboCountry.SelectedItem.Text() & "</Destination>")
sb.AppendLine(" <EffectiveDate>" & StartDate & "</EffectiveDate>")
sb.AppendLine(" <ExpiryDate>" & EndDate & "</ExpiryDate>")
sb.AppendLine(" <PolicyDuration>" & ((EndDate.Date - StartDate.Date).TotalDays).ToString().Trim() & "</PolicyDuration>")
sb.AppendLine(" <Premium>" & txtLastName.Text.Trim() & "</Premium>")
sb.AppendLine(" <TotalPremium>" & txtLastName.Text.Trim() & "</TotalPremium>")
sb.AppendLine(" <IssuedDate>" & Date.Today.Date.AddHours(4) & "</IssuedDate>")
sb.AppendLine(" <CreatedDate>" & Date.Today.Date.AddHours(4) & "</CreatedDate>")
sb.AppendLine(" <Status>Active</Status>")
sb.AppendLine(" <CreatedBy>" & txtClientID.Text.Trim() & "</CreatedBy>")
sb.AppendLine(" </PolicyDetail>")
End If
Next
sb.AppendLine(" </PolicyDetails>")
Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
Dim cmd As SqlCommand = New SqlCommand("PolicyBulkInsert", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@XMLData", sb.ToString())
If con.State <> ConnectionState.Open Then
con.Open()
End If
Dim affRow As Integer = cmd.ExecuteNonQuery()
If affRow > 0 Then
lblMsg.Text = "Successfully " & affRow & " record inserted."
'PopulateData()
AddRowsToGrid()
End If
End Using
End Sub
June 18, 2019 at 4:25 pm
XML is case sensitive. Your bulk insert use DateofBirth but your VB script uses Dateofbirth. Make sure that the case matches.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 18, 2019 at 4:32 pm
You smart....it is my code and didn't notice that.
thank you so much dear.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply