Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 655
  • Last Modified:

error '80040e14'

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
bvinson
Asked:
bvinson
  • 7
  • 6
  • 3
  • +2
1 Solution
 
fozyletCommented:
tried [tblItem] using [] ?!
i can't see any logic to that suggestion... still...! ;P
0
 
bvinsonAuthor Commented:
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
 
Saqib KhanSenior DeveloperCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
bvinsonAuthor Commented:
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
 
fozyletCommented:
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
 
Saqib KhanSenior DeveloperCommented:
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
 
bvinsonAuthor Commented:
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
 
Saqib KhanSenior DeveloperCommented:
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
 
Saqib KhanSenior DeveloperCommented:
Maybe the name you using for your Recordset object is a reserved word.
0
 
fozyletCommented:
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
 
bvinsonAuthor Commented:
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
 
Saqib KhanSenior DeveloperCommented:
> 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
 
bvinsonAuthor Commented:
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
 
Saqib KhanSenior DeveloperCommented:
Post the Code for rsProd as well.
0
 
bvinsonAuthor Commented:
The ful file is on its way to your email account.

bvinson
0
 
bvinsonAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
ee_ai_constructCommented:
Closed, 250 points refunded.
ee_ai_construct
Community Support Moderator
(re-order part number xm34)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now