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

     

  • 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.

    Drew

    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