bulk insertion in XML causes date field always to insert 1900-01-01

  • ahsal

    SSC Enthusiast

    Points: 103

    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
    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)

    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

    @XMLData.nodes('/PolicyDetails/PolicyDetail')AS xmlData(PolicyDetail)


    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
    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!"
    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

    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
    End If

    Dim affRow As Integer = cmd.ExecuteNonQuery()

    If affRow > 0 Then
    lblMsg.Text = "Successfully " & affRow & " record inserted."
    End If
    End Using
    End Sub


  • drew.allen

    SSC Guru

    Points: 76458

    XML is case sensitive.  Your bulk insert use DateofBirth but your VB script uses Dateofbirth.  Make sure that the case matches.


    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • ahsal

    SSC Enthusiast

    Points: 103

    You smart....it is my code and didn't notice that.

    thank you so much dear.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply