Solved

Error 3146 running SQL statement in VB6

Posted on 2002-06-18
4
663 Views
Last Modified: 2013-12-25
I am using VB6 connecting to Oracle 8.0.5 database via DAO trying to select data that will be inserted into local database (oracle lite), but keep getting an error 3146: ODBC Call--Failed when trying to select the data. I have changed the original program to include dialog box that gives the user option to select a specific type of order, ie. Open, Closed, Archived.
The original SQL statement is;
strSQL = "SELECT status, " & _
            "projectNumber, " & _
            "customerNumber, " & _
            "customer, " & _
            "Office, " & _
            "CheckOutBy " & _
            "FROM Project " & _
            "ORDER BY projectNumber"
Set rsProjects = ObjServerCon.OpenRecordset(strSQL, dbOpenDynaset)
'This runs fine and I get all projects from the database


I have changed the original code to add a variable set by user via radio buttons on dialog box;

strSQL = "SELECT status, " & _
            "projectNumber, " & _
            "customerNumber, " & _
            "customer, " & _
            "Office, " & _
            "CheckOutBy " & _
            "FROM Project " & _
            "WHERE Status LIKE " & strFiltName1 & _
            " ORDER BY projectNumber"
Set rsProjects = ObjServerCon.OpenRecordset(strSQL, dbOpenDynaset)
The variable strFiltName1 shows the user selected variable correctly, but I keep getting the error. If anybody can point me in the right direction, please let me know.
If I'm leaving out any information please let me know.
Thanks,
Darrel
0
Comment
Question by:darrel
4 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 200 total points
ID: 7089761
change it to

strSQL = "SELECT status, " & _
           "projectNumber, " & _
           "customerNumber, " & _
           "customer, " & _
           "Office, " & _
           "CheckOutBy " & _
           "FROM Project " & _
           "WHERE Status LIKE '" & strFiltName1 & "%' " & _
           " ORDER BY projectNumber"
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7090608
emoreau's code is showing two things, first, if your database column Status is a text type of column (varchar, char etc.), then it's important to wrap the value that you get from strFiltName1 in Single Quotes.  If the Status column is a numeric type of column, then you do not have to wrap the value in quotes.

The other thing emoreau is doing is appending a % on the end of the value, this would give you all projects where the status "starts with" whatever you are passing in strFiltName1.  While this might work for you, or it may not be exactly what you wanted.

If your strFiltName1 is going to have any strange characters in it, like an apostrophy, then you will have to do additional work.  So, for example, if Status = Project's Closed, this will give you an error unless you double the single quote.  If that is the case, then I prefer not to try to format the SQL with the variable data concatenated, but to rather place a ? where the variable data is, and then pass the variable data in a Parameter Object appended to a command object
0
 

Expert Comment

by:sjpuzhikadu
ID: 7091318
Check this

strSQL = "SELECT status, " & _
           "projectNumber, " & _
           "customerNumber, " & _
           "customer, " & _
           "Office, " & _
           "CheckOutBy " & _
           "FROM Project " & _
           "WHERE Status LIKE " & "%" & rtrim(strFiltName1) & "%" & _
           " ORDER BY projectNumber"
Set rsProjects = ObjServerCon.OpenRecordset(strSQL, dbOpenDynaset)

if you have any problem, please let me know.

SJP
0
 

Author Comment

by:darrel
ID: 7095968
Thanks emoreau!! It worked great.
I'm also in the process of posting points for mdougan, and sjpuzhikadu and the subject will be "Points for mdougan" and "Points for sjpuzhikadu." Reason for the additional postings is
#1. I don't know how to spread points between more than one expert (there may not be a way)
#2. mdougan, I really appreciate you explaining why emoreau was doing what he was doing in the SQL statement. I've been thrust into supporting and continuing development on a program at work and I'm so not qualified, but with help from experts like yourself, I not only get help with the code I'm trying to write, but also a learning experience that will help with my next SQL statement. To me this is more important than just getting the code fix.
sjpuzhikadu, while I didn't need to trim the variable and use the wildcard, I do on another user selection, so this will help me as well.
Thanks to everybody for their help, I'm sure you'll see more posts as I continue to learn. Wish I could provide some expertise to you as well!
Darrel
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question