Solved

Error 3146 running SQL statement in VB6

Posted on 2002-06-18
4
668 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Suggested Courses
Course of the Month4 days, left to enroll

630 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