loading xml data into database using datatable

Hi friends hope all are doing well. I am facing one problem while saving data into sql server database from xml file using datatable using vb.net i.e. "Conversion from type 'DBNull' to type 'String' is not valid". But not always getting error may be based on xml file. Below follows one of my xml files. I used to generate xml file randomly.

<Jobs>
- <Job ID="895562" PositionID="2300056">
  <Title>Senior Network Administrator</Title> 
- <Summary>
- <![CDATA[ Join a global leader in an exciting Australia and New Zealand role. As the Leader of Technical Support you will be responsible for mentoring and managing a technical team striving to become the technical centre of excellence in the region. A hands on role, you will not only be the technical expert but you will work along side the sales team and directly contribute to the growth of the business. Th
  ]]> 
  </Summary>
  <DateActive Date="2009-10-15T19:06:00-05:00">10/15/2009</DateActive> 
  <DateExpires Date="2009-11-14T19:06:00-05:00">11/14/2009</DateExpires> 
  <DateUpdated Date="2009-10-15 19:06:00">10/15/2009</DateUpdated> 
  <Country>xxxx</Country> 
  <State>xxx</State> 
  <City>xxx</City> 
  <PostalCode>888899</PostalCode> 
  <Min Valu开发者_运维知识库e="90000">90,000.00</Min> 
  <Max Value="100000">100,000.00</Max> 
  <Type ID="1">Per Year</Type> 
  <Currency ID="4">xxx</Currency> 
  <BuilderFields /> 
  <DisplayOptions>4</DisplayOptions> 
  <AddressType>6</AddressType> 
  </Job>
- <Job ID="84000527" PositionID="61383275">
  <Title>QA Test Analyst- Banking and Finance</Title> 
- <Summary>
- <![CDATA[ Extensive background in QA testing within the finance sector essential ISEB Testing qualification essential About our Client Our client is a leading global banking organisation Job Description In this role you will be responsible for systems integration testing, Quality Assurance and Environment support for a wide variety of technology projects. Responsibilities will include: Working closely with d
  ]]> 
  </Summary>
  <DateActive Date="2009-10-15T18:31:11-05:00">10/15/2009</DateActive> 
  <DateExpires Date="2009-11-14T20:00:35-05:00">11/14/2009</DateExpires> 
  <DateUpdated Date="2009-10-15 20:01:00">10/15/2009</DateUpdated> 
  <Country>xxx</Country> 
  <City>xxx</City> 
  <PostalCode>8888</PostalCode> 
  <Min Value="90000">90,000.00</Min> 
  <Max Value="100000">100,000.00</Max> 
  <Type ID="1">Per Year</Type> 
  <Currency ID="4">xxx</Currency> 
  <CompanyName>Michael Page International</CompanyName> 
  <BuilderFields /> 
  <DisplayOptions /> 
  <AddressType>6</AddressType> 
  </Job>
<Job ID="895562" PositionID="2300056">
  <Title>Senior Network Administrator</Title> 
- <Summary>
- <![CDATA[ Join a global leader in an exciting Australia and New Zealand role. As the Leader of Technical Support you will be responsible for mentoring and managing a technical team striving to become the technical centre of excellence in the region. A hands on role, you will not only be the technical expert but you will work along side the sales team and directly contribute to the growth of the business. Th
  ]]> 
  </Summary>
  <DateActive Date="2009-10-15T19:06:00-05:00">10/15/2009</DateActive> 
  <DateExpires Date="2009-11-14T19:06:00-05:00">11/14/2009</DateExpires> 
  <DateUpdated Date="2009-10-15 19:06:00">10/15/2009</DateUpdated> 
  <Country>xxxx</Country> 
  <State>xxx</State> 
  <City>xxx</City> 
  <PostalCode>888899</PostalCode> 
  <Min Value="90000">90,000.00</Min> 
  <Max Value="100000">100,000.00</Max> 
  <Type ID="1">Per Year</Type> 
  <Currency ID="4">xxx</Currency> 
  <BuilderFields /> 
  <DisplayOptions>4</DisplayOptions> 
  <AddressType>6</AddressType> 
  </Job>
</Jobs>

Pls. help me to overcome this problem. Thanks in advance. ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++=

Protected Sub lnkbtnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs)
    Dim fid, filename As String
    fid = CType(sender.parent.controls(0).parent.parent.controls(0), TableCell).Text
    filename = fid & ".xml"


    Dim uploadDS As New DataSet
    Dim tempDT As New DataTable
    Dim i As Integer
    Dim ConeFilePath As String = Nothing

     ConeFilePath = "~/CareerOneModifiedFeeds/" & filename.ToString()
    uploadDS.ReadXml(Server.MapPath(ConeFilePath))
    tempDT = uploadDS.Tables(0).DefaultView.ToTable
    Dim dtRow As DataRow
    Dim JobCount As Integer
    Dim Count As Integer = 0

    Dim strsaltype, strsalcur As String
    Dim strMinSal, strMaxSal As String

    For i = 0 To tempDT.Rows.Count - 1
        Count = Count + 1
        dtRow = tempDT.Rows(i)
        'Code to get the check whether the job id already exists or not
        JobCount = Dr.CheckJobIDExists(UserID, Trim(dtRow.Item("JOB_ID")))

        Dim FullLoc As String = Replace(dtRow.Item("COUNTRY"), "'", "''").Trim() + "-" + Replace(dtRow.Item("STATE"), "'", "''").Trim()
        If dtRow.Item("CITY") <> "" Then
            FullLoc = FullLoc + "-" + Replace(dtRow.Item("CITY"), "'", "''").Trim()
        End If



        If JobCount = 1 Then

            '   Update the record aganist that jobid
            DR.UpdateExistingCareerOneJobs_XML(UserID, Trim(dtRow.Item("JOB_ID")), Trim(Replace(dtRow.Item("TITLE"), "'", "''")), _
            Trim(Replace(dtRow.Item("Summary"), "'", "''")), Trim(Replace(dtRow.Item("DateActive"), "'", "''")), _
            Trim(Replace(dtRow.Item("DateExpire"), "'", "''")), FullLoc, Trim(Replace(dtRow.Item("COUNTRY"), "'", "''")), _
            Trim(Replace(dtRow.Item("STATE"), "'", "''")), Trim(Replace(dtRow.Item("CITY"), "'", "''")), Trim(Replace(dtRow.Item("PostalCode"), "'", "''")), _
            Trim(Replace(dtRow.Item("BuilderFields"), "'", "''")), Trim(Replace(dtRow.Item("DisplayOptions"), "'", "''")), _
            Trim(Replace(dtRow.Item("AddressType"), "'", "''")), Trim(Replace(dtRow.Item("CompanyName"), "'", "''")), _
            Trim(Replace(dtRow.Item("PositionID"), "'", "''")), Trim(Replace(dtRow.Item("SalMin"), "'", "''")), Trim(Replace(dtRow.Item("SalMax"), "'", "''")), _
            Trim(Replace(dtRow.Item("SalType"), "'", "''")), Trim(Replace(dtRow.Item("SalCurrency"), "'", "''")), Replace(JobCat, "'", "''"), DateTime.Now())

        Else
            '   Insert record new jobid

                  Dim z As Integer = DR.InsertCareeroneJobs_XML(UserID, Trim(dtRow.Item("JOB_ID")), Trim(Replace(dtRow.Item("TITLE"), "'", "''")), _
            Trim(Replace(dtRow.Item("Summary"), "'", "''")), Trim(Replace(dtRow.Item("DateActive"), "'", "''")), _
            Trim(Replace(dtRow.Item("DateExpire"), "'", "''")), FullLoc, Trim(Replace(dtRow.Item("COUNTRY"), "'", "''")), _
            Trim(Replace(dtRow.Item("STATE"), "'", "''")), Trim(Replace(dtRow.Item("CITY"), "'", "''")), _
            Trim(dtRow.Item("PostalCode")), Trim(Replace(dtRow.Item("BuilderFields"), "'", "''")), _
            Trim(Replace(Convert.ToString(dtRow.Item("DisplayOptions")), "'", "''")), Trim(Replace(dtRow.Item("AddressType"), "'", "''")), _
            Trim(Replace(dtRow.Item("CompanyName"), "'", "''")), Trim(Replace(dtRow.Item("PositionID"), "'", "''")), Trim(strsaltype.ToString()), _
            Trim(strsalcur.ToString()), Trim(Replace(Convert.ToString(JobCat), "'", "''")), strMinSal, strMaxSal, DateTime.Now())
            '  Trim(Replace(dtRow.Item("PositionID"), "'", "''")),Trim(strsaltype.ToString()), Trim(strsalcur.ToString()), _

            Response.Write(z)
        End If

    Next i
End Sub

---

I don't think it's a problem with your SQL code - but you need to be careful in your vb.net code when dealing with datarows that could contain empty fields:

Trim(Replace(dtRow.Item("DateExpire"), "'", "''"))
Trim(Replace(dtRow.Item("COUNTRY"), "'", "''"))

All these calls to Replace might fall flat on their nose if the dtRow.Item("COUNTRY") (or whichever you're using) is empty/NULL.

You need to be more careful applying those methods and check for DBNull.Value first!

Create a function something like this (in C# - should have no trouble converting that to VB.NET):

public string ReplaceString(DataRow row, string fieldname)
{
   // first make sure the value of the field in the row is not NULL !
   if(!row.IsNull(fieldname))
   {
       // only if NOT null, do the replace
       return row[fieldname].Replace("'", "''").Trim();
   }

   // if the field is DBNull, return an empty string
   return string.Empty;
}

and then use that in place of your explicit calls to Trim(Replace(...)).

Marc

---

You can try the following storedproc where you can send the xml content of the xml file and insert into table. visit this link

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE JobsBulkInsertXML @items xml

    AS
    DECLARE @hDoc int
    exec sp_xml_preparedocument @hDoc OUTPUT, @items

    Insert Into absorbentorder
    SELECT dpc, absorbentcode, quantity
    FROM OPENXML (@hDoc, '/absorbentitems',1)
    WITH (dpc char(15), absorbentcode char(15), quantity char(15)) XMLItems

    EXEC sp_xml_removedocument @hDoc


    GO