Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Feb 2005
    Location
    Thane, Maharashtra,India
    Posts
    17

    Question Unanswered: SQL Server equivalent to Access UCASE and TRIM (was "Solution needed Urgently!!!")

    Please provide me with this queries solution


    "select leadsource_id from Leadsource_Master where trim(ucase(leadsource_name))='" & Address_Module.name_check(Trim(UCase(cmbLeadSource ))) & "'"

    It is a query in access, I want to use the same in SQL SERVER 2000 using VB 6.0

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sure you could find the solution for yourself if you would only take a moment to have a quick look at the manual

    however, i shall give you a couple of hints

    the sql server equivalent to the access UCASE function is UPPER

    there is no direct equivalent to TRIM, sql server uses RTRIM and LTRIM
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    As for the sql, just change the change the UCASE to UPPER and the same syntax should work. Do you know how to connect to SQL Server from VB6? Do you know how to send your query to SQL Server from VB 6? The standard is called Active X Data Objects (ADO). You will want to know about Connection and Recordset objects.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by r937
    there is no direct equivalent to TRIM, sql server uses RTRIM and LTRIM
    The TRIM function appears to be part of his VB6 code where he is concatenating together his sql string and therefore it should still be valid.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus
    The TRIM function appears to be part of his VB6 code ....
    yes, but it is also being used in the sql --

    "select leadsource_id from Leadsource_Master where trim(ucase(leadsource_name))='" & Address_Module.name_check(Trim(UCase(cmbLeadSource ))) & "'"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    not enough coffee yet.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2005
    Location
    Thane, Maharashtra,India
    Posts
    17
    but Upper is not a valid fuction in VB 6.0 and I need a code which will work both in ACCESS and SQL server for the application developed in VB 6.0

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you will probably need something like

    IF access THEN access sql ELSE sql server sql END

    it's not possible to use the same sql against both microsoft databases
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Case conversion is really a presentation issue. It ought to be handled at the client, not via the server anyway.

    Retrieve your data using SQL. Format your data (using Trim() and UCase() as needed) using VB.

    -PatP

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    Retrieve your data using SQL.
    you're making this sound a lot easier than it really is

    i might agree about the case issue, but how do you "retrieve" with the same SQL if you want to use LIKE with a wildcard? answer: you can't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can't retrieve data from SQL databases, Microsoft Jet, textfiles, and the USB port with the same syntax in VB either.

    -PatP

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thank you for underscoring the validity of my earlier post --

    IF access THEN access sql ELSE sql server sql END

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by vaisal
    but Upper is not a valid fuction in VB 6.0 and I need a code which will work both in ACCESS and SQL server for the application developed in VB 6.0

    Just put this code in a module in your Access database:

    Code:
    Function UPPER(string_value As String)
    UPPER = UCase(string_value)
    End Function
    Voila'

    Now UPPER is a valid MSAccess function...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hey blindman, that's pretty elegant

    how would you deal with this in access --

    ... WHERE columnname LIKE '%keyword%'

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Elegant? No, its a hack.

    The LIKE one is tough though. I though it might be possible to create a function in Access and another identically named function in SQL Server so that they could be used interchangeably, but SQL Server's function calls requires that the owner be included: select dbo.MyLike([MyString])
    ...and Access VB won't accept function names like "dbo.MyLike".

    Ultimately, this guy is heading upstream without a paddle. I doubt that this is the last hurdle he will have to overcome in creating code that will run seamlessly on both Access and SQL Server.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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