asked on

Error 3146 running SQL statement in VB6

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

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mdougan

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

sjpuzhikadu

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

darrel

ASKER

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