Results 1 to 11 of 11

Thread: [RESOLVED] SQL statement problem

  1. #1

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    790

    Resolved [RESOLVED] SQL statement problem

    I am writing as sql statement in VB6 code, I have two where clause parameters. One is simple. I just pass a parameter to the function and the sql statement picks it up correctly.

    The parameter value is a space, for example

    "WHERE TBL.NAME = '" & PASSEDARGUMENT & "' "
    "AND TBL.NAME2 <> " & space

    How do I represent a space and end the query?

    Thanks

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

    Re: SQL statement problem

    ?
    "AND TBL.NAME2<>'" & Chr(32) & "'"
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE 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.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: SQL statement problem

    Ideally you'd use a proper parameter query instead of cobbled together "dynamically" hard-coded SQL as you're doing.

    But barring that why wouldn't you just use:

    "AND TBL.NAME2<>"" """

    or:

    "AND TBL.NAME2<>' '"

  4. #4

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    790

    Re: SQL statement problem

    I get Err code 3095 Too Few Parameters 1

    strSQL = "SELECT tblNAME.Field1, tblNAME.Field2 " strSQL = strSQL & "From tblNAME "
    strSQL = strSQL & "WHERE tblNAME.Field1 = '" & sParam & "' "
    strSQL = strSQL & " AND tblNAME.Field2 <> '" & Chr(32) & "'"

  5. #5

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    790

    Re: SQL statement problem

    dilettante
    with both your suggestions I still get Err code 3095 Too Few Parameters 1

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL statement problem

    Then you're having a typo in your fieldname
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE 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.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: SQL statement problem

    Or the text in sPAram has a ' in it and is breaking the string... this is why you use parameters... Also, post the SQL that's causing the error. And not a screenshot of it it. It's text. You can print it out to the console and copy it. You can set breakpoints and see what the value is...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    790

    Re: SQL statement problem

    Very interesting. I copied the SQL statement into the database, It ran perfectly

  9. #9

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    790

    Re: SQL statement problem

    If I can copy the SQL statement and put it into the DB and it runs correctly, What does that mean?

  10. #10
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: SQL statement problem

    Code:
    strSQL = "SELECT tblNAME.Field1, tblNAME.Field2 " strSQL = strSQL & "From tblNAME "
    strSQL = strSQL & "WHERE tblNAME.Field1 = '" & sParam & "' "
    strSQL = strSQL & " AND tblNAME.Field2 <> '" & Chr(32) & "'"
    Is not a valid way to code. You have a second assignment on the first line. If that compiles then you are getting something much different than you expect.

    Are your fields actually named Field1 and Field2? If so then you really should consider changing those names to something more descriptive of what the field will contain.

    Have you used debug.print to output the content of strSQL? Is that what you used in the DB query? If not then you are testing apples to oranges and can not trust the results.

  11. #11

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    790

    Re: SQL statement problem

    I have found the problem.

    1. The field name are NOT Field1 etc. That was not the problem.

    2. The problem was I was accessing the WRONG DB. When I changed the directory, in the code, the sql statement worked perfectly.

    Sorry for the hassle. The original problem, using a <> space, was valid. I had trouble with that. he rest was an error on my part.

    Thanks

Posting Permissions

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



Click Here to Expand Forum to Full Width