Improve company productivity with a Business Account.Sign Up

x
?
Solved

Error 3146 running SQL statement in VB6

Posted on 2002-06-18
4
Medium Priority
?
674 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 71

Accepted Solution

by:
Éric Moreau earned 800 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

607 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