ReadXML Dataset throws Insert Into error

[Guest]

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

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

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=" _

 

[Guest]

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