Solved

Same Error, different statement - Error 3146 running SQL statement in VB6

Posted on 2002-06-24
10
251 Views
Last Modified: 2013-12-25
My setup is identical to the previous question I asked, and the situation is almost identical. The only difference is that I now need to run a select statement that queries on 2 fields and I'm still getting the 3146 error. Can I get one of you guys to look at this again and see if you can see what I'm doing wrong. I made the changes you suggested in previous question, and it worked great. 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 2 variables set via dialog box where user selects a value from drop down.
The SQL statement is as follows;
strSQL = "SELECT status, " & _
            "projectNumber, " & _
            "customerNumber, " & _
            "customer, " & _
            "Office, " & _
            "CheckOutBy " & _
            "FROM Project " & _
            "WHERE Status='" & strFiltName1 & "' " & _
          "AND WHERE Customer='" & strFiltName2 & "' " & _
            "ORDER BY projectNumber"

strSQLCnt = "SELECT COUNT(*) as TotalProject " & _
   "FROM Project WHERE Status='" & strFiltName1 & "' " & _
        "AND WHERE Customer='" & strFiltName2 & "' "
The strsql statement is the one that sends program into error handler.
Let me know if I need to provide any further information.
Thanks for the help!!
0
Comment
Question by:darrel
  • 5
  • 3
  • 2
10 Comments
 
LVL 5

Expert Comment

by:rpai
ID: 7104817
Darrel,
The error is very generic.
Please try to gather somewhat detailed error message as mentioned in the article here:-http://support.microsoft.com/default.aspx?scid=kb;EN-US;q161288
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7105027
The quick answer is that you have two WHERE clauses in your SQL Statement:

Where ....
AND Where....
0
 
LVL 18

Accepted Solution

by:
mdougan earned 200 total points
ID: 7105076
A few things.  First, I always put a breakpoint on the instruction immediately following the one you gave us forming the SQL Statement, and then I go to the immediate window and tell it to:

Print strSQL    or
? strSQL

This will display the contents of strSQL and you can quickly see if there are any problems, such as if you have forgotten to include a space before the Where clause, or perhaps your customer name has a single quote in it etc.

Most of the time, the cause of the error will be obvious.

Second, double-check the data types of the columns mentioned in your Where clause.  You are wrapping Status and Customer in single-quotes.  That is fine, as long as they are defined in Oracle as some kind of text column like varchar.  However, if either of them is a numeric column, then you can't use the single-quotes.

Third, to elimiate the need to worry about the single-quotes, I prefer to use Command objects and parameter objects.  You could change your code to this:

Dim CMD As ADODB.Command
Dim PARM1 As ADODB.Parameter
Dim PARM2 As ADODB.Parameter
Dim RS As ADODB.Recordset
Dim strSQL As String

' I do this instead of line continuation as it is easier to line things up
' Also, I find it is easier to see spaces at the front of each line instead of at the end
    strSQL = "SELECT status,"
    strSQL = strSQL & " projectNumber,"
    strSQL = strSQL & " customerNumber,"
    strSQL = strSQL & " customer,"
    strSQL = strSQL & " Office,"
    strSQL = strSQL & " CheckOutBy"
    strSQL = strSQL & " FROM Project"
    strSQL = strSQL & " WHERE Status=?"
    strSQL = strSQL & " AND Customer=?"
    strSQL = strSQL & " ORDER BY projectNumber"

' In case (unlikely) they were open, close them and free up the resources
    Set CMD = Nothing
    Set PARM1 = Nothing
    Set PARM2 = Nothing
   
' Create New empty instances
    Set CMD = New ADODB.Command
    Set PARM1 = New ADODB.Parameter
    Set PARM2 = New ADODB.Parameter
   
' The the properties for the command object, assumes CN, an ADODB.Connection has already been opened
    With CMD
        .ActiveConnection = CN
        .CommandType = adCmdText
        .CommandText = strSQL
    End With
   
' Set the properties for the first parameter.  Just for example sake, I'm pretending that Status is
' an integer column defined in the DB
    With PARM1
        .Direction = adParamInput
        .Type = adInteger
        .Value = CInt(txtStatusCd.Text)
    End With
   
' Parameters must be appended in the order they appear in the SQL Statement
    CMD.Parameters.Append PARM1
   
' Set the properties for the second parameter.  This shows a character column, notice, no
' special code for wrapping in single quotes.  And, this code will work if the customer column
' happens to contain an embedded single quote like   O'Brian
    With PARM2
        .Direction = adParamInput
        .Type = adVarChar
        .Size = 50
        .Value = txtCustomerName.Text
    End With
   
    CMD.Parameters.Append PARM2
   
' Now, declare the new recordset
    Set RS = New ADODB.Recordset
' Here, you could set recordset properties like locktype, cursortype etc.
   
' Tell the recordset to get it's data from the command object
    Set RS.Source = CMD
   
' This runs the query
    RS.Open
   
' As long as we're here, this is a way to ensure that you cover all possible bases
' for the recordset's Open statement.  If you try to check EOF on a Closed recordset
' that will generate a run-time error
    If RS.State = adStateOpen Then
        If Not RS.EOF And Not RS.BOF Then
           While Not RS.EOF
              '...
           Wend
        Else
           MsgBox "No Records Were Found Matching that Criteria"
        End If
' This is the best place to close the recordset as we've already determined that it was open
        RS.Close
    Else
        MsgBox "Error Opening the Recordset"
    End If
   
' Set everything to nothing to free up the resources (assuming that you are done with the Recordset)
    Set RS = Nothing
    Set PARM1 = Nothing
    Set PARM2 = Nothing
    Set CMD = Nothing

Fourth, double-check the spelling of the column names and ensure that they match the column definitions in the DB.  Often, it's a simple typo that causes the problem (usually it generates a different error code though, I think).

Oh, and also you might want to set up an error handler that looks like this:

On Error GoTo ErrorRtn

.......

ExitRtn:
   Exit Sub
ErrorRtn:
    Dim adoErr As Error
    Dim sMsg As String

    For Each adoErr In CN.Errors
        With adoErr
            sMsg = "Error # " & Str(.Number) & " was generated by " _
                 & .Source & vbCr & .Description
           
            MsgBox sMsg, , "Error: CN: cmd_retrieve", .HelpFile, .HelpContext
        End With
    Next

    GoTo ExitRtn
0
 
LVL 5

Expert Comment

by:rpai
ID: 7105085
mdougan, Great catch!
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7105280
I only saw it after I reformatted the SQL in the way that I prefer:

   strSQL = "SELECT status,"
   strSQL = strSQL & " projectNumber,"
   strSQL = strSQL & " customerNumber,"
   strSQL = strSQL & " customer,"
   strSQL = strSQL & " Office,"
   strSQL = strSQL & " CheckOutBy"
   strSQL = strSQL & " FROM Project"
   strSQL = strSQL & " WHERE Status=?"
   strSQL = strSQL & " AND Customer=?"
   strSQL = strSQL & " ORDER BY projectNumber"

Then, it was easier for me to see.
0
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

 

Author Comment

by:darrel
ID: 7107391
mdougan,
Thanks much for the tutorial!!! I like the structure of SQL statement the way you write it much better, it is easier to read.
I appreciate you taking time to explain this, no matter how many times I go thru books etc., things aren't nearly as clear as when someone explains it who has evidently done it before. You wouldn't happen to be an instructor, would you? You explain things very well!
Thanks again,
Darrel
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7107687
Hi Darrel,  thanks.  Yes, in fact, I taught VB, C++ and other languages for several years.  If you click on a person's underlined name in one of their comments, you can go and read their profile if they've left one.  I haven't updated mine for a while, but I mention the teaching there.

One other advantage to doing it this way, is that there is a maximum number of allowable line continuations, and I have hit cases where I had a SQL statement that was bigger than the allowable max (I forget what the amount is, but it's something like 64 lines).  Also, under VB 3, even if you were below the maximum, I found that having too many line continuations would cause corruptions in the forms.  So, you'd write your code, save the form, come back the next day, and maybe a few of the lines in the middle of the pack would be missing, and/or maybe just half the line would be there!  So, after that, I stick with this method.  Also, this method allows you to comment out one or two lines in the middle without affecting any of the other lines.  When you use the line continuation it doesn't allow you to comment out sections in the middle.  So, if you want to temporarily comment out a couple of columns in the select, you can't do it the other way.
0
 
LVL 5

Expert Comment

by:rpai
ID: 7107734
mdougan,
No wonder you are simply great with the explanations!
0
 

Author Comment

by:darrel
ID: 7108480
mdougan,
I hate to keep bothering you, but I've got another question. I read your bio and you've been working in development for 15yrs, and thought you'd be a good person to ask this.
Is there a preferred path to follow in becoming a better programmer/developer? There is the college route, Microsoft Certification, and I guess just learning on your own. What kind of advice would you give someone who wants to continue in development? I work for a telecommunications company supporting and developing applications for our detail engineering group. Unfortunately, I can't get the company to train me, unless I create a degree path then they will pay for college. So I've basically bought books and spent many hours at home so I could do my job during the day. Last year before the bottom fell out of the telecom industry(we've laid off approx. %80 of our workforce, and I've made it this far), I had a couple of headhunters, who saw my resume on Monster, tell me that if I had more programming experience I could basically write my ticket in telecom. They told me there were few if any programmers with my background in telecom(approx. 16 years).
I appeciate any advice you might have!
Darrel
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7109394
Well, you didn't say how much programming experience you have, or if your progamming experience is mostly what you've done on your own.  If that is the case, then like the headhunter said, with just a little bit of official programming experience you can start to write your own ticket.

I worked as a full-time programmer for two years, and then I decided to switch to consulting.  At first I was afraid that they would expect me to be some real wizard, but really, a consultant is just like any full-time programmer, it's just that the company doesn't have to pay all of your health insurance, vacation pay and other benefits, and it's much easier to let you go.

That sounds like a big disadvantage, but with consulting, you can make probably 4 to 5 times what you would make as a full-time employee, so, you could pay for your own insurance and take 8 months of the year off and still make as much money....

As far as learning the technical stuff, I wouldn't take the college route at all.  They move too slowly and don't really teach what you need to know.

The best thing would be to take Microsoft Certified courses, and maybe getting the MCSE.  But, that will be expensive if your company won't pay for it.

If you can't take the classes, then the best way to gain technical proficiency is to just build your own programs at home at night.  I did that for most of that 15 years (actually, now 17 years, and I'm still writing software at home).  Just keep getting technical books and keep going through them, and that way you'll learn better programming techniques.

There is one less conventional approach that really worked for me.  I started out as a mainframe programmer and did mainframe consulting for about 8 years.  Then, when it got harder to find contracts as a COBOL programmer, I got a job at a technical training company.  They didn't care that I didn't know many PC programming tools, instead, they sent me to the Microsoft Certified classes.  After I finished the classes I was "certified" to teach them!  In fact, I taught a VB class the week after I'd taken that class myself!

When you teach a class, you really learn the material.  First, because you have to go over and over the material to know it well for the class, but also because then your company will keep sending you to classes and/or giving you new training materials to teach different VB course material.  You will learn a ton of stuff.

I learned VB, Powerbuilder, C++, Lotus Notes, SQL Server this way.

And, if you are a freelance instructor, you can ask for about $600 a day, which is pretty good pay.  You don't work every week, only when they can schedule a class, so it evens out over the month, but, you get to travel some and it's pretty cool.

Then, after a year or two, you have everything you need to be a programming consultant and charge (depending on where you live) up to a couple hundred dollars per hour!

So, my advice is to get as much training and experience as you can get, but to get other people to pay for it.  If you can't find a way to get your employer to pay for it, then just do it yourself at home.  Once you feel that you have 1 to 2 years of pretty good programming experience, then go and start up your own Corporation, and call the headhunters and tell them you want to go "1099", that means that they pay your company, and your company cuts you a paycheck.  You might have to lie and tell them that you have at least 5 people in your company and that you have an office somewhere etc. but that's just because the section 1099 tax laws governing corp to corp payments are a little quirky.

Once you are a consultant, you'll find that you move from interesting project to interesting project, and you'll gain a lot more experience a lot faster than as a full-time employee.  And, with your telcom experience, you should command a pretty high rate.

Well, let me know if you have other questions.  Good Luck.
Mike

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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…
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 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…

759 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