ASP and MSAccess error

fogofogo · November 28th, 2005, 06:35 AM

Hi All,

I have this script that basically takes information from an XML file and checks one condition, and then inserts it into a SQL Server Database. I need to use a access database instead, so I changes the connection string and it seems to connect to the database ok. Here is the code (its a .vbs file)

Quote:

quote:
Dim XMLDom
Dim ItemID
Dim DbConn
Dim SQLString
Dim ANArticleNode
Dim CollectionOfArticleNodes

Set XMLDom = CreateObject("MSXML2.DomDocument.4.0")
XMLDom.async = False
XMLDom.setProperty "ServerHTTPRequest", True

Set DbConn = Createobject("adodb.connection")
DbConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=casinonewsxml.mdb"

'-- Load the XML data from your live URL
XMLDom.Load("http://directnews.dehavilland.co.uk/webhost.asp?wci=feed&wcp=xmlresults&wcu=172323S123 45S0S0S0S1S0S0S0S0S1S0S0S0&orderid=1")

'-- Create a reference to a collection of all Article Tags within the downloaded XML Document
Set CollectionOfArticleNodes = XMLDom.SelectNodes("InfoStreamResults/Article")

'-- Iterate the collection of Article Tags
For Each ANArticleNode in CollectionOfArticleNodes
    ItemID = ANArticleNode.SelectSingleNode("@ID").text
    Heading = ANArticleNode.SelectSingleNode("Heading").text
    Contents = ANArticleNode.SelectSingleNode("Contents").text
    sDate = ANArticleNode.SelectSingleNode("Date").text

    '-- Delete the item from the local database if it exists
    SQLString = "DELETE FROM DeHavillandNews WHERE ItemID='" & ItemID & "';"
    DbConn.Execute(SQLString)

    '-- Insert the item into the local database
    SQLString = "INSERT INTO DeHavillandNews (ItemID,Heading,Contents,Date) " _
          & "VALUES('" & ItemID & "','" & EncodeIt(Heading) & "','" & EncodeIt(Contents) & "', '" & sDate & "');"
    DbConn.Execute(SQLString)

Next

'-- Handles quotations in text
Function EncodeIt(TextString)
    TextString = Replace(CStr(TextString), "''", "'")
    TextString = Replace(TextString, "'", "''")
    EncodeIt = TextString
End Function

when I run the code I get an error on this line :

Quote:

quote:

DbConn.Execute(SQLString)

and the error says this :

Syntax Error in INSERT INTO statment.

I have also attached a screen shot of how my database is set up.

Does anyone have any ideas as to what might be going wrong with this? I really appreciate your help

Thanks

Fogo. :)

ChrisScott · November 28th, 2005, 07:03 AM

Hey Fogo,

You have two lines that read DbConn.Execute(SQLString) :-)

If the error is on the DELETE statement, you might need to change it to "DELETE * FROM...", if on the INSERT statement, you may need to surround the word Date with square brackets [Date].

HTH,

Chris

fogofogo · November 28th, 2005, 12:00 PM

Thanks Chris for the reply.

You were right in both cases - with the date issue and with the select all symbol!

Thanks for your help - its appreciated!