 |
|
04-13-2007, 02:30 AM
|
|
SEO Junior
|
|
Join Date: Mar 2007
Posts: 26
|
|
Hi BSolveIT,
I know this is a lot to ask but i am having problems writing the code to insert the data in to the database, I just cant figure out the right way to do it.
My Field names are :ProductCode, Manufacturer, Model, ProductName, Category, ShortDescription, LongDescription, ProductPageURL, SmallImageURL, LargeImageURL, PriceGBP, StockAvailability, DeliveryperiodDays, PromotionalLineRental, PromotionalPeriod, FullPriceLineRental, Network, Tariff
and this is the code i have so far with the split
Code:
<%
Dim URL
URL = "http://summitfeed.co.uk/three/three-affil.txt"
Dim objXMLHTTP
Set objXMLHTTP = Server.CreateObject("Msxml2.ServerXMLHTTP")
objXMLHTTP.Open "GET", URL, False
objXMLHTTP.Send
IF objXMLHTTP.statusText = "OK" THEN
txtFile = objXMLHTTP.responseText
END IF
'*** Clean up!
Set objXMLHTTP = nothing
Response.Write "<table border=1>"
Dim EachLine, i, EachLine2, i2
EachLine = Split(txtFile, vbCrLF)
For i = 0 to Ubound(EachLine)
Response.Write "<tr><td><strong>"&i&"</strong></td>"
EachLine2 = Split(EachLine(i), vbTAB)
For i2 = 0 to Ubound(EachLine2)
Response.Write "<td>" & EachLine2(i2) & "</td>"
Next
Response.Write "</tr>"&vbCrLF
Next
Response.Write "</table>"
%>
This is my starting point but this just puts the data in to a table, I cant figure out how to construct the Recordset around this to do the job.
This is my best attempt So Far
Code:
<%
Dim URL
URL = "http://summitfeed.co.uk/three/three-affil.txt"
Dim objXMLHTTP
Set objXMLHTTP = Server.CreateObject("Msxml2.ServerXMLHTTP")
objXMLHTTP.Open "GET", URL, False
objXMLHTTP.Send
IF objXMLHTTP.statusText = "OK" THEN
txtFile = objXMLHTTP.responseText
END IF
'*** Clean up!
Set objXMLHTTP = nothing
Dim EachLine, i, EachLine2, i2, sSQL, sConnString
EachLine = Split(txtFile, vbCrLF)
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("db1.mdb")
Set connection = Server.CreateObject("ADODB.Connection")
connection.Open(sConnString)
sSQL1 = "INSERT into XXXXXXX (ProductCode, Manufacturer, Model, ProductName, Category, ShortDescription, LongDescription, ProductPageURL, SmallImageURL, LargeImageURL, PriceGBP, StockAvailability, DeliveryperiodDays, PromotionalLineRental, PromotionalPeriod, FullPriceLineRental, Network, Tariff) values"
For i = 0 to Ubound(EachLine)
EachLine2 = Split(EachLine(i), vbTAB)
For i2 = 0 to Ubound(EachLine2)
sSQL2 = " ('" & EachLine2(0) & "', '" & EachLine2(1) & "', '" & EachLine2(2) & "', '" & EachLine2(3) & "', '" & EachLine2(4) & "', '" & EachLine2(5) & "', '" & EachLine2(6) & "', '" & EachLine2(7) & "', '" & EachLine2(8) & "', '" & EachLine2(9) & "', '" & EachLine2(10) & "', '" & EachLine2(11) & "', '" & EachLine2(12) & "', '" & EachLine2(13) & "', '" & EachLine2(14) & "', '" & EachLine2(15) & "', '" & EachLine2(16) & "', '" & EachLine2(17) & "')"
Next
sSQL = sSQL1 & sSQL2
if i > 0 then
connection.execute(sSQL)
end if
Next
connection.Close
Set connection = Nothing
%>
Please Help!  |
04-13-2007, 03:46 AM
|
 |
SEO
|
|
Join Date: May 2005
Location: England, UK
Posts: 621
|
|
|
Ahhh!
What I would do is store a copy of the feed "as is" when you retreive it. Then, each subsequent time, do a text compare of the new feed with the old one - if it is not identical, then run your scripts.
I did a similar thing on a site last year. The event that triggered the whole thing was when a user looked at a page on the site containing information from the feed. The site would go and fetch a new copy and compare it with the old version, etc..
Hopefully I've undestood and this is all you need?
|
04-13-2007, 04:19 AM
|
|
SEO
|
|
Join Date: May 2005
Location: England, UK
Posts: 621
|
|
Sorry, I read your posts out of sequence there. I've checked over your code, and have corrected it - as far as I can tell.
Code:
<%
Dim URL
URL = "http://summitfeed.co.uk/three/three-affil.txt"
Dim objXMLHTTP
Set objXMLHTTP = Server.CreateObject("Msxml2.ServerXMLHTTP")
objXMLHTTP.Open "GET", URL, False
objXMLHTTP.Send
IF objXMLHTTP.statusText = "OK" THEN
txtFile = objXMLHTTP.responseText
END IF
'*** Clean up!
Set objXMLHTTP = nothing
Dim rows, i, columns, i2, sSQL, sConnString
rows = Split(txtFile, vbCrLF)
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("db1.mdb")
Set connection = Server.CreateObject("ADODB.Connection")
connection.Open(sConnString)
sSQL1 = "INSERT into XXXXXXX (ProductCode, Manufacturer, Model, ProductName, Category, ShortDescription, LongDescription, ProductPageURL, SmallImageURL, LargeImageURL, PriceGBP, StockAvailability, DeliveryperiodDays, PromotionalLineRental, PromotionalPeriod, FullPriceLineRental, Network, Tariff) values"
For x = 0 to Ubound(rows)
columns = Split(rows(x), vbTAB)
sSQL2 = " ('" & columns(0) & "', '" & columns(1) & "', '" & columns(2) & "', '" & columns(3) & "', '" & columns(4) & "', '" & columns(5) & "', '" & columns(6) & "', '" & columns(7) & "', '" & columns(8) & "', '" & columns(9) & "', '" & columns(10) & "', '" & columns(11) & "', '" & columns(12) & "', '" & columns(13) & "', '" & columns(14) & "', '" & columns(15) & "', '" & columns(16) & "', '" & columns(17) & "')"
sSQL = sSQL1 & sSQL2
connection.execute(sSQL)
Next
connection.Close
Set connection = Nothing
%>
I didn't check your SQL statement, but assuming it's fine, then this will work now.
 |
04-13-2007, 04:47 AM
|
|
SEO Junior
|
|
Join Date: Mar 2007
Posts: 26
|
|
|
I cant thank you enough BSolveIT, You are a star !!!
It worked perfectly first time
Thank You.
|
04-13-2007, 04:48 AM
|
|
SEO
|
|
Join Date: May 2005
Location: England, UK
Posts: 621
|
|
Your completely welcome - my pleasure.
 |
04-13-2007, 05:58 AM
|
|
SEO Junior
|
|
Join Date: Mar 2007
Posts: 26
|
|
|
I'm not sure when i will actually manage to get this all fully implemented in my site but when i do it should update it's self automaticly saving me the responsibility of checking it every day and some time as well.
I'll be sure to let you know how it all goes.
|
04-13-2007, 02:33 PM
|
|
SEO
|
|
Join Date: May 2005
Location: England, UK
Posts: 621
|
|
Hi Tommy
Well hopefully you've got a handle now on everything you'll need to know to implement it. If you have any problems though, just give me a shout.
All the best! 
- BSolveIT |
04-13-2007, 02:38 PM
|
|
SEO
|
|
Join Date: May 2005
Location: England, UK
Posts: 621
|
|
Oh sorry - one quick update. IN the code above that I gave you, I should really have put all the code relating to the splitting of the source file, and inserting it into the database INSIDE the IF statement block. If the attempt to retreive the text file is unsuccessful, the rest of the code will only produce errors.
Sorry, I forgot to do it for you earlier. You really need to make sure you do that, as you can end up bringing your site down if the object fails and you don't do the cleanup.
Best of luck again! |
|
|
| Thread Tools |
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
|
