Solved

error '80040e14'

Posted on 2004-03-20
19
629 Views
Last Modified: 2008-02-01
I'm using this SQL statement:
       strSQL = "SELECT tblItem.ItemName, tblItem.ItemPartNum, "&_
             "tblItem.ItemActive, tblItem.ItemPrice, tblItem.ItemSupp, "&_
             "tblItem.ItemDesc, tblItem.ItemShortDesc, tblItem.ItemPic, "&_
             "tblItem.ItemThumb, tblItem.ItemALT, tblItem.ItemMan "&_
            "FROM tblItem"

When I do a response.write for the output statement I get:
SELECT tblItem.ItemName, tblItem.ItemPartNum, tblItem.ItemActive, tblItem.ItemPrice, tblItem.ItemSupp, tblItem.ItemDesc, tblItem.ItemShortDesc, tblItem.ItemPic, tblItem.ItemThumb, tblItem.ItemALT, tblItem.ItemMan FROM tblItem

and it runs fine in Access, but on the page I get a big flaming:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

Any ideas?

Thanks!
bvinson
0
Comment
Question by:bvinson
  • 7
  • 6
  • 3
  • +2
19 Comments
 
LVL 8

Expert Comment

by:fozylet
ID: 10642854
tried [tblItem] using [] ?!
i can't see any logic to that suggestion... still...! ;P
0
 
LVL 6

Author Comment

by:bvinson
ID: 10642860
A swing and a miss.

I'd thought about delimiters as well, but couldn't see that anything might conflict with a standard.
I tried your suggestion anyway and still came up short.

SELECT tblItem.ItemName, tblItem.ItemPartNum, tblItem.ItemActive, tblItem.ItemPrice, tblItem.ItemSupp, tblItem.ItemDesc, tblItem.ItemShortDesc, tblItem.ItemPic, tblItem.ItemThumb, tblItem.ItemALT, tblItem.ItemMan FROM [tblItem]
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.


Thanks for playing!!  :)
bvinson
0
 
LVL 23

Expert Comment

by:adilkhan
ID: 10642928
Try

SELECT tbl.ItemName, tbl.ItemPartNum, tbl.ItemActive, tbl.ItemPrice, tbl.ItemSupp, tbl.ItemDesc, tbl.ItemShortDesc, tbl.ItemPic, tbl.ItemThumb, tbl.ItemALT, tbl.ItemMan FROM tblItem as tbl
0
 
LVL 6

Author Comment

by:bvinson
ID: 10642936
Sorry, no dice.

The output:
SELECT tbl.ItemName, tbl.ItemPartNum, tbl.ItemActive, tbl.ItemPrice, tbl.ItemSupp, tbl.ItemDesc, tbl.ItemShortDesc, tbl.ItemPic, tbl.ItemThumb, tbl.ItemALT, tbl.ItemMan FROM tblItem as tbl
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

I didn't know you could structure a SQL statement like this....and it looks like Access didn't either...but I was willing to give it the ole' college try.

bvinson
0
 
LVL 8

Expert Comment

by:fozylet
ID: 10642945
Not as a solution, but just out of curiosity...

Does this work? SELECT * FROM tblItem

do inform if you settle this out-of-court!
0
 
LVL 23

Expert Comment

by:adilkhan
ID: 10642947
Syntax is Correct, I would Start Dbugging by Grabbing only one Column and then keep on Adding to see where the Problem happens.
Also try Select * from tblItem and see what happens
0
 
LVL 6

Author Comment

by:bvinson
ID: 10642964
Negative on the SELECT * FROM tblItem.

adikhan - thank you for your additional suggestion, but it unfortunately fails as well.

I couldn't grab any of the fields - but this exact same statement succeeds on another page in the same directory, using the same db connection include file.  The only thing that has changed it the name of the Recordset object and when I try to name it the same thing as the working one I get the same error.

bvinson

0
 
LVL 23

Expert Comment

by:adilkhan
ID: 10642969
Problem Could be your Recordset Object is Assigned as a Session Object or Defined Twice or some Other SQL statement was Assigned to it already.

Post your RS object and some of your Page source.
0
 
LVL 23

Expert Comment

by:adilkhan
ID: 10642971
Maybe the name you using for your Recordset object is a reserved word.
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

 
LVL 8

Expert Comment

by:fozylet
ID: 10642973
bvinson,
can't imagine why it would not work!
may i suggest checking the spellings (may be missed smthing... like TBIITEM instead of TBLITEM)
0
 
LVL 6

Author Comment

by:bvinson
ID: 10642985
adikhan:
I did a search in the file, and the below code has all the references to this recordset.  It is not previously defined, specified, addressed or referenced prior to what I have pasted below.

The name of the recordset is rsProdInfo, and it isn't reserved, I've also tried just a generic objRS and simply RS.  None of these work either.

fozylet:
I copied the SQL statement out of Access after about the 20th time of getting this error to make sure the spelling was correct.  The table name in Access is, indeed tblItem, and "SELECT * FROM tblItem" is about as easy as it gets, but still it crashes out.  (Don't worry, I don't let SELECT * stay in any of my code.)

I really appreciate your help - both of you.

=======Copy and Paste from UltraEdit (yes, I hand-code everything)==========

<%
       strSQL = "SELECT * from tblItem"
      response.write strSQL
             set rsProdInfo = Server.CreateObject("ADODB.Recordset")
             rsProdInfo.Open strSQL, objConn, adOpenStatic, adLockOptimistic, adCmdTable
 
             intTotal = 0
 
             rsProd.MoveFirst
             while not rsProd.EOF
                 rsProdInfo.MoveFirst
                 rsProdInfo.Find "productID = " & rsProd("productID")
                 intProdID = rsProd("productID")
                 strProdName = rsProdInfo("productName")
                 intPrice = formatNumber(rsProdInfo("productPrice"), 2)
                 intQuant = rsProd("quantity")
                 intExtPrice = formatNumber((intPrice * intQuant), 2)
                 intTotal = intTotal + intExtPrice
 %>
                 <tr>
                     <td width="15%" align="left" valign="middle" nowrap><font face="Verdana" size="2"><%= intQuant %></font></td>
                     <td width="15%" align="left" valign="middle" nowrap><font face="Verdana" size="2"><%= intProdID %></font></td>
                     <td width="40%" align="left" valign="middle" nowrap><font face="Verdana" size="2"><%= strProdName %></font></td>
                     <td width="15%" align="right" valign="middle" nowrap><font face="Verdana" size="2">$<%= intPrice %></font></td>
                     <td width="15%" align="right" valign="middle" nowrap><font face="Verdana" size="2">$<%= intExtPrice %></font></td>
                 </tr>
                 <input type="hidden" name="strOrderItem" value="<%= intProdID %>,<%= intQuant %>">
<%
                rsProd.MoveNext
            wend                        
%>
<%
                  'display total for products in cart
%>                        
            <tr>
                <td colspan="4" align="left" valign="middle" nowrap><font face="Verdana" size="2">Subtotal:</font></td>
                <td align="right" valign="middle" nowrap><font face="Verdana" size="2">
                    $<%= formatNumber(intTotal, 2) %>
                </font></td>
            </tr>
<%
                  'add and display amount charged for shipping:
                  'in this case it's 5% of total for products in cart
                  'change the .05 to meet your requirements
%>                        
            <tr>
                <td colspan="4" align="left" valign="middle" nowrap><font face="Verdana" size="2">Shipping (5%):</font></td>
                <td align="right" valign="middle" nowrap><font face="Verdana" size="2">
                    $<%= formatNumber((intTotal * .05), 2) %>
                    <input type="hidden" name="intShipping" value="<%= formatNumber((intTotal * .05), 2) %>">
                </font></td>
            </tr>
<%
                  'add and display amount charged for sales tax:
                  'in this case it's 8% of total for products in cart
                  'change the .08 to meet your state/province requirements
%>                          
            <tr>
                <td colspan="4" align="left" valign="middle" nowrap><font face="Verdana" size="2">Tax (8%):</font></td>
                <td align="right" valign="middle" nowrap><font face="Verdana" size="2">
                    $<%= formatNumber((intTotal * .08), 2) %>
                    <input type="hidden" name="intTax" value="<%= formatNumber((intTotal * .08), 2) %>">
                </font></td>
            </tr>
<%
                  'display Grand Total: to get the multiplier 1.0 is the product(s) total plus .08
                  'for sales tax and .05 for shipping which equals 1.13. Be sure to change this
                  'to meet your requirements
%>              
            <tr>
                <td colspan="4" align="left" valign="middle" nowrap><font face="Verdana" size="2"><b>Total:</b></font></td>
                <td align="right" valign="middle" nowrap><font face="Verdana" size="2"><b>
                    $<%= formatNumber((intTotal*1.13), 2) %>
                    <input type="hidden" name="intTotal" value="<%= formatNumber((intTotal * 1.13), 2) %>">
                </b></font></td>
            </tr>
            </table>        
            <p><font face="Verdana" size="4"><strong>Payment/Shipping Information</strong></font></p>
            <table width="100%">
            <tr>
                <td bgcolor="#ff9900" colspan="2"><font color="#FFFFFF" face="Verdana" size="2" style="background-color: #ff9900"><strong>Customer Information</strong></font></td>
                  </tr>
            <tr>
                <td><font face="Verdana" size="2">Name(First, Last): </font></td>
                <td>
                <input type="text" name="strFirst" size="10">
                <input type="text" name="strLast" size="10">
                </td>
            </tr>
            <tr>
                <td><font face="Verdana" size="2">E-mail Address: </font></td>
                <td>
                <input type="text" name="strEmail" size="21"><font face="Verdana" size="1">&nbsp;Example: user@metalinks.com</font>
                </td>
            </tr>
            <tr>
                <td><font face="Verdana" size="2">Street Address: </font></td>
                <td>
                <input type="text" name="strAddress1" size="21">
                </td>
            </tr>
            <tr>
                <td><font face="Verdana" size="2">P.O. Box/Rural Route: </font></td>
                <td>
                <input type="text" name="strAddress2" size="21"><font face="Verdana" size="1">&nbsp;Enter &quot;NA&quot; if not applicable</font>
                </td>
            </tr>
            <tr>
                <td><font face="Verdana" size="2">City, State, Postal Code: </font></td>
                <td>
                <input type="text" name="strCity" size="10">
                <input type="text" name="strState" size="5">
                <input type="text" name="strPostalCode" size="5">
                </td>
            </tr>
            <tr>
                <td><font face="Verdana" size="2">Country: </td>
                <td>
                <input type="text" name="strCountry" size="21">
                </td>
            </tr>
            <tr>
                <td><font face="Verdana" size="2">Telephone: </font></td>
                <td>
                <input type="text" name="strPhone" size="21"><font face="Verdana" size="1">&nbsp;Example: 512-555-5555</font>
                </td>
            </tr>
            <tr>
            <td colspan="2"><hr color="#CCCCCC" size="1" noshade></td>
            </tr>
            <tr>
                <td bgcolor="#ff9900" colspan="2"><font color="#FFFFFF" face="Verdana" size="2" style="background-color: #ff9900"><strong>Shipping Information (if different than Customer Information)</strong></font></td>
                  </tr>
                  <tr>
                <td><font face="Verdana" size="2">Name(First, Last): </font></td>
                <td>
                <input type="text" name="strShipFirst" size="10">
                <input type="text" name="strShipLast" size="10">
                </td>
            </tr>
            <tr>
                <td><font face="Verdana" size="2">Address: </font></td>
                <td>
                <input type="text" name="strShipAddress" size="21">
                </td>
            </tr>
            <tr>
                <td><font face="Verdana" size="2">City, State, Postal Code: </font></td>
                <td>
                <input type="text" name="strShipCity" size="10">
                <input type="text" name="strShipState" size="5">
                <input type="text" name="strShipPostalCode" size="5">
                </td>
            </tr>
            <tr>
                <td><font face="Verdana" size="2">Country: </td>
                <td>
                <input type="text" name="strShipCountry" size="21">
                </td>
            </tr>
                  <tr>
            <td colspan="2"><hr color="#CCCCCC" size="1" noshade></td>
            </tr>
                  <tr>
                <td bgcolor="#ff9900" colspan="2"><font color="#FFFFFF" face="Verdana" size="2" style="background-color: #ff9900"><strong>Shipping Method</strong></font></td>
                  </tr>
                  <tr>
                <td colspan="2"><font face="Verdana" size="2">
                <input type="radio" name="strShipMethod" value="UPS" CHECKED>
                United Parcel Service
                <input type="radio" name="strShipMethod" value="FedEx">
                Federal Express
                </font></td>
            </tr>
            <tr>
            <td colspan="2"><hr color="#CCCCCC" size="1" noshade></td>
            </tr>
           
            <tr>
                <td colspan="2" align="center">
                <input type="submit" value="Submit Order" id="submit1" name="submit1">
                </td>
            </tr>
            </table>            
            </form>
            </td>
  </tr>
  </table>
  </td>
  </tr>
  </table>
      <hr color="#CCCCCC" size="1" noshade>
     
<table border="0" cellpadding="0" cellspacing="6" width="100%">  
  <tr>
    <td colspan="3" valign="BOTTOM"><font face="Verdana, Arial, Helvetica" size="1">      
     
      Last Updated: <!--webbot bot="Timestamp" s-type="EDITED" s-format="%A, %B %d, %Y" startspan -->Saturday, July 07, 2001<!--webbot bot="Timestamp" endspan i-checksum="49640" -->
      <br>
      Powered by <a href="http://metalinks.com/metacart.htm">MetaCart</a>,
      ©2000-2001 <a href="http://metalinks.com/">MetaLinks.com</a><br>
      </font></td>
  <td colspan="3" align="right" valign="TOP">&nbsp;
  </td>
  </tr>
</table>
</body>
</html>
<%            
rsProd.Close
set rsProd = Nothing

rsProdInfo.Close
set rsProdInfo = Nothing
            end if
      end if
end if

Conn.Close
set objConn = Nothing
%>

=========End Copy and Paste==============

Thanks again,
bvinson
0
 
LVL 23

Expert Comment

by:adilkhan
ID: 10643012
> rsProdInfo.Open strSQL, objConn, adOpenStatic, adLockOptimistic, adCmdTable

you are using objConn as your Connection Object , but why you doing Conn.Close on the Bottom?

Conn is your Connection object or objConn.
0
 
LVL 6

Author Comment

by:bvinson
ID: 10643069
Hadn't bedebugged it that far down.

objConn is the correct object.  I had been tying to change things up testing for this error.
0
 
LVL 23

Expert Comment

by:adilkhan
ID: 10643075
Post the Code for rsProd as well.
0
 
LVL 6

Author Comment

by:bvinson
ID: 10643082
The ful file is on its way to your email account.

bvinson
0
 
LVL 6

Author Comment

by:bvinson
ID: 10643138
Found it!!

It was a wrong CursorType on the rsProdInfo query.

Changed that bad boy out and it slipped right through.

Again, I appreicate your help.

bvinson
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10644725
Actually and not to quible (as I think you know this, but for everone else's sake), but there is nothing wrong with the CursorType (adOpenStatic), but the Options parameter for the Recordset's Open method.  And more specifically the CommandTypeEnum used (adCmdTable).  

In other words this line:
rsProdInfo.Open strSQL, objConn, adOpenStatic, adLockOptimistic, adCmdTable

Should have been written:
rsProdInfo.Open strSQL, objConn, adOpenStatic, adLockOptimistic, adCmdText

amp,
Fair enough.
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 10678998
Closed, 250 points refunded.
ee_ai_construct
Community Support Moderator
(re-order part number xm34)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

705 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

18 Experts available now in Live!

Get 1:1 Help Now