EDN Admin
Well-known member
First off, I am brand new to any kind of coding and have no formal training at all. My first program is referred to below and is a result of Googling for answers when I was finally able to figure out what the questions were. The question I am
about to ask is probably more about knowing what the question is than getting the exact answer, although the exact answer would be nice.
I have an app that gets data from a database using OledbDbAdapter. I am trying to get this data written to an xml file that I can use to populate PDF files that I am able to link using Adobe Designer. My code gathers the information and writes
the xml file as well as the xsd file but when I bind the PDF to the schema and try to fill it, there is nothing there. Comparing the xsd file to the xml shows some differences in the header which I think is the cause of the problem. I think the
question is "How do I get the XML Writer to include information that links the xml to the xsd when it is written". Please correct me if that is not the question.
Sorry for the length of the code but when I posted a question to the SQL forum when I was trying to get the SELECT string working, I got flamed for not giving enough information.
Again, I am trying to get the XML file to link, associate, reference, or whatever it is that they do with XSD files to get the dataset written to the XML in such a way that I can populate a PDF I have linked to the XSD. A little acronym madness there...
sorry.
I am a complete NOOB so any and all constructive suggestions/criticisms are welcome.
Thanks for any help anyone can provide.
Here is the code along with the first lines of the xml and xsd files at the end. The 60,000 character limit prevented me from posting the entire files but if you need them, I can post them:
<pre>Imports System.Xml
Public Class Form1
Public custcovSQLString As String
Public drvSQLString As String
Public vehSQLString As String
Public covSQLString As String
Public RcdKey As String
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim custAdapter As OleDb.OleDbDataAdapter
Dim custDataSet As New DataSet
Dim drvDataSet As DataSet
Dim drvAdapter As OleDb.OleDbDataAdapter
Dim vehDataSet As DataSet
Dim vehAdapter As OleDb.OleDbDataAdapter
Dim myCon As OleDb.OleDbConnection
myCon = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=C:UsersfalconDocumentsQuote1comp_2011_09_25.accdb")
Select the data from database Customer_info table and write to dataset and datatable
RcdKey = GetID()
MsgBox(RcdKey)
custcovSQLString = MakecustcovSQLString()
custAdapter = New OleDb.OleDbDataAdapter((custcovSQLString), myCon)
custDataSet = New System.Data.DataSet()
custAdapter.FillSchema(custDataSet, SchemaType.Mapped)
custAdapter.Fill(custDataSet)
Dim custTable As DataTable = custDataSet.Tables(0)
drvSQLString = MakedrvSQLString()
drvAdapter = New OleDb.OleDbDataAdapter((drvSQLString), myCon)
drvDataSet = New System.Data.DataSet()
drvAdapter.FillSchema(drvDataSet, SchemaType.Mapped)
drvAdapter.Fill(drvDataSet)
Dim drvTable As DataTable = drvDataSet.Tables(0)
vehSQLString = MakevehSQLString()
vehAdapter = New OleDb.OleDbDataAdapter((vehSQLString), myCon)
vehDataSet = New System.Data.DataSet()
vehAdapter.FillSchema(vehDataSet, SchemaType.Mapped)
vehAdapter.Fill(vehDataSet)
Dim vehTable As DataTable = vehDataSet.Tables(0)
Dim OutputDataSet As DataSet = custDataSet.Copy()
OutputDataSet.Merge(drvTable)
OutputDataSet.Merge(vehTable)
Dim filePath As String<br/> Here is my attempt at getting the xsd into the dataset<br/> filePath = "C:InsuranceP&CtemptestSchemaTable.xsd"
OutputDataSet.ReadXmlSchema(filePath)
DataGridView1.DataSource = custDataSet.Tables(0)
DataGridView1.AutoGenerateColumns = True
DataGridView2.DataSource = drvDataSet.Tables(0)
DataGridView2.AutoGenerateColumns = True
DataGridView3.DataSource = vehDataSet.Tables(0)
DataGridView3.AutoGenerateColumns = True
Write to xml file
Dim o As Object = custTable.Rows.Item(0).Item("Legal_Name")
MsgBox(CStr(o))
OutputDataSet.WriteXml("C:InsuranceP&CtemptestXMLDoc" + CStr(o) + ".xml")
OutputDataSet.WriteXmlSchema("C:InsuranceP&CtemptestSchemaFile.xsd")
myCon.Close()
End Sub
Public Function MakecustcovSQLString()
Dim custcovSQLString As String
custcovSQLString = ("" + "select * from [Customer_Info], [Coverage] Where Customer_Info.ID=" + (RcdKey) + _
" And Coverage.ID=" + (RcdKey) + "")
drvSQLString = ("" + "select * from [Drivers] Where Driver.ID=" + (RcdKey) + "")
Return drvSQLString
vehSQLString = ("" + "select * from [Vehicles] Where Vehicles.ID=" + (RcdKey) + "")
Return vehSQLString
MsgBox(custcovSQLString)
Return custcovSQLString
End Function
Public Function MakedrvSQLString()
Dim drvSQLString As String
Enter Database ID
drvSQLString = ("" + "select * from [Drivers] Where ID=" + (RcdKey) + "")
MsgBox(drvSQLString)
drvSQLString = SQLString
Return drvSQLString
End Function
Public Function MakevehSQLString()
Dim vehSQLString As String
Enter Database ID
vehSQLString = ("" + "select * from [Vehicles] Where ID=" + (RcdKey) + "")
MsgBox(vehSQLString)
vehSQLString = SQLString
Return vehSQLString
End Function
Public Function MakecovSQLString()
Dim SQLString As String
Enter Database ID
SQLString = ("" + "select * from [Coverage] Where ID=" + (RcdKey) + "")
MsgBox(SQLString)
covSQLString = SQLString
Return covSQLString
End Function
Private Function GetID()
Dim RcdKey As String
Dim Message As String
Dim title As String
Dim defaultvalue As String
Message = "Enter ID"
title = "DataBase ID Number Input"
defaultvalue = "" Set default value.
Display message, title, and default value.
RcdKey = InputBox(CStr(Message), CStr(title), CStr(defaultvalue))
Return RcdKey
End Function<br/><br/> Here is the Schema File (SchemaTable.xsd):<br/><br/><br/><?xml version="1.0" standalone="yes"?><br/><xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata <br/> <xs:element name="dataroot <br/> <xs:complexType><br/> <xs:sequence><br/> <xs:element name="Customer_Info" minOccurs="0" maxOccurs="unbounded <br/> <xs:complexType><br/> <xs:sequence><br/> <xs:element name="ID" type="xs:int" /><br/> <xs:element name="Date" type="xs:dateTime" minOccurs="0" /><br/> <xs:element name="Gender" minOccurs="0 <br/> <xs:simpleType><br/> <xs:restriction base="xs:string <br/> <xs:maxLength value="255" /><br/> </xs:restriction><br/> </xs:simpleType><br/> </xs:element><br/> <xs:element name="First" minOccurs="0 <br/> <br/><br/>And a sample resulting XML (XMLDocTERRY L. DAVIS.xml):<br/><br/><?xml version="1.0" standalone="yes"?><br/><NewDataSet><br/> <Table><br/> <Customer_Info.ID>431</Customer_Info.ID><br/> <Date>2011-06-30T00:00:00-05:00</Date><br/> <First>TERRY</First><br/> <Middle>L</Middle><br/> <Last>DAVIS</Last><br/> <DOB>7/26/1956</DOB><br/> <Legal_Name>TERRY L. DAVIS</Legal_Name><br/> <MC>753323</MC><br/> <DOT>2168549</DOT><br/> <Garage_Address>3411 ANDTREE BLVD</Garage_Address><br/> <Garage_City>AUSTIN</Garage_City><br/> <Garage_ST>TX</Garage_ST><br/> <Garage_ZIP>78724</Garage_ZIP><br/> <Garage_County>Travis</Garage_County><br/> <Mail_Address>3411 ANDTREE BLVD</Mail_Address><br/> <Mail_City>AUSTIN</Mail_City><br/> <Mail_ST>TX</Mail_ST><br/> <Mail_ZIP>78724</Mail_ZIP><br/> <Telephone>512 947 2123</Telephone><br/> <Commodities>Flatbed</Commodities><br/> <Radius>Unl</Radius><br/> <Notes>21 Days for FMCSA</Notes><br/> <Quote_Status>Lost Hallmark</Quote_Status><br/> <Common>false</Common><br/> <Contract>true</Contract><br/> <Leased>false</Leased><br/> <Private>false</Private><br/> <Sole_prop>false</Sole_prop><br/> <Partnership>false</Partnership><br/> <Corp>false</Corp><br/> <Individual>false</Individual><br/> <Coverage.ID>431</Coverage.ID><br/> <Liability>1000000</Liability><br/> <UM_UIM>yes</UM_UIM><br/> <UMPD>yes</UMPD><br/> <Collision>38000</Collision><br/> <Coll_Ded>1000</Coll_Ded><br/> <Comprehensive>38000</Comprehensive><br/> <Comp_Ded>1000</Comp_Ded><br/> <Cargo>100000</Cargo><br/> <Cargo_Ded>1000</Cargo_Ded><br/> <Gen_Liab_Charge>$1M/$1M</Gen_Liab_Charge><br/> </Table><br/> <br/>[/code]
<br/>
<br/>
<hr class="sig JMC
View the full article
about to ask is probably more about knowing what the question is than getting the exact answer, although the exact answer would be nice.
I have an app that gets data from a database using OledbDbAdapter. I am trying to get this data written to an xml file that I can use to populate PDF files that I am able to link using Adobe Designer. My code gathers the information and writes
the xml file as well as the xsd file but when I bind the PDF to the schema and try to fill it, there is nothing there. Comparing the xsd file to the xml shows some differences in the header which I think is the cause of the problem. I think the
question is "How do I get the XML Writer to include information that links the xml to the xsd when it is written". Please correct me if that is not the question.
Sorry for the length of the code but when I posted a question to the SQL forum when I was trying to get the SELECT string working, I got flamed for not giving enough information.
Again, I am trying to get the XML file to link, associate, reference, or whatever it is that they do with XSD files to get the dataset written to the XML in such a way that I can populate a PDF I have linked to the XSD. A little acronym madness there...
sorry.
I am a complete NOOB so any and all constructive suggestions/criticisms are welcome.
Thanks for any help anyone can provide.
Here is the code along with the first lines of the xml and xsd files at the end. The 60,000 character limit prevented me from posting the entire files but if you need them, I can post them:
<pre>Imports System.Xml
Public Class Form1
Public custcovSQLString As String
Public drvSQLString As String
Public vehSQLString As String
Public covSQLString As String
Public RcdKey As String
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim custAdapter As OleDb.OleDbDataAdapter
Dim custDataSet As New DataSet
Dim drvDataSet As DataSet
Dim drvAdapter As OleDb.OleDbDataAdapter
Dim vehDataSet As DataSet
Dim vehAdapter As OleDb.OleDbDataAdapter
Dim myCon As OleDb.OleDbConnection
myCon = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=C:UsersfalconDocumentsQuote1comp_2011_09_25.accdb")
Select the data from database Customer_info table and write to dataset and datatable
RcdKey = GetID()
MsgBox(RcdKey)
custcovSQLString = MakecustcovSQLString()
custAdapter = New OleDb.OleDbDataAdapter((custcovSQLString), myCon)
custDataSet = New System.Data.DataSet()
custAdapter.FillSchema(custDataSet, SchemaType.Mapped)
custAdapter.Fill(custDataSet)
Dim custTable As DataTable = custDataSet.Tables(0)
drvSQLString = MakedrvSQLString()
drvAdapter = New OleDb.OleDbDataAdapter((drvSQLString), myCon)
drvDataSet = New System.Data.DataSet()
drvAdapter.FillSchema(drvDataSet, SchemaType.Mapped)
drvAdapter.Fill(drvDataSet)
Dim drvTable As DataTable = drvDataSet.Tables(0)
vehSQLString = MakevehSQLString()
vehAdapter = New OleDb.OleDbDataAdapter((vehSQLString), myCon)
vehDataSet = New System.Data.DataSet()
vehAdapter.FillSchema(vehDataSet, SchemaType.Mapped)
vehAdapter.Fill(vehDataSet)
Dim vehTable As DataTable = vehDataSet.Tables(0)
Dim OutputDataSet As DataSet = custDataSet.Copy()
OutputDataSet.Merge(drvTable)
OutputDataSet.Merge(vehTable)
Dim filePath As String<br/> Here is my attempt at getting the xsd into the dataset<br/> filePath = "C:InsuranceP&CtemptestSchemaTable.xsd"
OutputDataSet.ReadXmlSchema(filePath)
DataGridView1.DataSource = custDataSet.Tables(0)
DataGridView1.AutoGenerateColumns = True
DataGridView2.DataSource = drvDataSet.Tables(0)
DataGridView2.AutoGenerateColumns = True
DataGridView3.DataSource = vehDataSet.Tables(0)
DataGridView3.AutoGenerateColumns = True
Write to xml file
Dim o As Object = custTable.Rows.Item(0).Item("Legal_Name")
MsgBox(CStr(o))
OutputDataSet.WriteXml("C:InsuranceP&CtemptestXMLDoc" + CStr(o) + ".xml")
OutputDataSet.WriteXmlSchema("C:InsuranceP&CtemptestSchemaFile.xsd")
myCon.Close()
End Sub
Public Function MakecustcovSQLString()
Dim custcovSQLString As String
custcovSQLString = ("" + "select * from [Customer_Info], [Coverage] Where Customer_Info.ID=" + (RcdKey) + _
" And Coverage.ID=" + (RcdKey) + "")
drvSQLString = ("" + "select * from [Drivers] Where Driver.ID=" + (RcdKey) + "")
Return drvSQLString
vehSQLString = ("" + "select * from [Vehicles] Where Vehicles.ID=" + (RcdKey) + "")
Return vehSQLString
MsgBox(custcovSQLString)
Return custcovSQLString
End Function
Public Function MakedrvSQLString()
Dim drvSQLString As String
Enter Database ID
drvSQLString = ("" + "select * from [Drivers] Where ID=" + (RcdKey) + "")
MsgBox(drvSQLString)
drvSQLString = SQLString
Return drvSQLString
End Function
Public Function MakevehSQLString()
Dim vehSQLString As String
Enter Database ID
vehSQLString = ("" + "select * from [Vehicles] Where ID=" + (RcdKey) + "")
MsgBox(vehSQLString)
vehSQLString = SQLString
Return vehSQLString
End Function
Public Function MakecovSQLString()
Dim SQLString As String
Enter Database ID
SQLString = ("" + "select * from [Coverage] Where ID=" + (RcdKey) + "")
MsgBox(SQLString)
covSQLString = SQLString
Return covSQLString
End Function
Private Function GetID()
Dim RcdKey As String
Dim Message As String
Dim title As String
Dim defaultvalue As String
Message = "Enter ID"
title = "DataBase ID Number Input"
defaultvalue = "" Set default value.
Display message, title, and default value.
RcdKey = InputBox(CStr(Message), CStr(title), CStr(defaultvalue))
Return RcdKey
End Function<br/><br/> Here is the Schema File (SchemaTable.xsd):<br/><br/><br/><?xml version="1.0" standalone="yes"?><br/><xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata <br/> <xs:element name="dataroot <br/> <xs:complexType><br/> <xs:sequence><br/> <xs:element name="Customer_Info" minOccurs="0" maxOccurs="unbounded <br/> <xs:complexType><br/> <xs:sequence><br/> <xs:element name="ID" type="xs:int" /><br/> <xs:element name="Date" type="xs:dateTime" minOccurs="0" /><br/> <xs:element name="Gender" minOccurs="0 <br/> <xs:simpleType><br/> <xs:restriction base="xs:string <br/> <xs:maxLength value="255" /><br/> </xs:restriction><br/> </xs:simpleType><br/> </xs:element><br/> <xs:element name="First" minOccurs="0 <br/> <br/><br/>And a sample resulting XML (XMLDocTERRY L. DAVIS.xml):<br/><br/><?xml version="1.0" standalone="yes"?><br/><NewDataSet><br/> <Table><br/> <Customer_Info.ID>431</Customer_Info.ID><br/> <Date>2011-06-30T00:00:00-05:00</Date><br/> <First>TERRY</First><br/> <Middle>L</Middle><br/> <Last>DAVIS</Last><br/> <DOB>7/26/1956</DOB><br/> <Legal_Name>TERRY L. DAVIS</Legal_Name><br/> <MC>753323</MC><br/> <DOT>2168549</DOT><br/> <Garage_Address>3411 ANDTREE BLVD</Garage_Address><br/> <Garage_City>AUSTIN</Garage_City><br/> <Garage_ST>TX</Garage_ST><br/> <Garage_ZIP>78724</Garage_ZIP><br/> <Garage_County>Travis</Garage_County><br/> <Mail_Address>3411 ANDTREE BLVD</Mail_Address><br/> <Mail_City>AUSTIN</Mail_City><br/> <Mail_ST>TX</Mail_ST><br/> <Mail_ZIP>78724</Mail_ZIP><br/> <Telephone>512 947 2123</Telephone><br/> <Commodities>Flatbed</Commodities><br/> <Radius>Unl</Radius><br/> <Notes>21 Days for FMCSA</Notes><br/> <Quote_Status>Lost Hallmark</Quote_Status><br/> <Common>false</Common><br/> <Contract>true</Contract><br/> <Leased>false</Leased><br/> <Private>false</Private><br/> <Sole_prop>false</Sole_prop><br/> <Partnership>false</Partnership><br/> <Corp>false</Corp><br/> <Individual>false</Individual><br/> <Coverage.ID>431</Coverage.ID><br/> <Liability>1000000</Liability><br/> <UM_UIM>yes</UM_UIM><br/> <UMPD>yes</UMPD><br/> <Collision>38000</Collision><br/> <Coll_Ded>1000</Coll_Ded><br/> <Comprehensive>38000</Comprehensive><br/> <Comp_Ded>1000</Comp_Ded><br/> <Cargo>100000</Cargo><br/> <Cargo_Ded>1000</Cargo_Ded><br/> <Gen_Liab_Charge>$1M/$1M</Gen_Liab_Charge><br/> </Table><br/> <br/>[/code]
<br/>
<br/>
<hr class="sig JMC
View the full article