#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    9
    Rep Power
    0

    Copying XML file Data


    Hello ppl

    I am trying to copy data from an XML file into firebird table but it will not allow me to do this keeps saying unkown column

    Below is the XML File

    <DbName>
    <Item>MAKE</Item>
    </Dbname>

    Asp.net code

    Protected Sub LinkButton2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LinkButton2.Click

    Dim CS As String = Nothing
    Dim conn As FbConnection
    Dim cmd As FbCommand
    Dim FbAdapter As New FbDataAdapter()
    Dim ds As New DataSet()
    Dim xmlFile As XmlReader
    Dim Fb As String = Nothing
    Dim Item As String = Nothing
    CS = "Provider=LCPI.IBProvider.3;User=SYSDBA;Password=masterkey;Database= DbLocation;DataSource=Ip location;Dialect=3;Charset=ISO8859_1; MultipleActiveResultSets=true"
    conne = New FbConnection(CS)
    xmlFile = XmlReader.Create("LocationofXMLfile", New XmlReaderSettings())
    ds.ReadXml(xmlFile)
    Dim i As Integer = 0
    connection.Open()
    For i = 0 To ds.Tables(0).Rows.Count - 1
    Item = Convert.ToInt32(ds.Tables(0).Rows(i).ItemArray(0))
    Fb = "insert into TableName (Col of inserting data into) values(" & Item & ")"
    command = New FbCommand(Fb, connection)
    adpter.InsertCommand = command
    adpter.InsertCommand.ExecuteNonQuery()
    Next
    connection.Close()
    End Sub

    Data Type are differents hence the conversion

    Question

    How can i get the Data from the XML file into an existing Firebird table without carrying over the Colum heading across just the record themselfs?

    Addtion

    Can some explain to me about the Dynamic SQL eroor as i an a bit unclear to what it means?

    Any help is much appriated
    Thanks in adavanced
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    9
    Rep Power
    0
    Hi I keep getting this error and tried my best to get my head round it but i can seem to understand whats happening it seems to happen around the FbAdapter.InsertCommand.ExecuteNonQuery();
    and this error keeps coming up
    Dynamic SQL Error
    SQL error code = -104
    Token unknown - line 1, column 41
    )

    Just need help understanding why i am getting this error any help i will be grateful thanks
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    You need to find out the actual SQL text that
    Code:
    Fb = "insert into TableName (Col of inserting data into) values(" & Item & ")"
    is generating. That will probably show the problem quite clearly.

    Clive
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    9
    Rep Power
    0
    Originally Posted by clivew
    You need to find out the actual SQL text that
    Code:
    Fb = "insert into TableName (Col of inserting data into) values(" & Item & ")"
    is generating. That will probably show the problem quite clearly.

    Clive
    I think its the insert itself so i rekon what if i used a FbCommandBuilder FbCb
    Adapter.InsertCommand = FbCb.GetInsertCommand

    However i feel that this will create another error but will test run and get back to you.

    I found the fb statement and reworte it but still the same eroor came up.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    224
    Rep Power
    12
    What is the exact SQL statement that is generated from your code?
    It's something like "INSERT INTO ..." You need to see the exact statement because there is an error in it. Missing ',' missing ')' wrong number of fields/values, etc.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    9
    Rep Power
    0
    Originally Posted by mIRCata
    What is the exact SQL statement that is generated from your code?
    It's something like "INSERT INTO ..." You need to see the exact statement because there is an error in it. Missing ',' missing ')' wrong number of fields/values, etc.
    Its suppose to look at <code> bit but insert the value of MAKE into the Table of Aborted. Below XML Data Sample of XML Data Below:

    <DbName>
    <Code>MAKE</Code>
    </Dbname>

    Code = Convert.ToString(xmlFile.GetAttribute("Code"));
    Exact Fb Statment "Insert Into Aborted Values (" +Code+ ")

    but the thing is the table already has data in it, which sould matter as they are both cloumns are of the same datatype. Both Take Text, i've gone through my code and each open bracket has a closing bracket

    Also i've cross refernenced my statment and nothing seem to be wring with it when ran with the Firebird cmd and SSMS the insert works fine
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    What we are both trying to explain is that you have to get passed
    your code and the SQL you think is being generated and look at
    the actual SQL text that is being sent to the server.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    9
    Rep Power
    0
    Originally Posted by clivew
    What we are both trying to explain is that you have to get passed
    your code and the SQL you think is being generated and look at
    the actual SQL text that is being sent to the server.
    I follow you now, Thanks for you help both of you
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    9
    Rep Power
    0
    Originally Posted by FBSQLBeginner
    I follow you now, Thanks for you help both of you
    I got it work finally found out it was my insert command i forgot to put in my node and to look in innerText
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    Glad you found your solution.
    Hope we helped point you in a useful direction.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    9
    Rep Power
    0
    Originally Posted by clivew
    Glad you found your solution.
    Hope we helped point you in a useful direction.
    I tried looking for the most difficult solution and it turns out to be the most simple solution, Its true google dose become a developers best friend

IMN logo majestic logo threadwatch logo seochat tools logo