-
Nov 17th, 2021, 10:46 AM
#1
Thread Starter
Fanatic Member
[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
-
Nov 17th, 2021, 10:47 AM
#2
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
-
Nov 17th, 2021, 10:56 AM
#3
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<>' '"
-
Nov 17th, 2021, 10:59 AM
#4
Thread Starter
Fanatic Member
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) & "'"
-
Nov 17th, 2021, 11:04 AM
#5
Thread Starter
Fanatic Member
Re: SQL statement problem
dilettante
with both your suggestions I still get Err code 3095 Too Few Parameters 1
-
Nov 17th, 2021, 11:05 AM
#6
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
-
Nov 17th, 2021, 11:33 AM
#7
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
-
Nov 17th, 2021, 11:50 AM
#8
Thread Starter
Fanatic Member
Re: SQL statement problem
Very interesting. I copied the SQL statement into the database, It ran perfectly
-
Nov 17th, 2021, 12:13 PM
#9
Thread Starter
Fanatic Member
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?
-
Nov 17th, 2021, 12:26 PM
#10
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.
-
Nov 17th, 2021, 12:39 PM
#11
Thread Starter
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|