PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Change a Path via SQL-VBForums
Results 1 to 17 of 17

Thread: Change a Path via SQL

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,345

    Change a Path via SQL

    Hi to all:

    I would like to chance a path in my database via SQL, but sincerelly i don't know the way to do this:

    Example:

    I have in my database this:
    E:\WP\image.bmp
    E:\WP\fotosindividuais\IMG_001.jpg

    I would like to chance the path to:
    C:\WBP\ .....keeping the rest of the path

    Any help
    Thanks

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,682

    Re: Change a Path via SQL

    In SQL Server, something like this should do the trick:
    sql Code:
    1. UPDATE MyTable SET MyColumn = REPLACE(MyColumn, 'E:\WP\', 'C:\WBP\')
    Other databases would likely support something similar.

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,345

    Re: Change a Path via SQL

    Hi,

    Thanks for the reply...
    I had forgot one think....The problem it just keeping the final part of the path

    Any way to exclude all the path to \image.bmp or \fotosindividuais\IMG_001.jpg and then insert all the path again? like:

    C:\WBP\image.bmp
    or C:\WBP\fotosindividuais\IMG_001.jpg

    Thanks


    Thanks

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,345

    Re: Change a Path via SQL

    Hi,

    i had write this code:

    Code:
    anocriacao= "2019"
    s1 = pathfoto
    Var_Path_diretoria_Nova = "E:\WBP\"
    Var_Path_diretoria_Antiga = "C:\WBP"
    
    SQL = "update " & anocriacao & "  set " & s1 & " = '" & Var_Path_diretoria_Nova & "' where " & s1 & " = '" & Var_Path_diretoria_Antiga & "'"
    db.Execute SQL

    In my database i have records like:
    C:\WBP\image.bmp
    C:\WBP\fotosindividuais\image.bmp


    Like you see in my SQL, he can't update because i don't have mentioned all the path, but just part of the path.

    Wich way i can just put on query parte of the path? like "E:\WBP" and replace it?

    THanks

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,345

    Re: Change a Path via SQL

    Your sugestion is:

    Code:
    anocriacao= "2019"
    s1 = pathfoto
    Var_Path_diretoria_Nova = "E:\WBP\"
    Var_Path_diretoria_Antiga = "C:\WBP"
    
    SQL = "update " & anocriacao & "  set " & s1 & " = replace (" & s1 & ", '" & Var_Path_diretoria_Antiga & "', '" & Var_Path_diretoria_Nova & "')"
    
    db.Execute SQL
    but sort an error:
    Undefined function "Replace" in expression

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,682

    Re: Change a Path via SQL

    What database are you using?

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,345

    Re: Change a Path via SQL

    Database Access

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,682

    Re: Change a Path via SQL

    Go back and READ post #2 instead of just copying and pasting the code.

  9. #9

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,345

    Re: Change a Path via SQL

    This:
    Other databases would likely support something similar.
    And can you tell me what is the Function that is similar to Replace for Access Database?

    Thanks

  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,682

    Re: Change a Path via SQL

    Are you arms painted on? Look it up.

  11. #11

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,345

    Re: Change a Path via SQL

    I searched, but not find any function similar to "Replace" for MSAccess...better all the informations converge to Replace

  12. #12
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,682

    Re: Change a Path via SQL

    When I took the time to do the research that you should have, I found that the Access REPLACE function is VBA, like many functions apparently, and thus not available via Jet or presumably ACE. I saw mention of some dodgy workarounds but if you're interested in one then I'll leave it to you to find and test something appropriate. Just another reason to use a proper database I suppose. SQLite is the recommended option for file-based databases and it has a similar REPLACE function.

  13. #13

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,345

    Re: Change a Path via SQL

    Hi,
    Maybe in the future i can use SQLite, but for now i need to arrange a way to resolve this problem in access

    Thanks

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,149

    Re: Change a Path via SQL

    Quote Originally Posted by sacramento View Post
    Hi,
    Maybe in the future i can use SQLite, but for now i need to arrange a way to resolve this problem in access

    Thanks
    And your problem is what?
    Then just do a SELECT in your Frontend-Code to retrieve your old path AND your PrimaryKey
    Change Old path to new Path
    UPDATE to new Path using your PrimaryKey to point it to the correct record.
    maybe 4-5 lines of code
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  15. #15

    Re: Change a Path via SQL

    Hello, @sacramento

    Follow this step,To Change a Path via SQL

    Step 1:-Right Click on Server and Select "Properties".
    Step 2:-in the "Server Properties" dialog box, navigate to "Database Settings" tab and data/log files location under "Database default locations" group. ...
    Step 3:-Click on "OK" to apply changes.

    I hope this information will be usefull for you.
    Thank you.

  16. #16
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,682

    Re: Change a Path via SQL

    Quote Originally Posted by Prahlad View Post
    Hello, @sacramento

    Follow this step,To Change a Path via SQL

    Step 1:-Right Click on Server and Select "Properties".
    Step 2:-in the "Server Properties" dialog box, navigate to "Database Settings" tab and data/log files location under "Database default locations" group. ...
    Step 3:-Click on "OK" to apply changes.

    I hope this information will be usefull for you.
    Thank you.
    I don't think that you're answering the question that was asked.

  17. #17
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,440

    Re: Change a Path via SQL

    Quote Originally Posted by jmcilhinney View Post
    Are you arms painted on?
    Never heard that one
    Please remember next time...elections matter!

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width