SQLExecQuery help
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: SQLExecQuery help

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Vancouver, Canada
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I'm using the SQLExecQuery command to pull data from my database. But I want to use a cell within the Excel worksheet as part of the query.
    ie.
    "SELECT CLIENTS.NAME FROM DATA.CLIENTS WHERE CLIENTS.CLIENT_ID = ??"

    Where ??, I'd like to reference cell B4.
    Is this possible?
    What's the formula?
    Thanks

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ="SELECT CLIENTS.NAME FROM DATA.CLIENTS WHERE CLIENTS.CLIENT_ID = '"&B4&"'"


    [ This Message was edited by: Mark W. on 2002-04-09 16:18 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Vancouver, Canada
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the help Mark, but I tried your suggestion and I can't get it to work.

    Have you any other suggestions please?

    Thanks

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I always tie myself in knots with quotation marks but you could try...

    "SELECT CLIENTS.NAME FROM DATA.CLIENTS WHERE CLIENTS.CLIENT_ID = " & Range("B4").Value

    (I'm not sure whether the B4 needs a double set of quotes on either side??)

    Or, have a variable...

    myClientID = Range("B4").Value

    Then

    "SELECT CLIENTS.NAME FROM DATA.CLIENTS WHERE CLIENTS.CLIENT_ID = " & myClientID



  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, I didn't recognize this as a VBA command... and, confused it with the SQL.REQUEST worksheet function. VBA isn't my forte, but couldn't you assign Rang.("B4") to a variable and concatenate that to your string? Like they did with "chan" in the example from the SQLExecQuery Function help topic...

    databaseName = "Northwind"
    queryString = _
    "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)"
    chan = SQLOpen("DSN=" & databaseName)
    SQLExecQuery chan, queryString
    Set output = Worksheets("Sheet1").Range("A1")
    SQLRetrieve chan, output, , , True
    SQLClose chan

  6. #6
    New Member
    Join Date
    Apr 2002
    Location
    Vancouver, Canada
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You help does make sense, but I still can't get it to work.

    If I enter a CLIENT_ID ie 'GW0004' into the script, it works fine.
    But not when I try to reference a cell on the worksheet.

    Here's all the code, just incase I've screwed up somewhere else.
    Your continuing help is much appricated.

    Sub LeaseName()
    Dim DB As Variant
    Dim MYID As Variant

    MYID = Range("B5").Value

    DB = SQLOpen("DSN=Database;UID=User;PWD=Password")

    SQLExecQuery DB, "SELECT CLIENTS.NAME FROM DATA_MERGED.CLIENTS CLIENTS WHERE CLIENTS.CLIENT_ID = " & MYID

    SQLRetrieve DB, ActiveCell

    SQLClose (DB)

    End Sub

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is beginning to bug me coz I know it can work! I do the same thing with a mySQL database and have no problems.
    My exact code (well, relevant bits anyway) is below...



    Dim Chan As Variant
    Dim MyBase As Variant
    Dim myCompany As Variant

    MyBase = "DSN=alexismedical;DB=alexismedical;SERVER=10.10.10.254;UID=xxxx;PWD=xxxx;PORT=;OPTION=3098;STMT=;"

    Range("D" & currentrow).Select
    myCompany = ActiveCell.Value


    Chan = SQLOpen _
    (MyBase, , 2)

    SQLExecQuery Chan, _
    "SELECT company_id, lookup_label FROM company_lookup WHERE lookup_label='" & myCompany & "' "

    SQLRetrieve Chan, ActiveSheet.Range("E" & currentrow), , , False, False, False, False

    SQLClose (Chan)



    Now, the only discernible difference I can see is that I wrap my variable in the SQL statement with 'single' quotes.

    Try this. If it doesn't work then I think I'm well stumped.

    Rgds
    AJ


  8. #8
    New Member
    Join Date
    Apr 2002
    Location
    Vancouver, Canada
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for help...
    Works great!!!

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Cool!
    Very pleased it worked!

    And, just for your information, I've just found out why (thanks to a know-it-all mate sat a few desks away!).

    For any ANSI compliant database system when you write a query with alpha or alphanumerics in it, you have to enclose them in single quotes so that it can tell whether you are looking for a value in a column or an another actual column itself in the table.

    For example, if you say

    SELECT ... FROM TABLE... WHERE COLUMNX=BLAHBLAHBLAH,

    it will look for a column BLAHBLAHBLAH and see if the value in COLUMNX equals it!

    However, if the BLAHBLAHBLAH is in quotes, it will look for the item BLAHBLAHBLAH within COLUMNX.

    For numeric stuff though, it's cool (as long as the properties of that field in the table is defined as numeric of course).

    The fact that Excel is the database interface is irreleveant when using the xlodbc.xla addin. It sends the query to the database exactly as if you'd done it to the database directly.

    Enough of my ramblings!
    Hope this makes sense!

    Cheers
    AJ



User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com