Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jan 2009
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Question SQL Server: Copying from one DB to another

    Hi I have a database with photos and text. I can copy the text from one DB to another without any problems but I'm having trouble copying the photos. Someone suggested using an INSERT Statement. I'm not sure how it works but this is what I have so far. Can anyone help me I'm really stuck.

    'Read in the record number to be inserted
    RecordNo3 = CLng(Request.QueryString("ID"))

    'Create an ADO connection object
    Set Conn3 = Server.CreateObject("ADODB.Connection")

    'Set an active connection to the Connection object using a DSN-less connection
    Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("\db\FileDB.mdb")

    'Create an ADO recordset object
    Set oRS3 = Server.CreateObject("ADODB.Recordset")

    'Initialise the strSQL variable with an SQL statement to query the database
    sSQL3 = "SELECT Files.* FROM Files WHERE ID=" & RecordNo3

    Set oRS3 = Conn3.Execute(sSQL3)

    'Reset server objects
    Set Conn3 = Nothing

    'Create an ADO connection object
    Set Conn4 = Server.CreateObject("ADODB.Connection")

    Conn4.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("\db\FileDB_bak.mdb")

    'Create an ADO recordset object
    Set oRS4 = Server.CreateObject("ADODB.Recordset")

    sSQL4 = INSERT INTO Files VALUES oRS3

    Set oRS4 = Conn4.Execute(sSQL4)

    'Reset server objects
    oRS4.Close
    Set oRS4 = Nothing
    Set Conn4 = Nothing


    This is the line I'm having problems with sSQL4 = INSERT INTO Files VALUES oRS3 not sure how to put two SQL statement together? Thanks
    Last edited by guelphdad; 01-02-2009 at 04:53 PM.

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,852
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Please read http://www.codingforums.com/postguide.htm
    1) Do not cross post your question in multiple forum categories- When posting, there is no excuse to post the same question in multiple categories in hopes of getting a quicker response. This is called spamming, and can get you banned. Choose one category that best accommodates your question, and post it there, once. If you're following up on a question, reply to the original thread, not start a new one! Everyone here is volunteering their time to help out others. The least you can do is make their jobs easier and less confusing.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    you are not using mysql as your database, as such your question belongs in the general database forum and the subject marked as sql server along with the subject.

    second, in my opinion, you should not store images in your database at all, store them in your data directory on your server and store a path to the file in the database itself.

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Is this a MySQL database you are using?

    (edit) sorry, I had this post up for 30 minutes before replying, in which time my question got deprecated.

  • #5
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    you can easily copy records to access, by using IN in your insert query:

    INSERT INTO targettablename IN accessfilename SELECT * FROM sourcetablename

    for the accessfilename, you have to specify the full filename, including path. Execute this query against an open connection to the SQL database.

    some documentation: http://office.microsoft.com/en-us/ac...322661033.aspx
    I am the luckiest man in the world

  • Users who have thanked Roelf for this post:

    Emma123 (01-06-2009)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •