Solved

Error 3146 running SQL statement in VB6

Posted on 2002-06-18
4
661 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 69

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now