Moving data between databases using ASP

  • Hi,

    I am using ASP to extract information from a database on a remote server (SQL 7) and updating/inserting into a local database.

    The last column is an image. I add the data now without the image column and now want to update the image column based on the rows I have added or changed.

    Here is the code I have tried:

    ****(idList is a number list, ie 123456,654321,253614

    oConn and oConn2 are the 2 server connections****

    mySQL2 = "Select count(*) NUMBER from mlsvel where id in (" & idList & ") and medium_photo is not null"

    set rsTemp1 = oConn2.Execute(mySQL2)

    rsPCount = rsTemp1("NUMBER")

    mySQL3 = "Select id, medium_photo from mlsvel where id in (" & idList & ") and medium_photo is not null"

    set rsTemp2 = oConn2.Execute(mySQL3)

    Do While Not rsTemp2.EOF

    mySQL = "update mlsvel set medium_photo = " & rsTemp2("medium_photo").GetChunk(1024000) & " where id = " & trim(rsTemp2("id"))

    oConn.Execute (mySQL)

    How can I pull the images into a recordset and then update the local database from the recordset?

    Thanks!

  • You're trying to mix two techniques. The easiest way is to use two ado recordsets, so you're just assigning the contents of one column to another, like this:

    rsDestination.fields("ImageField")=rsSource.fields("ImageField")

    Getchunk works, but you should take a look at the stream object, it hides the "chunking". I havent tested the direct assignment with large objects to see if it always works or not.

    Andy

  • I am not sure how I would use this. Could you give me an example please?

    Also, any good sources to find out about the object stream?

    Thank you Andy

  • I'll try to get some sample code up tomorrow or Friday, got a lot going on this week. In the interim if you search MSDN for ado stream you should find some good stuff.

    Andy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply