Solved

error '80040e14'

Posted on 2004-03-20
19
634 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:Saqib Khan
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 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