ReadXML Dataset throws Insert Into error

I'm in the process of learning ADO.net with Vb.Net and I've been stumped by the following problem.

I created an XML file of an Access Table that is filled with 400 records (using .WriteXml("c:\HospitalList.xml", XmlWriteMode.WriteSchema)). I then purged the table of all the records, I thought I should be simply able to repopulate the table with the XML file, but when ever I try I get a "Syntax Error in Insert Into Statement". I'm allowing the DataAdapter to create the Insert command, here is what is being created: CommandText: "INSERT INTO HospitalList( Index , HospitalName , TransferAgency , Visible , HospCode , StateIndex ) VALUES ( ? , ? , ? , ? , ? , ? )

Thanks for you help, code belo
Andy Moye

------Code snipet---

Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& "C:\Working_D\Lists.mdb

Dim cn As OleDbConnection = New OleDbConnection(strConnection
cn.Open(

Dim strSelect As String = "SELECT * FROM HospitalList
Dim dscmd As New OleDbDataAdapter(strSelect, cn

' Load a data set
Dim ds As New DataSe
Dim dsXML As New DataSe

' Set the data adapter object's UPDATE, INSERT, and DELET
' commands. Use the SqlCommandBuilder class's ability to auto
' generate these commands from the SELECT comman
Dim autogen As New OleDbCommandBuilder(dscmd


'Simply fills an empty DS with the Select Statemen
'dscmd.Fill(ds, "HospitalList"

'OR Fills the Data set with the Scehma only, no dat
dscmd.FillSchema(ds, SchemaType.Source, "HospitalList"
dscmd.Fill(ds, "HospitalList"

ds.ReadXml("C:\hospitallist.xml", XmlReadMode.ReadSchema
dt = ds.Tables.Item("HospitalList"

'Simply do this to see if any records are flagged as change
Dim dt2 As DataTable = dt.GetChanges(DataRowState.Added
MsgBox("Rows Added " & dt2.Rows.Count

dscmd.Update(ds, "HospitalList"

Miha
Tue Jan 20 12:41:19 CST 2004

Hi Andy,

Index is a reserved word. Embed it with square brackets in insert statament:
INSERT INTO HospitalList( [Index] ....

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"Andy Moyer" <amoyer@med-media.com> wrote in message
news:95C6BD8D-FDC4-4BD5-8B4E-CB03B6BC12EC@microsoft.com...
> I'm in the process of learning ADO.net with Vb.Net and I've been stumped
by the following problem.
>
> I created an XML file of an Access Table that is filled with 400 records
(using .WriteXml("c:\HospitalList.xml", XmlWriteMode.WriteSchema)). I then
purged the table of all the records, I thought I should be simply able to
repopulate the table with the XML file, but when ever I try I get a "Syntax
Error in Insert Into Statement". I'm allowing the DataAdapter to create the
Insert command, here is what is being created: CommandText: "INSERT INTO
HospitalList( Index , HospitalName , TransferAgency , Visible , HospCode ,
StateIndex ) VALUES ( ? , ? , ? , ? , ? , ? )"
>
> Thanks for you help, code below
> Andy Moyer
>
> ------Code snipet----
>
> Dim strConnection As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
> & "C:\Working_D\Lists.mdb"
>
> Dim cn As OleDbConnection = New OleDbConnection(strConnection)
> cn.Open()
>
> Dim strSelect As String = "SELECT * FROM HospitalList"
> Dim dscmd As New OleDbDataAdapter(strSelect, cn)
>
> ' Load a data set.
> Dim ds As New DataSet
> Dim dsXML As New DataSet
>
> ' Set the data adapter object's UPDATE, INSERT, and DELETE
> ' commands. Use the SqlCommandBuilder class's ability to auto-
> ' generate these commands from the SELECT command
> Dim autogen As New OleDbCommandBuilder(dscmd)
>
>
> 'Simply fills an empty DS with the Select Statement
> 'dscmd.Fill(ds, "HospitalList")
>
> 'OR Fills the Data set with the Scehma only, no data
> dscmd.FillSchema(ds, SchemaType.Source, "HospitalList")
> dscmd.Fill(ds, "HospitalList")
>
> ds.ReadXml("C:\hospitallist.xml", XmlReadMode.ReadSchema)
> dt = ds.Tables.Item("HospitalList")
>
> 'Simply do this to see if any records are flagged as changed
> Dim dt2 As DataTable = dt.GetChanges(DataRowState.Added)
> MsgBox("Rows Added " & dt2.Rows.Count)
>
> dscmd.Update(ds, "HospitalList")
>
>

anonymous
Tue Jan 20 13:31:07 CST 2004

Ugh, right you are and thank you.
I renamed the field and it worked.