Results 1 to 19 of 19

Thread: Microsoft OLE DB Provider for SQL Server error '80040e14'

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jun 2005
    Location
    Ohio, USA
    Posts
    140

    Microsoft OLE DB Provider for SQL Server error '80040e14'

    I have a page that is supposed to display the results of a search. I
    keep getting the following error message.

    Microsoft OLE DB Provider for SQL Server error '80040e14'

    Line 1: Incorrect syntax near '1'.

    /osDetail2.asp, line 35

    Line 35 is rsResults.Open()

    what could be causing me to get this message?? Thanks for any help you
    can give.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    There is an error in your SQL statement... if you can show it to us, we can probably spot the issue.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Jun 2005
    Location
    Ohio, USA
    Posts
    140

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    Dim rsResults
    Dim rsResults_cmd
    Dim rsResults_numRows

    Set rsResults_cmd = Server.CreateObject ("ADODB.Command")
    rsResults_cmd.ActiveConnection = MM_chwcConn_STRING
    rsResults_cmd.CommandText = "SELECT * FROM dbo.tblOpenShifts WHERE Location = 'MMColParam' ORDER BY Location ASC"
    rsResults_cmd.Prepared = true

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    Strange... there's nothing obviously wrong there (I just checked, and Location isn't a reserved word).

    I doubt it will help, but see if this makes any difference:
    Code:
    rsResults_cmd.CommandText = "SELECT * FROM dbo.tblOpenShifts t WHERE t.Location = 'MMColParam' ORDER BY t.Location ASC"
    ..if not, can you show us the other code, as far as the rs.Open

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Jun 2005
    Location
    Ohio, USA
    Posts
    140

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    I should have said it before but I'm using Dreamweaver to help me write the SQL. I played around with it and by using the following code, all the records are returned, I can't figure out how to filter them.
    Code:
    Dim rsResults
    Dim rsResults_cmd
    Dim rsResults_numRows
    
    Set rsResults_cmd = Server.CreateObject ("ADODB.Command")
    rsResults_cmd.ActiveConnection = MM_chwcConn_STRING
    rsResults_cmd.CommandText = "SELECT * FROM dbo.tblOpenShifts t ORDER BY t.Location ASC" 
    rsResults_cmd.Prepared = true
    rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param1", 200, 1, 255, rsResults__MMColParam) ' adVarChar
    
    Set rsResults = rsResults_cmd.Execute
    rsResults_numRows = 0

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    Ah yes, it seems I'm a bit too tired today... I completely forgot about parameters!

    It should work fine if you change the Where clause to this:
    ... WHERE t.Location = ? ORDER ...

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Jun 2005
    Location
    Ohio, USA
    Posts
    140

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    Ok, I had a chance to test it today and I can get it to filter the results by one criteria, but I need to filter the results by 3 different criteria. I only want the Location, Shift and Position that the user entered in the search forms. Any help/direction will be greatly appreciated.

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    Well first of all you need to extend the Where clause (using ? for values as before), eg:
    Code:
    ... WHERE t.Location = ? And t.Shift = ? And t.Position = ? ORDER ...
    Then you need to add the extra parameters as apt (using .Parameters.Append again).

    An important thing to note is that no matter what names you give the parameters, you should have them in the same order as they appear in the SQL statement (as they are set by position, not name).

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Jun 2005
    Location
    Ohio, USA
    Posts
    140

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    I'll give it a try tomorrow. I'm heading out for today.

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Jun 2005
    Location
    Ohio, USA
    Posts
    140

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    I'm really getting frustrated with this!! Here's my code that creates the recordset, maybe you can spot what's causing it to not work.

    Code:
    <%
    Dim rsResults__varLocation
    rsResults__varLocation = "1"
    If (Request.Form("Location")  <> "") Then 
      rsResults__varLocation = Request.Form("Location") 
    End If
    %>
    <%
    Dim rsResults__varPosition
    rsResults__varPosition = "%"
    If (Request.Form("Position") <> "") Then 
      rsResults__varPosition = Request.Form("Position")
    End If
    %>
    <%
    Dim rsResults__varShift
    rsResults__varShift = "1"
    If (Request.Form("Shift") <> "") Then 
      rsResults__varShift = Request.Form("Shift")
    End If
    %>
    <%
    Dim rsResults
    Dim rsResults_cmd
    Dim rsResults_numRows
    
    Set rsResults_cmd = Server.CreateObject ("ADODB.Command")
    rsResults_cmd.ActiveConnection = MM_chwcConn_STRING
    rsResults_cmd.CommandText = "SELECT Location, Position, ShiftTime, SpecialNote, DatePosted, DateExpired, ContactEmail, ContactPhone, PositionOpen FROM dbo.tblOpenShifts t WHERE t.Location = ? AND t.Position = ? AND t.ShiftTime = ? ORDER BY Location ASC" 
    rsResults_cmd.Prepared = true
    rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param1", 200, 1, 255, rsResults__varLocation) ' adVarChar
    rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param2", 200, 1, 255, rsResults__varPosition) ' adVarChar
    rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param3", 200, 1, 255, rsResults__varShift) ' adVarChar
    
    Set rsResults = rsResults_cmd.Execute
    rsResults_numRows = 0
    %>

  11. #11
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    There is nothing obvious... are you getting any error messages?

    Are all of your fields VarChar? (if not, you need to set an appropriate type when creating the parameters)

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Jun 2005
    Location
    Ohio, USA
    Posts
    140

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    Right now I'm not getting any error messages, just not getting any results either,doesn't matter what I try as search terms. I'll have to check to see which columns are VarChars, I think they all are except for DatePosted and DateExpired. If they aren't VarChars how do I set an appropriate type?

  13. #13
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    The help page for CreateParameter contains an explanation of the parameters, and the values for each data type etc.. the online version can be found here.

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Jun 2005
    Location
    Ohio, USA
    Posts
    140

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    I think I'm getting closer. This is how the code is now as created by Dreamweaver.

    Code:
    rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param1", 200, 1, 255, rsResults__varLocation) ' adVarChar
    Should the adVarChar not be commented out? After reading on the page you gave, I think the code should be:

    Code:
    rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param1", adVarChar, 200, 1, 255, rsResults__varLocation)
    Am I correct in thinking that?

  15. #15
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    That's not right I'm afraid.. as shown at the top of the page I linked to, you can only specify up to 5 values, which are: Name, Type, Direction, Size, Value.

    As you are using ASP, the constants (like adVarChar) are not defined - so you need to use the numeric equivalent. The 200 actually means adVarChar, which can be found by clicking on the DataTypeEnum link in the Type section of the page - along with the values for all the other data types.

    I guess the reason that adVarChar was left as a comment is so that you can tell what it is, without having to look it up on that page.

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Jun 2005
    Location
    Ohio, USA
    Posts
    140

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    I still can't figure out how to get it to give my the results of my search. If I posted more of my code, would that help any? I'm not getting any error mesages, other than the message I should get if the recordset is empty.

  17. #17
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    It would help to see the code from CommandText onwards, as well as the values of the variables (rsResults__varShift etc), and the row(s) from the database that you think should be returned.

  18. #18

    Thread Starter
    Addicted Member
    Join Date
    Jun 2005
    Location
    Ohio, USA
    Posts
    140

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    Here's ALL the Code for the page.
    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    <%Option Explicit%>
    <!--#include file="Connections/chwcConn.asp" -->
    <%
    Dim rsResults__varLocation
    rsResults__varLocation = "1"
    If (Request.Form("Location")  <> "") Then 
      rsResults__varLocation = Request.Form("Location") 
    End If
    %>
    <%
    Dim rsResults__varPosition
    rsResults__varPosition = "%"
    If (Request.Form("Position") <> "") Then 
      rsResults__varPosition = Request.Form("Position")
    End If
    %>
    <%
    Dim rsResults__varShift
    rsResults__varShift = "1"
    If (Request.Form("Shift") <> "") Then 
      rsResults__varShift = Request.Form("Shift")
    End If
    %>
    <%
    Dim rsResults
    Dim rsResults_cmd
    Dim rsResults_numRows
    
    Set rsResults_cmd = Server.CreateObject ("ADODB.Command")
    rsResults_cmd.ActiveConnection = MM_chwcConn_STRING
    rsResults_cmd.CommandText = "SELECT Location, Position, ShiftTime, SpecialNote, DatePosted, DateExpired, ContactEmail, ContactPhone, PositionOpen FROM dbo.tblOpenShifts t WHERE t.Location = ? AND t.Position = ? AND t.ShiftTime = ? ORDER BY Location ASC" 
    rsResults_cmd.Prepared = true
    rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param1", 200, 1, 255, rsResults__varLocation) ' adVarChar
    rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param2", 200, 1, 255, rsResults__varPosition) ' adVarChar
    rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter("param3", 200, 1, 255, rsResults__varShift) ' adVarChar
    
    Set rsResults = rsResults_cmd.Execute
    rsResults_numRows = 0
    %>
    
    <%
    Dim Repeat1__numRows
    Dim Repeat1__index
    
    Repeat1__numRows = -1
    Repeat1__index = 0
    rsResults_numRows = rsResults_numRows + Repeat1__numRows
    %>
    <html><!-- InstanceBegin template="/Templates/basePage.dwt.asp" codeOutsideHTMLIsLocked="false" -->
    <head>
    <meta NAME="GENERATOR" Content="Microsoft FrontPage 4.0">
    <!-- InstanceBeginEditable name="doctitle" -->
    <title>Community Hospitals and Wellness Centers</title>
    <!-- InstanceEndEditable -->
    <link REL="stylesheet" TYPE="text/css" HREF="stylesheet.css">
    <!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
    </head>
    <body bgcolor="#ffffff" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0" link="#009966" vlink="#cc6633" alink="#663300" text="#000000" bottomMargin="0" rightMargin="0">
    <!--#include file="topnav.htm"-->
    
    <map NAME="home">
    <area SHAPE="RECT" COORDS="15,10,120,70" HREF="<%=Session("URL")%>default.asp" Alt="Click here to go to the CHWC home page.">
    </map>
    
    <table width="780" border="0" cellspacing="0" cellpadding="0" vspace="0" hspace="0">
      <tr> 
        <td height="80" colspan="7"><img height="80" src="images/Header.jpg" width="780" UseMap="FountainPark.asp#home" border="0"></td>
      </tr>
      <tr>
        <td width="15" bgcolor="#999966" height="15"><img height="10" src="images/10PixelSpace.gif" width="10"></td>
        <td width="160" height="25"><img height="5" src="images/160PixelSpace.gif" width="160"></td>
        <td width="13" height="15"><img height="5" src="images/13PixelSpace.gif" width="13"></td>
        <td width="402" height="15"><img height="10" src="images/402PixelSpace.gif" width="402"></td>
        <td width="10" height="15"><img height="10" src="images/10PixelSpace.gif" width="10"></td>
        <td width="170" height="15"><img height="10" src="images/170PixelSpace.gif" width="170"></td>
        <td width="10" height="15"><img height="10" src="images/10PixelSpace.gif" width="10"></td>
      </tr>
      <tr>
        <td width="15" bgcolor="#999966">&nbsp;</td>
        <td width="160" valign="top"><!--#include file="leftnav.htm"--></td>
        <td width="13">&nbsp;</td>
        <td width="582" colSpan="3" valign="top" align="left">
    		<br>
    		<!-- InstanceBeginEditable name="PageTitleImage" --><img SRC="images/OpenShifts.gif" WIDTH="391" HEIGHT="21"><!-- InstanceEndEditable -->		<br>
    	  <!-- InstanceBeginEditable name="PageText" --><a href="os1.asp" class="text">Back
    to Open Shift Search</a>
    	  <% 
    While ((Repeat1__numRows <> 0) AND (NOT rsResults.EOF)) 
    %>
                <input name="shift" type="hidden" id="shift" value="<%= Request.Form("Shift") %>">
                <input name="Position" type="hidden" id="Position" value="<%= Request.Form("Position") %>">
                <br>
            <table width="545" border="1" cellspacing="2" cellpadding="2">
              <tr class="pageTextBold">
                <td width="138">Location</td>
                <td width="75">Shift</td>
                <td width="198">Unit/Position</td>
                <td width="98">Special Notes </td>
              </tr>
              <tr class="text">
                <td class="text"><%=(rsResults.Fields.Item("Location").Value)%></td>
                <td><%=(rsResults.Fields.Item("ShiftTime").Value)%></td>
                <td><%=(rsResults.Fields.Item("Position").Value)%></td>
                <td><%=(rsResults.Fields.Item("SpecialNote").Value)%></td>
              </tr>
            </table>
            <p>If you can work this postion, call <%=(rsResults.Fields.Item("ContactPhone").Value)%> or send an <a href="mailto:<%=(rsResults.Fields.Item("ContactEmail").Value)%>">e-mail</a> </p>
              <% 
      Repeat1__index=Repeat1__index+1
      Repeat1__numRows=Repeat1__numRows-1
      rsResults.MoveNext()
    Wend
    %>
              <% If rsResults.EOF And rsResults.BOF Then %>
                <p>Sorry your search did not find any results. </p>
                <% End If ' end rsResults.EOF And rsResults.BOF %>
    		    <p class="text"><a href="os1.asp">Back
    		      to Open Shift Search </a></p>
          <!-- InstanceEndEditable -->	  <br>
          <br>
        <!--#include file="footer.htm"--> </td>
      </tr>
    </table>
    
    </body>
    <!-- InstanceEnd --></html>
    <%
    rsResults.Close()
    Set rsResults = Nothing
    %>
    <%
    rsResults.Close()
    Set rsResults = Nothing
    %>

  19. #19
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Microsoft OLE DB Provider for SQL Server error '80040e14'

    As your message is only shown when both EOF And BOF are true, we can be sure that there are no records being returned.

    Other than suggest you check the data type of the fields again, all I can do is ask for what I did in my previous post - the actual values of rsResults__varShift etc, and the row(s) from the database that you think should be returned by the query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width