PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
VS 2010 SQLBulkCopy problem with Boolean Values-VBForums
Results 1 to 14 of 14

Thread: SQLBulkCopy problem with Boolean Values

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2007
    Location
    India
    Posts
    1,133

    SQLBulkCopy problem with Boolean Values

    I am to trying to export the data of an SQL table to an xml file, Later this file will be used to import the data back.
    To do so i am using the following code.
    But i am getting an error in WriteAllProducts() as
    Code:
    The given value of type String from the data source cannot be converted to type bit of the specified target column.

    vb Code:
    1. Private Sub ReadAllProducts()
    2.         Using conn As New SqlConnection(My.Settings.GeneralStore)
    3.             Using cmd As New SqlCommand
    4.                 Try
    5.                     cmd.CommandText = "Select * From Products FOR XML AUTO,ELEMENTS XSINIL"
    6.                     cmd.Connection = conn
    7.                     conn.Open()
    8.  
    9.                     Dim XMLRead As XmlReader = cmd.ExecuteXmlReader
    10.                     Dim xp As New XPath.XPathDocument(XMLRead)
    11.                     Dim xn As XPath.XPathNavigator = xp.CreateNavigator
    12.                     Dim xd As New XmlDocument
    13.                     Dim root As XmlNode = xd.CreateElement("Data")
    14.                     root.InnerXml = xn.OuterXml
    15.                     xd.AppendChild(root)
    16.  
    17.  
    18.                     Dim fstream As New FileStream("d:\test\source\products.xml", FileMode.Create, FileAccess.ReadWrite)
    19.                     xd.Save(fstream)
    20.                     fstream.Close()
    21.                 Catch ex As Exception
    22.                     MessageBox.Show(ex.Message, MsgboxHeader, MessageBoxButtons.OK, MessageBoxIcon.Error)
    23.                 End Try
    24.             End Using
    25.         End Using
    26.     End Sub

    vb Code:
    1. Private Sub WriteAllProducts()
    2.         Dim ds As New DataSet()
    3.         Dim SourceData As New DataTable()
    4.         Dim i As Integer = 0
    5.  
    6.         ds.ReadXml("d:\test\source\products.xml")
    7.  
    8.         Using conn As New SqlConnection(My.Settings.GeneralStore)
    9.             Using cmd As New SqlCommand
    10.                   Try
    11.                 SourceData = ds.Tables(0)
    12.  
    13.                 cmd.CommandText = "Truncate Table Products"
    14.                 cmd.Connection = conn
    15.                 conn.Open()
    16.                 cmd.ExecuteNonQuery()
    17.  
    18.                 Using bulkcopy As New SqlBulkCopy(conn)
    19.                     bulkcopy.DestinationTableName = "Products"
    20.  
    21.                     For Each dc As DataColumn In SourceData.Columns
    22.                           bulkcopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName)
    23.                     Next
    24.  
    25.                                       bulkcopy.WriteToServer(SourceData)
    26.                 End Using
    27.  
    28.                    Catch ex As Exception
    29.                      MessageBox.Show(ex.Message, MsgboxHeader, MessageBoxButtons.OK, MessageBoxIcon.Error)
    30.                  End Try
    31.             End Using
    32.         End Using
    33.     End Sub

    i ibeleive the problem is when the sqlbulkcopy is reading the values from xml to write, it is reading all values as string, hence the error.

    Please suggest an workaround

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,947

    Re: SQLBulkCopy problem with Boolean Values

    Is the data in the database XML?

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2007
    Location
    India
    Posts
    1,133

    Re: SQLBulkCopy problem with Boolean Values

    No.. there are around 23 different columns of NVarchar,Decimal,Bit

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,947

    Re: SQLBulkCopy problem with Boolean Values

    In that case, the way you are saving the data in the first place makes no sense. Create a DataTable and call its Load method to load the contents of the data reader into it, then call its WriteXml method. When you want to load the data again, create a DataTable and call ReadXml.

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2007
    Location
    India
    Posts
    1,133

    Re: SQLBulkCopy problem with Boolean Values

    I tried Using DataTable before, but its skipping the columns which has null values..

    vb Code:
    1. Dim dt As New DataTable("Products")
    2.   dt.Load(cmd.ExecuteReader, LoadOption.OverwriteChanges)
    3.   dt.WriteXml("d:\test\source\Products.xml")

    Is there any property we need to set so that datatable includes NULL values

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,947

    Re: SQLBulkCopy problem with Boolean Values

    Please provide a FULL and CLEAR explanation of the problem. What rows are being omitted? Rows with all nulls? Why would you have rows with all nulls to begin with?

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,947

    Re: SQLBulkCopy problem with Boolean Values

    Also, you should not be passing the result of ExecuteReader directly to the Load method because you should be creating the data reader with a Using statement.

  8. #8

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2007
    Location
    India
    Posts
    1,133

    Re: SQLBulkCopy problem with Boolean Values

    This is the output of the xml file

    HTML Code:
    <?xml version="1.0" standalone="yes" ?> 
     <DocumentElement>
     <Products>
      <ProductName>product 1</ProductName> 
      <ProductGroup>Group 1</ProductGroup>
      <CostPrice>100.00</CostPrice>
      <SalePrice>150.00</SalePrice>
    </Products>
    
     <Products>
      <ProductName>product 2</ProductName> 
    
          *!*!*!*  'Note Here ProductGroup is Missing as it contains NULL  *!*!*!*
    
      <CostPrice>100.00</CostPrice>
      <SalePrice>150.00</SalePrice>
    </Products>
    </DocumentElement>
    So that "might" be an problem when we write the data to the Table.

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,947

    Re: SQLBulkCopy problem with Boolean Values

    I tested this code:
    vb.net Code:
    1. Module Module1
    2.  
    3.     Sub Main()
    4.         Dim table As New DataTable("Table")
    5.  
    6.         With table.Columns
    7.             .Add("Column1", GetType(String))
    8.             .Add("Column2", GetType(String))
    9.             .Add("Column3", GetType(String))
    10.         End With
    11.  
    12.         With table.Rows
    13.             .Add("a", "b", DBNull.Value)
    14.             .Add("c", DBNull.Value, "d")
    15.             .Add(DBNull.Value, "e", "f")
    16.         End With
    17.  
    18.         table.WriteXml("D:\johnm\Documents\test\test1.xml")
    19.         table.WriteXml("D:\johnm\Documents\test\test2.xml", XmlWriteMode.WriteSchema)
    20.     End Sub
    21.  
    22. End Module
    The first file looked like this:
    xml Code:
    1. <?xml version="1.0" standalone="yes"?>
    2. <DocumentElement>
    3.   <Table>
    4.     <Column1>a</Column1>
    5.     <Column2>b</Column2>
    6.   </Table>
    7.   <Table>
    8.     <Column1>c</Column1>
    9.     <Column3>d</Column3>
    10.   </Table>
    11.   <Table>
    12.     <Column2>e</Column2>
    13.     <Column3>f</Column3>
    14.   </Table>
    15. </DocumentElement>
    while the second looked like this:
    xml Code:
    1. <?xml version="1.0" standalone="yes"?>
    2. <NewDataSet>
    3.   <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    4.     <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="Table" msdata:UseCurrentLocale="true">
    5.       <xs:complexType>
    6.         <xs:choice minOccurs="0" maxOccurs="unbounded">
    7.           <xs:element name="Table">
    8.             <xs:complexType>
    9.               <xs:sequence>
    10.                 <xs:element name="Column1" type="xs:string" minOccurs="0" />
    11.                 <xs:element name="Column2" type="xs:string" minOccurs="0" />
    12.                 <xs:element name="Column3" type="xs:string" minOccurs="0" />
    13.               </xs:sequence>
    14.             </xs:complexType>
    15.           </xs:element>
    16.         </xs:choice>
    17.       </xs:complexType>
    18.     </xs:element>
    19.   </xs:schema>
    20.   <Table>
    21.     <Column1>a</Column1>
    22.     <Column2>b</Column2>
    23.   </Table>
    24.   <Table>
    25.     <Column1>c</Column1>
    26.     <Column3>d</Column3>
    27.   </Table>
    28.   <Table>
    29.     <Column2>e</Column2>
    30.     <Column3>f</Column3>
    31.   </Table>
    32. </NewDataSet>
    The ReadXml method allows you to specify that schema be read too, so there will be no issue that columns containing NULL are omitted from the data because the schema is specified explicitly. That schema also includes data types, so your original issue should disappear too.

    I was able to do this with no prior experience with the WriteXml method, simply because I bothered to open the relevant documentation and read. If I can do that, you can do that too. One has to wonder why you didn't. One has to hope that you will do so in future.

  10. #10
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,452

    Re: SQLBulkCopy problem with Boolean Values

    Right... it contains null, so it doesn't write it out to the XML ... so what? That's expected. What happens when you read it back in? You get NULL values back in those fields... Nothing is written out when there is a null because that's what a NULL represents - a NOTHING value. Or an unknown value. What would you expect it to write out instead? An empty XML tag? That would represent and empty string... which IS a value, DIFFERENT from a NULL. Distinctly different.

    So that "might" be an problem when we write the data to the Table.
    Wait... so... you didn't bother to even try?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,947

    Re: SQLBulkCopy problem with Boolean Values

    Just did a quick further test:
    vb.net Code:
    1. Module Module1
    2.  
    3.     Sub Main()
    4.         Dim table As New DataTable("Table")
    5.  
    6.         With table.Columns
    7.             .Add("Column1", GetType(String))
    8.             .Add("Column2", GetType(String))
    9.             .Add("Column3", GetType(String))
    10.             .Add("Column4", GetType(String))
    11.         End With
    12.  
    13.         With table.Rows
    14.             .Add("a", "b", DBNull.Value)
    15.             .Add("c", DBNull.Value, "d")
    16.             .Add(DBNull.Value, "e", "f")
    17.         End With
    18.  
    19.         table.WriteXml("D:\johnm\Documents\test\test1.xml")
    20.         table.WriteXml("D:\johnm\Documents\test\test2.xml", XmlWriteMode.WriteSchema)
    21.  
    22.         Dim table2 As New DataTable
    23.         Dim table3 As New DataTable
    24.  
    25.         table2.ReadXml("D:\johnm\Documents\test\test1.xml")
    26.         table3.ReadXml("D:\johnm\Documents\test\test2.xml")
    27.     End Sub
    28.  
    29. End Module
    The first call to ReadXml threw an exception because there was no schema information in the file. When I commented that out, the other call succeeded and reproduced the original table faithfully.

  12. #12
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,947

    Re: SQLBulkCopy problem with Boolean Values

    Quote Originally Posted by techgnome View Post
    Right... it contains null, so it doesn't write it out to the XML ... so what? That's expected. What happens when you read it back in? You get NULL values back in those fields... Nothing is written out when there is a null because that's what a NULL represents - a NOTHING value. Or an unknown value. What would you expect it to write out instead? An empty XML tag? That would represent and empty string... which IS a value, DIFFERENT from a NULL. Distinctly different.


    Wait... so... you didn't bother to even try?

    -tg
    I think the issue would be if a particular column contained NULL in every row then it would not get a mention in the XML at all and thus could not be reproduced on read from that file. I'm not sure whether a missing column would be an issue for SqlBulkCopy or not but writing the schema gets over that anyway, by including a reference to every column in the file, even if there's no data in it.

  13. #13
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,452

    Re: SQLBulkCopy problem with Boolean Values

    I guess I was a bit more annoyed at the "might" bit, meaning that it hadn't even been tried yet... if they had tried it and it was a problem, then sure, OK, roll with it... but to not even try it, and just make assumptions based on the output... grrrrrr....


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  14. #14
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,947

    Re: SQLBulkCopy problem with Boolean Values

    Quote Originally Posted by techgnome View Post
    I guess I was a bit more annoyed at the "might" bit, meaning that it hadn't even been tried yet... if they had tried it and it was a problem, then sure, OK, roll with it... but to not even try it, and just make assumptions based on the output... grrrrrr....


    -tg
    I concur.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width